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

Метод 1: использование подзапросов с ROW_NUMBER()
Пример кода:

SELECT *
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY record_column) AS row_num_asc,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY record_column DESC) AS row_num_desc
  FROM your_table
) AS subquery
WHERE row_num_asc = 1 OR row_num_desc = 1;

Объяснение:
Этот метод использует подзапросы и функцию ROW_NUMBER() для присвоения номеров строк каждой записи в каждой группе. Внутренний запрос присваивает номера строк по возрастанию на основе столбца записи, а внешний запрос назначает номера строк по убыванию. Наконец, мы фильтруем результаты, чтобы получить записи со строкой номер 1 в порядке возрастания или убывания.

Метод 2: использование общих табличных выражений (CTE) с ROW_NUMBER()
Пример кода:

WITH cte AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY record_column) AS row_num_asc,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY record_column DESC) AS row_num_desc
  FROM your_table
)
SELECT *
FROM cte
WHERE row_num_asc = 1 OR row_num_desc = 1;

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

Метод 3: использование агрегатных функций MIN() и MAX()
Пример кода:

SELECT t1.*
FROM your_table t1
INNER JOIN (
  SELECT group_column, MIN(record_column) AS min_record, MAX(record_column) AS max_record
  FROM your_table
  GROUP BY group_column
) t2 ON t1.group_column = t2.group_column AND (t1.record_column = t2.min_record OR t1.record_column = t2.max_record);

Объяснение:
В этом методе мы используем агрегатные функции MIN() и MAX() для расчета минимального и максимального значений записи в каждой группе. Затем мы выполняем внутреннее соединение между исходной таблицей и подзапросом, который содержит столбец группы, минимальную запись и максимальную запись. Сопоставляя столбец группы и столбец записи с минимальным и максимальным значениями, мы получаем первую и последнюю записи каждой группы.

Получить первую и последнюю записи каждой группы в SQL можно различными методами. В этой статье мы рассмотрели три подхода: использование подзапросов с ROW_NUMBER(), использование общих табличных выражений (CTE) с ROW_NUMBER() и использование агрегатных функций MIN() и MAX(). Каждый метод имеет свои преимущества и может быть более подходящим в различных сценариях. Используя эти методы, вы можете эффективно извлекать нужные данные из наборов данных SQL.