https://pastein.ru/t/F_a
Загрузка данных
SET search_path = study;
-- =====================================================================
-- 3.1. Работа с агрегатными функциями
-- Для каждого месяца 2024 года выведите количество проектов
-- =====================================================================
-- 3.1.1. Получим список месяцев
SET search_path = study;
WITH months AS (
SELECT 'январь' AS month_nm, 1 AS month_nus
UNION ALL SELECT 'февраль', 2
UNION ALL SELECT 'март', 3
UNION ALL SELECT 'апрель', 4
UNION ALL SELECT 'май', 5
UNION ALL SELECT 'июнь', 6
UNION ALL SELECT 'июль', 7
UNION ALL SELECT 'август', 8
UNION ALL SELECT 'сентябрь', 9
UNION ALL SELECT 'октябрь', 10
UNION ALL SELECT 'ноябрь', 11
UNION ALL SELECT 'декабрь', 12
)
SELECT months.month_nus,
months.month_nm,
COUNT(projects.id) AS project_cnt
FROM months
LEFT JOIN projects
ON months.month_nus = EXTRACT(MONTH FROM projects.start_date)
AND EXTRACT(YEAR FROM projects.start_date) = 2026
GROUP BY months.month_nus, months.month_nm
ORDER BY months.month_nus;
-- =====================================================================
-- 3.2. Задания для выполнения
-- =====================================================================
-- ---------------------------------------------------------------------
-- 3.2.1. Порядковый номер проекта для каждого дня:
-- ROW_NUMBER() с окном по месяцу проекта и сортировкой по дню
-- ---------------------------------------------------------------------
SET search_path = study;
SELECT id,
project_name,
start_date,
ROW_NUMBER() OVER (PARTITION BY date_trunc('month', start_date)
ORDER BY EXTRACT(DAY FROM start_date)) AS rn
FROM projects
ORDER BY start_date, rn;
-- ---------------------------------------------------------------------
-- 3.2.2. Тот же запрос с RANK(), DENSE_RANK(), NTILE()
-- ---------------------------------------------------------------------
SELECT id,
project_name,
start_date,
ROW_NUMBER() OVER w AS rn,
RANK() OVER w AS rnk,
DENSE_RANK() OVER w AS dense_rnk,
NTILE(3) OVER w AS ntile_3
FROM projects
WINDOW w AS (PARTITION BY date_trunc('month', start_date)
ORDER BY EXTRACT(DAY FROM start_date))
ORDER BY start_date, rn;
/* Разница между функциями (видна на проектах, начатых в один день):
ROW_NUMBER() — всегда уникальный номер 1,2,3,4… ; при равных днях
порядок между «одинаковыми» строками произвольный.
RANK() — равные значения получают одинаковый ранг, после
группы одинаковых остаётся «дыра»: 1,2,2,4…
DENSE_RANK() — равные значения получают одинаковый ранг, но без
пропусков: 1,2,2,3…
NTILE(n) — делит строки окна на n групп примерно равного размера
и возвращает номер группы (1..n); от равенства значений
не зависит, нужен для разбиения на квантили/корзины. */
-- ---------------------------------------------------------------------
-- 3.2.3. Аналог запроса 3.1 с применением оконной функции
-- ---------------------------------------------------------------------
WITH months(month_num, month_nm) AS (
VALUES (1, 'Январь'), (2, 'Февраль'), (3, 'Март'), (4, 'Апрель'),
(5, 'Май'), (6, 'Июнь'), (7, 'Июль'), (8, 'Август'),
(9, 'Сентябрь'), (10, 'Октябрь'), (11, 'Ноябрь'), (12, 'Декабрь')
)
SELECT DISTINCT
m.month_num,
m.month_nm,
COUNT(p.id) OVER (PARTITION BY m.month_num) AS project_cnt
FROM months m
LEFT JOIN projects p
ON EXTRACT(MONTH FROM p.start_date) = m.month_num
AND EXTRACT(YEAR FROM p.start_date) = 2026
ORDER BY m.month_num;
-- Оконная функция не схлопывает строки, поэтому дубликаты убираем DISTINCT.
-- ---------------------------------------------------------------------
-- 3.2.4. Номер каждого студента в группе (сортировка по фамилии и имени)
-- ---------------------------------------------------------------------
SELECT s.group_number,
ROW_NUMBER() OVER (PARTITION BY s.group_number
ORDER BY s.last_name, s.first_name) AS num_in_group,
s.last_name,
s.first_name
FROM students s
ORDER BY s.group_number, num_in_group;
-- ---------------------------------------------------------------------
-- 3.2.5. В каждом месяце — начало работы над проектами указанной команды
-- (команда задаётся в WHERE, здесь idkom = 1)
-- ---------------------------------------------------------------------
SELECT DISTINCT
c.team,
date_trunc('month', p.start_date)::date AS month_start,
FIRST_VALUE(p.project_name) OVER w AS first_project,
FIRST_VALUE(p.start_date) OVER w AS work_start,
COUNT(*) OVER (PARTITION BY date_trunc('month', p.start_date)) AS projects_in_month
FROM projects p
JOIN team c ON c.idkom = p.idkom
WHERE p.idkom = 1
WINDOW w AS (PARTITION BY date_trunc('month', p.start_date) ORDER BY p.start_date)
ORDER BY month_start;
-- ---------------------------------------------------------------------
-- 3.2.6. Разница между стоимостью проекта и средней стоимостью всех проектов
-- ---------------------------------------------------------------------
SELECT id,
project_name,
price::numeric AS price,
ROUND(AVG(price::numeric) OVER (), 2) AS avg_price,
ROUND(price::numeric - AVG(price::numeric) OVER (), 2) AS diff_from_avg
FROM projects
ORDER BY diff_from_avg DESC;
-- ---------------------------------------------------------------------
-- 3.2.7. Поименованное окно: ранг проекта по оценке и накопительная
-- сумма баллов в рамках трека (трек = тип проекта, project_type)
-- ---------------------------------------------------------------------
SELECT project_type AS track,
project_name,
total_score,
RANK() OVER w AS rank_in_track,
SUM(total_score) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS cum_score_in_track
FROM v_project_score
WINDOW w AS (PARTITION BY project_type ORDER BY total_score DESC)
ORDER BY track, rank_in_track;
-- ---------------------------------------------------------------------
-- 3.2.8. Нарастающая сумма баллов проектов, отсортированных по баллам:
-- фрейм ROWS и фрейм RANGE
-- ---------------------------------------------------------------------
-- Запрос 1: ROWS
SELECT project_name,
total_score,
SUM(total_score) OVER (ORDER BY total_score
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_rows
FROM v_project_score
ORDER BY total_score;
-- Запрос 2: RANGE
SELECT project_name,
total_score,
SUM(total_score) OVER (ORDER BY total_score
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_range
FROM v_project_score
ORDER BY total_score;
-- Для наглядного сравнения — оба столбца в одном запросе
SELECT project_name,
total_score,
SUM(total_score) OVER (ORDER BY total_score
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_rows,
SUM(total_score) OVER (ORDER BY total_score
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_range
FROM v_project_score
ORDER BY total_score;
/* Объяснение.
ROWS — «физический» фрейм: от первой строки до ТЕКУЩЕЙ СТРОКИ.
Сумма растёт на каждой строке, даже если баллы одинаковые.
RANGE — «логический» фрейм: от начала до ТЕКУЩЕГО ЗНАЧЕНИЯ ключа
сортировки включительно. Строки с одинаковыми баллами являются
«равноправными» (peers) и все сразу попадают во фрейм, поэтому
для них сумма одинакова — значение во втором столбце дублируется
и равно сумме по последней строке группы одинаковых баллов.
Пример: баллы 10, 15, 15, 20
ROWS : 10, 25, 40, 60
RANGE: 10, 40, 40, 60
Без явного указания фрейма при наличии ORDER BY по умолчанию
используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. */
-- ---------------------------------------------------------------------
-- 3.2.9. Накопительная сумма стоимости для каждого статуса проектов
-- ---------------------------------------------------------------------
SELECT status,
project_name,
start_date,
price::numeric AS price,
SUM(price::numeric) OVER (PARTITION BY status
ORDER BY start_date, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_price
FROM projects
ORDER BY status, start_date, id;
-- ---------------------------------------------------------------------
-- 3.2.10. Скользящее среднее оценок: текущий и два предыдущих проекта
-- (по дате начала), фрейм ROWS
-- ---------------------------------------------------------------------
SELECT project_name,
start_date,
avg_score,
ROUND(AVG(avg_score) OVER (ORDER BY start_date, id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM v_project_score
ORDER BY start_date, id;
-- ---------------------------------------------------------------------
-- 3.2.12. RANGE с интервалом дат: сумма стоимости и средняя оценка
-- проектов за последние 3 месяца относительно даты начала проекта
-- ---------------------------------------------------------------------
SELECT project_name,
start_date,
price,
avg_score,
SUM(price) OVER w3 AS sum_price_3m,
ROUND(AVG(avg_score) OVER w3, 2) AS avg_score_3m,
COUNT(*) OVER w3 AS projects_3m
FROM v_project_score
WINDOW w3 AS (ORDER BY start_date
RANGE BETWEEN INTERVAL '3 months' PRECEDING AND CURRENT ROW)
ORDER BY start_date;
