sql_labs/lab5_views.sql - master - nmktth/web-development-sem-4-advanced-git

sql_labs/lab5_views.sql
150 строк8 KB
Artem Ilin
first_commit
14 июн 2026, 21:45
14 июн 2026, 21:4567055a5
-- =========================================================================
-- ЛАБОРАТОРНАЯ №5 (ЧАСТЬ 2): ПРЕДСТАВЛЕНИЯ (VIEWS)
-- =========================================================================

-- 1. Вьюха для активных объявлений со статой по жалобам (добавляем JOIN).
CREATE OR REPLACE VIEW v_active_ads_extended AS
SELECT 
    a.id, a.title, a.city, u.email as author_email, a.price, a.created_at,
    COALESCE(c.complaints_count, 0) as complaints_count
FROM realty_advertisement a
JOIN realty_user u ON u.id = a.user_id
LEFT JOIN (
    SELECT content_id, COUNT(*) as complaints_count 
    FROM realty_complaint 
    WHERE content_type = 'advertisement' GROUP BY content_id
) c ON c.content_id = a.id
WHERE a.is_active = true;

-- =========================================================================
-- ПРОЦЕДУРА ГЕНЕРАЦИИ ТЕМПОВЫХ ПРЕДСТАВЛЕНИЙ (TEMP VIEWS)
-- Генерит временные вьюхи, они живут только пока открыта сессия.
-- =========================================================================
CREATE OR REPLACE PROCEDURE lab5_create_temp_views()
LANGUAGE plpgsql
AS $$
BEGIN
    -- 2. Средние цены по городам (чтобы не писать GROUP BY каждый раз)
    EXECUTE $sql$
        CREATE OR REPLACE TEMP VIEW tv_city_type_avg_price AS
        SELECT city, type, COUNT(id) AS ads_count, ROUND(AVG(price)::NUMERIC, 2) AS avg_price
        FROM realty_advertisement WHERE is_active = true GROUP BY city, type
    $sql$;

    -- 3. Выборка премиум сегмента
    EXECUTE $sql$
        CREATE OR REPLACE TEMP VIEW tv_premium_ads AS
        SELECT id, title, city, price, created_at FROM realty_advertisement WHERE price > 100000 AND is_active = true
    $sql$;

    -- 4. Популярные объявления (залетели в тренды)
    EXECUTE $sql$
        CREATE OR REPLACE TEMP VIEW tv_popular_ads AS
        SELECT id, title, city, views_count FROM realty_advertisement WHERE views_count > 100 AND is_active = true
    $sql$;

    -- 5. Юзеры, которые ищут соседей прям сейчас
    EXECUTE $sql$
        CREATE OR REPLACE TEMP VIEW tv_recent_neighbor_seekers AS
        SELECT u.email, n.city, n.created_at FROM realty_user u JOIN realty_neighborrequest n ON u.id = n.user_id WHERE n.created_at >= NOW() - INTERVAL '1 month'
    $sql$;

    -- 7. Токсичные объявления (на которые отклонили кучу жалоб)
    EXECUTE $sql$
        CREATE OR REPLACE TEMP VIEW tv_problematic_ads AS
        SELECT a.id, a.title, COUNT(c.id) as rejected_complaints FROM realty_advertisement a JOIN realty_complaint c ON a.id = c.content_id AND c.content_type = 'advertisement' WHERE c.status = 'rejected' GROUP BY a.id, a.title HAVING COUNT(c.id) > 2
    $sql$;

    -- 8. Юзеры-спамеры, чьи репорты отбраковывают
    EXECUTE $sql$
        CREATE OR REPLACE TEMP VIEW tv_users_with_rejected_complaints AS
        SELECT u.id, u.email, COUNT(c.id) as rejected_count FROM realty_user u LEFT JOIN realty_complaint c ON u.id = c.complainant_id WHERE c.status = 'rejected' GROUP BY u.id, u.email
    $sql$;

    -- 10. Свежие неотвеченные сообщения
    EXECUTE $sql$
        CREATE OR REPLACE TEMP VIEW tv_unread_recent_messages AS
        SELECT m.id, u.email as sender, m.sent_at FROM realty_message m JOIN realty_user u ON u.id = m.sender_id WHERE m.is_read = false AND m.sent_at >= NOW() - INTERVAL '7 days'
    $sql$;
END;
$$;


-- =========================================================================
-- МАТЕРИАЛИЗОВАННЫЕ ПРЕДСТАВЛЕНИЯ (MATERIALIZED VIEWS)
-- Сохраняет результат на диск. Помогает для тяжелой аналитики, чтобы не класть базу.
-- =========================================================================

-- 6. Аналитика объявлений по сезонам (зима, весна...)
DROP MATERIALIZED VIEW IF EXISTS mv_ads_by_season;
CREATE MATERIALIZED VIEW mv_ads_by_season AS
SELECT 
    CASE
        WHEN EXTRACT(MONTH FROM created_at) IN (12, 1, 2) THEN 'winter'
        WHEN EXTRACT(MONTH FROM created_at) IN (3, 4, 5) THEN 'spring'
        WHEN EXTRACT(MONTH FROM created_at) IN (6, 7, 8) THEN 'summer'
        ELSE 'autumn'
    END AS season_name,
    city, COUNT(*) AS total_ads, ROUND(AVG(price)::NUMERIC, 2) AS avg_price
FROM realty_advertisement GROUP BY 1, 2;

-- 9. Стата по дням недели (в какой день чаще постят)
DROP MATERIALIZED VIEW IF EXISTS mv_activity_by_weekday;
CREATE MATERIALIZED VIEW mv_activity_by_weekday AS
SELECT
    EXTRACT(ISODOW FROM created_at)::INTEGER AS iso_weekday,
    TRIM(TO_CHAR(created_at, 'Day')) AS weekday_name,
    COUNT(*) AS new_ads_count, ROUND(AVG(price)::NUMERIC, 2) AS avg_price
FROM realty_advertisement GROUP BY 1, 2;

-- 11. Средний прайс по городу и типу жилья
DROP MATERIALIZED VIEW IF EXISTS mv_avg_price_by_route;
CREATE MATERIALIZED VIEW mv_avg_price_by_route AS
SELECT city, type, deal_type, COUNT(*) as ads_count, ROUND(AVG(price), 2) AS avg_price
FROM realty_advertisement WHERE is_active = true GROUP BY city, type, deal_type;

-- 12. Топ самых загруженных городов по просмотрам
DROP MATERIALIZED VIEW IF EXISTS mv_busiest_cities;
CREATE MATERIALIZED VIEW mv_busiest_cities AS
SELECT 
    a.city, COUNT(DISTINCT a.id) AS ads_count, COALESCE(SUM(a.views_count), 0) AS total_views, COUNT(DISTINCT n.id) AS neighbor_requests_count
FROM realty_advertisement a LEFT JOIN realty_neighborrequest n ON n.city = a.city
GROUP BY a.city ORDER BY total_views DESC;

-- Процедура для рефреша мат-вьюх (чтобы обновить кэш)
CREATE OR REPLACE PROCEDURE lab5_refresh_materialized_views()
LANGUAGE plpgsql
AS $$
BEGIN
    REFRESH MATERIALIZED VIEW mv_ads_by_season;
    REFRESH MATERIALIZED VIEW mv_activity_by_weekday;
    REFRESH MATERIALIZED VIEW mv_avg_price_by_route;
    REFRESH MATERIALIZED VIEW mv_busiest_cities;
END;
$$;

-- =========================================================================
-- РЕКУРСИВНЫЕ ПРЕДСТАВЛЕНИЯ (RECURSIVE VIEWS)
-- Рекурсия, генерит последовательности и все такое.
-- =========================================================================

-- 13.1. Генерация последовательности от 1 до 100
CREATE OR REPLACE RECURSIVE VIEW rv_numbers_1_10 (n) AS
    SELECT 1 UNION ALL SELECT n + 1 FROM rv_numbers_1_10 WHERE n < 100;

-- 13.2. Генерация календарика (даты на 30 дней вперед)
CREATE OR REPLACE RECURSIVE VIEW rv_next_30_days (step_no, calendar_day) AS
    SELECT 1, CURRENT_DATE UNION ALL SELECT step_no + 1, calendar_day + 1 FROM rv_next_30_days WHERE step_no < 30;

-- 13.3. Лестница торга (скидываем цену по 5% за шаг)
CREATE OR REPLACE RECURSIVE VIEW rv_price_drops (step, current_price) AS
    SELECT 1, 100000.0 UNION ALL SELECT step + 1, current_price * 0.95 FROM rv_price_drops WHERE current_price > 70000.0;

-- 13.4. Иерархия вычислений (факториал)
CREATE OR REPLACE RECURSIVE VIEW rv_factorial (n, fact) AS
    SELECT 1, 1 UNION ALL SELECT n + 1, fact * (n + 1) FROM rv_factorial WHERE n < 10;

-- 13.5. Почасовые слоты (например, для показа рекламы)
CREATE OR REPLACE RECURSIVE VIEW rv_ad_show_hours (window_id, hour_point, slot_end) AS
    SELECT 1, date_trunc('hour', NOW()), date_trunc('hour', NOW() + INTERVAL '24 hours')
    UNION ALL SELECT window_id, hour_point + INTERVAL '1 hour', slot_end FROM rv_ad_show_hours WHERE hour_point + INTERVAL '1 hour' < slot_end;