КАКИЕ БЫВАЮТ ФУНКЦИИ СМЕЩЕНИЯ

1. LAG() - значение из предыдущей строки
2. LEAD() - значение из следующей строки
3. FIRST_VALUE() - первое значение в окне
4. LAST_VALUE() - последнее значение в окне
5. NTH_VALUE() - N-ное значение в окне

Важные заметки про FIRST_VALUE()  и LAST_VALUE():

1. Функции FIRST_VALUE()  и LAST_VALUE() не надо путать с MIN() и MAX()

Если MIN() и MAX() забирают минимальное и максимальное значение, независимо от того, где они находятся, то FIRST_VALUE()  и LAST_VALUE() обращаются именно к месту строки внутри окна и на основании этого забирают значение. 

Например, если мы возьмем выражение:

FIRST_VALUE(quantity_sale) over (PARTITION BY storename ORDER BY date), то оно означает, что мы берем quantity_sale для самого первого дня продаж в рамках каждого магазина. Поэтому, чтобы забирать корректно n-е значение, важно обязательно сортировать значения в окне, когда как в MIN() и MAX()не нужно ничего сортировать.  

2. Если с функцией FIRST_VALUE() все просто, она всегда возвращает первую строку внутри окна по какому то признаку, то с функцией LAST_VALUE() легко запутаться. По идее LAST_VALUE() должна работать наоборот по отношению к FIRST_VALUE(), то есть возвращать значение из последней строки внутри окна, однако она будет возвращать почему-то текущее значение. Давайте посмотрим на пример:

С помощью LAST_VALUE() выведем игрока, команду, рейтинг игрока и самый высокий рейтинг в команде:

select
    short_name,
    club,
    overall,
    last_value(overall) over (partition by club order by overall) as best_overall_in_team
from
    fifa_players_2019_2020

Как вы можете наблюдать, функция LAST_VALUE() не вернула нам максимальный рейтинг в команде, а возвращает текущий рейтинг для каждого игрока. Это происходит, потому что когда мы пишем:  LAST_VALUE(overall) over (PARTITON BY club ORDER BY overall)  -

по умолчанию имеется ввиду LAST_VALUE(overall) over (PARTITON BY club ORDER BY overall RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

То есть в этом случаее функция забирает все строки от начала окна (RANGE BETWEEN UNBOUNDED PRECEDING) до текущей строки (CURRENT ROW).

Поэтому, чтобы мы могли использовать функцию LAST_VALUE() по прямому назначению, нужно написать: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. То есть мы включаем все строки в окне, давайте попробуем применить это к нашему примеру:

select
    short_name,
    club,
    overall,
    last_value(overall) over (partition by club order by overall RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as best_overall_in_team
from
    fifa_players_2019_2020
where club = '1. FC Heidenheim 1846'
order by
    overall desc

А еще проще мы можем добиться результата, если просто будем использовать обратную сортировку с функцией first_value():

select
    short_name,
    club,
    overall,
    first_value(overall) over (partition by club order by overall desc) as best_overall_in_team
from
    fifa_players_2019_2020
where club = '1. FC Heidenheim 1846'
order by
    overall desc

3.  Когда мы ищем строки, для которой какой-то признак имеет максимальное или минимальное значение, оконные функции FIRST_VALUE()или LAST_VALUE() могут отработать не совсем очевидно. А вот почему так происходит, отчетливо демонстрирует вот такое пример:

Вам необходимо выяснить название товара, которого меньше всего осталось в конкретном магазине. Попробуем решить ее двумя способами:

select
    distinct 
    storeid,
    first_value(productid) over (partition by storeid order by quantityavailable asc) as productid,
    first_value(quantityavailable) over (partition by storeid order by quantityavailable asc) as min_quantityavailable
from 
    supermarket_inventory

Получим вот такой ответ, где для каждого магазина есть id продукта, которого по количеству товара меньше всего на складе. 

Всего у нас 34 магазина, значит предыдущий запрос вернул 1 товар для 1 магазина. Но как будто существует вероятность, что у нас для 1 магазина может быть несколько товаров с минимальным остатком на складе. 

Проверим это утверждение простым агрегатным запросом без использование оконки:

select
    i.storeid,
    i.productid,
    t.qty
from
    supermarket_inventory as i
join
    (
        select
            storeid,
            min(quantityavailable) as qty
        from supermarket_inventory
        group by
            storeid
    ) as t
    on i.storeid = t.storeid
    and i.quantityavailable = t.qty
order by
    storeid

В этом случае мы получили 108 строк, потому что для каждого магазина мы получили несколько товаров, которые содержат минимальное количество товаров. В таком случае, возможно, скажете вы, что функции смещения тут не подходят, но это не совсем так. Подходят, просто нужно написать немного по другому. 

Из второго запроса следует, что мы ищем товары, которых меньше всего на складе, значит главное условие в нашем запросе - это поиск по минимальному количеству. Давайте реализуем теперь правильный вариант запроса с использованием оконных функций:

select
    distinct
    storeid,
    productid,
    quantityavailable
from
    (
        select
            storeid,
            productid,
            quantityavailable,
            first_value(quantityavailable) over (partition by storeid order by quantityavailable) as min_quantityavailable
        from 
            supermarket_inventory
    ) as t 
where 1=1
    and quantityavailable = min_quantityavailable

В этом запросе мы использовали такой же поиск по минимальному в условии

where 1=1
    and quantityavailable = min_quantityavailable

А само минимальное значение уже нашли с помощью оконки. 

1. В первом примере мы нашли рандомный товар с минимальным остатком на складе для каждого магазина.
2. В остальных примерах мы нашли все товары с минимальным остатком для каждого магази6а.

Этот пример не призван вас запутать, а наоборот еще раз показывает важность понимания исходного результата, который вы желаете получить. Поэтому всегда проверяйте себя и задавайте вопрос, а правильно ли вы используйте тот или иной инструмент?