본문 바로가기
반응형

분류 전체보기462

[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.
[sql] 카테고리별 매출 계산 (ABC 분석, 팬차트, 히스토그램) 카테고리별 매출 소계 집계 select substring(date::text, 6, 2) as months ,category , sum(amount) as total_amount from sale_data group by ROLLUP(months, category) order by months, category ABC 분석 with month_sales as ( select category , sum(amount) as amount from sale_data where date between '2019-01-01' and '2019-01-31' group by category ) , sales_ratio as ( select category ,amount ,round(100.0 * amount / sum.. 2022. 9. 26.
반응형

티스토리툴바