freedakgmail
6e0c1bd7a4
- 创建选址分析7个SQL视图+5张物化表（38s→33ms） - 后端新增5个选址API端点 - 前端新增SiteSelectionPage页面（散点图+4Tab） - 侧边栏新增门店选址导航入口 - 修复SQL列引用错误（d.→a.，去掉重复列） - 创建v_store_action_priority_deep_april和v_store_area_efficiency_april基础视图
191 lines
10 KiB
SQL
191 lines
10 KiB
SQL
-- 连锁餐饮门店选址分析
|
|
-- 基准期：2026年4月
|
|
-- 数据：经营、菜品、会员、平台、成本、库存、面积、店龄、租约及精确坐标
|
|
|
|
CREATE OR REPLACE VIEW analytics.v_store_site_profile_april AS
|
|
SELECT a.*,
|
|
CASE
|
|
WHEN a.business_type='特殊业态' THEN '特殊业态'
|
|
WHEN a.business_address ~ '机场|航站楼' THEN '交通枢纽'
|
|
WHEN a.business_address ~ '大学|食堂|档口' THEN '校园档口'
|
|
WHEN a.business_address ~ '总部|科技园|产业园|创业园|商务楼|写字楼|信息产业基地|生命科学园|自贸试验区|经海|荣华' THEN '办公园区'
|
|
WHEN a.business_address ~ '商场|商城|购物|超市|万科|龙湖|大悦|搜秀|美食城|商业大厦|商铺' THEN '商场商业体'
|
|
WHEN a.business_address ~ '社区|小区|家园|里|园一区|园东街' THEN '社区居民'
|
|
ELSE '街边综合'
|
|
END AS site_scene,
|
|
CASE
|
|
WHEN a.business_address ~ '地下一层|负一层|-1层|-1至|B1|b1' THEN '地下层'
|
|
WHEN a.business_address ~ '二层|2层|四层|4层|4F|五层|5层|23层' THEN '非首层'
|
|
WHEN a.business_address ~ '一层|1层|底商' THEN '首层'
|
|
ELSE '楼层不明'
|
|
END AS floor_type,
|
|
CASE
|
|
WHEN a.area_sqm IS NULL THEN '面积缺失'
|
|
WHEN a.area_sqm<=180 THEN '≤180㎡'
|
|
WHEN a.area_sqm<=250 THEN '181-250㎡'
|
|
WHEN a.area_sqm<=350 THEN '251-350㎡'
|
|
WHEN a.area_sqm<=500 THEN '351-500㎡'
|
|
ELSE '>500㎡'
|
|
END AS area_band,
|
|
CASE
|
|
WHEN a.store_age_years IS NULL THEN '店龄缺失'
|
|
WHEN a.store_age_years<1 THEN '新店<1年'
|
|
WHEN a.store_age_years<3 THEN '成长期1-3年'
|
|
WHEN a.store_age_years<8 THEN '成熟期3-8年'
|
|
ELSE '老店≥8年'
|
|
END AS age_band
|
|
FROM analytics.v_store_area_efficiency_april a;
|
|
|
|
CREATE OR REPLACE VIEW analytics.v_store_spatial_pairs_april AS
|
|
WITH physical AS (
|
|
SELECT store_code,store_name,district,site_scene,action_priority,received,
|
|
monthly_received_per_sqm,repeat_rate_pct,actual_food_cost_rate_pct,
|
|
latitude_gcj02 AS lat,longitude_gcj02 AS lon
|
|
FROM analytics.v_store_site_profile_april
|
|
WHERE latitude_gcj02 IS NOT NULL AND longitude_gcj02 IS NOT NULL
|
|
), pairs AS (
|
|
SELECT a.store_code AS store_code_a,a.store_name AS store_name_a,
|
|
b.store_code AS store_code_b,b.store_name AS store_name_b,
|
|
a.district AS district_a,b.district AS district_b,
|
|
a.site_scene AS scene_a,b.site_scene AS scene_b,
|
|
a.action_priority AS priority_a,b.action_priority AS priority_b,
|
|
a.received AS received_a,b.received AS received_b,
|
|
a.monthly_received_per_sqm AS sqm_efficiency_a,
|
|
b.monthly_received_per_sqm AS sqm_efficiency_b,
|
|
6371 * acos(least(1.0,greatest(-1.0,
|
|
cos(radians(a.lat))*cos(radians(b.lat))*cos(radians(b.lon-a.lon))+
|
|
sin(radians(a.lat))*sin(radians(b.lat))
|
|
))) AS distance_km
|
|
FROM physical a
|
|
JOIN physical b ON a.store_code<b.store_code
|
|
)
|
|
SELECT *,
|
|
CASE WHEN distance_km<1 THEN '高度重叠<1km'
|
|
WHEN distance_km<2 THEN '较高重叠1-2km'
|
|
WHEN distance_km<3 THEN '观察2-3km'
|
|
ELSE '相对独立≥3km' END AS proximity_level
|
|
FROM pairs;
|
|
|
|
CREATE OR REPLACE VIEW analytics.v_store_nearest_neighbor_april AS
|
|
WITH directed AS (
|
|
SELECT store_code_a AS store_code,store_name_a AS store_name,
|
|
store_code_b AS nearest_store_code,store_name_b AS nearest_store_name,
|
|
distance_km
|
|
FROM analytics.v_store_spatial_pairs_april
|
|
UNION ALL
|
|
SELECT store_code_b,store_name_b,store_code_a,store_name_a,distance_km
|
|
FROM analytics.v_store_spatial_pairs_april
|
|
), ranked AS (
|
|
SELECT *,row_number() OVER(PARTITION BY store_code ORDER BY distance_km) AS rn
|
|
FROM directed
|
|
)
|
|
SELECT store_code,store_name,nearest_store_code,nearest_store_name,
|
|
round(distance_km::numeric,2) AS nearest_distance_km,
|
|
CASE WHEN distance_km<1 THEN '高度重叠<1km'
|
|
WHEN distance_km<2 THEN '较高重叠1-2km'
|
|
WHEN distance_km<3 THEN '观察2-3km'
|
|
ELSE '相对独立≥3km' END AS nearest_proximity_level
|
|
FROM ranked WHERE rn=1;
|
|
|
|
CREATE OR REPLACE VIEW analytics.v_site_segment_benchmark_april AS
|
|
SELECT site_scene,area_band,count(*) AS store_count,
|
|
round(avg(area_sqm)::numeric,1) AS avg_area_sqm,
|
|
round(avg(received)::numeric,2) AS avg_received,
|
|
round(percentile_cont(0.5) WITHIN GROUP(ORDER BY received)::numeric,2) AS median_received,
|
|
round(avg(monthly_received_per_sqm)::numeric,2) AS avg_received_per_sqm,
|
|
round(percentile_cont(0.5) WITHIN GROUP(ORDER BY monthly_received_per_sqm)::numeric,2) AS median_received_per_sqm,
|
|
round(avg(avg_bill_value)::numeric,2) AS avg_bill_value,
|
|
round(avg(discount_rate_pct)::numeric,2) AS avg_discount_rate_pct,
|
|
round(avg(repeat_rate_pct)::numeric,2) AS avg_repeat_rate_pct,
|
|
round(avg(actual_food_cost_rate_pct) FILTER(WHERE comparison_status='可比')::numeric,2) AS avg_actual_cost_rate_pct,
|
|
round(avg(delivery_bill_share_pct)::numeric,2) AS avg_delivery_share_pct,
|
|
round(avg(noodle_drink_attach_pct)::numeric,2) AS avg_drink_attach_pct
|
|
FROM analytics.v_store_site_profile_april
|
|
WHERE business_type='标准门店' AND received>0 AND area_sqm IS NOT NULL
|
|
GROUP BY site_scene,area_band;
|
|
|
|
CREATE OR REPLACE VIEW analytics.v_district_site_benchmark_april AS
|
|
SELECT city,district,count(*) AS store_count,
|
|
round(avg(area_sqm)::numeric,1) AS avg_area_sqm,
|
|
round(sum(received)::numeric,2) AS total_received,
|
|
round(avg(received)::numeric,2) AS avg_received,
|
|
round(percentile_cont(0.5) WITHIN GROUP(ORDER BY received)::numeric,2) AS median_received,
|
|
round(avg(monthly_received_per_sqm)::numeric,2) AS avg_received_per_sqm,
|
|
round(avg(avg_bill_value)::numeric,2) AS avg_bill_value,
|
|
round(avg(discount_rate_pct)::numeric,2) AS avg_discount_rate_pct,
|
|
round(avg(repeat_rate_pct)::numeric,2) AS avg_repeat_rate_pct,
|
|
round(avg(actual_food_cost_rate_pct) FILTER(WHERE comparison_status='可比')::numeric,2) AS avg_actual_cost_rate_pct,
|
|
round(avg(combined_platform_cost_rate_pct)::numeric,2) AS avg_platform_cost_rate_pct,
|
|
count(*) FILTER(WHERE action_priority LIKE 'P0%') AS p0_count,
|
|
count(*) FILTER(WHERE action_priority='P1-重点整改') AS p1_count
|
|
FROM analytics.v_store_site_profile_april
|
|
WHERE business_type='标准门店' AND received>0
|
|
GROUP BY city,district;
|
|
|
|
CREATE OR REPLACE VIEW analytics.v_store_site_replication_score_april AS
|
|
WITH eligible AS (
|
|
SELECT p.*,n.nearest_store_code,n.nearest_store_name,n.nearest_distance_km,
|
|
percent_rank() OVER(ORDER BY p.monthly_received_per_sqm) AS sqm_score,
|
|
percent_rank() OVER(ORDER BY p.avg_daily_received) AS daily_score,
|
|
percent_rank() OVER(ORDER BY p.repeat_rate_pct NULLS FIRST) AS repeat_score,
|
|
1-percent_rank() OVER(ORDER BY p.discount_rate_pct) AS discount_score,
|
|
1-percent_rank() OVER(ORDER BY p.actual_food_cost_rate_pct NULLS LAST) AS cost_score,
|
|
1-percent_rank() OVER(ORDER BY p.combined_platform_cost_rate_pct NULLS LAST) AS platform_score,
|
|
greatest(0,1-p.problem_count/6.0) AS execution_score
|
|
FROM analytics.v_store_site_profile_april p
|
|
LEFT JOIN analytics.v_store_nearest_neighbor_april n USING(store_code,store_name)
|
|
WHERE p.business_type='标准门店' AND p.received>0 AND p.area_sqm IS NOT NULL
|
|
), scored AS (
|
|
SELECT *,round((
|
|
0.30*sqm_score+0.20*daily_score+0.15*repeat_score+
|
|
0.10*discount_score+0.10*cost_score+0.10*platform_score+
|
|
0.05*execution_score
|
|
)::numeric*100,2) AS site_replication_score
|
|
FROM eligible
|
|
)
|
|
SELECT *,
|
|
CASE
|
|
WHEN site_replication_score>=75 AND problem_count<=1 THEN '优先提炼选址原型'
|
|
WHEN site_replication_score>=60 THEN '可作为同类参考'
|
|
WHEN site_replication_score<40 THEN '不宜作为选址标杆'
|
|
ELSE '观察验证'
|
|
END AS replication_recommendation,
|
|
CASE
|
|
WHEN nearest_distance_km>=3 AND site_replication_score>=70 THEN '高表现且周边相对独立，可研究相似商圈扩张'
|
|
WHEN nearest_distance_km<1.5 THEN '邻店较近，新址需重点防止同店分流'
|
|
ELSE '常规评估'
|
|
END AS spatial_recommendation
|
|
FROM scored;
|
|
|
|
CREATE OR REPLACE VIEW analytics.v_store_location_overlap_risk_april AS
|
|
SELECT p.*,
|
|
a.problem_count AS problem_count_a,b.problem_count AS problem_count_b,
|
|
CASE
|
|
WHEN p.distance_km<1 AND (a.action_priority LIKE 'P0%' OR b.action_priority LIKE 'P0%' OR a.action_priority='P1-重点整改' OR b.action_priority='P1-重点整改')
|
|
THEN '高风险：距离近且至少一家经营承压'
|
|
WHEN p.distance_km<1.5 THEN '中风险：需核查客群和配送圈重叠'
|
|
ELSE '观察'
|
|
END AS overlap_risk
|
|
FROM analytics.v_store_spatial_pairs_april p
|
|
JOIN analytics.v_store_site_profile_april a ON a.store_code=p.store_code_a
|
|
JOIN analytics.v_store_site_profile_april b ON b.store_code=p.store_code_b
|
|
WHERE p.distance_km<3;
|
|
|
|
-- 典型结果查询
|
|
SELECT site_scene,count(*) store_count,round(avg(received)::numeric,0) avg_received,
|
|
round(avg(monthly_received_per_sqm)::numeric,2) avg_received_per_sqm
|
|
FROM analytics.v_store_site_profile_april
|
|
WHERE business_type='标准门店' AND received>0 AND area_sqm IS NOT NULL
|
|
GROUP BY site_scene ORDER BY avg_received_per_sqm DESC;
|
|
|
|
SELECT store_name,site_scene,area_sqm,received,monthly_received_per_sqm,
|
|
nearest_store_name,nearest_distance_km,site_replication_score,
|
|
replication_recommendation,spatial_recommendation
|
|
FROM analytics.v_store_site_replication_score_april
|
|
ORDER BY site_replication_score DESC LIMIT 20;
|
|
|
|
SELECT store_name_a,store_name_b,round(distance_km::numeric,2) distance_km,
|
|
received_a,received_b,overlap_risk
|
|
FROM analytics.v_store_location_overlap_risk_april
|
|
ORDER BY distance_km LIMIT 30;
|