mariadb 해당 데이터 뽑는 방법 0 4 186

by alues [MySQL] [2020.06.25 13:19:26]


20200625_131500~2.jpg (707,958Bytes)

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 집합이 원본이 아닌 중간 결과 집합 아닌가요?
중간 집합이라면? 해당 쿼리도 보고 싶네요.
중간 집합으로부터 최종 집합을 뽑는 방식보다
원본 집합으로부터 최종 집합을 뽑는 방식이 더 간결할 수도 있습니다.

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