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

                        ) AS t

            GROUP BY team_id

            ORDER BY percent DESC;

SELECT * FROM teams_rating;

--Анализ эффективности и рейтинг игроков за последний месяц

--Примечание: игрок А считается более эффективным, чем игрок B, если в указанный

--период уигрока А больше голов и ассистов, чем у игрока B

CREATE VIEW players_rating

AS

            SELECT

                        row_number() over(ORDER BY goals DESC, assists DESC),

                        players.player_id,

                        players.name,

                        players.surname,

                        t.goals,

                        t.assists

            FROM

                        (SELECT

                                    player_id,

                                    SUM(goals) AS goals,

                                    SUM(assists) AS assists

                        FROM stats

                        INNER JOIN games ON games.game_id = stats.game_id

                        WHERE

                                    games.day BETWEEN '20111001' AND '20111031'

                        GROUP BY player_id) AS t

            INNER JOIN players

            ON players.player_id = t.player_id

            ORDER BY goals DESC, assists DESC;

SELECT * FROM players_rating;

--Созданиепользователей

--Оператор БД. Может пополнять и просматривать таблицы-справочники

CREATE USER bdoperator WITH PASSWORD '0000' VALID UNTIL 'infinity';

GRANT EXECUTE ON FUNCTION

            add_city(VARCHAR(20)),

            add_team(VARCHAR(20), VARCHAR(20)),

            edit_team(INT, VARCHAR(20), VARCHAR(20)),

add_player(VARCHAR(20),VARCHAR(20),DATE,VARCHAR(20),INT,INT,exp),

edit_player(INT,VARCHAR(20),VARCHAR(20),DATE,VARCHAR(20),INT,INT,exp),

            set_income(INT,INT,INT,INT)

TO bdoperator;

GRANT SELECT ON

            city,

            teams,

            players

TO bdoperator;

--Пользователь БД. Может вызывать все функции и просматривать почти все таблицы

CREATE USER bduser WITH PASSWORD '0000' VALID UNTIL 'infinity';

GRANT EXECUTE ON FUNCTION

            add_city(VARCHAR(20)),

            remove_city(VARCHAR(20)),

            add_team(VARCHAR(20), VARCHAR(20)),

            edit_team(INT, VARCHAR(20), VARCHAR(20)),

            remove_team(INT),

add_player(VARCHAR(20),VARCHAR(20),DATE,VARCHAR(20),INT,INT,exp),

edit_player(INT,VARCHAR(20),VARCHAR(20),DATE,VARCHAR(20),INT,INT,exp),

            remove_player(INT),

            set_income(INT,INT,INT,INT),

            add_player_to_team(INT,INT,pos,INT),

            add_game(DATE,VARCHAR(20),INT,INT,INT,INT,INT),

            remove_game(INT),

            sell_tickets(INT, INT),

            edit_statistics(INT,INT,INT,INT,INT,INT)

TO bduser;

GRANT SELECT ON

            city,

            teams,

            players,

            games,

            stats

TO bduser;

--Аналитик. Имеет право выполнять любые запросы

CREATE USER bdanalyst WITH PASSWORD '0000' VALID UNTIL 'infinity';

GRANT SELECT ON

            city,

            teams,

            players,

            games,

            stats,

            team_rating,

            players_rating

TO bdanalyst;

--Администратор. Имеет все права, в том числе просмотр и изменение журнала

CREATE USER bdadmin WITH PASSWORD '0000' VALID UNTIL 'infinity';

GRANT ALL PRIVILEGES ON

            log,

            city,

            teams,

            players,

            games,

            stats,

            team_rating,

            players_rating

TO bdadmin WITH GRANT OPTION;

Выводы

В результате выполнения лабораторной работы я познакомился с командами создания функций в PostgreSQL, с языком PL/pgSQL, спроектировал готовую БД с использованием всех полученных навыков и знаний.