93 lines
5 KiB
PL/PgSQL
93 lines
5 KiB
PL/PgSQL
-- 165_velocity_mapping_reviewed_gate.sql
|
||
-- #307 OBJ-2 — gate objective_complex_mapping by confidence в mv_layout_velocity.
|
||
--
|
||
-- Контекст: fuzzy-trgm backfill (#1331/#1333, миграции 155/156/116/117/150) добавил
|
||
-- ~115+ auto-matched строк в objective_complex_mapping с is_reviewed=false и низким
|
||
-- match_score (вплоть до 0.50-0.625). Они с высокой вероятностью false-positive
|
||
-- (Objective project name ≠ тот же ЖК что domrf_obj_id) и искажали velocity-агрегаты
|
||
-- Site Finder, попадая в MV наравне с проверенными маппингами.
|
||
--
|
||
-- Fix: redefine MV с confidence-gate в JOIN — принимаем маппинг только если
|
||
-- is_reviewed = TRUE (человек подтвердил), ИЛИ
|
||
-- match_method = 'manual' (ручной маппинг, score обычно NULL), ИЛИ
|
||
-- match_score >= 0.85 (AUTO_ACCEPT_THRESHOLD из objective_backfill.py —
|
||
-- high-confidence auto, fuzzy_trgm 0.85+ надёжен).
|
||
--
|
||
-- Строгий gate только на is_reviewed=true дал бы 2 строки из 303 EKB-маппингов →
|
||
-- обнулил бы velocity. Порог 0.85 сохраняет ~264/303, отбрасывая ~39 низкоуверенных.
|
||
-- Тот же предикат применён в runtime-пути backend/app/services/site_finder/velocity.py.
|
||
--
|
||
-- Сохраняет weighted-average формулу из #21 (100_fix_mv_layout_velocity_weighted_avg.sql) —
|
||
-- меняется ТОЛЬКО WHERE/JOIN-условие на objective_complex_mapping.
|
||
--
|
||
-- Dependencies: mv_layout_velocity не имеет зависимых VIEW/MV (проверено 2026-05-17/100_fix).
|
||
-- Idempotency: DROP IF EXISTS → безопасен при повторном запуске.
|
||
-- Deploy order: SQL migration → app deploy. Auto-applied через _schema_migrations.
|
||
-- ВАЖНО: DROP + CREATE + REFRESH (non-concurrent, блокировка ~30 с на ~19 738 source rows).
|
||
|
||
BEGIN;
|
||
|
||
DROP MATERIALIZED VIEW IF EXISTS mv_layout_velocity CASCADE;
|
||
|
||
CREATE MATERIALIZED VIEW mv_layout_velocity AS
|
||
WITH last24mo AS (
|
||
SELECT
|
||
ocm.project_name,
|
||
CASE
|
||
WHEN ocm.room_bucket = 'студия' THEN 'studio'
|
||
ELSE ocm.room_bucket
|
||
END AS room_bucket,
|
||
ocm.deals_total_count,
|
||
ocm.deals_total_avg_area_m2,
|
||
ocm.deals_total_avg_price_thousand_rub_per_m2,
|
||
ocm.deals_total_vol_m2,
|
||
ocm.report_month
|
||
FROM objective_corpus_room_month ocm
|
||
WHERE ocm.report_month >= (NOW() - INTERVAL '24 months')::date
|
||
)
|
||
SELECT
|
||
cm.domrf_obj_id AS obj_id,
|
||
l.room_bucket,
|
||
SUM(l.deals_total_count)::int AS total_deals_24mo,
|
||
|
||
-- weighted average (#21) — нули из месяцев без сделок не тянут вниз.
|
||
(SUM(l.deals_total_avg_area_m2 * l.deals_total_count)
|
||
/ NULLIF(SUM(l.deals_total_count), 0))::numeric(10, 2) AS avg_area_m2,
|
||
|
||
(SUM(l.deals_total_avg_price_thousand_rub_per_m2 * l.deals_total_count)
|
||
/ NULLIF(SUM(l.deals_total_count), 0))::numeric(12, 2) AS avg_price_thousand_rub_per_m2,
|
||
|
||
SUM(l.deals_total_vol_m2)::numeric(12, 2) AS total_vol_m2,
|
||
MIN(l.report_month) AS window_start,
|
||
MAX(l.report_month) AS window_end,
|
||
COUNT(DISTINCT l.report_month)::int AS months_with_data
|
||
FROM last24mo l
|
||
JOIN objective_complex_mapping cm
|
||
ON cm.objective_complex_name = l.project_name
|
||
WHERE l.room_bucket IS NOT NULL
|
||
AND cm.domrf_obj_id IS NOT NULL
|
||
AND cm.objective_group = 'Екатеринбург'
|
||
-- #307 OBJ-2: confidence-gate — исключаем unreviewed low-score auto-matches.
|
||
AND (cm.is_reviewed = TRUE OR cm.match_method = 'manual' OR cm.match_score >= 0.85)
|
||
GROUP BY cm.domrf_obj_id, l.room_bucket
|
||
WITH NO DATA;
|
||
|
||
-- UNIQUE index: required for REFRESH CONCURRENTLY (periodic via layout_velocity_refresh.py).
|
||
CREATE UNIQUE INDEX mv_layout_velocity_pk
|
||
ON mv_layout_velocity (obj_id, room_bucket);
|
||
|
||
-- Lookup index for /best-layouts endpoint queries by obj_id.
|
||
CREATE INDEX mv_layout_velocity_obj_idx
|
||
ON mv_layout_velocity (obj_id);
|
||
|
||
-- Initial populate (non-concurrent — MV just created, CONCURRENTLY requires populated MV).
|
||
REFRESH MATERIALIZED VIEW mv_layout_velocity;
|
||
|
||
COMMENT ON MATERIALIZED VIEW mv_layout_velocity IS
|
||
'Per-(obj_id, room_bucket) deals aggregation за last 24 months. '
|
||
'WEIGHTED average площади и цены (SF Bug #21). '
|
||
'Confidence-gated mapping: is_reviewed/manual/score>=0.85 (#307 OBJ-2). '
|
||
'Source: objective_corpus_room_month × objective_complex_mapping (EKB only). '
|
||
'Refresh via layout_velocity_refresh.py (CONCURRENTLY после initial populate).';
|
||
|
||
COMMIT;
|