120 lines
7.2 KiB
PL/PgSQL
120 lines
7.2 KiB
PL/PgSQL
-- Resolve stat_series.territory ↔ domrf_regions.region_name name mismatches.
|
||
-- Stat_series uses Rosstat-style names that differ in 18 of 99 territories:
|
||
-- • 9 regions: "г. Москва" vs "Город Москва", "Республика Адыгея (Адыгея)"
|
||
-- vs "Республика Адыгея", "- Алания"/"(Татарстан)"/"- Чувашия" suffixes;
|
||
-- • 8 federal districts ("Центральный ФО") — not in domrf_regions at all;
|
||
-- • 1 synthetic row "Российская Федерация (без г. Москва)";
|
||
-- • 2 noise rows '-' and 'г. Москва1' (XLSX parser artefacts).
|
||
--
|
||
-- Solution: an alias lookup that maps any incoming text to a structured target
|
||
-- (level + region_id|fo_id). View v_stat_series_resolved joins observations
|
||
-- through it for clean territory resolution without rewriting 809K rows.
|
||
|
||
BEGIN;
|
||
|
||
-- ── 1. Federal districts as a first-class table ─────────────────────────────
|
||
CREATE TABLE IF NOT EXISTS domrf_federal_districts (
|
||
fo_id INT PRIMARY KEY,
|
||
fo_name TEXT NOT NULL UNIQUE
|
||
);
|
||
INSERT INTO domrf_federal_districts (fo_id, fo_name) VALUES
|
||
(1, 'Центральный ФО'),
|
||
(2, 'Северо-Западный ФО'),
|
||
(3, 'Южный ФО'),
|
||
(4, 'Северо-Кавказский ФО'),
|
||
(5, 'Приволжский ФО'),
|
||
(6, 'Уральский ФО'),
|
||
(7, 'Сибирский ФО'),
|
||
(8, 'Дальневосточный ФО')
|
||
ON CONFLICT (fo_id) DO NOTHING;
|
||
|
||
COMMENT ON TABLE domrf_federal_districts IS
|
||
'8 федеральных округов РФ. Связывается с domrf_regions.federal_district по имени, и используется как target для domrf_territory_aliases (level=fo).';
|
||
|
||
-- ── 2. Generic territory alias table ────────────────────────────────────────
|
||
CREATE TABLE IF NOT EXISTS domrf_territory_aliases (
|
||
territory_text TEXT PRIMARY KEY,
|
||
level TEXT NOT NULL CHECK (level IN ('rf','fo','region','synthetic','noise')),
|
||
region_id INT REFERENCES domrf_regions(region_id),
|
||
fo_id INT REFERENCES domrf_federal_districts(fo_id),
|
||
canonical_name TEXT NOT NULL,
|
||
notes TEXT,
|
||
CONSTRAINT alias_target_chk CHECK (
|
||
(level='rf' AND region_id=0 AND fo_id IS NULL) OR
|
||
(level='region' AND region_id IS NOT NULL AND fo_id IS NULL) OR
|
||
(level='fo' AND region_id IS NULL AND fo_id IS NOT NULL) OR
|
||
(level='synthetic' AND region_id IS NULL AND fo_id IS NULL) OR
|
||
(level='noise' AND region_id IS NULL AND fo_id IS NULL)
|
||
)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_territory_aliases_region ON domrf_territory_aliases (region_id);
|
||
CREATE INDEX IF NOT EXISTS idx_territory_aliases_fo ON domrf_territory_aliases (fo_id);
|
||
|
||
COMMENT ON TABLE domrf_territory_aliases IS
|
||
'Text→(level, region_id|fo_id) lookup. Resolves stat_series territory naming variants to canonical regions/FO. Use v_stat_series_resolved for joined views.';
|
||
|
||
-- ── 3a. Auto-populate identity aliases for the 81 regions whose names match ─
|
||
-- (and the RF row at region_id=0)
|
||
INSERT INTO domrf_territory_aliases (territory_text, level, region_id, canonical_name, notes)
|
||
SELECT
|
||
r.region_name,
|
||
CASE WHEN r.region_id = 0 THEN 'rf' ELSE 'region' END,
|
||
r.region_id,
|
||
r.region_name,
|
||
'auto: identity alias from domrf_regions.region_name'
|
||
FROM domrf_regions r
|
||
WHERE r.region_name IN (SELECT DISTINCT territory FROM domrf_stat_series_observations)
|
||
ON CONFLICT (territory_text) DO NOTHING;
|
||
|
||
-- ── 3b. Manual aliases for 9 regions with format differences ────────────────
|
||
INSERT INTO domrf_territory_aliases (territory_text, level, region_id, canonical_name, notes) VALUES
|
||
('г. Москва', 'region', 77, 'Город Москва', 'г./Город prefix mismatch'),
|
||
('г. Санкт-Петербург', 'region', 78, 'Город Санкт-Петербург', 'г./Город prefix mismatch'),
|
||
('г. Севастополь', 'region', 92, 'Город Севастополь', 'г./Город prefix mismatch'),
|
||
('Республика Адыгея (Адыгея)', 'region', 1, 'Республика Адыгея', 'parenthetical suffix in Rosstat naming'),
|
||
('Республика Татарстан (Татарстан)', 'region', 16, 'Республика Татарстан', 'parenthetical suffix in Rosstat naming'),
|
||
('Республика Северная Осетия - Алания', 'region', 15, 'Республика Северная Осетия', '- Алания suffix in Rosstat naming'),
|
||
('Чувашская Республика - Чувашия', 'region', 21, 'Чувашская Республика', '- Чувашия suffix in Rosstat naming')
|
||
ON CONFLICT (territory_text) DO NOTHING;
|
||
-- Note: "Республика Саха (Якутия)" and "Кемеровская область - Кузбасс" use the
|
||
-- same parenthetical convention BUT match exactly between sources, so identity
|
||
-- aliases above already cover them.
|
||
|
||
-- ── 3c. Federal districts — 8 aliases pointing at the new FO table ──────────
|
||
INSERT INTO domrf_territory_aliases (territory_text, level, fo_id, canonical_name, notes)
|
||
SELECT fo_name, 'fo', fo_id, fo_name, 'auto: identity alias from domrf_federal_districts'
|
||
FROM domrf_federal_districts
|
||
ON CONFLICT (territory_text) DO NOTHING;
|
||
|
||
-- ── 3d. Synthetic + noise rows ──────────────────────────────────────────────
|
||
INSERT INTO domrf_territory_aliases (territory_text, level, canonical_name, notes) VALUES
|
||
('Российская Федерация (без г. Москва)', 'synthetic', 'РФ без Москвы',
|
||
'Synthetic row in some XLSX summaries — RF totals excluding Moscow.'),
|
||
('-', 'noise', '-', 'XLSX parser artefact: empty separator row swallowed by parser.'),
|
||
('г. Москва1', 'noise', 'г. Москва1','XLSX source typo (extra "1" suffix). Real "г. Москва" present separately.')
|
||
ON CONFLICT (territory_text) DO NOTHING;
|
||
|
||
-- ── 4. Resolved view: stat_series_observations + alias target ───────────────
|
||
CREATE OR REPLACE VIEW v_stat_series_resolved AS
|
||
SELECT
|
||
o.snapshot_date,
|
||
o.file_name,
|
||
o.sheet_code,
|
||
i.indicator_name,
|
||
o.territory,
|
||
a.level AS territory_level,
|
||
a.region_id,
|
||
a.fo_id,
|
||
a.canonical_name,
|
||
o.obs_date,
|
||
o.value
|
||
FROM domrf_stat_series_observations o
|
||
LEFT JOIN domrf_territory_aliases a
|
||
ON a.territory_text = o.territory
|
||
LEFT JOIN domrf_stat_series_indicators i
|
||
ON i.file_name = o.file_name AND i.sheet_code = o.sheet_code;
|
||
|
||
COMMENT ON VIEW v_stat_series_resolved IS
|
||
'stat_series_observations + indicator name + resolved territory (level/region_id/fo_id). Filter WHERE territory_level=''region'' for clean region-level analysis.';
|
||
|
||
COMMIT;
|