Функции в PostgreSQL. Проектирование структуры БД хоккейной лиги, страница 6

SELECT add_player('kolya', 'lenin', '19931007', 'sochi', 165, 60, 'beginner');

SELECT add_player('maxim', 'engels', '19830105', 'sochi', 165, 80, 'candidate');

SELECT add_player('anton', 'elkin', '19740410', 'omsk', 195, 100, 'master');

SELECT add_player('mihail', 'palkin', '19780309', 'tomsk', 187, 98, 'master');

SELECT add_player('sashka', 'borodach', '19880910', 'grozny', 176, 78, 'candidate');

SELECT add_player('dimko', 'koromshuk', '19910706', 'novosibirsk', 168, 68, 'beginner');

SELECT add_player('fedya', 'putin', '19610504', 'kemerovo', 173, 77, 'master');

SELECT add_player('borya', 'medvedev', '19690403', 'magnitogorsk', 164, 88, 'master');

SELECT add_player('vitya', 'brezhnev', '19861110', 'ekaterinburg', 194, 99, 'candidate');

SELECT add_player('tahir', 'stalin', '19871011', 'krasnoyarsk', 168, 78, 'candidate');

SELECT add_team('sibir', 'novosibirsk');

SELECT add_team('metallurg', 'magnitogorsk');

SELECT add_team('severstal', 'ekaterinburg');

SELECT add_team('amur', 'omsk');

SELECT add_player_to_team(1, 1, 'goalkeeper', 1);          

SELECT add_player_to_team(2, 1, 'defender', 5);

SELECT add_player_to_team(3, 1, 'forward', 7);

SELECT add_player_to_team(4, 2, 'goalkeeper', 2);

SELECT add_player_to_team(5, 2, 'defender', 4);

SELECT add_player_to_team(6, 2, 'forward', 8);

SELECT add_player_to_team(7, 3, 'goalkeeper', 3);

SELECT add_player_to_team(8, 3, 'defender', 7);

SELECT add_player_to_team(9, 3, 'forward', 6);

SELECT add_player_to_team(10, 4, 'goalkeeper', 2);

SELECT add_player_to_team(11, 4, 'defender', 6);

SELECT add_player_to_team(12, 4, 'forward', 9);

SELECT add_game('20111001', 'novosibirsk', 1, 2, round(random()*10^6)::INT, round(random()*10^3)::INT, round(random()*10^4)::INT);

SELECT add_game('20111006', 'sochi', 1, 3, round(random()*10^6)::INT, round(random()*10^3)::INT, round(random()*10^4)::INT);

SELECT add_game('20111008', 'omsk', 1, 4, round(random()*10^6)::INT, round(random()*10^3)::INT, round(random()*10^4)::INT);

SELECT add_game('20111010', 'grozny', 2, 3, round(random()*10^6)::INT, round(random()*10^3)::INT, round(random()*10^4)::INT);

SELECT add_game('20111011', 'tomsk', 2, 4, round(random()*10^6)::INT, round(random()*10^3)::INT, round(random()*10^4)::INT);

SELECT add_game('20111015', 'irkutsk', 3, 4, round(random()*10^6)::INT, round(random()*10^3)::INT, round(random()*10^4)::INT);         

SELECT fill();

--Содержаниетаблиц

SELECT * FROM players;

SELECT * FROM games;

SELECT * FROM teams;

SELECT * FROM city;

SELECT * FROM stats;

SELECT * FROM log;

--Анализ популярности команд за последний месяц

--Примечание: команда считается наиболее популярной, если отношение числа

--проданных билетов за все игры команды в указанный период к общему числу билетов

--максимально.

CREATE VIEW teams_rating

AS

            SELECT

                        team_id,

                        SUM(tickets) AS tickets,

                        SUM(sold_tickets) AS sold_tickets,

                        cast(SUM(sold_tickets) AS real) / cast(SUM(tickets) AS real) AS percent

            FROM

                        (SELECT

                                    team1_id AS team_id,

                                    tickets,

                                    sold_tickets

                        FROM games

                        WHERE

                                    games.day BETWEEN '20111001' AND '20111031'

                        UNION

                        SELECT

                                    team2_id AS team_id,

                                    tickets,

                                    sold_tickets

                        FROM games

                        WHERE

                                    games.day BETWEEN '20111001' AND '20111031'