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

sql_labs/lab5_views.sql
161 строка8 KB
artem
Небольшие исправления SQL-лабораторных
15 июн 2026, 01:49
15 июн 2026, 01:49c75995d
-- =========================================================================
-- ЛАБОРАТОРНАЯ №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_ads_in_price_range AS
        SELECT id, title, city, price, created_at FROM realty_advertisement WHERE price BETWEEN 40000 AND 80000 AND is_active = true
    $sql$;

    -- 4. Все доступные объявления, по которым можно написать автору
    EXECUTE $sql$
        CREATE OR REPLACE TEMP VIEW tv_available_ads AS
        SELECT a.id, a.title, a.city, a.price, u.email AS owner_email
        FROM realty_advertisement a JOIN realty_user u ON u.id = a.user_id
        WHERE a.is_active = true
    $sql$;

    -- 5. Пользователи с заявками на поиск соседа за ближайший/текущий месяц
    EXECUTE $sql$
        CREATE OR REPLACE TEMP VIEW tv_neighbor_seekers_month 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 BETWEEN NOW() - INTERVAL '1 month' AND NOW() + INTERVAL '1 month'
    $sql$;

    -- 7. Объявления с низким интересом: меньше 50 просмотров
    EXECUTE $sql$
        CREATE OR REPLACE TEMP VIEW tv_low_interest_ads AS
        SELECT id, title, city, price, views_count
        FROM realty_advertisement
        WHERE is_active = true AND views_count < 50
    $sql$;

    -- 8. Города с количеством архивных объявлений
    EXECUTE $sql$
        CREATE OR REPLACE TEMP VIEW tv_cities_with_archived_ads AS
        SELECT city, COUNT(*) AS archived_ads_count
        FROM realty_advertisement
        WHERE is_active = false
        GROUP BY city
    $sql$;

    -- 10. Пользователи с активностью в объявлениях за ближайшие 7 дней от текущей даты
    EXECUTE $sql$
        CREATE OR REPLACE TEMP VIEW tv_users_with_ads_7_days AS
        SELECT DISTINCT u.id, u.email, a.title, a.created_at
        FROM realty_user u JOIN realty_advertisement a ON a.user_id = u.id
        WHERE a.created_at BETWEEN NOW() - INTERVAL '7 days' AND 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;