SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
-- Создание таблиц CREATE SCHEMA anime_website; CREATE TABLE anime_website.group ( group_id SERIAL PRIMARY KEY, group_nm VARCHAR(255) NOT NULL ); CREATE TABLE anime_website.director ( director_id SERIAL PRIMARY KEY, director_nm VARCHAR(255) NOT NULL ); CREATE TABLE anime_website.anime ( anime_id SERIAL PRIMARY KEY, anime_nm VARCHAR(255) NOT NULL, type_nm VARCHAR(127), season_nm INTEGER, released_series_cnt INTEGER, series_cnt INTEGER, release_status_nm VARCHAR(127), anime_desc TEXT, anime_rate DOUBLE PRECISION, release_dt DATE, release_end_dt DATE, rating_value INTEGER, group_id INTEGER NOT NULL, FOREIGN KEY (group_id) REFERENCES anime_website.group (group_id) ); CREATE TABLE anime_website.genre ( genre_id SERIAL PRIMARY KEY, genre_nm VARCHAR(255) NOT NULL, genre_desc TEXT ); CREATE TABLE anime_website.user ( user_id SERIAL PRIMARY KEY, user_nm VARCHAR(255) NOT NULL, registration_dt DATE ); CREATE TABLE anime_website.dub_studio ( studio_id SERIAL PRIMARY KEY, studio_nm VARCHAR(255) NOT NULL ); CREATE TABLE anime_website.animedirectors ( animedirectors_id SERIAL PRIMARY KEY, director_id INTEGER NOT NULL, anime_id INTEGER NOT NULL, FOREIGN KEY (director_id) REFERENCES anime_website.director (director_id), FOREIGN KEY (anime_id) REFERENCES anime_website.anime (anime_id) ); CREATE TABLE anime_website.dub ( dub_id SERIAL PRIMARY KEY, studio_id INTEGER NOT NULL, anime_id INTEGER NOT NULL, series_cnt INTEGER, FOREIGN KEY (studio_id) REFERENCES anime_website.dub_studio (studio_id), FOREIGN KEY (anime_id) REFERENCES anime_website.anime (anime_id) ); CREATE TABLE anime_website.view ( view_id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, anime_id INTEGER NOT NULL, dub_id INTEGER, status_nm VARCHAR(127) NOT NULL, from_dt DATE NOT NULL, to_dt DATE, FOREIGN KEY (user_id) REFERENCES anime_website.user (user_id), FOREIGN KEY (anime_id) REFERENCES anime_website.anime (anime_id) ); CREATE TABLE anime_website.animegenres ( animegenres_id SERIAL PRIMARY KEY, genre_id INTEGER NOT NULL, anime_id INTEGER NOT NULL, FOREIGN KEY (genre_id) REFERENCES anime_website.genre (genre_id), FOREIGN KEY (anime_id) REFERENCES anime_website.anime (anime_id) ); ------------------------------- --Заполнение таблиц INSERT INTO anime_website.group (group_nm) VALUES ('Наруто'), ('Блич'), ('Ван-Пис'), ('Семья шпиона'), ('Атака титанов'), ('Стальной алхимик'), ('Врата Штейна'), ('Тетрадь смерти'), ('Код Гиас'), ('Хвост феи'); INSERT INTO anime_website.genre (genre_nm, genre_desc) VALUES ('сёнэн', NULL), ('сёдзе', NULL), ('комедия', NULL), ('приключения', NULL), ('романтика', NULL), ('фантастика', NULL), ('фэнтези', NULL), ('триллер', NULL), ('повседневность', NULL), ('меха', NULL), ('экшен', NULL), ('сверхъестественное', NULL), ('психологическое', NULL), ('драма', NULL); INSERT INTO anime_website.director (director_nm) VALUES ('Хаято Датэ'), ('Хироюки Ямасита'), ('Осаму Кобаяси'), ('Кадзухиро Фурухаси'), ('Мунэхиса Сакай'), ('Кохэй Курэта'), ('Мэгуми Иситани'), ('Коносукэ Уда'), ('Тацуя Нагаминэ'), ('Тэцуро Араки'), ('Сюхэй Ябута'); INSERT INTO anime_website.anime (anime_nm, type_nm, season_nm, released_series_cnt, series_cnt, release_status_nm, anime_desc, anime_rate, release_dt, release_end_dt, rating_value, group_id) VALUES ('Наруто', 'ТВ Сериал', 1, 220, 220, 'Вышел', NULL, 9.2, '2002-10-03', '2007-08-02', 13, 1), ('Наруто: Ураганные хроники', 'ТВ Сериал', 1, 500, 500, 'Вышел', NULL, 8.22, '2007-02-15', '2017-03-23', 13, 1), ('Семья шпиона', 'ТВ Сериал', 1, 5, 12, 'Онгоинг', NULL, 9.09, '2022-04-09', NULL, 13, 4), ('Ван-Пис', 'ТВ Сериал', 1, 1016, NULL, 'Онгоинг', NULL, 8.63, '1999-10-20', NULL, 13, 3), ('Блич', 'ТВ Сериал', 1, 366, 366, 'Вышел', NULL, 7.82, '2004-10-5', '2012-03-27', 13, 2), ('Атака титанов', 'ТВ Сериал', 1, 25, 25, 'Вышел', NULL, 8.52, '2013-04-07', '2013-09-29', 17, 5), ('Атака титанов 2', 'ТВ Сериал', 2, 12, 12, 'Вышел', NULL, 8.49, '2017-04-01', '2017-06-17', 17, 5), ('Стальной алхимик: Братство', 'ТВ Сериал', 1, 64, 64, 'Вышел', NULL, 9.14, '2009-04-05', '2010-07-04', 17, 6), ('Стальной алхимик', 'ТВ Сериал', 1, 51, 51, 'Вышел', NULL, 8.13, '2003-10-04', '2004-10-02', 17, 6), ('Тетрадь смерти', 'ТВ Сериал', 1, 37, 37, 'Вышел', NULL, 8.63, '2006-10-04', '2007-06-27', 17, 8); INSERT INTO anime_website.animedirectors(anime_id,director_id) VALUES (1, 1), (2, 2), (2, 1), (2, 3), (3, 4), (4, 5), (4, 6), (4, 7), (4, 8), (4, 9); INSERT INTO anime_website.dub_studio(studio_nm) VALUES ('AniLibria'), ('AniDUB'), ('2x2'), ('AniStar'), ('JAM CLUB'), ('SHIZA Project'), ('Ancord'), ('AniFilm'), ('AniMedia'); INSERT INTO anime_website.dub (anime_id, studio_id, series_cnt) VALUES (1, 3, 220), (1, 6, 220), (2, 3, 500), (2, 2, 500), (2, 6, 500), (3, 1, 4), (3, 2, 5), (3, 6, 3), (3, 8, 5), (3, 9, 1); INSERT INTO anime_website.animegenres(anime_id, genre_id) VALUES (1, 3), (1, 1), (1, 4), (1, 11), (2, 3), (2, 1), (2, 11), (3, 1), (3, 3), (3, 11), (4, 1), (4, 3), (4, 4), (4, 7), (4, 11), (4, 14); INSERT INTO anime_website.user (user_nm, registration_dt) VALUES ('kriaq1', '2022-01-01'), ('kriaq2', '2022-02-02'), ('kriaq3', '2021-03-03'), ('kriaq4', '2020-04-04'), ('kriaq5', '2022-04-04'), ('kriaq6', '2020-05-05'), ('kriaq7', '2020-06-06'), ('kriaq8', '2022-05-05'), ('kriaq9', '2021-06-06'), ('kriaq10', '2020-07-07'); INSERT INTO anime_website.view (user_id, anime_id, dub_id, status_nm, from_dt, to_dt) VALUES (1, 1, 3, 'Запланировано', '2022-05-05', '2022-05-06'), (1, 1, 3, 'Смотрю', '2022-05-06', NULL), (2, 3, NULL, 'Смотрю', '2022-04-10', NULL), (3, 5, NULL, 'Брошено', '2021-10-10', NULL), (5, 4, NULL, 'Смотрю', '2022-04-05', NULL), (6, 10, NULL, 'Запланировано', '2020-10-03', '2021-01-04'), (6, 10, NULL, 'Смотрю', '2021-01-04', '2021-01-23'), (6, 10, NULL, 'Просмотрено', '2021-01-23', '2022-03-05'), (6, 10, NULL, 'Пересматриваю', '2022-03-05', NULL), (8, 8, NULL, 'Смотрю', '2022-05-06', NULL); UPDATE anime_website.genre SET genre_desc = 'Сёнэн - аниме, манга и ранобэ, рассчитанные на особую целевую аудиторию мальчиков и юношей в возрасте от 12 до 18 лет.' WHERE genre_nm = 'сёнэн'; UPDATE anime_website.genre SET genre_desc = 'Триллер - жанр произведений, нацеленный вызвать у зрителя или читателя чувства тревожного ожидания, волнения или страха.' WHERE genre_nm = 'триллер'; UPDATE anime_website.anime SET anime_desc = 'В день рождения Наруто Узумаки на деревню под названием Коноха напал легендарный демон, Девятихвостый лис. Четвёртый Хокагэ ценой своей жизни спас деревню, запечатав демона в новорождённом Наруто, неосознанно обрекая его на жизнь в ненависти односельчан. Несмотря на недостаток таланта во многих областях ниндзюцу, неусидчивость и задиристость, у Наруто есть мечта — стать Хокагэ, сильнейшим ниндзя в деревне. Желая признания, которого не получал, он упорно работает и тренируется вместе со своими напарниками, Саскэ Учихой и Сакурой Харуно, а также со своим наставником Какаши Хатакэ. Ему и его напарникам придётся пройти через многое по пути к своим заветным мечтам: сражения, любовь, дружба, предательство, жажда силы...' WHERE anime_nm = 'Наруто'; SELECT * FROM anime_website.view WHERE user_id = 1; UPDATE anime_website.view SET to_dt = '2022-05-09' WHERE user_id = 1 AND anime_id = 1 AND to_dt is NULL; INSERT INTO anime_website.view (user_id, anime_id, dub_id, status_nm, from_dt, to_dt) VALUES (1, 1, NULL, 'Брошено', '2022-09-05', NULL); SELECT * FROM anime_website.view WHERE user_id = 1; INSERT INTO anime_website.dub (studio_id, anime_id, series_cnt) VALUES (7,10, 10); DELETE FROM anime_website.dub WHERE studio_id IN (SELECT studio_id FROM anime_website.dub_studio WHERE studio_nm = 'Ancord'); SELECT genre_desc FROM anime_website.genre WHERE genre_nm = 'сёнэн'; INSERT INTO anime_website.genre (genre_nm, genre_desc) VALUES ('детектив', NULL); INSERT INTO anime_website.animegenres (genre_id, anime_id) VALUES ((SELECT genre_id FROM anime_website.genre WHERE genre_nm = 'сёнэн'), (SELECT anime_id FROM anime_website.anime WHERE anime_nm = 'Тетрадь смерти')); ----------------------- -- ORDER BY SELECT status_nm, COUNT(user_id) FROM (anime_website.view INNER JOIN anime_website.anime ON view.anime_id = anime.anime_id) WHERE anime_nm = 'Наруто' AND to_dt is NULL GROUP BY status_nm ORDER BY COUNT(user_id); -- В результате данного запроса для аниме Наруто и для каждого статуса просмотра будет выведено количество -- пользователей, у которых стоит этот статус -- Атрибуты: статус, колво пользователей ----------------------- -- GROUP BY + HAVING SELECT anime_nm FROM (anime_website.view INNER JOIN anime_website.anime ON view.anime_id = anime.anime_id) WHERE to_dt is NULL GROUP BY anime_nm HAVING SUM(CASE WHEN status_nm = 'Брошено' THEN 1 ELSE 0 END) / COUNT(*) > 0.1; -- В результате данного запроса будет выведен список аниме, для которых у более 10% пользователей -- в статусе просмотра этих аниме стоит 'Брошено' -- Атрибуты: название аниме ----------------------- -- GROUP BY SELECT t.user_id, user_nm, count_entries FROM anime_website.user INNER JOIN (SELECT user_id, COUNT(*) AS count_entries FROM anime_website.view GROUP BY user_id) AS t ON anime_website.user.user_id = t.user_id; -- В результате данного запроса для каждого пользователя будет выведено количество записей в таблице view -- Атрибуты: id пользователя, ник и колво записей ----------------------- -- OVER: AVG + PARTITION BY SELECT anime_nm, anime_rate, AVG(anime_rate) OVER(PARTITION BY group_id) FROM anime_website.anime; -- В результате данного запроса для каждого аниме будет выведен его рейтинг и средний рейтинг связанных с ним(в одной группе с ним) -- Атрибуты: название аниме, рейтинг аниме, средний рейтинг ----------------------- -- OVER: RANK + PARTITION BY + OVER SELECT director_nm, genre_nm, RANK() OVER(PARTITION BY director_nm ORDER BY COUNT(*) DESC) FROM (SELECT director.director_nm, anime_id FROM anime_website.director JOIN anime_website.animedirectors ON director.director_id = animedirectors.director_id) as t1 JOIN ((SELECT anime.anime_id, genre_id FROM anime_website.anime JOIN anime_website.animegenres ON anime.anime_id = animegenres.anime_id) as t2 JOIN (SELECT genre.genre_id, genre_nm FROM anime_website.animegenres JOIN anime_website.genre ON animegenres.genre_id = genre.genre_id) as t3 ON t2.genre_id = t3.genre_id) as t4 ON t1.anime_id = t4.anime_id GROUP BY director_nm, genre_nm ORDER BY director_nm; -- В результате данного запроса для каждого режиссера будет отранжирован список жанров по количеству аниме от данного режиссера с таким жанром -- Атрибуты: имя режиссера, наименование жанра, ранг жанра для данного режиссера ----------------------- -- LAG + ORDER BY SELECT *FROM (SELECT LAG(to_dt,1) OVER(ORDER BY to_dt) AS from_dt, from_dt AS to_dt FROM anime_website.view WHERE user_id IN (SELECT user_id FROM anime_website.user WHERE user_nm = 'kriaq1') AND anime_id IN (SELECT anime_id FROM anime_website.anime WHERE anime_nm = 'Наруто')) as t WHERE to_dt !=from_dt; -- В результате данного запроса будут выведены все промежутки времени, в которые у kriaq1 не стоял статус просмотра для аниме Наруто -- Атрибуты: начало промежутка, конец промежутка -- При создании индексов будем исходить из того, что в основном запросы приходят по наваниям -- и таблица anime является основной, и по запросам к ней нужно доставать значения из других таблиц -- также при выборе поля для индекса я исхожу из того, что запросы к такому полю производятся в основном -- поэлементные и возвращаемая таблица небольших размеров CREATE INDEX idx_anime ON anime_website.anime(anime_nm); -- anime_nm, так как в основном поиск по названию аниме ----------------------- CREATE INDEX idx_director ON anime_website.director(director_id); -- director_id, чтобы мы могли уметь доставать для конкретного аниме имена режиссеров ----------------------- CREATE INDEX idx_genre ON anime_website.genre(genre_id); -- genre_id, аналогично можем доставать список жанров по аниме ----------------------- CREATE INDEX idx_animegenres ON anime_website.animegenres(anime_id); -- anime_id, для дальнейшего обращения к таблице genre ----------------------- CREATE INDEX idx_animedirectors ON anime_website.animedirectors(anime_id); -- anime_id, для дальнейшего обращения к таблице director ----------------------- CREATE INDEX idx_group ON anime_website.group(group_nm); -- group_nm для быстрого поиска по названию(по group_id создавать индекс не имеет смысла) ----------------------- CREATE INDEX idx_dub_studio ON anime_website.dub_studio(studio_id); -- studio_id чтобы выводить названия студии озвучек при запросе к таблице anime ----------------------- CREATE INDEX idx_dub ON anime_website.dub(anime_id); -- anime_id аналгично вышеперечисленному, хотим выводить список озвучки для конкретного аниме ----------------------- CREATE INDEX idx_user ON anime_website.user(user_nm); -- user_nm по идее запросы извне к этой таблицы должен быть по имени что бы далее работать с user_id ----------------------- CREATE INDEX idx_view ON anime_website.view(user_id); -- user_id чтобы можно было искать просмотры конкретного пользователя ----------------------- -- Представления -- Представление нынешних статусов просмотров(to_dt is NULL) CREATE VIEW anime_website.current_view_status AS SELECT user_nm, anime_nm, t1.status_nm FROM (SELECT view.user_id, view.anime_id, user_nm,status_nm FROM anime_website.user JOIN anime_website.view ON anime_website.user.user_id = view.user_id WHERE to_dt is NULL) as t1 JOIN anime_website.anime ON t1.anime_id = anime.anime_id; SELECT* FROM anime_website.current_view_status; -- Представление статистики статусов просмотра по аниме CREATE VIEW anime_website.view_status_statistics AS SELECT anime_nm, status_nm, CAST(100 * COUNT(*) / (SUM(COUNT(*)) OVER(PARTITION BY anime_nm)) AS NUMERIC(10,2)) AS percent FROM anime_website.current_view_status GROUP BY anime_nm, status_nm; -- Представление для "главной страницы" CREATE VIEW anime_website.main_page AS SELECT anime_nm ,rating_value, anime_rate, released_series_cnt, type_nm, release_status_nm,STRING_AGG(genre_nm, ', ') FROM (anime_website.genre JOIN anime_website.animegenres ON genre.genre_id = animegenres.genre_id) AS t1 JOIN anime_website.anime ON t1.anime_id = anime.anime_id GROUP BY anime.anime_id ORDER BY MAX(anime_rate) DESC; -- Представление, возвращающее для каждого аниме студии озвучки с наибольшим колвом озвученных серий SELECT anime_nm, studio_nm, series_cnt FROM (SELECT anime_nm, studio_nm, series_cnt,MAX(series_cnt) OVER(PARTITION BY anime_id) AS max_cnt FROM (SELECT anime_nm,anime.anime_id, studio_nm, t3.series_cnt FROM ((anime_website.dub_studio JOIN anime_website.dub ON dub_studio.studio_id = dub.studio_id) as t3 JOIN anime_website.anime ON t3.anime_id = anime.anime_id)) as t1) AS t2 WHERE series_cnt = max_cnt; -- Процедуры CREATE OR REPLACE PROCEDURE anime_website.get_view_list(user_nm VARCHAR(255), status VARCHAR(127)) LANGUAGE SQL AS $$ SELECT 'qwe' FROM anime_website.anime $$; SELECT *FROM(CALL anime_website.get_view_list('kriaq1', 'Смотрю')) AS t;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear