작성
·
32
0
WITH Sales AS (
SELECT 1 AS sale_id, DATE '2023-01-01' AS sale_date, 101 AS employee_id, 500 AS amount FROM dual UNION ALL
SELECT 2 AS sale_id, DATE '2023-01-02' AS sale_date, 102 AS employee_id, 700 AS amount FROM dual UNION ALL
SELECT 3 AS sale_id, DATE '2023-01-03' AS sale_date, 101 AS employee_id, 300 AS amount FROM dual UNION ALL
SELECT 4 AS sale_id, DATE '2023-01-04' AS sale_date, 103 AS employee_id, 900 AS amount FROM dual UNION ALL
SELECT 5 AS sale_id, DATE '2023-01-05' AS sale_date, 101 AS employee_id, 400 AS amount FROM dual UNION ALL
SELECT 6 AS sale_id, DATE '2023-01-06' AS sale_date, 104 AS employee_id, 600 AS amount FROM dual UNION ALL
SELECT 7 AS sale_id, DATE '2023-01-07' AS sale_date, 105 AS employee_id, 800 AS amount FROM dual UNION ALL
SELECT 8 AS sale_id, DATE '2023-01-08' AS sale_date, 101 AS employee_id, 350 AS amount FROM dual UNION ALL
SELECT 9 AS sale_id, DATE '2023-01-09' AS sale_date, 102 AS employee_id, 450 AS amount FROM dual UNION ALL
SELECT 10 AS sale_id, DATE '2023-01-10' AS sale_date, 103 AS employee_id, 750 AS amount FROM dual UNION ALL
SELECT 11 AS sale_id, DATE '2023-01-11' AS sale_date, 101 AS employee_id, 250 AS amount FROM dual UNION ALL
SELECT 12 AS sale_id, DATE '2023-01-12' AS sale_date, 104 AS employee_id, 650 AS amount FROM dual UNION ALL
SELECT 13 AS sale_id, DATE '2023-01-13' AS sale_date, 105 AS employee_id, 850 AS amount FROM dual UNION ALL
SELECT 14 AS sale_id, DATE '2023-01-14' AS sale_date, 101 AS employee_id, 450 AS amount FROM dual UNION ALL
SELECT 15 AS sale_id, DATE '2023-01-15' AS sale_date, 102 AS employee_id, 550 AS amount FROM dual UNION ALL
SELECT 16 AS sale_id, DATE '2023-01-16' AS sale_date, 103 AS employee_id, 950 AS amount FROM dual UNION ALL
SELECT 17 AS sale_id, DATE '2023-01-17' AS sale_date, 104 AS employee_id, 700 AS amount FROM dual UNION ALL
SELECT 18 AS sale_id, DATE '2023-01-18' AS sale_date, 105 AS employee_id, 900 AS amount FROM dual UNION ALL
SELECT 19 AS sale_id, DATE '2023-01-19' AS sale_date, 101 AS employee_id, 500 AS amount FROM dual UNION ALL
SELECT 20 AS sale_id, DATE '2023-01-20' AS sale_date, 102 AS employee_id, 600 AS amount FROM dual
)
SELECT SALE_ID,
TO_CHAR(SALE_DATE, 'YYYY-MM-DD') AS sale_date,
employee_id,
amount,
FIRST_VALUE(amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS "FIRST_VALUE"
FROM (
SELECT SALE_ID,
SALE_DATE,
employee_id,
amount,
ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY sale_date) AS rn
FROM Sales
) WHERE rn = 1;
안녕하세요 SQLD 준비하면서 강의 듣고 있는
전진호 학생입니다.
이렇게 SUBQUERY에서 ROW-NUMBER를 이용해서 하지 않고,
PARTITION BY 의 첫번째 값만 뽑아낼 수 있는
좀 더 쉬운 방법이 있을까요?
감사합니다.
답변