데이터베이스 질문과 답변
안녕하세요 질문이 있어 이렇게 올립니다.
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]
너무나도 감사합니다.!!!
) 버튼을 클릭하여 작성 하시면 됩니다.