Привет, коллеги! На связи Павел Беляев, ментор курса «Аналитик данных» и ведущий канала Тимлидское об аналитике 👋🏻

Важнейший инструмент дата-аналитика (не считая его головы, конечно) — это SQL. Очевидно, нужно не только знать синтаксис и структуру запросов — нужно расширять свой арсенал приёмов использования SQL.

Предлагаю пару лайфхаков SQL, которые мы используем в своей работе.

📌 Последний элемент в исторических данных

В таблицах с историческими данными строки, отражающие состояние некоей сущности, не изменяют своё значение со временем. Если состояние изменилось, к таблице добавляется новая строка для той же сущности, но с другим значением соответствующего поля (например, поля status). Каждая строка при этом снабжена полем date_updated, содержащим дату и время обновления.

Так вот, чтобы вытащить строку с текущим состоянием сущности, можно использовать оконную функцию:

SELECT *
FROM
(
    SELECT *
        MAX(updated_at) OVER (PARTITION BY payment_id) AS last_update
    FROM payment
)
WHERE updated_at = last_update

📌 Сводные таблицы

Предположим, у нас есть таблица с фильмами, где для каждого из них имеется столбец release_year с годом выпуска и length с длительностью в минутах. Мы хотим подсчитать количество фильмов до 90 минут, до 120 минут и более 120 минут.

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

WITH films AS
(
    SELECT 2006 AS release_year, 86 AS "length"
    UNION ALL
    SELECT 2006 AS release_year, 180 AS "length"
    UNION ALL
    SELECT 2006 AS release_year, 95 AS "length"
    UNION ALL
    SELECT 2006 AS release_year, 135 AS "length"
    UNION ALL
    SELECT 2006 AS release_year, 120 AS "length"
    UNION ALL
    SELECT 2007 AS release_year, 85 AS "length"
    UNION ALL
    SELECT 2007 AS release_year, 130 AS "length"
    UNION ALL
    SELECT 2007 AS release_year, 120 AS "length"
    UNION ALL
    SELECT 2007 AS release_year, 97 AS "length"
)
SELECT release_year,
    SUM(CASE WHEN "length"<90 THEN 1 ELSE 0 END) AS less_90 ,
    SUM(CASE WHEN "length">=90 AND "length"<120 THEN 1 ELSE 0 END) AS less_120,
    SUM(CASE WHEN "length">=120 THEN 1 ELSE 0 END) AS over_120
FROM films
GROUP BY 1 
-- GROUP BY ROLLUP (1) -- добавляет в конец строку с итогом
ORDER BY 1

Больше приёмов SQL смотрите в моих статьях — и сохраняйте к себе, чтобы не потерять:
💡 Семь лайфхаков SQL
💡 Ещё шесть лайфхаков SQL

📊 Simulative