데이터베이스 질문과 답변
with TEST as (
select 'A' as 'GBN', 10 as MINVAL1, '2020-01-01 10:00' as MINTIME1, 11 as MINVAL2, '2020-01-02 10:00' as MINTIME2
, 20 as MAXVAL1, '2020-01-01 15:00' as MAXTIME1, 51 as MAXVAL2, '2020-01-02 11:00' as MAXTIME2 union all
select 'A' as 'GBN', 15 as MINVAL1, '2020-01-01 11:00' as MINTIME1, 17 as MINVAL2, '2020-01-03 10:00' as MINTIME2
, 30 as MAXVAL1, '2020-01-01 16:00' as MAXTIME1, 51 as MAXVAL2, '2020-01-03 11:00' as MAXTIME2 union all
select 'B' as 'GBN', 22 as MINVAL1, '2020-02-02 10:00' as MINTIME1, 55 as MINVAL2, '2020-03-01 10:00' as MINTIME2
, 30 as MAXVAL1, '2020-03-01 15:00' as MAXTIME1, 31 as MAXVAL2, '2020-01-02 15:00' as MAXTIME2 union all
select 'B' as 'GBN', 33 as MINVAL1, '2020-03-03 10:00' as MINTIME1, 57 as MINVAL2, '2020-04-04 17:00' as MINTIME2
, 40 as MAXVAL1, '2020-07-01 15:00' as MAXTIME1, 11 as MAXVAL2, '2020-01-02 18:00' as MAXTIME2 )
select a.gbn, a.minval1, a.mintime1, b.minval2, b.mintime2, c.maxval1, c.maxtime1, d.maxval2, d.maxtime2
from (
(select * from (select gbn, row_number() over(partition by gbn order by minval1) n, minval1, mintime1 from TEST) z where n=1) a,
(select * from (select gbn, row_number() over(partition by gbn order by minval2) n, minval2, mintime2 from TEST) z where n=1) b,
(select * from (select gbn, row_number() over(partition by gbn order by maxval1 desc) n, maxval1, maxtime1 from TEST) z where n=1) c,
(select * from (select gbn, row_number() over(partition by gbn order by maxval2 desc) n, maxval2, maxtime2 from TEST) z where n=1) d
) where a.gbn = b.gbn and a.gbn = c.gbn and a.gbn = d.gbn ;
;
쿼리도 첨부합니다.
이렇게 하니 갑자기 되는데 방법이 맞는지 궁금합니다. 데이터가 나오지않는게 발생한다고 생각하여 outer join과 where조건을 처리하는방법을 고민했었는데 잘못생각한거같습니다. 데이터는 무조건있는상황입니다.
by 마농
[2020.06.25 13:23:32]
결과만 보여주지 마시고 쿼리도 보여주세요.
by alues
[2020.06.25 14:00:21]
모바일이라 답댓글 다는방법을 몰라서 댓글로 답변드립니다. 쿼리도 추가했습니다.
by 마농
[2020.06.25 14:36:43]
WITH test AS
(
SELECT 'A' gbn, 10 minval1, '2020-01-01 10:00' mintime1, 11 minval2, '2020-01-02 10:00' mintime2
, 20 maxval1, '2020-01-01 15:00' maxtime1, 51 maxval2, '2020-01-02 11:00' maxtime2
UNION ALL SELECT 'A', 15, '2020-01-01 11:00', 17, '2020-01-03 10:00', 30, '2020-01-01 16:00', 51, '2020-01-03 11:00'
UNION ALL SELECT 'B', 22, '2020-02-02 10:00', 55, '2020-03-01 10:00', 30, '2020-03-01 15:00', 31, '2020-01-02 15:00'
UNION ALL SELECT 'B', 33, '2020-03-03 10:00', 57, '2020-04-04 17:00', 40, '2020-07-01 15:00', 11, '2020-01-02 18:00'
)
SELECT gbn
, MIN(minval1) minval1, MIN(CASE a1 WHEN 1 THEN mintime1 END) mintime1
, MIN(minval2) minval2, MIN(CASE a2 WHEN 1 THEN mintime2 END) mintime2
, MAX(maxval1) maxval1, MAX(CASE d1 WHEN 1 THEN maxtime1 END) maxtime1
, MAX(maxval2) maxval2, MAX(CASE d2 WHEN 1 THEN maxtime2 END) maxtime2
FROM (SELECT gbn
, minval1, mintime1, minval2, mintime2
, maxval1, maxtime1, maxval2, maxtime2
, ROW_NUMBER() OVER(PARTITION BY gbn ORDER BY minval1 ASC ) a1
, ROW_NUMBER() OVER(PARTITION BY gbn ORDER BY minval2 ASC ) a2
, ROW_NUMBER() OVER(PARTITION BY gbn ORDER BY maxval1 DESC) d1
, ROW_NUMBER() OVER(PARTITION BY gbn ORDER BY maxval2 DESC) d2
FROM test
) a
GROUP BY gbn
;
by 마농
[2020.06.25 14:38:24]
혹시 test 집합이 원본이 아닌 중간 결과 집합 아닌가요?
중간 집합이라면? 해당 쿼리도 보고 싶네요.
중간 집합으로부터 최종 집합을 뽑는 방식보다
원본 집합으로부터 최종 집합을 뽑는 방식이 더 간결할 수도 있습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.