52bcf9d30c
* Task 2+3: complexes.obj_class и district_name из Objective
(350 ЖК с классом, 1525 с district; objective приоритетнее)
* Task 1 (fuzzy match orphan Objective ↔ domrf_kn):
- pg_trgm extension
- auto-merge 14 high-confidence (similarity ≥ 0.85)
- v_complex_match_review для остальных 82 кандидатов
- complexes 1752 → 1740 · cross-source 114 → 124
* Task 4 (naming unification без RENAME COLUMN):
- GENERATED ALWAYS AS (...) STORED для синонимов
- region_id в 15 таблиц + 9 partitions
- developer_id в 7, developer_name в 5
- COMMENT 'DEPRECATED' на region_cd/region_code/dev_id/dev_name
* Task 5 (unified scrape dashboard):
- GET /api/v1/admin/scrape/all/runs + /all/logs (поверх v_scrape_runs_unified)
- new page /admin/scrape/all с filter табами + 4 stat-плитки
- +tab «📊 Все скраперы» в admin layout
Файлы: data/sql/75_unification_naming.sql · backend/app/api/v1/admin_scrape.py
· frontend/src/app/admin/scrape/all/page.tsx · admin/layout.tsx
256 lines
13 KiB
SQL
256 lines
13 KiB
SQL
-- Phase B + D: унификация дублирующихся таблиц через VIEW (UNION ALL).
|
||
--
|
||
-- Стратегия: НЕ дропаем источники (они продолжают писать своими скраперами/
|
||
-- импортёрами), но создаём поверх единые view для удобных запросов из
|
||
-- аналитики и UI. Это zero-risk миграция: можно делать в любой момент
|
||
-- и откатывать DROP VIEW без потери данных.
|
||
--
|
||
-- После того как код в backend перейдёт на use views — старые имена таблиц
|
||
-- можно будет либо превратить в views поверх objединённой структуры, либо
|
||
-- мигрировать данные целиком. Сейчас — promotion path.
|
||
--
|
||
-- Применено в проде через mcp postgres 2026-05-10.
|
||
-- Идемпотентно через CREATE OR REPLACE VIEW.
|
||
|
||
-- ── 1. ekb_districts: merge geom-таблицы в основную ─────────────────────────
|
||
-- ekb_districts (9, dimension с метриками) + ekb_districts_geom (8, polygons)
|
||
-- → одна таблица ekb_districts с PostGIS-колонкой geom
|
||
-- + view-alias ekb_districts_geom для backward-compat.
|
||
|
||
ALTER TABLE ekb_districts
|
||
ADD COLUMN IF NOT EXISTS geom geometry(MultiPolygon, 4326),
|
||
ADD COLUMN IF NOT EXISTS osm_id BIGINT,
|
||
ADD COLUMN IF NOT EXISTS geom_fetched_at timestamptz;
|
||
|
||
UPDATE ekb_districts d
|
||
SET geom = g.geom, osm_id = g.osm_id, geom_fetched_at = g.fetched_at
|
||
FROM ekb_districts_geom g
|
||
WHERE g.district_name = d.district_name;
|
||
|
||
CREATE INDEX IF NOT EXISTS ekb_districts_geom_idx
|
||
ON ekb_districts USING GIST(geom) WHERE geom IS NOT NULL;
|
||
|
||
DROP TABLE IF EXISTS ekb_districts_geom;
|
||
CREATE OR REPLACE VIEW ekb_districts_geom AS
|
||
SELECT district_name, geom, osm_id, geom_fetched_at AS fetched_at
|
||
FROM ekb_districts WHERE geom IS NOT NULL;
|
||
|
||
-- ── 2. v_mortgage_unified — 4 источника ипотечных метрик в long-формате ─────
|
||
|
||
CREATE OR REPLACE VIEW v_mortgage_unified AS
|
||
SELECT
|
||
'cbr' AS source,
|
||
NULL::date AS snapshot_date,
|
||
period AS period_label,
|
||
region,
|
||
NULL::text AS bank_name,
|
||
title AS metric_kind,
|
||
'total' AS segment,
|
||
value,
|
||
NULL::numeric AS delta_pct,
|
||
series_id AS metric_id
|
||
FROM cbr_mortgage_series
|
||
UNION ALL
|
||
SELECT 'domrf_dashboard', snapshot_date, NULL, 'РФ', NULL,
|
||
'avg_rate_pct', 'total', total_credit_avg_rate, total_credit_avg_rate_delta, NULL
|
||
FROM domrf_mortgage_dashboard
|
||
UNION ALL
|
||
SELECT 'domrf_dashboard', snapshot_date, NULL, 'РФ', NULL,
|
||
'avg_rate_pct', 'primary', primary_credit_avg_rate, primary_credit_avg_rate_delta, NULL
|
||
FROM domrf_mortgage_dashboard
|
||
UNION ALL
|
||
SELECT 'domrf_dashboard', snapshot_date, NULL, 'РФ', NULL,
|
||
'avg_rate_pct', 'secondary', secondary_credit_avg_rate, secondary_credit_avg_rate_delta, NULL
|
||
FROM domrf_mortgage_dashboard
|
||
UNION ALL
|
||
SELECT 'domrf_dashboard', snapshot_date, NULL, 'РФ', NULL,
|
||
'credit_count', 'total', total_credit_count::numeric, total_credit_count_delta_pct, NULL
|
||
FROM domrf_mortgage_dashboard
|
||
UNION ALL
|
||
SELECT 'domrf_dashboard', snapshot_date, NULL, 'РФ', NULL,
|
||
'credit_count', 'primary', primary_credit_count::numeric, primary_credit_count_delta_pct, NULL
|
||
FROM domrf_mortgage_dashboard
|
||
UNION ALL
|
||
SELECT 'domrf_dashboard', snapshot_date, NULL, 'РФ', NULL,
|
||
'credit_count', 'secondary', secondary_credit_count::numeric, secondary_credit_count_delta_pct, NULL
|
||
FROM domrf_mortgage_dashboard
|
||
UNION ALL
|
||
SELECT 'domrf_dashboard', snapshot_date, NULL, 'РФ', NULL,
|
||
'credit_amount_rub', 'total', total_credit_amount, total_credit_amount_delta_pct, NULL
|
||
FROM domrf_mortgage_dashboard
|
||
UNION ALL
|
||
SELECT 'domrf_dashboard', snapshot_date, NULL, 'РФ', NULL,
|
||
'credit_amount_rub', 'primary', primary_credit_amount, primary_credit_amount_delta_pct, NULL
|
||
FROM domrf_mortgage_dashboard
|
||
UNION ALL
|
||
SELECT 'domrf_dashboard', snapshot_date, NULL, 'РФ', NULL,
|
||
'credit_amount_rub', 'secondary', secondary_credit_amount, secondary_credit_amount_delta_pct, NULL
|
||
FROM domrf_mortgage_dashboard
|
||
UNION ALL
|
||
SELECT 'domrf_details', snapshot_date, NULL, 'РФ', NULL,
|
||
'credit_amount_avg_rub', currency, credit_amount_avg, credit_amount_avg_delta_pct, NULL
|
||
FROM domrf_mortgage_details
|
||
UNION ALL
|
||
SELECT 'domrf_details', snapshot_date, NULL, 'РФ', NULL,
|
||
'credit_avg_period_months', currency, credit_avg_period, credit_avg_period_delta_pct, NULL
|
||
FROM domrf_mortgage_details
|
||
UNION ALL
|
||
SELECT 'domrf_details', snapshot_date, NULL, 'РФ', NULL,
|
||
'credit_debts_amount_rub', currency, credit_debts_amount, credit_debts_amount_delta_pct, NULL
|
||
FROM domrf_mortgage_details
|
||
UNION ALL
|
||
SELECT 'domrf_details', snapshot_date, NULL, 'РФ', NULL,
|
||
'overdue_pct', currency, credit_debts_overdue_percent, credit_debts_overdue_percent_delta, NULL
|
||
FROM domrf_mortgage_details
|
||
UNION ALL
|
||
SELECT 'domrf_rates', snapshot_date, NULL, 'РФ', bank_name,
|
||
'rate_pct', 'primary', primary_rate, NULL, NULL FROM domrf_mortgage_rates
|
||
UNION ALL
|
||
SELECT 'domrf_rates', snapshot_date, NULL, 'РФ', bank_name,
|
||
'rate_pct', 'secondary', secondary_rate, NULL, NULL FROM domrf_mortgage_rates
|
||
UNION ALL
|
||
SELECT 'domrf_rates', snapshot_date, NULL, 'РФ', bank_name,
|
||
'rate_pct', 'refinance', refinance_rate, NULL, NULL FROM domrf_mortgage_rates;
|
||
|
||
COMMENT ON VIEW v_mortgage_unified IS
|
||
'Long-формат всех 4 источников ипотечных метрик. metric_kind: avg_rate_pct '
|
||
'/ credit_count / credit_amount_rub / overdue_pct / etc. segment: primary / '
|
||
'secondary / total. ~3385 rows total. Источники не дропаются.';
|
||
|
||
-- ── 3. v_construction_metrics_unified — 5 launch/commissioning ──────────────
|
||
|
||
CREATE OR REPLACE VIEW v_construction_metrics_unified AS
|
||
SELECT 'planned_commissioning' AS source, snapshot_date, plan_year::text AS period_label,
|
||
'РФ' AS region, 'planned_total_th_sqm' AS metric_kind,
|
||
NULL::text AS breakdown_kind, NULL::text AS breakdown_value,
|
||
total_th_sqm AS value,
|
||
jsonb_build_object('escrow', escrow_th_sqm, 'fund', fund_th_sqm,
|
||
'no_attraction', no_attraction_th_sqm) AS extra
|
||
FROM domrf_planned_commissioning
|
||
UNION ALL
|
||
SELECT 'commissioning', snapshot_date, rep_year::text, region_name,
|
||
'fact_area_total', NULL, NULL, accumulated_fact_area,
|
||
jsonb_build_object('multifamily', accumulated_fact_area_multifamily,
|
||
'private', accumulated_fact_area_private,
|
||
'change', accumulated_fact_area_change,
|
||
'change_share', accumulated_fact_area_change_share)
|
||
FROM domrf_commissioning
|
||
UNION ALL
|
||
SELECT 'launch_monthly', snapshot_date,
|
||
(rep_year::text || '-' || LPAD(rep_month::text, 2, '0')),
|
||
'РФ', 'launch_app_value', 'calc_type', calc_type, app_value,
|
||
jsonb_build_object('rnv_value', rnv_value)
|
||
FROM domrf_launch_monthly
|
||
UNION ALL
|
||
SELECT 'launch_monthly', snapshot_date,
|
||
(rep_year::text || '-' || LPAD(rep_month::text, 2, '0')),
|
||
'РФ', 'launch_rnv_value', 'calc_type', calc_type, rnv_value, NULL
|
||
FROM domrf_launch_monthly
|
||
UNION ALL
|
||
SELECT 'launch_top', snapshot_date, rep_year::text, entity_name,
|
||
metric_type, dim_type, entity_id, value,
|
||
jsonb_build_object('calc_type', calc_type)
|
||
FROM domrf_launch_top
|
||
UNION ALL
|
||
SELECT 'launch_obj_class', snapshot_date, rep_year::text, 'РФ',
|
||
'launch_app_value', 'obj_class', obj_class_desc, app_value,
|
||
jsonb_build_object('rnv_value', rnv_value, 'calc_type', calc_type,
|
||
'obj_class_cd', obj_class_cd)
|
||
FROM domrf_launch_obj_class
|
||
UNION ALL
|
||
SELECT 'launch_obj_class', snapshot_date, rep_year::text, 'РФ',
|
||
'launch_rnv_value', 'obj_class', obj_class_desc, rnv_value,
|
||
jsonb_build_object('calc_type', calc_type)
|
||
FROM domrf_launch_obj_class;
|
||
|
||
COMMENT ON VIEW v_construction_metrics_unified IS
|
||
'Long-формат для 5 таблиц про launch/commissioning. metric_kind: '
|
||
'planned_total_th_sqm / fact_area_total / launch_app_value / launch_rnv_value. '
|
||
'breakdown_kind: calc_type / obj_class / dim_type. ~1579 rows.';
|
||
|
||
-- ── 4. v_sold_ready_unified — 4 sold_ready / sold_out ──────────────────────
|
||
|
||
CREATE OR REPLACE VIEW v_sold_ready_unified AS
|
||
SELECT 'sold_out' AS source, snapshot_date, NULL::text AS period_label,
|
||
territory_name AS scope, NULL::text AS breakdown_kind, NULL::text AS breakdown_value,
|
||
'sold_pct' AS metric_kind, sold_pct AS value,
|
||
jsonb_build_object('total_area_th_sqm', total_area_th_sqm,
|
||
'sold_area_th_sqm', sold_area_th_sqm,
|
||
'unsold_pct', unsold_pct,
|
||
'avg_price_per_sqm', avg_price_per_sqm,
|
||
'attracted_funds_mln_rub', attracted_funds_mln_rub) AS extra
|
||
FROM domrf_sold_out
|
||
UNION ALL
|
||
SELECT 'sold_ready_index', snapshot_date,
|
||
(rep_year::text || '-' || LPAD(rep_month::text, 2, '0')),
|
||
'РФ', NULL, NULL, 'sold_ready_perc', sold_ready_perc,
|
||
jsonb_build_object('square_sum', square_sum, 'sold_sum', sold_sum,
|
||
'sold_perc', sold_perc, 'ready_perc', ready_perc)
|
||
FROM domrf_sold_ready_index
|
||
UNION ALL
|
||
SELECT 'sold_ready_dynamics', snapshot_date,
|
||
(rep_year::text || '-' || LPAD(rep_month::text, 2, '0')),
|
||
'РФ', 'dynamic_chart_type', dynamic_chart_type,
|
||
dynamic_chart_type, value, NULL
|
||
FROM domrf_sold_ready_dynamics
|
||
UNION ALL
|
||
SELECT 'sold_ready_breakdown', snapshot_date,
|
||
(rep_year::text || '-' || LPAD(COALESCE(rep_month, 0)::text, 2, '0')),
|
||
COALESCE(NULLIF(dev_group_value, ''), 'РФ'),
|
||
chart_type, entity_key, 'sold_perc', sold_perc,
|
||
jsonb_build_object('ready_perc', ready_perc, 'sold_ready_perc', sold_ready_perc,
|
||
'square_sum', square_sum, 'fo_cd', fo_cd, 'region_cd', region_cd,
|
||
'obj_class', obj_class, 'ready_year', ready_year,
|
||
'city_population', city_population)
|
||
FROM domrf_sold_ready_breakdown;
|
||
|
||
COMMENT ON VIEW v_sold_ready_unified IS
|
||
'Long-формат 4 sold_ready таблиц. metric_kind: sold_pct / sold_ready_perc / '
|
||
'<dynamic_chart_type>. Big source: sold_ready_breakdown (~39k) — фильтруй '
|
||
'по chart_type/entity_key. Total: ~42858 rows.';
|
||
|
||
-- ── 5. v_scrape_runs_unified — 3 скрапера в одном журнале ───────────────────
|
||
|
||
CREATE OR REPLACE VIEW v_scrape_runs_unified AS
|
||
SELECT 'kn' AS scraper_type, run_id, started_at, finished_at, heartbeat_at,
|
||
status, error, NULL::text AS triggered_by,
|
||
region_codes::text AS scope, requests_count,
|
||
objects_count AS items_ok, NULL::integer AS items_failed,
|
||
flats_count AS sub_items_ok,
|
||
progress_obj_index AS progress_idx, total_obj_count AS progress_total,
|
||
jsonb_build_object('developers', developer_ids, 'snapshot_date', snapshot_date,
|
||
'resumed_from', resumed_from_run_id, 'params', params) AS extra
|
||
FROM kn_scrape_runs
|
||
UNION ALL
|
||
SELECT 'nspd', run_id, started_at, finished_at, heartbeat_at, status, error,
|
||
triggered_by, region_code::text, requests_count,
|
||
quarters_ok, quarters_failed, buildings_ok,
|
||
NULL, pending_count,
|
||
jsonb_build_object('waf_429_count', waf_429_count)
|
||
FROM nspd_scrape_runs
|
||
UNION ALL
|
||
SELECT 'objective', run_id, started_at, finished_at, heartbeat_at, status, error,
|
||
triggered_by, group_name, requests_count,
|
||
reports_ok, reports_failed, rows_lots,
|
||
NULL, NULL,
|
||
jsonb_build_object('rows_corpus_room', rows_corpus_room, 'rows_history', rows_history)
|
||
FROM objective_scrape_runs;
|
||
|
||
COMMENT ON VIEW v_scrape_runs_unified IS
|
||
'Унифицированный журнал прогонов для всех 3 скраперов. Полиморфные общие '
|
||
'поля (items_ok / sub_items_ok / scope) — для UI; специфичные — в extra '
|
||
'jsonb. Используй для общего admin /scrape dashboard вместо 3 отдельных endpoint-ов.';
|
||
|
||
-- ── 6. v_scrape_log_unified — kn + nspd логи ────────────────────────────────
|
||
|
||
CREATE OR REPLACE VIEW v_scrape_log_unified AS
|
||
SELECT 'kn' AS scraper_type, log_id, run_id, ts, level, stage,
|
||
obj_id::text AS entity_id, message
|
||
FROM kn_scrape_log
|
||
UNION ALL
|
||
SELECT 'nspd', log_id, run_id, ts, level, stage, cad_number, message
|
||
FROM nspd_scrape_log;
|
||
|
||
COMMENT ON VIEW v_scrape_log_unified IS
|
||
'Per-step log двух скраперов. entity_id = obj_id для kn / cad_number для nspd. '
|
||
'objective scraper пока не пишет log — добавим если понадобится debug.';
|