0Pricing
SQL Academy · Урок

Написание аналитических запросов

Разрезайте данные, детализируйте их и агрегируйте показатели

«Написание аналитических запросов» — бесплатный урок SQL Academy на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Что такое аналитические запросы

Аналитические запросы выходят за рамки простого поиска строк. Вместо вопроса какой заказ оформил клиент 42? они спрашивают: какова общая выручка по регионам и кварталам? или как этот месяц сравнивается с предыдущим?

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

Повторение звёздной схемы

Звёздная схема содержит одну центральную таблицу фактов (например, fact_sales), окружённую таблицами измерений (например, dim_date, dim_product, dim_store). Аналитические запросы объединяют таблицу фактов с теми таблицами измерений, которые нужны для текущего анализа.

SELECT
    s.store_name,
    d.year,
    d.quarter,
    SUM(f.revenue)   AS total_revenue,
    SUM(f.units_sold) AS total_units
FROM fact_sales f
JOIN dim_store  s ON s.store_id  = f.store_id
JOIN dim_date   d ON d.date_id   = f.date_id
GROUP BY
    s.store_name,
    d.year,
    d.quarter
ORDER BY
    d.year,
    d.quarter,
    s.store_name;

Срез: фильтрация одного измерения

Срез означает ограничение набора результатов одним значением одного измерения — например, просмотр только данных за 2024 год. Предложение WHERE — ваш инструмент для среза.

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

-- Slice: only year 2024
SELECT
    p.category,
    SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY p.category
ORDER BY total_revenue DESC;

Сечение: фильтрация нескольких измерений

Сечение означает одновременное применение фильтров к двум или более измерениям — например, просмотр продаж электроники в северном регионе в первом квартале. Каждое дополнительное условие WHERE выделяет меньший куб данных.

-- Dice: category = 'Electronics', region = 'North', Q1
SELECT
    d.month,
    SUM(f.revenue)    AS revenue,
    SUM(f.units_sold) AS units
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE
    p.category  = 'Electronics'
    AND s.region = 'North'
    AND d.year   = 2024
    AND d.quarter = 1
GROUP BY d.month
ORDER BY d.month;

Свёртка: агрегация до более общего уровня

Свёртка означает переход от детального уровня (дневные продажи по магазинам) к более общему уровню (месячные продажи по регионам). Для этого удаляются столбцы нижнего уровня из GROUP BY и выполняется повторная агрегация.

Модификатор ROLLUP позволяет получить промежуточные и общие итоги в одном запросе вместо написания нескольких блоков UNION ALL.

-- Roll up from store/month to region/quarter with subtotals
SELECT
    s.region,
    d.quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date  d ON d.date_id  = f.date_id
WHERE d.year = 2024
GROUP BY ROLLUP(s.region, d.quarter)
ORDER BY s.region NULLS LAST, d.quarter NULLS LAST;

Сравнение периодов с помощью LAG

Один из наиболее распространённых аналитических шаблонов — сравнение показателя с тем же показателем за предыдущий период. Оконная функция LAG() позволяет напрямую получить значение предыдущей строки в текущей строке без самосоединения.

Здесь мы вычисляем рост выручки по сравнению с предыдущим месяцем в процентах.

WITH monthly AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY year, month) AS prev_month_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY year, month))
             / NULLIF(LAG(revenue) OVER (ORDER BY year, month), 0),
    2) AS mom_growth_pct
FROM monthly
ORDER BY year, month;

Накопительные итоги с помощью SUM OVER

Накопительный итог (кумулятивная сумма) прибавляет значение каждой строки к сумме всех предыдущих строк в заданном порядке. Это удобно для отслеживания накопленной выручки в течение года или контроля расходования бюджета.

Условие рамки окна ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW делает окно явным и однозначным.

SELECT
    d.year,
    d.month,
    SUM(f.revenue)                                      AS monthly_revenue,
    SUM(SUM(f.revenue)) OVER (
        PARTITION BY d.year
        ORDER BY d.month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                                   AS ytd_revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
ORDER BY d.year, d.month;

Ранжирование измерений с помощью DENSE_RANK

Ранжирование позволяет находить лучшие или худшие результаты внутри группы. DENSE_RANK() присваивает последовательные ранги без пропусков при совпадении значений, поэтому это предпочтительный вариант для таблиц лидеров в отчётах BI.

Если поместить ранжированный результат в CTE и отфильтровать его по рангу, шаблон выборки первых N результатов становится понятным и аккуратным.

WITH ranked_products AS (
    SELECT
        p.product_name,
        p.category,
        SUM(f.revenue) AS revenue,
        DENSE_RANK() OVER (
            PARTITION BY p.category
            ORDER BY SUM(f.revenue) DESC
        ) AS rnk
    FROM fact_sales f
    JOIN dim_product p ON p.product_id = f.product_id
    JOIN dim_date   d ON d.date_id     = f.date_id
    WHERE d.year = 2024
    GROUP BY p.product_name, p.category
)
SELECT *
FROM ranked_products
WHERE rnk <= 3
ORDER BY category, rnk;

Доля в процентах с помощью оконной SUM

Знать абсолютную выручку продукта полезно, но знать, что на него приходится 38 % выручки категории, практичнее. Оконная SUM() по всему разделу даёт знаменатель без соединения с подзапросом.

SELECT
    p.category,
    p.product_name,
    SUM(f.revenue)                               AS product_revenue,
    SUM(SUM(f.revenue)) OVER (PARTITION BY p.category) AS category_revenue,
    ROUND(
        100.0 * SUM(f.revenue)
             / SUM(SUM(f.revenue)) OVER (PARTITION BY p.category),
    1)                                           AS pct_of_category
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id     = f.date_id
WHERE d.year = 2024
GROUP BY p.category, p.product_name
ORDER BY p.category, pct_of_category DESC;

Скользящие средние для сглаживания тренда

Дневные или еженедельные показатели продаж содержат много шума. Скользящее среднее сглаживает краткосрочные колебания, позволяя увидеть основной тренд. Здесь скользящее среднее за 3 месяца вычисляется с помощью рамки скользящего окна.

WITH monthly_rev AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY year, month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
    2) AS moving_avg_3m
FROM monthly_rev
ORDER BY year, month;

CUBE для всех сочетаний измерений

CUBE расширяет возможности ROLLUP, вычисляя промежуточные итоги для всех возможных сочетаний перечисленных измерений, а не только по иерархическому пути свёртки. Это позволяет за один проход получить полную межмерную сводку — полезную для многомерных панелей, где пользователи могут свободно менять разрезы.

NULL в столбце группировки означает все значения этого измерения — используйте GROUPING(), чтобы отличать намеренные NULL в данных от NULL, появившихся при свёртке.

SELECT
    CASE WHEN GROUPING(s.region)   = 1 THEN 'ALL REGIONS'    ELSE s.region        END AS region,
    CASE WHEN GROUPING(p.category) = 1 THEN 'ALL CATEGORIES' ELSE p.category      END AS category,
    CASE WHEN GROUPING(d.quarter)  = 1 THEN 'ALL QUARTERS'   ELSE d.quarter::TEXT END AS quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY CUBE(s.region, p.category, d.quarter)
ORDER BY s.region NULLS LAST, p.category NULLS LAST, d.quarter NULLS LAST;

Какая операция ограничивает результаты одним значением измерения

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

Итоги: написание аналитических запросов

В этом уроке вы изучили основные шаблоны написания аналитических запросов для звёздной схемы:

  • Срез — фильтрация одного измерения с помощью WHERE для выделения конкретного сегмента.
  • Сечение — одновременная фильтрация нескольких измерений для выделения точного куба данных.
  • Свёртка — агрегация до более общего уровня; используйте ROLLUP или CUBE для многоуровневых промежуточных итогов.
  • LAG / LEAD — сравнение периодов без самосоединений.
  • Накопительные итоги & скользящие средние — кумулятивные и сглаженные показатели с помощью рамок окон.
  • DENSE_RANK — понятное ранжирование первых N результатов внутри разделов.
  • Доля, % — оконная SUM в качестве знаменателя для вычисления долей.

Сочетание этих шаблонов охватывает подавляющее большинство требований к аналитике и отчётности, с которыми вы столкнётесь в реальных хранилищах данных.

Часто задаваемые вопросы

Урок «Написание аналитических запросов» бесплатный?

Да — полный текст урока «Написание аналитических запросов» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Написание аналитических запросов»?

Разрезайте данные, детализируйте их и агрегируйте показатели Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Сколько времени занимает урок «Написание аналитических запросов»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. OLTP и OLAP
  2. Таблицы фактов и измерений
  3. Звёздная и снежинка-схема
  4. Написание аналитических запросов
← Назад к SQL Academy