98 lines
4.9 KiB
SQL
98 lines
4.9 KiB
SQL
-- Materialized aggregates for fast UI queries.
|
||
-- Refresh strategy: REFRESH MATERIALIZED VIEW CONCURRENTLY after each new quarter load.
|
||
|
||
-- 1. Per cadastral quarter × period × doc_type × area_bucket
|
||
DROP MATERIALIZED VIEW IF EXISTS rr_agg_cad_quarter CASCADE;
|
||
CREATE MATERIALIZED VIEW rr_agg_cad_quarter AS
|
||
SELECT
|
||
region_code,
|
||
quarter_cad_number,
|
||
period_start_date,
|
||
doc_type,
|
||
-- area bucket inspired by NSPD MarketAnalytics filter
|
||
CASE
|
||
WHEN area < 25 THEN 'lt25'
|
||
WHEN area < 40 THEN '25-40'
|
||
WHEN area < 60 THEN '40-60'
|
||
WHEN area < 90 THEN '60-90'
|
||
WHEN area < 150 THEN '90-150'
|
||
ELSE 'gt150'
|
||
END AS area_bucket,
|
||
SUM(deal_count) AS deals_count,
|
||
COUNT(*) AS rows_count,
|
||
SUM(deal_count * area) AS total_area_sqm,
|
||
SUM(deal_count * deal_price) AS total_value_rub,
|
||
percentile_cont(0.10) WITHIN GROUP (ORDER BY price_per_sqm) AS p10_price_sqm,
|
||
percentile_cont(0.50) WITHIN GROUP (ORDER BY price_per_sqm) AS median_price_sqm,
|
||
percentile_cont(0.90) WITHIN GROUP (ORDER BY price_per_sqm) AS p90_price_sqm,
|
||
AVG(price_per_sqm) AS mean_price_sqm,
|
||
MIN(price_per_sqm) AS min_price_sqm,
|
||
MAX(price_per_sqm) AS max_price_sqm
|
||
FROM rosreestr_deals
|
||
WHERE area BETWEEN 5 AND 1000
|
||
AND deal_price BETWEEN 100000 AND 1e10 -- discard outliers (typos, junk in source)
|
||
AND realestate_type_code IN ('002001003000') -- помещения (квартиры). Здания/ЗУ — отдельный rollup
|
||
GROUP BY region_code, quarter_cad_number, period_start_date, doc_type,
|
||
CASE WHEN area<25 THEN 'lt25' WHEN area<40 THEN '25-40' WHEN area<60 THEN '40-60'
|
||
WHEN area<90 THEN '60-90' WHEN area<150 THEN '90-150' ELSE 'gt150' END
|
||
WITH NO DATA;
|
||
|
||
CREATE UNIQUE INDEX rr_agg_cq_pk ON rr_agg_cad_quarter
|
||
(region_code, quarter_cad_number, period_start_date, doc_type, area_bucket);
|
||
CREATE INDEX rr_agg_cq_period_idx ON rr_agg_cad_quarter (period_start_date);
|
||
CREATE INDEX rr_agg_cq_region_period_idx ON rr_agg_cad_quarter (region_code, period_start_date);
|
||
|
||
-- 2. Per region × period × doc_type — для регионального обзора (Урал dashboard)
|
||
DROP MATERIALIZED VIEW IF EXISTS rr_agg_region CASCADE;
|
||
CREATE MATERIALIZED VIEW rr_agg_region AS
|
||
SELECT
|
||
region_code,
|
||
period_start_date,
|
||
doc_type,
|
||
realestate_type_code,
|
||
SUM(deal_count) AS deals_count,
|
||
COUNT(*) AS rows_count,
|
||
SUM(deal_count * area) AS total_area_sqm,
|
||
SUM(deal_count * deal_price) AS total_value_rub,
|
||
percentile_cont(0.10) WITHIN GROUP (ORDER BY price_per_sqm) AS p10_price_sqm,
|
||
percentile_cont(0.50) WITHIN GROUP (ORDER BY price_per_sqm) AS median_price_sqm,
|
||
percentile_cont(0.90) WITHIN GROUP (ORDER BY price_per_sqm) AS p90_price_sqm
|
||
FROM rosreestr_deals
|
||
WHERE area BETWEEN 5 AND 1000
|
||
AND deal_price BETWEEN 100000 AND 1e10
|
||
GROUP BY region_code, period_start_date, doc_type, realestate_type_code
|
||
WITH NO DATA;
|
||
|
||
CREATE UNIQUE INDEX rr_agg_region_pk ON rr_agg_region
|
||
(region_code, period_start_date, doc_type, realestate_type_code);
|
||
|
||
-- 3. Per city/settlement (для дашборда Екатеринбург)
|
||
DROP MATERIALIZED VIEW IF EXISTS rr_agg_settlement CASCADE;
|
||
CREATE MATERIALIZED VIEW rr_agg_settlement AS
|
||
SELECT
|
||
region_code,
|
||
COALESCE(city, district) AS settlement_name,
|
||
period_start_date,
|
||
doc_type,
|
||
realestate_type_code,
|
||
SUM(deal_count) AS deals_count,
|
||
SUM(deal_count * area) AS total_area_sqm,
|
||
percentile_cont(0.50) WITHIN GROUP (ORDER BY price_per_sqm) AS median_price_sqm,
|
||
percentile_cont(0.10) WITHIN GROUP (ORDER BY price_per_sqm) AS p10_price_sqm,
|
||
percentile_cont(0.90) WITHIN GROUP (ORDER BY price_per_sqm) AS p90_price_sqm,
|
||
COUNT(DISTINCT quarter_cad_number) AS distinct_cad_quarters
|
||
FROM rosreestr_deals
|
||
WHERE area BETWEEN 5 AND 1000
|
||
AND deal_price BETWEEN 100000 AND 1e10
|
||
AND COALESCE(city, district) IS NOT NULL
|
||
GROUP BY region_code, COALESCE(city, district), period_start_date, doc_type, realestate_type_code
|
||
WITH NO DATA;
|
||
|
||
CREATE UNIQUE INDEX rr_agg_settl_pk ON rr_agg_settlement
|
||
(region_code, settlement_name, period_start_date, doc_type, realestate_type_code);
|
||
CREATE INDEX rr_agg_settl_search ON rr_agg_settlement (region_code, settlement_name);
|
||
|
||
-- Initial fill
|
||
REFRESH MATERIALIZED VIEW rr_agg_cad_quarter;
|
||
REFRESH MATERIALIZED VIEW rr_agg_region;
|
||
REFRESH MATERIALIZED VIEW rr_agg_settlement;
|