본문 바로가기

전체 글463

[sql] 누적 접속자 쿼리, 최초 구매 누적접속자 selectaa.date ,aa.server ,count(distinct accountid) --dau ,sum(count(x)) over (partition by server order by aa.date) -- 누적 접속자 from ( selectdate ,server ,accountid ,row_number() over(partition by server, accountid order by date asc) as x fromworkdb2.dbo.dau2 with (nolock) where date between '2020-07-01' and '2020-07-02' ) as aa group by aa.date ,aa.server 최초구매 selectaaa.productname, suM(aa.. 2022. 9. 26.

[SQL] 잔존율 retention 집계 하기 D+7, M+1 use workdb2 go WITH ##repeat_interval AS ( SELECT * FROM ( VALUES ('01 day repeart', 1) ,('02 day repeart', 2) ,('03 day repeart', 3) ,('04 day repeart', 4) ,('05 day repeart', 5) ,('06 day repeart', 6) ,('07 day repeart', 7) ) AS a (index_name, interval_date) ) ,##rau_0501_0531 AS ( selectcast(time as date) 'date', server, accountid fromexample2.dbo.all_rau where cast(time as date) between '202.. 2022. 9. 26.

[sql] DB 컬럼 정보 조회 방법, sp 내용 확인하기 use log go DECLARE @tableName varchar(100) = N'ZenyFlowLog'; -- 테이블명 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLUMN_DEFAULT,IS_NULLABLE,CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @tableName; exec sp_helpindex @tableName exec sp_findModule @tableName Declare @squery nvarchar(1000); SET @squery = 'SELECT * FROM dbo.'.. 2022. 9. 26.

[sql] 과금 구간별 10단위 ntile ,중앙값 구하기 use workdb go selecta.ntile, count(distinct accountid) 'pu', min(sales) 'min_sales', max(sales) 'max_sales', avg(sales) 'avg_sales', sum(sales)'total.sum' from ( selectserver, accountid, sum(price) 'sales', ntile(10) over ( order by sum(price) desc) as ntile fromdbo.purchase a with (nolock) joindbo.cm_purchase b with (nolock) on a.clientproductid=b.clientproductid where cast(time as date) betwee.. 2022. 9. 26.

이전 1 ··· 37 38 39 40 41 42 43 ··· 116 다음

티스토리툴바