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

max관련 문의 드립니다.

by lgxj20 [Oracle 기초] [2024.01.16 11:34:00]

원하는건 가장 invo별로 qty가 가장큰 sty,col,size 를 가져오려고 합니다.

결과값

invo : 312390091204 일때는 sty : AA, col : 010, size : 999

invo : 312390091090 일때는 sty : DD, col : 010, size : 888

이렇게 나와야 합니다.

예시 sql

SELECT INVO
,SUM(QTY) AS QTY
,MAX(STY)

-- group by 문구 때문에 아래 는 사용 불가능. 그렇다고 실제 프로그램sql에서는 group by 를 사용해야되는 상황입니다.
       --,MAX(STY) OVER (PARTITION BY INVO ORDER BY qty desc)
       ,MAX(COL)
       ,MAX("SIZE")
FROM(
SELECT 'AA' AS STY
   ,'010' AS COL
   ,'555' AS "SIZE"
   ,10 AS QTY
   ,'312390091090' AS INVO
FROM DUAL
UNION ALL
SELECT 'AA' AS STY
   ,'010' AS COL
   ,'555' AS "SIZE"
   ,3 AS QTY
       ,'312390091090' AS INVO
FROM DUAL
UNION ALL
SELECT 'BB' AS STY
   ,'010' AS COL
   ,'666' AS "SIZE"
   ,5 AS QTY
       ,'312390091090' AS INVO
FROM DUAL
UNION ALL
SELECT 'CC' AS STY
   ,'010' AS COL
   ,'999' AS "SIZE"
   ,15 AS QTY
       ,'312390091090' AS INVO
FROM DUAL
UNION ALL
SELECT 'CC' AS STY
   ,'011' AS COL
   ,'888' AS "SIZE"
   ,15 AS QTY
       ,'312390091090' AS INVO
FROM DUAL
UNION ALL
SELECT 'DD' AS STY
   ,'010' AS COL
   ,'888' AS "SIZE"
   ,40 AS QTY
       ,'312390091090' AS INVO
FROM DUAL
--------------------
UNION ALL
SELECT 'AA' AS STY
   ,'010' AS COL
   ,'999' AS "SIZE"
   ,66 AS QTY
   ,'312390091204' AS INVO
FROM DUAL
UNION ALL
SELECT 'AA' AS STY
   ,'010' AS COL
   ,'999' AS "SIZE"
   ,3 AS QTY
       ,'312390091204' AS INVO
FROM DUAL
UNION ALL
SELECT 'BB' AS STY
   ,'010' AS COL
   ,'999' AS "SIZE"
   ,5 AS QTY
       ,'312390091204' AS INVO
FROM DUAL
UNION ALL
SELECT 'CC' AS STY
   ,'010' AS COL
   ,'999' AS "SIZE"
   ,15 AS QTY
       ,'312390091204' AS INVO
FROM DUAL
UNION ALL
SELECT 'CC' AS STY
   ,'011' AS COL
   ,'888' AS "SIZE"
   ,15 AS QTY
       ,'312390091204' AS INVO
FROM DUAL
UNION ALL
SELECT 'DD' AS STY
   ,'010' AS COL
   ,'888' AS "SIZE"
   ,40 AS QTY
       ,'312390091204' AS INVO
FROM DUAL
)
GROUP BY INVO;

by 마농 [2024.01.16 13:18:57]

WITH t AS
(
SELECT 'AA' sty, '010' col, '555' siz, 10 qty, '312390091090' invo FROM dual
UNION ALL SELECT 'AA', '010', '555',  3, '312390091090' FROM dual
UNION ALL SELECT 'BB', '010', '666',  5, '312390091090' FROM dual
UNION ALL SELECT 'CC', '010', '999', 15, '312390091090' FROM dual
UNION ALL SELECT 'CC', '011', '888', 15, '312390091090' FROM dual
UNION ALL SELECT 'DD', '010', '888', 40, '312390091090' FROM dual
UNION ALL SELECT 'AA', '010', '999', 66, '312390091204' FROM dual
UNION ALL SELECT 'AA', '010', '999',  3, '312390091204' FROM dual
UNION ALL SELECT 'BB', '010', '999',  5, '312390091204' FROM dual
UNION ALL SELECT 'CC', '010', '999', 15, '312390091204' FROM dual
UNION ALL SELECT 'CC', '011', '888', 15, '312390091204' FROM dual
UNION ALL SELECT 'DD', '010', '888', 40, '312390091204' FROM dual
)
-- 1. KEEP
SELECT invo
     , SUM(qty)
     , MAX(sty) KEEP(DENSE_RANK LAST ORDER BY qty) sty
     , MAX(col) KEEP(DENSE_RANK LAST ORDER BY qty) col
     , MAX(siz) KEEP(DENSE_RANK LAST ORDER BY qty) siz
  FROM t
 GROUP BY invo
;
-- 2. ROW_NUMBER = 1
SELECT invo, qty, sty, col, siz
  FROM (SELECT invo
             , SUM(qty) OVER(PARTITION BY invo) qty
             , sty, col, siz
             , ROW_NUMBER() OVER(PARTITION BY invo ORDER BY qty DESC) rn
          FROM t
        )
 WHERE rn = 1
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변