ФУНКЦИИ СМЕЩЕНИЯ

Функции смещения позволяют получить значение из другой строки. Рассмотрим, как работает функция смещения на примере:

Нам нужно найти прирост количества проданных товаров для каждого дня. Для этого выведем дату, количество проданных штук товара и прирост в процентах относительно предыдущего дня. 

Решается это так:

with cte as (
select
    date,
    sum(quantity) as quantity_sale
from supermarket_inventory
join 
    supermarket_sales
    using(storeid)
group by 
    date)
    
select 
    date,
    quantity_sale,
    (quantity_sale - lag(quantity_sale) over (order by date)) as dynamic
from
    cte

• В CTE мы для каждого дня посчитали количество проданного товара.
• Чтобы посчитать динамику, мы из суммы продаж по date вычли сумму продаж предыдущего дня.
• Сумму продаж предыдущего дня мы получаем с помощью оконной функции смещения: LAG(quantity_sale) over (order by date). Рассмотрим ее подробнее:
  1. LAG(quantity_sale) - берем сумму продаж из предыдущей строки.
  2. OVER - обращаемся к окну.
  3. (ORDER by date) - само окно, тут мы используем order by date, потому что изначально в таблице даты расположены случайным образом, и для получения значения по предыдущей дате нам нужно отсортировать их. Для этого мы пишем order by date. Если бы мы добавили desc то значения бралось из будущего дня, поскольку строки мы расположили с конца. 

В этом примере мы не использовали PARTITION BY, потому что у нас задача взять просто предыдущее значение по колонке quantity_sale. Давайте посмотрим, а в каких случаях нужно использовать PARTITION BY:

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

with cte as (
select
    storename,
    date,
    sum(quantity) as quantity_sale
from supermarket_inventory
join 
    supermarket_sales
    using(storeid)
group by 
    storename,
    date)
    
select 
    storename,
    date,
    quantity_sale,
    (quantity_sale - lag(quantity_sale) over (partition by storename order by date)) as dynamic
from
    cte

• В подзапросе мы для каждого дня и магазина посчитали количество проданного товара.
• Чтобы посчитать динамику, мы из суммы продаж по date вычли сумму продаж предыдущего дня.
• Сумму продаж предыдущего дня мы получаем с помощью оконной функции смещения: LAG(quantity_sale) over (PARTITION BY storename ORDER BY date). Рассмотрим ее подробнее:
  • Функция аналогична предудщей, но в этом случае мы используем партицирование, потому что берем quantity_sale предыдущего дня только в рамках определенного магазина. 

Если резюмировать, то в функциях смещения мы берем значения из другой строки в рамках одной группы и нам обязательно нужно сортировать значения, чтобы смещение работало корректно.