Урок 6: Веконные Функции — Даем Твоим Запросам Вид на Жизнь С Высоты!

Lesson 6

Урок 6: Веконные Функции — Даем Твоим Запросам Вид на Жизнь С Высоты!

Привет снова, искатель SQL-истины! Сегодня мы погружаемся в мир оконных функций — это как переехать в элитный пентхаус в мире SQL. Если ты когда-нибудь хотел добавить каплю лоска в свой анализ данных, оконные функции — твой выбор. Представь их как бинокли: они позволяют смотреть на каждый ряд данных, не упуская из виду общую картину.

Что Такое Оконные Функции?

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

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

Синтаксис Оконной Функции

Оконные функции используют простой синтаксис, что-то вроде этого:

SELECT column_name,
       window_function(column_name) OVER (PARTITION BY column_name ORDER BY column_name)
FROM table_name;

Самая важная часть в этой функции — это клаузула OVER. Она говорит PostgreSQL, через какое “окно” смотреть на данные во время вычисления.

RANK и DENSE_RANK: Потому что Каждый Рыцарь Заслуживает Своего Титула

Начнем с функций ранжирования. Представь, что у нас есть таблица рыцарей с количеством выполненных миссий, и ты хочешь узнать, кто тут самый грозный боец (или самый жуткий охотник на драконов):

SELECT imya, misyiyi_vykonano,
       RANK() OVER (ORDER BY misyiyi_vykonano DESC) AS rank
FROM rycari;

Этот запрос присвоит рыцарям ранги в зависимости от количества выполненных миссий. Но имей в виду: RANK пропустит номера, если у двух рыцарей одинаковое количество миссий. То есть если два рыцаря на первом месте, следующий будет уже третьим, а не вторым!

Хочешь, чтобы никто не обижался? Используй DENSE_RANK:

SELECT imya, misyiyi_vykonano,
       DENSE_RANK() OVER (ORDER BY misyiyi_vykonano DESC) AS dense_rank
FROM rycari;

Так будет честнее — никакой дискриминации, даже среди рыцарей!

ROW_NUMBER: Потому что Каждая Строка Достойна Внимания

Если тебе нужно просто присвоить каждой строке свой номер, ROW_NUMBER — твой лучший друг:

SELECT imya, misyiyi_vykonano,
       ROW_NUMBER() OVER (ORDER BY misyiyi_vykonano DESC) AS row_number
FROM rycari;

Идеально, если хочешь, чтобы каждый рыцарь получил свой уникальный номер, независимо от количества миссий.

PARTITION BY: Организуем Королевский Банкет

Теперь давай немного усложним задачу. PARTITION BY позволяет группировать строки в отдельные окна, как если бы мы рассаживали рыцарей по королевствам на большом банкете. Допустим, ты хочешь сортировать рыцарей внутри каждого королевства:

SELECT imya, korolivstvo, misyiyi_vykonano,
       RANK() OVER (PARTITION BY korolivstvo ORDER BY misyiyi_vykonano DESC) AS rank_v_korolivstve
FROM rycari;

Теперь каждый рыцарь имеет свой ранг внутри королевства, так что сэр Галахад может гордо чувствовать себя первым в Камелоте, даже если сэр Артур его обгоняет по общему количеству миссий.

Накопительные Суммы: Считаем Золото в Казне

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

SELECT imya, misyiyi_vykonano,
       SUM(misyiyi_vykonano) OVER (ORDER BY misyiyi_vykonano DESC) AS cumulative_missions
FROM rycari;

Это как складывать золотые монеты в казну: каждый вклад рыцарей добавляется в общую копилку, показывая текущее состояние.

Скользящие Средние: Круглый Стол Вычислений

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

SELECT imya, misyiyi_vykonano,
       AVG(misyiyi_vykonano) OVER (ORDER BY misyiyi_vykonano DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM rycari;

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

LEAD и LAG: Заглянем в Прошлое и Будущее

Хочешь увидеть, как рыцарь справился со своей прошлой миссией, или предсказать следующую? Используй LEAD и LAG, чтобы заглянуть в хронологию:

SELECT imya, misyiyi_vykonano,
       LAG(misyiyi_vykonano, 1) OVER (ORDER BY misyiyi_vykonano DESC) AS previous_mission,
       LEAD(misyiyi_vykonano, 1) OVER (ORDER BY misyiyi_vykonano DESC) AS next_mission
FROM rycari;

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

Подведем Итог: Великий Бал Оконных Функций!

Чтобы подытожить, вот запрос, который использует несколько оконных функций для ранжирования, суммирования и анализа:

WITH stat_rycari AS (
  SELECT imya, korolivstvo, misyiyi_vykonano,
         RANK() OVER (PARTITION BY korolivstvo ORDER BY misyiyi_vykonano DESC) AS rank_v_korolivstve,
         SUM(misyiyi_vykonano) OVER (ORDER BY misyiyi_vykonano DESC) AS cumulative_missions,
         LAG(misyiyi_vykonano, 1) OVER (ORDER BY misyiyi_vykonano DESC) AS previous_mission
  FROM rycari
)
SELECT * FROM stat_rycari;

Эта элегантная структура показывает, как оконные функции могут превратить даже самые сложные запросы в простое задание.

Что Мы Сегодня Узнали?

Сегодня мы исследовали мир оконных функций:

  • Функции Ранжирования: RANK, DENSE_RANK и ROW_NUMBER — для того, чтобы каждый рыцарь знал свое место.
  • Накопительные Функции: SUM, AVG и COUNT — для подсчета, кто на самом деле самый отважный.
  • LEAD и LAG: позволят взглянуть на предыдущие или будущие строки.

Что Дальше?

На следующем уроке мы погрузимся в мир Полнотекстового Поиска в PostgreSQL — пора дать твоим запросам голос и позволить данным говорить!

Вперед, и пусть твои запросы всегда будут на высоте!