(medium) refresh_ekb_districts_median: ST_Intersects двойного-счёта сделок на пограничных кварталах искажает медиану/среднюю цену района #1352

New issue

Closed

opened 2026-06-14 18:08:58 +00:00 by bot-backend · 1 comment

bot-backend commented

2026-06-14 18:08:58 +00:00

Collaborator

Описание

refresh_ekb_districts_median джойнит rosreestr_deals → cad_quarters_geom (по quarter_cad_number) → ekb_districts_geom через ST_Intersects(d.geom, cq.geom). Полигон кадастрового квартала, пересекающий границу района, пересекает НЕСКОЛЬКО полигонов районов, поэтому каждая сделка в таком квартале считается по разу на каждый пересечённый район. Это раздувает COUNT(*) (сделки) в сторону порога min_deals=50 И искажает PERCENTILE_CONT (медиана) / AVG (среднее) для всех районов пограничного квартала. Результат пишется напрямую в ekb_districts.median_price_per_m2 / mean_price_per_m2, которые питают recommend_mix district_factor и cadastre_vs_market_pct, показываемые пользователю → район с большим числом пограничных кварталов получает смещённую цену.

Корень

Джойн района по ST_Intersects без присвоения каждого квартала ровно одному району. Нет дедупликации, нет centroid-based assignment, нет DISTINCT ON, нет фильтра доминирующего района.

Evidence

/Users/anton/Птица/gendesign/data/sql/67_refresh_ekb_districts_median.sql:24-33

JOIN cad_quarters_geom cq ON cq.cad_number = rd.quarter_cad_number
JOIN ekb_districts_geom d ON ST_Intersects(d.geom, cq.geom)
...
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY rd.price_per_sqm)  -- counted once per intersected district

Suggested fix

Присвоить каждый квартал ровно одному району до агрегации: джойн по ST_Contains(d.geom, ST_Centroid(cq.geom)) либо для кварталов на границе выбирать район максимального перекрытия (DISTINCT ON (quarter) ... ORDER BY ST_Area(ST_Intersection(...)) DESC). Затем агрегировать по дедуплицированному набору сделок.

Обоснование severity

Medium: функция живая (Celery beat ежемесячно), пишет напрямую в ekb_districts, downstream-митигации нет. Импакт ограничен пограничными кварталами — районы с малым их числом почти не затронуты, но пограничные получают смещённую медиану/среднюю и могут пройти/не пройти порог min_deals=50.

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

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

/Users/anton/Птица/gendesign/data/sql/67_refresh_ekb_districts_median.sql

## Описание `refresh_ekb_districts_median` джойнит `rosreestr_deals` → `cad_quarters_geom` (по `quarter_cad_number`) → `ekb_districts_geom` через `ST_Intersects(d.geom, cq.geom)`. Полигон кадастрового квартала, пересекающий границу района, пересекает НЕСКОЛЬКО полигонов районов, поэтому каждая сделка в таком квартале считается по разу на каждый пересечённый район. Это раздувает `COUNT(*)` (сделки) в сторону порога `min_deals=50` И искажает `PERCENTILE_CONT` (медиана) / `AVG` (среднее) для всех районов пограничного квартала. Результат пишется напрямую в `ekb_districts.median_price_per_m2` / `mean_price_per_m2`, которые питают `recommend_mix` district_factor и `cadastre_vs_market_pct`, показываемые пользователю → район с большим числом пограничных кварталов получает смещённую цену. ## Корень Джойн района по `ST_Intersects` без присвоения каждого квартала ровно одному району. Нет дедупликации, нет centroid-based assignment, нет DISTINCT ON, нет фильтра доминирующего района. ## Evidence `/Users/anton/Птица/gendesign/data/sql/67_refresh_ekb_districts_median.sql:24-33` ```sql JOIN cad_quarters_geom cq ON cq.cad_number = rd.quarter_cad_number JOIN ekb_districts_geom d ON ST_Intersects(d.geom, cq.geom) ... PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY rd.price_per_sqm) -- counted once per intersected district ``` ## Suggested fix Присвоить каждый квартал ровно одному району до агрегации: джойн по `ST_Contains(d.geom, ST_Centroid(cq.geom))` либо для кварталов на границе выбирать район максимального перекрытия (`DISTINCT ON (quarter) ... ORDER BY ST_Area(ST_Intersection(...)) DESC`). Затем агрегировать по дедуплицированному набору сделок. ## Обоснование severity Medium: функция живая (Celery beat ежемесячно), пишет напрямую в `ekb_districts`, downstream-митигации нет. Импакт ограничен пограничными кварталами — районы с малым их числом почти не затронуты, но пограничные получают смещённую медиану/среднюю и могут пройти/не пройти порог `min_deals=50`. --- _week ревью 1 · multi-agent аудит 2026-06-14 · severity=**medium** · category=`correctness`_ Затронутые файлы: - `/Users/anton/Птица/gendesign/data/sql/67_refresh_ekb_districts_median.sql`

bot-backend added the

week ревью 1

label 2026-06-14 18:08:58 +00:00

bot-backend added the

scope/backend

label 2026-06-15 11:01:51 +00:00

lekss361 commented

2026-06-15 11:26:49 +00:00

Owner

🔍 week ревью 1 — верификация (ПОДТВЕРЖДЕНО)

Текущее состояние кода: data/sql/67_refresh_ekb_districts_median.sql:24-26 — FROM rosreestr_deals rd JOIN cad_quarters_geom cq ON cq.cad_number = rd.quarter_cad_number JOIN ekb_districts_geom d ON ST_Intersects(d.geom, cq.geom), агрегация PERCENTILE_CONT(0.5) / AVG / COUNT(*) GROUP BY d.district_name (стр. 22-23, 33), запись в ekb_districts (UPDATE, стр. 36-43).

Корень: Джойн района по ST_Intersects(d.geom, cq.geom) без присвоения квартала ровно одному району. Кадастровый квартал, пересекающий границу района, матчит несколько полигонов ekb_districts_geom → каждая сделка такого квартала считается по разу на каждый пересечённый район. Раздувает COUNT(*) (порог min_deals=50) и искажает PERCENTILE_CONT/AVG. Нет DISTINCT ON, нет centroid-assignment, нет dominant-overlap фильтра.

Доп. наблюдение: ekb_districts_geom сейчас — это VIEW поверх ekb_districts (создан в data/sql/74_dedupe_unified_views.sql:34-36), не таблица. На корректность находки это не влияет (геометрия та же), но fix писать поверх view.

Что править:

Присвоить каждый квартал ровно одному району до агрегации. Вариант A (centroid): JOIN ekb_districts_geom d ON ST_Contains(d.geom, ST_Centroid(cq.geom)). Вариант B (max-overlap, точнее на вытянутых кварталах): подзапрос DISTINCT ON (cq.cad_number) ... ORDER BY cq.cad_number, ST_Area(ST_Intersection(d.geom, cq.geom)) DESC, затем агрегировать по дедуплицированному (квартал → район).
Перепроверить, что после дедупа районы по-прежнему набирают min_deals=50 (сейчас порог завышается двойным счётом).

Что посмотреть:

Код: 67_refresh_ekb_districts_median.sql:19-47; downstream ekb_districts.median_price_per_m2/mean_price_per_m2 → recommend_mix (district_factor) + cadastre_vs_market_pct. Функция вызывается Celery beat ежемесячно.
Vault: релевантных заметок не найдено.

**🔍 week ревью 1 — верификация (ПОДТВЕРЖДЕНО)** **Текущее состояние кода:** `data/sql/67_refresh_ekb_districts_median.sql:24-26` — `FROM rosreestr_deals rd JOIN cad_quarters_geom cq ON cq.cad_number = rd.quarter_cad_number JOIN ekb_districts_geom d ON ST_Intersects(d.geom, cq.geom)`, агрегация `PERCENTILE_CONT(0.5)` / `AVG` / `COUNT(*)` `GROUP BY d.district_name` (стр. 22-23, 33), запись в `ekb_districts` (UPDATE, стр. 36-43). **Корень:** Джойн района по `ST_Intersects(d.geom, cq.geom)` без присвоения квартала ровно одному району. Кадастровый квартал, пересекающий границу района, матчит несколько полигонов `ekb_districts_geom` → каждая сделка такого квартала считается по разу на каждый пересечённый район. Раздувает `COUNT(*)` (порог `min_deals=50`) и искажает `PERCENTILE_CONT`/`AVG`. Нет `DISTINCT ON`, нет centroid-assignment, нет dominant-overlap фильтра. **Доп. наблюдение:** `ekb_districts_geom` сейчас — это VIEW поверх `ekb_districts` (создан в `data/sql/74_dedupe_unified_views.sql:34-36`), не таблица. На корректность находки это не влияет (геометрия та же), но fix писать поверх view. **Что править:** - Присвоить каждый квартал ровно одному району до агрегации. Вариант A (centroid): `JOIN ekb_districts_geom d ON ST_Contains(d.geom, ST_Centroid(cq.geom))`. Вариант B (max-overlap, точнее на вытянутых кварталах): подзапрос `DISTINCT ON (cq.cad_number) ... ORDER BY cq.cad_number, ST_Area(ST_Intersection(d.geom, cq.geom)) DESC`, затем агрегировать по дедуплицированному (квартал → район). - Перепроверить, что после дедупа районы по-прежнему набирают `min_deals=50` (сейчас порог завышается двойным счётом). **Что посмотреть:** - Код: `67_refresh_ekb_districts_median.sql:19-47`; downstream `ekb_districts.median_price_per_m2`/`mean_price_per_m2` → `recommend_mix` (district_factor) + `cadastre_vs_market_pct`. Функция вызывается Celery beat ежемесячно. - Vault: релевантных заметок не найдено.

bot-backend added the

needs-human

label 2026-06-15 16:43:58 +00:00

bot-backend referenced this issue from a commit

2026-06-17 17:29:42 +00:00

fix(sql): dedup deals-to-district assignment in ekb districts median refresh (#1352)