SBrainCO/db/migration_phase6_site_functions.sql

-- ============================================================
-- Phase 6: 选址/空间参数化函数
-- 替换 _april 后缀的选址/空间视图为参数化函数
-- 依赖: fn_store_area_efficiency(p_month) 已在 Phase 2 中创建
-- ============================================================

-- 1. fn_store_site_profile(p_month) — 替换 v_store_site_profile_april
-- 依赖: fn_store_area_efficiency
CREATE OR REPLACE FUNCTION analytics.fn_store_site_profile(p_month date)
RETURNS TABLE (
  store_code text, store_name text, bill_count bigint, active_days bigint,
  received numeric, avg_daily_received numeric, avg_bill_value numeric,
  discount_rate_pct numeric, theoretical_margin_pct numeric, member_bill_share_pct numeric,
  items_per_bill numeric, skus_per_bill numeric, delivery_bill_share_pct numeric,
  noodle_snack_attach_pct numeric, noodle_drink_attach_pct numeric, noodle_cold_attach_pct numeric,
  combo_bill_share_pct numeric, theoretical_cost numeric, actual_food_cost numeric,
  food_cost_variance numeric, theoretical_cost_rate_pct numeric, actual_food_cost_rate_pct numeric,
  variance_to_theoretical_pct numeric, comparison_status text, variance_level text,
  identified_members bigint, repeat_rate_pct numeric, repeat_revenue_share_pct numeric,
  benchmark_score numeric, meituan_received numeric, taobao_received numeric, jd_received numeric,
  combined_platform_cost_rate_pct numeric, business_type text, scale_tier text,
  problem_count bigint, problem_combination text, action_priority text,
  area_sqm numeric, business_address text, city text, district text,
  latitude_gcj02 double precision, longitude_gcj02 double precision,
  open_date date, lease_expiry_date date, store_age_years numeric,
  monthly_received_per_sqm numeric, daily_received_per_sqm numeric,
  estimated_inventory_days numeric, site_scene text, floor_type text,
  area_band text, age_band text
) AS $$
  SELECT
    a.store_code, a.store_name, a.bill_count, a.active_days,
    a.received, a.avg_daily_received, a.avg_bill_value,
    a.discount_rate_pct, a.theoretical_margin_pct, a.member_bill_share_pct,
    a.items_per_bill, a.skus_per_bill, a.delivery_bill_share_pct,
    a.noodle_snack_attach_pct, a.noodle_drink_attach_pct, a.noodle_cold_attach_pct,
    a.combo_bill_share_pct, a.theoretical_cost, a.actual_food_cost,
    a.food_cost_variance, a.theoretical_cost_rate_pct, a.actual_food_cost_rate_pct,
    a.variance_to_theoretical_pct, a.comparison_status, a.variance_level,
    a.identified_members, a.repeat_rate_pct, a.repeat_revenue_share_pct,
    a.benchmark_score, a.meituan_received, a.taobao_received, a.jd_received,
    a.combined_platform_cost_rate_pct, a.business_type, a.scale_tier,
    a.problem_count, a.problem_combination, a.action_priority,
    a.area_sqm, a.business_address, a.city, a.district,
    a.latitude_gcj02, a.longitude_gcj02,
    a.open_date, a.lease_expiry_date, a.store_age_years,
    a.monthly_received_per_sqm, a.daily_received_per_sqm,
    a.estimated_inventory_days,
    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.fn_store_area_efficiency(p_month) a
$$ LANGUAGE SQL STABLE;

-- 2. fn_store_spatial_pairs(p_month) — 替换 v_store_spatial_pairs_april
CREATE OR REPLACE FUNCTION analytics.fn_store_spatial_pairs(p_month date)
RETURNS TABLE (
  store_code_a text, store_name_a text, store_code_b text, store_name_b text,
  district_a text, district_b text, scene_a text, scene_b text,
  priority_a text, priority_b text, received_a numeric, received_b numeric,
  sqm_efficiency_a numeric, sqm_efficiency_b numeric, distance_km double precision,
  proximity_level text
) AS $$
  WITH physical AS (
    SELECT store_code, store_name, district, site_scene, action_priority,
      received, monthly_received_per_sqm, latitude_gcj02 AS lat, longitude_gcj02 AS lon
    FROM analytics.fn_store_site_profile(p_month)
    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.0 * 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
    pairs.store_code_a, pairs.store_name_a, pairs.store_code_b, pairs.store_name_b,
    pairs.district_a, pairs.district_b, pairs.scene_a, pairs.scene_b,
    pairs.priority_a, pairs.priority_b, pairs.received_a, pairs.received_b,
    pairs.sqm_efficiency_a, pairs.sqm_efficiency_b, pairs.distance_km,
    CASE
      WHEN pairs.distance_km < 1 THEN '高度重叠<1km'
      WHEN pairs.distance_km < 2 THEN '较高重叠1-2km'
      WHEN pairs.distance_km < 3 THEN '观察2-3km'
      ELSE '相对独立≥3km'
    END AS proximity_level
  FROM pairs
$$ LANGUAGE SQL STABLE;

-- 3. fn_store_nearest_neighbor(p_month) — 替换 v_store_nearest_neighbor_april
CREATE OR REPLACE FUNCTION analytics.fn_store_nearest_neighbor(p_month date)
RETURNS TABLE (
  store_code text, store_name text, nearest_store_code text, nearest_store_name text,
  nearest_distance_km numeric, nearest_proximity_level text
) 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.fn_store_spatial_pairs(p_month)
    UNION ALL
    SELECT store_code_b, store_name_b, store_code_a, store_name_a, distance_km
    FROM analytics.fn_store_spatial_pairs(p_month)
  ), ranked AS (
    SELECT store_code, store_name, nearest_store_code, nearest_store_name, distance_km,
      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
$$ LANGUAGE SQL STABLE;

-- 4. fn_store_site_replication(p_month) — 替换 v_store_site_replication_score_april
CREATE OR REPLACE FUNCTION analytics.fn_store_site_replication(p_month date)
RETURNS TABLE (
  store_code text, store_name text, bill_count bigint, active_days bigint,
  received numeric, avg_daily_received numeric, avg_bill_value numeric,
  discount_rate_pct numeric, theoretical_margin_pct numeric, member_bill_share_pct numeric,
  items_per_bill numeric, skus_per_bill numeric, delivery_bill_share_pct numeric,
  noodle_snack_attach_pct numeric, noodle_drink_attach_pct numeric, noodle_cold_attach_pct numeric,
  combo_bill_share_pct numeric, theoretical_cost numeric, actual_food_cost numeric,
  food_cost_variance numeric, theoretical_cost_rate_pct numeric, actual_food_cost_rate_pct numeric,
  variance_to_theoretical_pct numeric, comparison_status text, variance_level text,
  identified_members bigint, repeat_rate_pct numeric, repeat_revenue_share_pct numeric,
  benchmark_score numeric, meituan_received numeric, taobao_received numeric, jd_received numeric,
  combined_platform_cost_rate_pct numeric, business_type text, scale_tier text,
  problem_count bigint, problem_combination text, action_priority text,
  area_sqm numeric, business_address text, city text, district text,
  latitude_gcj02 double precision, longitude_gcj02 double precision,
  open_date date, lease_expiry_date date, store_age_years numeric,
  monthly_received_per_sqm numeric, daily_received_per_sqm numeric,
  estimated_inventory_days numeric, site_scene text, floor_type text,
  area_band text, age_band text,
  nearest_store_code text, nearest_store_name text, nearest_distance_km numeric,
  sqm_score double precision, daily_score double precision, repeat_score double precision,
  discount_score double precision, cost_score double precision, platform_score double precision,
  execution_score numeric, site_replication_score numeric,
  replication_recommendation text, spatial_recommendation text
) 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.0 - percent_rank() OVER (ORDER BY p.discount_rate_pct) AS discount_score,
      1.0 - percent_rank() OVER (ORDER BY p.actual_food_cost_rate_pct) AS cost_score,
      1.0 - percent_rank() OVER (ORDER BY p.combined_platform_cost_rate_pct) AS platform_score,
      GREATEST(0, 1 - p.problem_count / 6.0) AS execution_score
    FROM analytics.fn_store_site_profile(p_month) p
    LEFT JOIN analytics.fn_store_nearest_neighbor(p_month) n USING (store_code, store_name)
    WHERE p.business_type = '标准门店' AND p.received > 0 AND p.area_sqm IS NOT NULL
  ), scored AS (
    SELECT eligible.*,
      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::double precision)::numeric * 100, 2) AS site_replication_score
    FROM eligible
  )
  SELECT scored.*,
    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
$$ LANGUAGE SQL STABLE;

-- 5. fn_store_overlap_risk(p_month) — 替换 v_store_location_overlap_risk_april
CREATE OR REPLACE FUNCTION analytics.fn_store_overlap_risk(p_month date)
RETURNS TABLE (
  store_code_a text, store_name_a text, store_code_b text, store_name_b text,
  district_a text, district_b text, scene_a text, scene_b text,
  priority_a text, priority_b text, received_a numeric, received_b numeric,
  sqm_efficiency_a numeric, sqm_efficiency_b numeric, distance_km double precision,
  proximity_level text, problem_count_a bigint, problem_count_b bigint,
  overlap_risk text
) AS $$
  SELECT
    p.store_code_a, p.store_name_a, p.store_code_b, p.store_name_b,
    p.district_a, p.district_b, p.scene_a, p.scene_b,
    p.priority_a, p.priority_b, p.received_a, p.received_b,
    p.sqm_efficiency_a, p.sqm_efficiency_b, p.distance_km, p.proximity_level,
    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.fn_store_spatial_pairs(p_month) p
  JOIN analytics.fn_store_site_profile(p_month) a ON a.store_code = p.store_code_a
  JOIN analytics.fn_store_site_profile(p_month) b ON b.store_code = p.store_code_b
  WHERE p.distance_km < 3
$$ LANGUAGE SQL STABLE;

-- 6. fn_district_site_benchmark(p_month) — 替换 v_district_site_benchmark_april
CREATE OR REPLACE FUNCTION analytics.fn_district_site_benchmark(p_month date)
RETURNS TABLE (
  city text, district text, store_count bigint, avg_area_sqm numeric,
  total_received numeric, avg_received numeric, median_received numeric,
  avg_received_per_sqm numeric, avg_bill_value numeric, avg_discount_rate_pct numeric,
  avg_repeat_rate_pct numeric, avg_actual_cost_rate_pct numeric,
  avg_platform_cost_rate_pct numeric, p0_count bigint, p1_count bigint
) AS $$
  SELECT
    city, district,
    count(*) AS store_count,
    round(avg(area_sqm), 1) AS avg_area_sqm,
    round(sum(received), 2) AS total_received,
    round(avg(received), 2) AS avg_received,
    round(percentile_cont(0.5) WITHIN GROUP (ORDER BY received::double precision)::numeric, 2) AS median_received,
    round(avg(monthly_received_per_sqm), 2) AS avg_received_per_sqm,
    round(avg(avg_bill_value), 2) AS avg_bill_value,
    round(avg(discount_rate_pct), 2) AS avg_discount_rate_pct,
    round(avg(repeat_rate_pct), 2) AS avg_repeat_rate_pct,
    round(avg(actual_food_cost_rate_pct) FILTER (WHERE comparison_status = '可比'), 2) AS avg_actual_cost_rate_pct,
    round(avg(combined_platform_cost_rate_pct), 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.fn_store_site_profile(p_month)
  WHERE business_type = '标准门店' AND received > 0
  GROUP BY city, district
$$ LANGUAGE SQL STABLE;

-- 7. fn_site_segment_benchmark(p_month) — 替换 v_site_segment_benchmark_april
CREATE OR REPLACE FUNCTION analytics.fn_site_segment_benchmark(p_month date)
RETURNS TABLE (
  site_scene text, area_band text, store_count bigint, avg_area_sqm numeric,
  avg_received numeric, median_received numeric, avg_received_per_sqm numeric,
  median_received_per_sqm numeric, avg_bill_value numeric, avg_discount_rate_pct numeric,
  avg_repeat_rate_pct numeric, avg_actual_cost_rate_pct numeric,
  avg_delivery_share_pct numeric, avg_drink_attach_pct numeric
) AS $$
  SELECT
    site_scene, area_band,
    count(*) AS store_count,
    round(avg(area_sqm), 1) AS avg_area_sqm,
    round(avg(received), 2) AS avg_received,
    round(percentile_cont(0.5) WITHIN GROUP (ORDER BY received::double precision)::numeric, 2) AS median_received,
    round(avg(monthly_received_per_sqm), 2) AS avg_received_per_sqm,
    round(percentile_cont(0.5) WITHIN GROUP (ORDER BY monthly_received_per_sqm::double precision)::numeric, 2) AS median_received_per_sqm,
    round(avg(avg_bill_value), 2) AS avg_bill_value,
    round(avg(discount_rate_pct), 2) AS avg_discount_rate_pct,
    round(avg(repeat_rate_pct), 2) AS avg_repeat_rate_pct,
    round(avg(actual_food_cost_rate_pct) FILTER (WHERE comparison_status = '可比'), 2) AS avg_actual_cost_rate_pct,
    round(avg(delivery_bill_share_pct), 2) AS avg_delivery_share_pct,
    round(avg(noodle_drink_attach_pct), 2) AS avg_drink_attach_pct
  FROM analytics.fn_store_site_profile(p_month)
  WHERE business_type = '标准门店' AND received > 0 AND area_sqm IS NOT NULL
  GROUP BY site_scene, area_band
$$ LANGUAGE SQL STABLE;

-- 8. fn_dish_member_repeat(p_month) — 替换 v_dish_member_repeat_april
CREATE OR REPLACE FUNCTION analytics.fn_dish_member_repeat(p_month date)
RETURNS TABLE (
  dish_name text, purchasing_members bigint, repeat_members bigint,
  repeat_member_rate_pct numeric, avg_member_orders numeric, member_received_amount numeric
) AS $$
  SELECT
    dish_name,
    count(*) AS purchasing_members,
    count(*) FILTER (WHERE order_count >= 2) AS repeat_members,
    round(count(*) FILTER (WHERE order_count >= 2)::numeric / NULLIF(count(*), 0) * 100, 2) AS repeat_member_rate_pct,
    round(avg(order_count), 2) AS avg_member_orders,
    sum(received_amount) AS member_received_amount
  FROM analytics.fn_dish_member_sku(p_month)
  GROUP BY dish_name
$$ LANGUAGE SQL STABLE;