데이터베이스 질문과 답변
안녕하세요... 쿼리 초보라 생각이 잘 안되서 도움을 청합니다...
| 날짜 | 구매이력 |
| 20230601 | AA |
| 20230601 | AB |
| 20230601 | AC |
| 20230602 | AC |
| 20230602 | TD |
| 20230602 | AB |
| 20230603 | TD |
| 20230603 | PP |
| 20230604 | ZZ |
결과물
| 날짜 | 구매이력 |
| 20230601 | AA |
| 20230602 | AC |
| 20230603 | TD |
날짜별로 구매이력을 아래 순위로 출력하는 형태입니다.
구매이력
1순위 : AA
2순위 : AA가 없으면 AC
3순위 : AA도 없고 AC도 없으면 TD
4순위 : 1.2.3모두가 없으면 그날짜는 제외시킵니다.
RANK도 좀 애매한것 같고
고수님들의 도움을 청합니다.
by 닭뇌
[2023.06.16 14:57:05]
순위(1.AA, 2.AC, 3.TD...)에 대한 기준은 따로 있는건가요?
by 초보개발
[2023.06.16 15:05:30]
아니요... 저 순서대로 데이터를 축출해달라는 요청입니다.
by 닭뇌
[2023.06.16 15:22:51]
WITH LST AS (
SELECT '20230601' DY, 'AA' CD FROM DUAL UNION ALL
SELECT '20230601' DY, 'AB' CD FROM DUAL UNION ALL
SELECT '20230601' DY, 'AC' CD FROM DUAL UNION ALL
SELECT '20230602' DY, 'AC' CD FROM DUAL UNION ALL
SELECT '20230602' DY, 'TD' CD FROM DUAL UNION ALL
SELECT '20230602' DY, 'AB' CD FROM DUAL UNION ALL
SELECT '20230603' DY, 'TD' CD FROM DUAL UNION ALL
SELECT '20230603' DY, 'PP' CD FROM DUAL UNION ALL
SELECT '20230604' DY, 'ZZ' CD FROM DUAL
),
MST AS (
SELECT 'AA' CD, 1 RK FROM DUAL UNION ALL
SELECT 'AC' CD, 2 RK FROM DUAL UNION ALL
SELECT 'TD' CD, 3 RK FROM DUAL
),
RANKED_LST AS (
SELECT LST.DY, LST.CD, MST.RK,
ROW_NUMBER() OVER (PARTITION BY LST.DY ORDER BY MST.RK) AS RN
FROM LST
LEFT JOIN MST ON LST.CD = MST.CD
)
SELECT DY, CD, RK
FROM RANKED_LST
WHERE RN = 1 AND RK IS NOT NULL
ORDER BY DY;
by 초보개발
[2023.06.16 15:34:21]
역시 고수님...
너무 너무 고맙습니다.
by 뉴비디비
[2023.06.16 18:47:14]
WITH tbl AS (
SELECT '20230601' DY, 'AA' CD FROM DUAL UNION ALL
SELECT '20230601' DY, 'AB' CD FROM DUAL UNION ALL
SELECT '20230601' DY, 'AC' CD FROM DUAL UNION ALL
SELECT '20230602' DY, 'AC' CD FROM DUAL UNION ALL
SELECT '20230602' DY, 'TD' CD FROM DUAL UNION ALL
SELECT '20230602' DY, 'AB' CD FROM DUAL UNION ALL
SELECT '20230603' DY, 'TD' CD FROM DUAL UNION ALL
SELECT '20230603' DY, 'PP' CD FROM DUAL UNION ALL
SELECT '20230604' DY, 'ZZ' CD FROM DUAL
)
SELECT
DY, CD
FROM (
SELECT
tbl.DY, tbl.CD
,ROW_NUMBER() OVER(PARTITION BY DY ORDER BY CASE WHEN CD='AA' THEN 1 WHEN CD='AC' THEN 2 WHEN CD='TD' THEN 3 END ) rn
FROM tbl
WHERE CD IN ('AA','AC','TD')
) AA
WHERE RN = 1
) 버튼을 클릭하여 작성 하시면 됩니다.