tasks1_3 - master - Louisset/Library

tasks1_3
281 строка10 KB
Louisset
create tasks1_3
14 ноя 2025, 22:45
14 ноя 2025, 22:458a15b3b
DROP TABLE IF EXISTS BookGenre CASCADE;
DROP TABLE IF EXISTS Books CASCADE;
DROP TABLE IF EXISTS Authors CASCADE;
DROP TABLE IF EXISTS Genre CASCADE;
DROP TABLE IF EXISTS Readers CASCADE;
DROP TABLE IF EXISTS BookRentals CASCADE;

CREATE TABLE Readers (
    reader_id INTEGER PRIMARY KEY,
    reader_name VARCHAR(100),
    join_date DATE
);

CREATE TABLE BookRentals (
    rental_id INTEGER PRIMARY KEY,
    book_id INTEGER,
    reader_id INTEGER,
    take_date DATE,          
    return_date DATE        
);

CREATE TABLE IF NOT EXISTS Authors(
  author_id INTEGER PRIMARY KEY,
  author_name VARCHAR(100) NOT NULL,
  author_lastname VARCHAR(100) NOT NULL,
  author_country VARCHAR(100),
  author_age INTEGER
);

CREATE TABLE IF NOT EXISTS Books(
  book_id INTEGER PRIMARY KEY,
  book_name VARCHAR(200) NOT NULL,
  number_of_pages INTEGER,
  author_id INTEGER,
  price DECIMAL(10,2),
  release_date DATE,
  FOREIGN KEY (author_id) REFERENCES Authors(author_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS Genre(
  genre_id INTEGER PRIMARY KEY,
  genre_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS BookGenre(
  book_id INTEGER,
  genre_id INTEGER,
  PRIMARY KEY (book_id, genre_id),
  FOREIGN KEY (book_id) REFERENCES Books(book_id) ON DELETE CASCADE,
  FOREIGN KEY (genre_id) REFERENCES Genre(genre_id) ON DELETE CASCADE
);

INSERT INTO Readers (reader_id, reader_name, join_date) VALUES
(1, 'Иван Иванов', '2020-05-15'),
(2, 'Петр Петров', '2022-01-20'),
(3, 'Мария Сидорова', '2023-03-10'),
(4, 'Анна Козлова', '2021-11-05'),
(5, 'Сергей Смирнов', '2022-08-30');

INSERT INTO BookRentals (rental_id, book_id, reader_id, take_date, return_date) VALUES
(1, 90, 2, '2024-01-15', NULL),      
(2, 88, 3, '2024-02-20', '2024-03-10'),
(3, 89, 3, '2024-03-15', NULL),     
(4, 91, 4, '2024-01-10', '2024-02-01'), 
(5, 92, 2, '2024-03-01', NULL),
(6, 93, 1, '2025-11-01', '2025-11-10'),
(7, 94, 5, '2025-11-01', '2025-11-05'),
(8, 95, 2, '2025-11-02', '2025-11-08'),
(9, 96, 3, '2025-11-02', NULL),
(10, 97, 4, '2025-11-03', '2025-11-09'),
(11, 98, 1, '2025-11-03', '2025-11-12'),
(12, 99, 5, '2025-11-04', '2025-11-07'),
(13, 100, 2, '2025-11-04', '2025-11-11'),
(14, 101, 3, '2025-11-05', NULL),
(15, 102, 4, '2025-11-05', '2025-11-15'),
(16, 103, 1, '2025-11-06', '2025-11-13'),
(17, 104, 5, '2025-11-07', '2025-11-14'),
(18, 105, 2, '2025-11-08', NULL),
(19, 106, 3, '2025-11-08', '2025-11-16'),
(20, 107, 4, '2025-11-09', '2025-11-17'),
(21, 108, 1, '2025-11-10', '2025-11-18'),
(22, 109, 5, '2025-11-11', NULL),
(23, 110, 2, '2025-11-12', '2025-11-19'),
(24, 111, 3, '2025-11-13', '2025-11-20'),
(25, 112, 4, '2025-11-14', '2025-11-21'),
(26, 113, 1, '2025-11-15', NULL),
(27, 114, 5, '2025-11-16', '2025-11-23'),
(28, 115, 2, '2025-11-17', '2025-11-24'),
(29, 116, 3, '2025-11-18', '2025-11-25'),
(30, 117, 4, '2025-11-19', NULL),
(31, 90, 1, '2025-11-10', '2025-11-15'),
(32, 91, 2, '2025-11-10', '2025-11-16'),
(33, 92, 3, '2025-11-10', '2025-11-17'),
(34, 93, 4, '2025-11-15', '2025-11-20'),
(35, 94, 5, '2025-11-15', '2025-11-21'),
(36, 95, 1, '2025-11-20', '2025-11-25'),
(37, 96, 2, '2025-11-20', '2025-11-26'),
(38, 97, 3, '2025-11-20', '2025-11-27'),
(39, 98, 4, '2025-11-25', '2025-11-30'),
(40, 99, 5, '2025-11-25', NULL);

INSERT INTO Authors (author_id, author_name, author_lastname, author_country, author_age) VALUES
(1, 'Лев Николаевич', 'Толстой', 'Россия', 82),
(2, 'Фёдор Михайлович', 'Достоевский', 'Россия', 59),
(3, 'Антон Павлович', 'Чехов', 'Россия', 44),
(4, 'Виктор', 'Гюго', 'Франция', 65),
(5, 'Жюль', 'Верн', 'Франция', 77),
(6, 'Александр', 'Дюма', 'Франция', 68),
(7, 'Уильям' ,'Шекспир', 'Англия', 52),
(8, 'Чарльз', 'Диккенс', 'Англия', 58),
(9, 'Джейн', 'Остин', 'Англия', 41),
(10, 'Габриэль Гарсиа', 'Маркес', 'Колумбия', 87),
(11, 'Харуки', 'Мураками', 'Япония', 51),
(12, 'Пауло', 'Коэльо', 'Бразилия', 75),
(13, 'Иван', 'Тургенев', 'Россия', 64),
(14, 'Агата', 'Кристи', 'Англия', 85),
(15, 'Антуан', 'Сент-Экзюпери', 'Франция', 44);

INSERT INTO Books (book_id, book_name, number_of_pages, author_id, price, release_date) VALUES
(87, 'Собор Парижской Богоматери', 1463, 4, 1600, '1831-01-01'),
(88, 'Преступление и наказание', 430, 2, 750, '1866-01-01'),
(89, 'Гордость и предубеждение', 279, 9, 680, '1813-01-01'),
(90, 'Война и мир', 1463, 1, 1200, '1869-01-01'),
(91, 'Большие надежды', 505, 8, 910, '1861-01-01'),
(92, 'Отцы и дети', 400, 13, 730, '1862-01-01'),
(93, 'Гамлет', 500, 7, 840, '1603-01-01'),
(94, 'Маленький принц', 96, 15, 670, '1943-01-01'),
(95, 'Три мушкетера', 700, 6, 890, '1844-01-01'),
(96, 'Двадцать тысяч лье под водой', 426, 5, 490, '1870-01-01'),
(97, 'Норвежский лес', 296, 11, 540, '1987-01-01'),
(98, 'Алхимик', 208, 12, 380, '1988-01-01'),
(99, 'Вишневый сад', 200, 3, 430, '1904-01-01'),
(100, 'Убийство в "Восточном экспрессе"', 320, 14, 620, '1934-01-01'),
(101, 'Сто лет одиночества', 417, 10, 590, '1967-01-01'),
(102, 'Смерть на Ниле', 352, 14, 500, '1937-01-01'),
(103, 'Убийства по алфавиту',288,14, 360, '1936-01-01'),
(104, 'Анна Каренина', 864, 1, 950, '1877-01-01'),
(105, 'Идиот', 640, 2, 820, '1869-01-01'),
(106, 'Чайка', 120, 3, 380, '1896-01-01'),               
(107, 'Отверженные', 1232, 4, 1450, '1862-01-01'),        
(108, 'Таинственный остров', 600, 5, 550, '1874-01-01'),     
(109, 'Граф Монте-Кристо', 928, 6, 1100, '1844-01-01'),      
(110, 'Ромео и Джульетта', 300, 7, 720, '1597-01-01'),       
(111, 'Оливер Твист', 480, 8, 780, '1838-01-01'),            
(112, 'Эмма', 474, 9, 650, '1815-01-01'),                    
(113, 'Любовь во время холеры', 348, 10, 520, '1985-01-01'), 
(114, 'Охота на овец', 448, 11, 580, '1982-01-01'),          
(115, 'Вероника решает умереть', 224, 12, 420, '1998-01-01'),
(116, 'Записки охотника', 320, 13, 480, '1852-01-01'),       
(117, 'Планета людей', 220, 15, 450, '1939-01-01');         

INSERT INTO Genre (genre_id, genre_name) VALUES
(1, 'Роман'),
(2, 'Фантастика'),
(3, 'Трагедия'),
(4, 'Философская сказка'),
(5, 'Драма'),
(6, 'Детектив');

INSERT INTO BookGenre (book_id, genre_id) VALUES
(87, 1),
(88, 1),
(89, 1),
(90, 1),
(91, 1),
(92, 1),
(93, 3),
(94, 4),
(95, 1),
(96, 2),
(97, 1),
(98, 1),
(99, 5),
(100, 6),
(101, 2),
(102, 6),
(103, 6),
(104, 1),  
(105, 1),    
(106, 5),
(107, 1),
(108, 2),
(109, 1),
(110, 3),
(111, 1),
(112, 1),
(113, 1),
(114, 1), 
(115, 1), 
(116, 1),  
(117, 1);


--1
WITH AuthorAvg AS (
SELECT author_id, ROUND(AVG(price), 2) AS avg_author_price
FROM Books
GROUP BY author_id
),

GenreAvg AS (
SELECT bookgenre.genre_id, ROUND(AVG(price), 2) AS avg_genre_price
FROM books
JOIN bookgenre ON books.book_id = bookgenre.book_id
GROUP BY bookgenre.genre_id
)

SELECT Books.book_name AS "Название книги", authors.author_name || ' ' || authors.author_lastname AS "Автор", books.price AS "Цена",
AuthorAvg.avg_author_price AS "Средняя цена по автору",
GenreAvg.avg_genre_price AS "Средняя цена по жанру",
ROUND(((Books.price - avg_genre_price)/avg_genre_price) * 100, 2) AS "Отклонение от среднего по жанру в %"
FROM books
JOIN authors ON books.author_id = authors.author_id
JOIN bookgenre ON books.book_id = bookgenre.book_id
JOIN AuthorAvg ON books.author_id = AuthorAvg.author_id
JOIN GenreAvg ON bookgenre.genre_id = GenreAvg.genre_id
ORDER BY "Автор", "Название книги";


--2
WITH BritishBooks AS (
SELECT authors.author_name || ' ' || authors.author_lastname AS author_full_name, books.book_name,
  authors.author_id, books.book_id, books.release_date, EXTRACT(YEAR FROM books.release_date) AS year_of_release
  FROM Authors
  JOIN Books ON authors.author_id = books.author_id
  WHERE authors.author_country = 'Англия'
),

AuthorCount AS(
  SELECT author_id, COUNT(*) AS total_books
  FROM BritishBooks
  GROUP BY author_id
),

BookRank AS (
  SELECT bb.*, (SELECT COUNT(*) +1 FROM BritishBooks bb2 WHERE bb2.author_id = bb.author_id
  AND bb2.release_date < bb.release_date) as publication_rank
  FROM BritishBooks bb
)

SELECT author_full_name AS "Автор", book_name AS "Название книги", year_of_release AS "Год публикации",
publication_rank AS "Порядковый номер", total_books AS "Всего книг у автора"
FROM BookRank
JOIN AuthorCount ON BookRank.author_id = AuthorCount.author_id 
ORDER BY author_full_name, release_date;


--3
WITH RECURSIVE last_month_dates AS (
  SELECT DATE '2025-11-01' AS date_day
  UNION ALL
  SELECT (date_day + INTERVAL '1 day') :: DATE
  FROM last_month_dates
  WHERE date_day < DATE '2025-11-30'
),

rental_counts AS(
  select lmd.date_day, COUNT(bookrentals.rental_id) AS daily_rentals
  FROM last_month_dates lmd
  LEFT JOIN BookRentals ON lmd.date_day = DATE(bookrentals.take_date)
  GROUP BY lmd.date_day
),

moving_avg AS (
  SELECT rental_counts.date_day, rental_counts.daily_rentals, 
  --вычисляем среднее для каждой строки
  ROUND(AVG(rental_counts.daily_rentals) OVER (
  --упорядочиваем по времени
  ORDER BY rental_counts.date_day
  --чтобы было именно 3 дня
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) 
  AS moving_avg_3_days
  FROM rental_counts
)

SELECT date_day AS "Дата",
daily_rentals AS "Количество выдач",
DENSE_RANK() OVER (ORDER BY daily_rentals DESC) AS "Ранг",
moving_avg_3_days AS "Скользящее среднее (3 дня)"
FROM moving_avg
ORDER BY date_day;