- FIRST_VALUE, LAST_VALUE

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 의 첫번째 값만 뽑아낼 수 있는

좀 더 쉬운 방법이 있을까요?

감사합니다.