본문 바로가기
반응형

sql12

[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.
[sql] STRING_SPLIT 함수, 구분자로 자르기, 행열 변환 STRING_SPLIT 함수, 구분자로 자르기 select top 100 * from ( select purchase_id ,product_ids from purchase_log ) as x cross apply string_split (product_ids, ',') as y --- 쉼표로 묶어진 컬럼을 하나로 value 형식으로 따내는 법 --- 결과값 purchase_id product_ids value 100001 A001,A002,A003 A001 100001 A001,A002,A003 A002 100001 A001,A002,A003 A003 100002 D001,D002 D001 100002 D001,D002 D002 100003 A001 A001 행열 변환하는 방법 /* 행 열 변환 SELE.. 2022. 9. 26.
반응형

티스토리툴바