task5_7 - master - Louisset/Library

task5_7
54 строки2 KB
Louisset
create task5_7
18 ноя 2025, 16:52
18 ноя 2025, 16:522a7eb91
WITH RussianBooks AS (
  SELECT Books.book_id,
  Books.book_name AS "Название книги",
  Authors.author_name || ' ' || authors.author_lastname AS "Автор",
  EXTRACT(YEAR FROM Books.release_date) AS "Год издания",
  Books.release_date,
  Authors.author_id
  FROM Books
  JOIN Authors ON Books.author_id = Authors.author_id
  WHERE Authors.author_country = 'Россия'
),

NumberedBooks AS (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY release_date) AS "Порядковый номер",
    LAG(EXTRACT(YEAR FROM release_date)) OVER (PARTITION BY author_id ORDER BY release_date) AS "Предыдущий год",
    LEAD(EXTRACT(YEAR FROM release_date)) OVER (PARTITION BY author_id ORDER BY release_date) AS "Следующий год"
    FROM RussianBooks
)
SELECT "Автор", "Название книги", "Год издания", "Порядковый номер",
"Год издания" - "Предыдущий год" AS "Разница с предыдущей",
"Следующий год" - "Год издания" AS "Разница со следующей"
FROM NumberedBooks
ORDER BY "Автор", "Год издания";




WITH MonthlyRentals AS (
SELECT DATE_TRUNC('month', take_date) AS rental_month,
COUNT(rental_id) AS monthly_rentals
FROM BookRentals
WHERE take_date IS NOT NULL
GROUP BY DATE_TRUNC('month', take_date)
),
MonthlyStats AS (
SELECT rental_month,
  monthly_rentals,
  ROUND(AVG(monthly_rentals) OVER (
ORDER BY rental_month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3_months,
LAG(monthly_rentals) OVER (ORDER BY rental_month) AS prev_month_rentals
FROM MonthlyRentals
)
SELECT TO_CHAR(rental_month, 'YYYY') AS "Год",
TO_CHAR(rental_month, 'MM') AS "Месяц", 
monthly_rentals AS "Количество выдач",
moving_avg_3_months AS "Скользящее среднее (3 месяца)",
CASE 
WHEN prev_month_rentals IS NULL OR prev_month_rentals = 0 THEN NULL
ELSE ROUND(((monthly_rentals - prev_month_rentals)::DECIMAL / prev_month_rentals) * 100, 2)
END AS "Изменение к предыдущему месяцу (%)"
FROM MonthlyStats
ORDER BY rental_month;