SQL PARTITION BY ORDER BY: методы и примеры

  1. Базовый синтаксис:

    SELECT column1, column2, ..., columnN
FROM table_name
PARTITION BY column1, column2, ..., columnN
ORDER BY column1, column2, ..., columnN;

  2. Пример. Вычисление номеров строк внутри разделов:

    SELECT column1, column2, ..., columnN,
 ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_number
FROM table_name;

  3. Пример. Вычисление совокупных сумм внутри разделов:

    SELECT column1, column2, ..., columnN,
 SUM(columnN) OVER (PARTITION BY column1 ORDER BY column2) AS cumulative_sum
FROM table_name;

  4. Пример: найти первую и последнюю записи в разделах:

    SELECT column1, column2, ..., columnN
FROM (
 SELECT column1, column2, ..., columnN,
   ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_number,
   COUNT(*) OVER (PARTITION BY column1) AS total_rows
 FROM table_name
) AS subquery
WHERE row_number = 1 OR row_number = total_rows;

  5. Пример. Вычисление средних значений внутри разделов:

    SELECT column1, column2, ..., columnN,
 AVG(columnN) OVER (PARTITION BY column1 ORDER BY column2) AS average_value
FROM table_name;