FIRST_VALUE и LAST_VALUE: получение первого/последнего значения в окне

Зачем нужны FIRST_VALUE и LAST_VALUE? 🔍

В аналитике данных часто требуется сравнить текущую строку с первой или последней в рамках группы. Например:

• Найти разницу между текущей зарплатой и зарплатой первого сотрудника в отделе
• Сравнить сегодняшние продажи с первым днём месяца
• Определить, насколько изменилась цена акции с начала года

Вот тут на сцену выходят FIRST_VALUE() и LAST_VALUE() — два мощных оконных функции SQL, которые делают именно это!

FIRST_VALUE(столбец) OVER (окно)
LAST_VALUE(столбец) OVER (окно)

Как работает FIRST_VALUE?

FIRST_VALUE() возвращает значение из первой строки оконного фрейма. Проще всего понять на примере:

SELECT 
    employee_id,
    department,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY hire_date) as first_salary
FROM employees;

Что здесь происходит:

1. Данные разбиваются по отделам (PARTITION BY department)
2. В каждом отделе сортируем по дате приёма (ORDER BY hire_date)
3. Для каждой строки берём зарплату из первой строки этого отдела

Результат:

LAST_VALUE: подводные камни 🚨

С LAST_VALUE() есть нюанс — по умолчанию она работает не так, как ожидают многие:

SELECT 
    employee_id,
    department,
    salary,
    LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY hire_date) as last_salary
FROM employees;

Вы удивитесь, но в результате last_salary часто равен текущей зарплате! Почему?

По умолчанию оконный фрейм для LAST_VALUE() — это все строки от начала партиции до текущей строки. Чтобы получить реально последнее значение, нужно явно указать фрейм:

LAST_VALUE(salary) OVER (
    PARTITION BY department 
    ORDER BY hire_date
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)

Теперь функция будет смотреть до конца партиции, и мы получим ожидаемый результат.

Практический пример: анализ продаж 📊

Допустим, у нас есть данные о ежедневных продажах:

WITH daily_sales AS (
    SELECT 
        date,
        product_id,
        amount,
        FIRST_VALUE(amount) OVER (PARTITION BY product_id ORDER BY date) as first_day_amount,
        LAST_VALUE(amount) OVER (
            PARTITION BY product_id 
            ORDER BY date
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) as last_day_amount
    FROM sales
    WHERE date BETWEEN '2023-01-01' AND '2023-01-31'
)
SELECT 
    date,
    product_id,
    amount,
    first_day_amount,
    last_day_amount,
    amount - first_day_amount as diff_from_first_day,
    (amount - first_day_amount) / first_day_amount * 100 as growth_percent
FROM daily_sales;

Этот запрос покажет:

• Продажи каждого товара в январе
• Продажи в первый день месяца
• Продажи в последний день месяца
• Абсолютный и процентный рост относительно первого дня

Продвинутые сценарии использования

Сравнение с первым и последним значением одновременно

SELECT 
    student_id,
    test_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY test_date) as first_score,
    LAST_VALUE(score) OVER (
        PARTITION BY student_id 
        ORDER BY test_date
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) as last_score,
    score - FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY test_date) as progress
FROM test_results;

Использование с другими оконными функциями

SELECT 
    date,
    department,
    revenue,
    FIRST_VALUE(revenue) OVER (PARTITION BY department ORDER BY date) as first_revenue,
    LAST_VALUE(revenue) OVER (
        PARTITION BY department 
        ORDER BY date
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) as last_revenue,
    AVG(revenue) OVER (PARTITION BY department) as avg_revenue
FROM department_stats;

Итоговый чек-лист по использованию ✅

1. Всегда указывайте ORDER BY в оконном определении — без него порядок строк не гарантирован
2. Для LAST_VALUE() явно задавайте фрейм ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
3. Используйте PARTITION BY для логического разделения на группы
4. Комбинируйте с другими оконными функциями для комплексного анализа
5. Проверяйте результат на небольших тестовых данных перед применением к большим таблицам

Теперь вы вооружены мощным инструментом для анализа данных! Попробуйте применить эти функции к своим задачам — они откроют новые возможности для анализа.