꿈꾸는 개발자, DBA 커뮤니티 구루비

엑셀의 필터처럼 특정값과 해당 값의 출현 횟수를 주간별로 뽑아내는 SQL

by 안떠니 [SQL Query] [2018.08.11 12:57:25]



WITH sales(s_cd, atime, group1, group2, price ) AS
(
          SELECT 1000, '07/01/2018', 100, 1,  10 FROM dual
UNION ALL SELECT 1000, '07/04/2018', 100, 2,   1 FROM dual
UNION ALL SELECT 1000, '07/07/2018', 102, 1,   2 FROM dual
UNION ALL SELECT 1000, '07/08/2018', 102, 2,  20 FROM dual
UNION ALL SELECT 1000, '07/11/2018', 103, 1,  12 FROM dual
UNION ALL SELECT 1000, '07/14/2018', 103, 2,   5 FROM dual
UNION ALL SELECT 1000, '07/15/2018', 104, 1,   3 FROM dual
UNION ALL SELECT 1000, '07/18/2018', 104, 2,   1 FROM dual
UNION ALL SELECT 1000, '07/21/2018', 105, 1,   6 FROM dual
UNION ALL SELECT 1000, '07/22/2018', 105, 2,   8 FROM dual
UNION ALL SELECT 1000, '07/25/2018', 106, 1,   4 FROM dual
UNION ALL SELECT 1000, '07/28/2018', 106, 2,   1 FROM dual
)

price 는 1 에서 200 사이의 어느값이든 될 수 있고,

동일한 price 가 각 주마다 몇번 판매되었고,

이것이 주간별로 나와야 합니다.

입력된 날짜는 판매 날짜이므로,

SQL 에서 07/072018 을 토요일로 자동(?)으로 분류해서 해당 주의 데이터만을 집계하는게
가능해야 하는데..이게 가능한가요?

즉, 목표 데이터 결과는

WEEK_END, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, .... , 200 // Column Title

07/07/2018, 1, 1, 0, 0, 0, 0, 0, 0, 0, 1 .........., 0
07/14/2018, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1,.....0
07/21/2018, 1, 0, 1, 0, 0, 1,...............................................................0
07/28/2018, 1, 0, 0, 1, 0, 0, 0, 1,.......................................................0

이게 불가능하면, 대안으로

Price, 07/07/2018, 07/14/2018, 07/21/2018, 07/28/2018............

1, 1, 0, 1, 1
2, 1, 0, 0, 0
3, 0, 0, 1, 0

그나마 첫번째 결과는 1 부터 200 이라는 제약이 있으니까, 작성하는게 가능해 보이기도 하고..

가격을 컬럼으로 주욱 나열하는 것부터 막히고,
다음은 날짜를 주별로 묶는건 상상도 못하고 있습니다.

여태 너무 쉬운 SQL 만 하다가 조금만 어려워지니까...손도 못대고 있네요 ㅠㅠ

by 야신 [2018.08.12 16:02:01]

WITH sales(s_cd, atime, group1, group2, price ) AS
(
          SELECT 1000, '07/01/2018', 100, 1,  10 FROM dual
UNION ALL SELECT 1000, '07/04/2018', 100, 2,   1 FROM dual
UNION ALL SELECT 1000, '07/07/2018', 102, 1,   2 FROM dual
UNION ALL SELECT 1000, '07/08/2018', 102, 2,  20 FROM dual
UNION ALL SELECT 1000, '07/11/2018', 103, 1,  12 FROM dual
UNION ALL SELECT 1000, '07/14/2018', 103, 2,   5 FROM dual
UNION ALL SELECT 1000, '07/15/2018', 104, 1,   3 FROM dual
UNION ALL SELECT 1000, '07/18/2018', 104, 2,   1 FROM dual
UNION ALL SELECT 1000, '07/21/2018', 105, 1,   6 FROM dual
UNION ALL SELECT 1000, '07/22/2018', 105, 2,   8 FROM dual
UNION ALL SELECT 1000, '07/25/2018', 106, 1,   4 FROM dual
UNION ALL SELECT 1000, '07/28/2018', 106, 2,   1 FROM dual
) 
SELECT conv_dt, COUNT(decode(price,1,1)) c1
,COUNT(decode(price,2,1)) c2
,COUNT(decode(price,3,1)) c3
,COUNT(decode(price,4,1)) c4
,COUNT(decode(price,5,1)) c5
,COUNT(decode(price,6,1)) c6
,COUNT(decode(price,7,1)) c7
,COUNT(decode(price,8,1)) c8
,COUNT(decode(price,9,1)) c9
,COUNT(decode(price,10,1)) c10
,COUNT(decode(price,11,1)) c11
,COUNT(decode(price,12,1)) c12
,COUNT(decode(price,13,1)) c13
,COUNT(decode(price,14,1)) c14
---- ....
FROM (
      SELECT t.*, next_day(dt-decode(to_char(dt,'D'),7,1,0),'토요일') AS conv_dt
      FROM (
          SELECT t.*, to_date(atime,'MM/DD/YYYY') AS dt 
          FROM sales t
          ) t
      )
GROUP BY conv_dt 
ORDER BY 1   ;

CONV_DT	C1	C2	C3	C4	C5	C6	C8	C10	C12
2018-07-07	1	1	0	0	0	0	0	1	0
2018-07-14	0	0	0	0	1	0	0	0	1
2018-07-21	1	0	1	0	0	1	0	0	0
2018-07-28	1	0	0	1	0	0	1	0	0

데이터베이스 질문과 답변