(low) Fan-out по objective_complex_mapping завышает SUM-метрики в _INLINE_VELOCITY_SQL (best_layouts) #1391

New issue

Closed

opened 2026-06-14 18:10:18 +00:00 by bot-backend · 0 comments

bot-backend commented

2026-06-14 18:10:18 +00:00

Collaborator

Описание

_INLINE_VELOCITY_SQL (best_layouts.py:114-146) джойнит objective_corpus_room_month crm к objective_complex_mapping cm по cm.objective_complex_name = crm.project_name, затем агрегирует SUM(crm.deals_total_count) и weighted-avg производные. Поскольку UNIQUE-ключ mapping — (objective_complex_name, objective_group), один objective_complex_name может иметь несколько строк mapping (разные группы / domrf_obj_id). При наличии >1 строки каждая строка crm дублируется в join N раз, и незащищённый SUM(crm.deals_total_count) умножается на N.

Последствия для затронутых project_name: завышены deals_window, velocity_per_month, total_sold_in_window и sold_pct_of_supply в N раз. Метрики competitor_obj_ids / competitor_count НЕ затронуты — они защищены array_agg(DISTINCT cm.domrf_obj_id) / COUNT(DISTINCT cm.domrf_obj_id). Weighted-avg поля (avg_area_m2, avg_price_per_m2_rub) устойчивы к fan-out, т.к. множитель N сокращается в числителе и знаменателе.

Корень

JOIN к таблице, у которой ключ уникальности шире, чем условие соединения (objective_complex_name — лишь часть UNIQUE (objective_complex_name, objective_group)), без дедупликации перед агрегатом SUM. Аналогичный риск в mv_layout_velocity (94_mv_layout_velocity.sql:69) закрыт явным guard AND cm.objective_group = 'Екатеринбург', а в inline-SQL этот предохранитель пропущен. competitors.py проблему обходит за счёт GROUP BY per-obj (без суммирования по дублям имени).

Evidence

best_layouts.py:120 — незащищённый SUM:

SUM(crm.deals_total_count)                           AS deals_window,

best_layouts.py:131-132 — DISTINCT-защита только для конкурентов:

array_agg(DISTINCT cm.domrf_obj_id)                  AS competitor_obj_ids,
COUNT(DISTINCT cm.domrf_obj_id)                      AS competitor_count,

best_layouts.py:135-139 — сам join без фильтра по objective_group:

FROM objective_corpus_room_month crm
JOIN objective_complex_mapping cm
    ON cm.objective_complex_name = crm.project_name
WHERE crm.report_month >= (NOW() - CAST(:window_interval AS interval))::date
  AND cm.domrf_obj_id = ANY(:competitor_obj_ids)

68_schema_objective.sql:321 — ключ уникальности шире условия join:

UNIQUE (objective_complex_name, objective_group)

94_mv_layout_velocity.sql:69 — guard, присутствующий в MV, но отсутствующий в inline-SQL:

AND cm.objective_group = 'Екатеринбург'  -- защита от cross-region Cartesian при future multi-city

Suggested fix

Убрать fan-out до агрегации одним из вариантов:

заменить JOIN на WHERE EXISTS (SELECT 1 FROM objective_complex_mapping cm WHERE cm.objective_complex_name = crm.project_name AND cm.domrf_obj_id = ANY(:competitor_obj_ids)), а competitor_obj_ids/competitor_count собирать отдельным подзапросом;
либо предварительно собрать DISTINCT project_name по competitor_obj_ids в CTE и джойнить 1:1;
как минимум добавить тот же guard AND cm.objective_group = 'Екатеринбург', что и в mv_layout_velocity, чтобы достичь паритета с MV.

Обоснование severity (low)

Дефект реальный, но латентный: все seed-файлы (104_seed_bulk_mappings.sql, 103_seed_prinzip_mappings.sql, 150_objective_mapping_backfill.sql) вставляют только objective_group='Екатеринбург', поэтому сегодня для каждого objective_complex_name ровно одна строка mapping → fan-out = 1 → дублирования в проде нет. Дополнительный фильтр cm.domrf_obj_id = ANY(:competitor_obj_ids) ещё снижает вероятность срабатывания (нужно двойное совпадение). Однако при добавлении второго города (первые seed-данные не-ЕКБ) SUM начнёт умножаться на N без какого-либо предохранителя в коде. Не medium, т.к. не воспроизводится на текущих prod-данных и требует multi-city rollout.

week ревью 1 · multi-agent аудит 2026-06-14 · severity=low · category=sql

Затронутые файлы:

/Users/anton/Птица/gendesign/backend/app/services/site_finder/best_layouts.py
/Users/anton/Птица/gendesign/data/sql/68_schema_objective.sql
/Users/anton/Птица/gendesign/data/sql/94_mv_layout_velocity.sql

## Описание `_INLINE_VELOCITY_SQL` (best_layouts.py:114-146) джойнит `objective_corpus_room_month crm` к `objective_complex_mapping cm` по `cm.objective_complex_name = crm.project_name`, затем агрегирует `SUM(crm.deals_total_count)` и weighted-avg производные. Поскольку UNIQUE-ключ mapping — `(objective_complex_name, objective_group)`, один `objective_complex_name` может иметь несколько строк mapping (разные группы / `domrf_obj_id`). При наличии >1 строки каждая строка `crm` дублируется в join N раз, и незащищённый `SUM(crm.deals_total_count)` умножается на N. Последствия для затронутых `project_name`: завышены `deals_window`, `velocity_per_month`, `total_sold_in_window` и `sold_pct_of_supply` в N раз. Метрики `competitor_obj_ids` / `competitor_count` НЕ затронуты — они защищены `array_agg(DISTINCT cm.domrf_obj_id)` / `COUNT(DISTINCT cm.domrf_obj_id)`. Weighted-avg поля (`avg_area_m2`, `avg_price_per_m2_rub`) устойчивы к fan-out, т.к. множитель N сокращается в числителе и знаменателе. ## Корень JOIN к таблице, у которой ключ уникальности шире, чем условие соединения (`objective_complex_name` — лишь часть UNIQUE `(objective_complex_name, objective_group)`), без дедупликации перед агрегатом `SUM`. Аналогичный риск в `mv_layout_velocity` (94_mv_layout_velocity.sql:69) закрыт явным guard `AND cm.objective_group = 'Екатеринбург'`, а в inline-SQL этот предохранитель пропущен. `competitors.py` проблему обходит за счёт `GROUP BY` per-obj (без суммирования по дублям имени). ## Evidence `best_layouts.py:120` — незащищённый SUM: ``` SUM(crm.deals_total_count) AS deals_window, ``` `best_layouts.py:131-132` — DISTINCT-защита только для конкурентов: ``` array_agg(DISTINCT cm.domrf_obj_id) AS competitor_obj_ids, COUNT(DISTINCT cm.domrf_obj_id) AS competitor_count, ``` `best_layouts.py:135-139` — сам join без фильтра по objective_group: ``` FROM objective_corpus_room_month crm JOIN objective_complex_mapping cm ON cm.objective_complex_name = crm.project_name WHERE crm.report_month >= (NOW() - CAST(:window_interval AS interval))::date AND cm.domrf_obj_id = ANY(:competitor_obj_ids) ``` `68_schema_objective.sql:321` — ключ уникальности шире условия join: ``` UNIQUE (objective_complex_name, objective_group) ``` `94_mv_layout_velocity.sql:69` — guard, присутствующий в MV, но отсутствующий в inline-SQL: ``` AND cm.objective_group = 'Екатеринбург' -- защита от cross-region Cartesian при future multi-city ``` ## Suggested fix Убрать fan-out до агрегации одним из вариантов: - заменить JOIN на `WHERE EXISTS (SELECT 1 FROM objective_complex_mapping cm WHERE cm.objective_complex_name = crm.project_name AND cm.domrf_obj_id = ANY(:competitor_obj_ids))`, а `competitor_obj_ids`/`competitor_count` собирать отдельным подзапросом; - либо предварительно собрать `DISTINCT project_name` по `competitor_obj_ids` в CTE и джойнить 1:1; - как минимум добавить тот же guard `AND cm.objective_group = 'Екатеринбург'`, что и в `mv_layout_velocity`, чтобы достичь паритета с MV. ## Обоснование severity (low) Дефект реальный, но латентный: все seed-файлы (104_seed_bulk_mappings.sql, 103_seed_prinzip_mappings.sql, 150_objective_mapping_backfill.sql) вставляют только `objective_group='Екатеринбург'`, поэтому сегодня для каждого `objective_complex_name` ровно одна строка mapping → fan-out = 1 → дублирования в проде нет. Дополнительный фильтр `cm.domrf_obj_id = ANY(:competitor_obj_ids)` ещё снижает вероятность срабатывания (нужно двойное совпадение). Однако при добавлении второго города (первые seed-данные не-ЕКБ) `SUM` начнёт умножаться на N без какого-либо предохранителя в коде. Не medium, т.к. не воспроизводится на текущих prod-данных и требует multi-city rollout. --- _week ревью 1 · multi-agent аудит 2026-06-14 · severity=**low** · category=`sql`_ Затронутые файлы: - `/Users/anton/Птица/gendesign/backend/app/services/site_finder/best_layouts.py` - `/Users/anton/Птица/gendesign/data/sql/68_schema_objective.sql` - `/Users/anton/Птица/gendesign/data/sql/94_mv_layout_velocity.sql`