주차별 통계 구하기 질문합니다 0 4 823

by 산기슭 [SQL Query] [2022.09.21 18:40:30]


안녕하세요 질문이 있어 이렇게 올립니다.

select 회원번호,count(수신일시) as cnt   from 회원 수신테이블  
where to_char(수신일시 ,'YYMMDD') > '220102'

회원별로 올해 1월부터 현재일까지 몇주차에 몇개의 메세지를 받았는지 아는 쿼리를 찾고 있습니다. 수신일시 기준으로 카운트를 하여 수신 횟수 를 구하고 있는데 

제가 생각하는 구조는

---------------------------------------------------------------

회원번호  1주차  2주차  3주차 4주차....... 연속됨

----------------------------------------------------------------

1231            2       2       3      4

23412          4        0        2    1

31241

41242235

5324234

65645

 

테이블 자체는 수신을 받을 떄 마다  새로운 로우가 생성되는 로그성 테이블입니다.  

 

어떤 쿼리를 써야 저런 결과를 뽑아 낼 수 있을까요? 답변 부탁드립니다.

감사합니다.

 

by 마농 [2022.09.22 08:35:29]
-- Oracle --
WITH t AS
(
SELECT 111 id, DATE '2022-01-03' dt FROM dual
UNION ALL SELECT 222, DATE '2022-01-04' FROM dual
UNION ALL SELECT 111, DATE '2022-01-09' FROM dual
UNION ALL SELECT 222, DATE '2022-01-10' FROM dual
UNION ALL SELECT 111, DATE '2022-01-10' FROM dual
UNION ALL SELECT 333, DATE '2022-01-17' FROM dual
)
SELECT *
  FROM (SELECT id
             , TO_CHAR(dt, 'iw') + 0 iw
          FROM t
         WHERE dt >= TRUNC(sysdate, 'iy')
        )
 PIVOT (COUNT(*) FOR iw IN (  1,  2,  3,  4,  5,  6,  7,  8,  9, 10
                           , 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
                           , 21, 22, 23, 24, 25, 26, 27, 28, 29, 30
                           , 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
                           , 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
                           , 51, 52, 53
                           ) )
 ORDER BY id
;

 


by 산기슭 [2022.09.22 10:35:34]

 extension 설치를 하면 안돼서 pivot이 안먹는데 다른 방법이 있나요? postgre 입니다


by 마농 [2022.09.22 11:21:16]
-- PostgreSQL --
WITH t AS
(
SELECT 111 id, DATE '2022-01-03' dt
UNION ALL SELECT 222, DATE '2022-01-04'
UNION ALL SELECT 111, DATE '2022-01-09'
UNION ALL SELECT 222, DATE '2022-01-10'
UNION ALL SELECT 111, DATE '2022-01-10'
UNION ALL SELECT 333, DATE '2022-01-17'
)
SELECT id
     , COUNT(CASE iw WHEN '01' THEN 1 END) w01
     , COUNT(CASE iw WHEN '02' THEN 1 END) w02
     , COUNT(CASE iw WHEN '03' THEN 1 END) w03
     , COUNT(CASE iw WHEN '04' THEN 1 END) w04
     , COUNT(CASE iw WHEN '05' THEN 1 END) w05
     , COUNT(CASE iw WHEN '06' THEN 1 END) w06
     , COUNT(CASE iw WHEN '07' THEN 1 END) w07
     , COUNT(CASE iw WHEN '08' THEN 1 END) w08
     , COUNT(CASE iw WHEN '09' THEN 1 END) w09
     , COUNT(CASE iw WHEN '10' THEN 1 END) w10
     , COUNT(CASE iw WHEN '11' THEN 1 END) w11
     , COUNT(CASE iw WHEN '12' THEN 1 END) w12
     , COUNT(CASE iw WHEN '13' THEN 1 END) w13
     , COUNT(CASE iw WHEN '14' THEN 1 END) w14
     , COUNT(CASE iw WHEN '15' THEN 1 END) w15
     , COUNT(CASE iw WHEN '16' THEN 1 END) w16
     , COUNT(CASE iw WHEN '17' THEN 1 END) w17
     , COUNT(CASE iw WHEN '18' THEN 1 END) w18
     , COUNT(CASE iw WHEN '19' THEN 1 END) w19
     , COUNT(CASE iw WHEN '20' THEN 1 END) w20
     , COUNT(CASE iw WHEN '21' THEN 1 END) w21
     , COUNT(CASE iw WHEN '22' THEN 1 END) w22
     , COUNT(CASE iw WHEN '23' THEN 1 END) w23
     , COUNT(CASE iw WHEN '24' THEN 1 END) w24
     , COUNT(CASE iw WHEN '25' THEN 1 END) w25
     , COUNT(CASE iw WHEN '26' THEN 1 END) w26
     , COUNT(CASE iw WHEN '27' THEN 1 END) w27
     , COUNT(CASE iw WHEN '28' THEN 1 END) w28
     , COUNT(CASE iw WHEN '29' THEN 1 END) w29
     , COUNT(CASE iw WHEN '30' THEN 1 END) w30
     , COUNT(CASE iw WHEN '31' THEN 1 END) w31
     , COUNT(CASE iw WHEN '32' THEN 1 END) w32
     , COUNT(CASE iw WHEN '33' THEN 1 END) w33
     , COUNT(CASE iw WHEN '34' THEN 1 END) w34
     , COUNT(CASE iw WHEN '35' THEN 1 END) w35
     , COUNT(CASE iw WHEN '36' THEN 1 END) w36
     , COUNT(CASE iw WHEN '37' THEN 1 END) w37
     , COUNT(CASE iw WHEN '38' THEN 1 END) w38
     , COUNT(CASE iw WHEN '39' THEN 1 END) w39
     , COUNT(CASE iw WHEN '40' THEN 1 END) w40
     , COUNT(CASE iw WHEN '41' THEN 1 END) w41
     , COUNT(CASE iw WHEN '42' THEN 1 END) w42
     , COUNT(CASE iw WHEN '43' THEN 1 END) w43
     , COUNT(CASE iw WHEN '44' THEN 1 END) w44
     , COUNT(CASE iw WHEN '45' THEN 1 END) w45
     , COUNT(CASE iw WHEN '46' THEN 1 END) w46
     , COUNT(CASE iw WHEN '47' THEN 1 END) w47
     , COUNT(CASE iw WHEN '48' THEN 1 END) w48
     , COUNT(CASE iw WHEN '49' THEN 1 END) w49
     , COUNT(CASE iw WHEN '50' THEN 1 END) w50
     , COUNT(CASE iw WHEN '51' THEN 1 END) w51
     , COUNT(CASE iw WHEN '52' THEN 1 END) w52
     , COUNT(CASE iw WHEN '53' THEN 1 END) w53
  FROM (SELECT id
             , TO_CHAR(dt, 'iw') iw
          FROM t
         WHERE dt >= DATE_TRUNC('week', DATE_TRUNC('year', NOW()) + INTERVAL '3' DAY)
        ) a
 GROUP BY id
 ORDER BY id
;

 


by 산기슭 [2022.09.22 13:41:23]

너무나도 감사합니다.!!!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입