118 lines
5.8 KiB
SQL
118 lines
5.8 KiB
SQL
-- v_zk_rosreestr_velocity refresh: + cad_buildings cost_value для cross-check.
|
||
--
|
||
-- Что добавилось vs 60_v_zk_rosreestr_velocity.sql:
|
||
-- 1. quarter_cadastre_avg_th_per_m2 — медианная кадастровая стоимость м²
|
||
-- строений (≥3 этажей) в том же кадастровом квартале.
|
||
-- 2. cadastre_vs_market_pct — премиум/дисконт рынка относительно кадастра
|
||
-- (положительные значения = рынок дороже кадастра, что норма).
|
||
-- Аномалии (>+50% или <-30%) пригодны для warning-badge на UI.
|
||
--
|
||
-- Логика: cad_buildings.cost_value хранится в ₽ на здание; делим на area
|
||
-- чтобы получить ₽/м². Берём только residential (purpose ILIKE
|
||
-- '%многокварт%' OR floors >= 3) — низкоэтажки и склады искажают.
|
||
-- Нет фильтра по году ввода: даже старый фонд даёт ориентир для зонирования.
|
||
--
|
||
-- Источник истины cost_value: НСПД thematicSearchId=1 → properties.options.cost_value.
|
||
|
||
CREATE OR REPLACE VIEW v_zk_rosreestr_velocity AS
|
||
WITH zk_pool AS (
|
||
SELECT DISTINCT ON (o.obj_id)
|
||
o.obj_id,
|
||
o.comm_name,
|
||
o.dev_name,
|
||
o.district_name,
|
||
o.flat_count,
|
||
o.cad_quarter,
|
||
o.obj_class
|
||
FROM domrf_kn_objects o
|
||
WHERE o.region_cd = 66
|
||
AND o.cad_quarter IS NOT NULL
|
||
AND o.flat_count IS NOT NULL
|
||
AND o.flat_count > 0
|
||
AND o.site_status = 'Строящиеся'
|
||
ORDER BY o.obj_id, o.snapshot_date DESC
|
||
),
|
||
quarter_totals AS (
|
||
SELECT cad_quarter, SUM(flat_count)::numeric AS total_flats
|
||
FROM zk_pool
|
||
GROUP BY cad_quarter
|
||
),
|
||
quarter_deals AS (
|
||
SELECT quarter_cad_number AS cad_quarter,
|
||
COUNT(*) AS deals_12mo,
|
||
AVG(price_per_sqm) AS avg_price_pm,
|
||
PERCENTILE_CONT(0.5) WITHIN GROUP
|
||
(ORDER BY price_per_sqm) AS median_price_pm,
|
||
AVG(area) AS avg_area_sqm
|
||
FROM rosreestr_deals
|
||
WHERE region_code = 66
|
||
AND doc_type = 'ДДУ'
|
||
AND realestate_type_code = '002001003000'
|
||
AND area > 10 AND area <= 200
|
||
AND price_per_sqm BETWEEN 30000 AND 1000000
|
||
AND period_start_date >= NOW() - INTERVAL '12 months'
|
||
GROUP BY quarter_cad_number
|
||
),
|
||
quarter_cadastre AS (
|
||
-- Медианная кадастровая стоимость ₽/м² по жилым строениям квартала.
|
||
-- Фильтры:
|
||
-- * cost_value > 0 и area >= 100 м² (исключаем подсобки)
|
||
-- * floors >= 3 ИЛИ purpose ILIKE '%многокв%' (отсеиваем ИЖС/гаражи)
|
||
-- * ₽/м² 5К..500К (уберает мусор и грубые ошибки росреестра)
|
||
SELECT
|
||
quarter_cad_num AS cad_quarter,
|
||
COUNT(*) AS buildings_n,
|
||
PERCENTILE_CONT(0.5) WITHIN GROUP
|
||
(ORDER BY cost_value / NULLIF(area, 0)) AS median_cost_per_m2
|
||
FROM cad_buildings
|
||
WHERE quarter_cad_num IS NOT NULL
|
||
AND cost_value IS NOT NULL
|
||
AND area IS NOT NULL
|
||
AND area >= 100
|
||
-- floors хранится как TEXT (встречаются '1-2', '2-3') —
|
||
-- считаем только чистые числа ≥3, либо purpose-fallback.
|
||
AND ((floors ~ '^[0-9]+$' AND floors::int >= 3)
|
||
OR purpose ILIKE '%многокв%')
|
||
AND (cost_value / NULLIF(area, 0)) BETWEEN 5000 AND 500000
|
||
GROUP BY quarter_cad_num
|
||
)
|
||
SELECT
|
||
z.obj_id,
|
||
z.comm_name,
|
||
z.dev_name,
|
||
z.district_name,
|
||
z.obj_class,
|
||
z.flat_count,
|
||
z.cad_quarter,
|
||
qt.total_flats AS quarter_total_flats,
|
||
qd.deals_12mo AS quarter_deals_12mo,
|
||
ROUND(qd.deals_12mo * z.flat_count::numeric / qt.total_flats, 1)
|
||
AS estimated_deals_12mo,
|
||
ROUND(qd.deals_12mo * z.flat_count::numeric / qt.total_flats / 12.0, 2)
|
||
AS estimated_velocity_pm,
|
||
ROUND(qd.median_price_pm / 1000.0, 1) AS median_price_th_per_m2,
|
||
ROUND(qd.avg_area_sqm, 1) AS avg_deal_area_sqm,
|
||
-- Кадастровая стоимость и cross-check.
|
||
qc.buildings_n AS cadastre_buildings_n,
|
||
ROUND(qc.median_cost_per_m2 / 1000.0, 1) AS cadastre_th_per_m2,
|
||
CASE
|
||
WHEN qc.median_cost_per_m2 IS NOT NULL
|
||
AND qd.median_price_pm IS NOT NULL
|
||
AND qc.median_cost_per_m2 > 0
|
||
THEN ROUND(
|
||
(qd.median_price_pm - qc.median_cost_per_m2)
|
||
/ qc.median_cost_per_m2 * 100.0,
|
||
1
|
||
)
|
||
ELSE NULL
|
||
END AS cadastre_vs_market_pct
|
||
FROM zk_pool z
|
||
JOIN quarter_totals qt ON qt.cad_quarter = z.cad_quarter
|
||
JOIN quarter_deals qd ON qd.cad_quarter = z.cad_quarter
|
||
LEFT JOIN quarter_cadastre qc ON qc.cad_quarter = z.cad_quarter
|
||
;
|
||
|
||
COMMENT ON VIEW v_zk_rosreestr_velocity IS
|
||
'v2: per-ЖК velocity/цена через rosreestr-сделки квартала + кадастровая '
|
||
'стоимость м² из cad_buildings (NSPD). cadastre_vs_market_pct = премиум '
|
||
'рынка над кадастром, аномалии (>+50% / <-30%) — outliers для warning UI.';
|