lekss361
579536b4ad
All checks were successful
Deploy Trade-In / changes (push) Successful in 12s
Deploy Trade-In / build-frontend (push) Has been skipped
Deploy Trade-In / build-browser (push) Has been skipped
Deploy Trade-In / test (push) Successful in 1m31s
Deploy Trade-In / build-backend (push) Successful in 1m6s
Deploy Trade-In / deploy (push) Successful in 54s
69 lines
5.2 KiB
PL/PgSQL
69 lines
5.2 KiB
PL/PgSQL
-- 144_gar_canon_addr_match.sql
|
||
--
|
||
-- CONTEXT: матч ГАР↔houses (мигр. 143) по ТОЧНОМУ равенству нормализованного адреса
|
||
-- (tradein_normalize_short_addr) на проде даёт 0 совпадений: нормализатор почти ничего
|
||
-- не канонизирует, поэтому houses-адрес «ул. Шаумяна,20» НИКОГДА не равен ГАР-строке
|
||
-- «ул. Шаумяна, 20» (тип улицы «ул.» vs «ул», запятая/пробел, точка). Знаменатель
|
||
-- «доля квартир дома в продаже» (v_building_sale_share) оставался пустым.
|
||
--
|
||
-- WHAT: канонический ключ адреса tradein_canon_addr(text) — агрессивно схлопывает:
|
||
-- 1. lower() + ё→е;
|
||
-- 2. срезает префикс района «р-н ...,»;
|
||
-- 3. удаляет типы улиц (улица/ул/переулок/пер/проспект/.../микрорайон/мкр), но ТОЛЬКО
|
||
-- как самостоятельные токены на границе не-кириллических символов — буква в составе
|
||
-- названия («ПРоспект» внутри слова) не трогается;
|
||
-- 4. оставляет только кириллицу-нижний-регистр + цифры (выбрасывает пробелы, точки,
|
||
-- запятые, дефисы, слэши).
|
||
-- Так «ул. Шаумяна,20» и «ул. Шаумяна, 20» → оба «шаумяна20» (совпадают),
|
||
-- «р-н Ленинский, улица Цвиллинга, 7/6» → «цвиллинга76». А «6Б» НЕ схлопывается до «6»
|
||
-- (буква корпуса остаётся кириллицей → другое здание).
|
||
--
|
||
-- Эмпирически на live-данных (ГАР region 66, 68 406 строк с flat_count>0) канон-ключ
|
||
-- поднимает матч с 0 до 2 769 из 6 808 вторичных домов (~41%, при ЕКБ-ограничении).
|
||
-- ЕКБ-ограничение (full_address ILIKE '%Екатеринбург%') обязательно: без него
|
||
-- «Машиностроителей 6» существует в 4 населённых пунктах region 66 → ложные коллизии.
|
||
-- Сам ре-матч (app/services/gar_flats_loader.match_houses_to_gar) — отдельный шаг после
|
||
-- деплоя (CLI `python -m app.tasks.gar_flats_load --match-only`); этот файл — только
|
||
-- функция + функциональный индекс под её JOIN.
|
||
--
|
||
-- DEPENDENCIES (existing prod objects):
|
||
-- - gar_house_flats (norm_address, full_address, flat_count, house_guid) — мигр. 143.
|
||
-- - houses.gar_* provenance-колонки — мигр. 143.
|
||
--
|
||
-- SAFETY / IDEMPOTENCY:
|
||
-- - CREATE OR REPLACE FUNCTION + CREATE INDEX IF NOT EXISTS → повторный прогон no-op.
|
||
-- - Функция IMMUTABLE (обязательно для функционального индекса).
|
||
-- - Раннер деплоя (deploy-tradein.yml) гонит файл через psql ON_ERROR_STOP=on БЕЗ
|
||
-- --single-transaction → транзакцию открывает сам файл (BEGIN/COMMIT ниже), как 143.
|
||
--
|
||
-- NB по нумерации: на forgejo/main последний — 143_building_sale_share_schema.sql
|
||
-- (139–142 заняты), следующий свободный sequential = 144 (проверено `ls | grep '^14'`).
|
||
--
|
||
-- Deploy order: после 143_building_sale_share_schema.sql.
|
||
|
||
BEGIN;
|
||
|
||
-- Канонический ключ адреса для устойчивого ГАР↔houses матча (см. шапку файла).
|
||
CREATE OR REPLACE FUNCTION tradein_canon_addr(s text) RETURNS text AS $func$
|
||
SELECT regexp_replace(
|
||
regexp_replace(
|
||
regexp_replace(
|
||
translate(lower(coalesce(s, '')), 'ё', 'е'),
|
||
'р-н[^,]*,', '', 'g'),
|
||
'(^|[^а-я])(улица|ул|переулок|пер|проспект|пркт|пр|бульвар|бр|шоссе|ш|проезд|набережная|наб|площадь|пл|тупик|туп|аллея|микрорайон|мкр)([^а-я]|$)',
|
||
'\1\3', 'g'),
|
||
'[^а-я0-9]', '', 'g');
|
||
$func$ LANGUAGE sql IMMUTABLE;
|
||
|
||
COMMENT ON FUNCTION tradein_canon_addr(text) IS
|
||
'Канонический ключ адреса для ГАР↔houses матча (мигр. 144): lower+ё→е, срез «р-н ...,», '
|
||
'удаление типов улиц как токенов на границе не-кириллицы, оставляет только [а-я0-9]. '
|
||
'Поднял матч с 0 до ~41%% (2769/6808 вторички, ЕКБ-restricted). См. gar-flats-runbook.md.';
|
||
|
||
-- Функциональный индекс под JOIN gar_pick (canon = tradein_canon_addr(norm_address)),
|
||
-- partial по flat_count>0 — матчим только дома с известным знаменателем.
|
||
CREATE INDEX IF NOT EXISTS gar_house_flats_canon_idx
|
||
ON gar_house_flats (tradein_canon_addr(norm_address))
|
||
WHERE flat_count > 0;
|
||
|
||
COMMIT;
|