데이터베이스 질문과 답변

쿼리 구현이 가능한지 정리가 되지않아 질문드립니다 0 1 2,940

by 새싹 [SQL Query] [2023.10.31 16:44:37]

전제 조건 PRICE가 0일 경우 1,2,3번 의 순서로 보정한다.

1    ITEM, LINE, WEEK 기준 PRICE
2    ITEM, LINE 기준 MAX(PRICE)
3    ITEM 기준 (PRICE)
4. 1,2,3 번 모두 거쳐도 값이 0 이라면 0

JORDAN의 경우 1번 로직 적합 으로 PRICE = 20
SON의 경우 동일 week 없어 1번로직 불가, 2번로직 으로 ITEM,LINE 기준 MAX 값 = 20
PART 의 경우 1,2번 로직 불가로 3 순서 ITEM 기준으로 MAX 값 = 20

CASE문으로는 해결을 못하고있는것같아서, 어떤 좋은 방법이 있을까요??

 

샘플 

WITH TEST AS
 (
  SELECT 'JORDAN' ITEM, 'AR_JORDAN' LINE , '202340' WEEK, 20 PRICE FROM DUAL UNION ALL
  SELECT 'JORDAN' ITEM, 'AR_JORDAN' LINE , '202339' WEEK, 15 PRICE FROM DUAL UNION ALL
  SELECT 'JORDAN' ITEM, 'AR_JORDAN' LINE , '202339' WEEK, 10 PRICE FROM DUAL UNION ALL
  SELECT 'JORDAN' ITEM, 'AR_JORDAN' LINE , '202340' WEEK, 0 PRICE FROM DUAL UNION ALL
  SELECT 'SON' ITEM, 'AR_SON' LINE , '-' WEEK, 20 PRICE FROM DUAL UNION ALL
  SELECT 'SON' ITEM, 'AR_SON' LINE , '-' WEEK, 10 PRICE FROM DUAL UNION ALL
  SELECT 'SON' ITEM, 'AR_SON' LINE , '-' WEEK, 10 PRICE FROM DUAL UNION ALL
  SELECT 'SON' ITEM, 'AR_SON' LINE , '202339' WEEK, 0 PRICE FROM DUAL UNION ALL
  SELECT 'PARK' ITEM, 'AR_PARK' LINE , '202339' WEEK, 10 PRICE FROM DUAL UNION ALL
  SELECT 'PARK' ITEM, 'AR_PARK' LINE , '-' WEEK, 20 PRICE FROM DUAL UNION ALL
  SELECT 'PARK' ITEM, 'AR_PARK' LINE , '-' WEEK, 20 PRICE FROM DUAL UNION ALL
  SELECT 'PARK' ITEM, 'AC_PARK' LINE , '202339' WEEK, 0 PRICE FROM DUAL 
 )
 SELECT * FROM TEST
ITEM LINE WEEK PRICE
JORDAN AR_JORDAN 202340 20
JORDAN AR_JORDAN 202339 15
JORDAN AR_JORDAN 202339 10
JORDAN AR_JORDAN 202340 0
SON AR_SON - 20
SON AR_SON - 10
SON AR_SON - 10
SON AR_SON 202339 0
PARK AR_PARK 202339 10
PARK AR_PARK - 20
PARK AR_PARK - 20
PARK AC_PARK 202339 0

 

 

원하는 결과

 

WITH TEST AS
 (
  SELECT 'JORDAN' ITEM, 'AR_JORDAN' LINE , '202340' WEEK, 20 PRICE FROM DUAL UNION ALL
  SELECT 'JORDAN' ITEM, 'AR_JORDAN' LINE , '202339' WEEK, 15 PRICE FROM DUAL UNION ALL
  SELECT 'JORDAN' ITEM, 'AR_JORDAN' LINE , '202339' WEEK, 10 PRICE FROM DUAL UNION ALL
  SELECT 'JORDAN' ITEM, 'AR_JORDAN' LINE , '202340' WEEK, 20 PRICE FROM DUAL UNION ALL
  SELECT 'SON' ITEM, 'AR_SON' LINE , '-' WEEK, 20 PRICE FROM DUAL UNION ALL
  SELECT 'SON' ITEM, 'AR_SON' LINE , '-' WEEK, 10 PRICE FROM DUAL UNION ALL
  SELECT 'SON' ITEM, 'AR_SON' LINE , '-' WEEK, 10 PRICE FROM DUAL UNION ALL
  SELECT 'SON' ITEM, 'AR_SON' LINE , '202339' WEEK, 20 PRICE FROM DUAL UNION ALL
  SELECT 'PARK' ITEM, 'AR_PARK' LINE , '202339' WEEK, 10 PRICE FROM DUAL UNION ALL
  SELECT 'PARK' ITEM, 'AR_PARK' LINE , '-' WEEK, 20 PRICE FROM DUAL UNION ALL
  SELECT 'PARK' ITEM, 'AR_PARK' LINE , '-' WEEK, 20 PRICE FROM DUAL UNION ALL
  SELECT 'PARK' ITEM, 'AC_PARK' LINE , '202339' WEEK, 20 PRICE FROM DUAL 
 )
 SELECT * FROM TEST

 

ITEM LINE WEEK PRICE
JORDAN AR_JORDAN 202340 20
JORDAN AR_JORDAN 202339 15
JORDAN AR_JORDAN 202339 10
JORDAN AR_JORDAN 202340 20
SON AR_SON - 20
SON AR_SON - 10
SON AR_SON - 10
SON AR_SON 202339 20
PARK AR_PARK 202339 10
PARK AR_PARK - 20
PARK AR_PARK - 20
PARK AC_PARK 202339 20

 

PRICE 0 에 조건에 맞는 price 값을 넣고싶습니다.

 

by 마농 [2023.10.31 17:57:54] 
WITH test AS
(
SELECT 'JORDAN' item, 'AR_JORDAN' line, '202340' week, 20 price FROM dual
UNION ALL SELECT 'JORDAN', 'AR_JORDAN', '202339', 15 FROM dual
UNION ALL SELECT 'JORDAN', 'AR_JORDAN', '202339', 10 FROM dual
UNION ALL SELECT 'JORDAN', 'AR_JORDAN', '202340',  0 FROM dual -- Case 1
UNION ALL SELECT 'SON'   , 'AR_SON'   , '-'     , 20 FROM dual
UNION ALL SELECT 'SON'   , 'AR_SON'   , '-'     , 10 FROM dual
UNION ALL SELECT 'SON'   , 'AR_SON'   , '-'     , 10 FROM dual
UNION ALL SELECT 'SON'   , 'AR_SON'   , '202339',  0 FROM dual -- Case 2
UNION ALL SELECT 'PARK'  , 'AR_PARK'  , '202339', 10 FROM dual
UNION ALL SELECT 'PARK'  , 'AR_PARK'  , '-'     , 20 FROM dual
UNION ALL SELECT 'PARK'  , 'AR_PARK'  , '-'     , 20 FROM dual
UNION ALL SELECT 'PARK'  , 'AC_PARK'  , '202339',  0 FROM dual -- Case 3
)
SELECT item
     , line
     , week
     , price
     , COALESCE(NULLIF(price, 0)
          , MAX(NULLIF(price, 0)) OVER(PARTITION BY item, line, week)
          , MAX(NULLIF(price, 0)) OVER(PARTITION BY item, line)
          , MAX(NULLIF(price, 0)) OVER(PARTITION BY item)
          , 0) price_new
  FROM test
 ORDER BY ROWNUM
;

 

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