freedakgmail
25db0f3854
- 创建12个按月物化视图替代实时函数调用 - phase10: mv_dish_sku_summary_monthly, mv_dish_sku_abc_monthly - phase11: mv_store_risk_rating/platform_economics/category_mix/member_opportunity/benchmark_composite/deep_diagnosis/dish_basket/dish_store_summary/theoretical_actual_cost (全部_monthly) - 后端所有路由从 fn_xxx() 改为 mv_xxx_monthly WHERE month_start = - 新增 analytics.refresh_all_monthly_views() 刷新函数 - 优化 fn_dish_sku_summary: CTE物化避免重复调用 - 优化 fn_dish_sku_abc: CTE物化 fn_dish_sku_summary 只调用一次 - 优化 fn_dish_sales: 添加 dish_name IS NOT NULL 提前过滤
104 lines
4.5 KiB
PL/PgSQL
104 lines
4.5 KiB
PL/PgSQL
-- ============================================================
|
|
-- Phase 10: SKU 物化视图 — 按月预计算 fn_dish_sku_summary / fn_dish_sku_abc
|
|
-- 解决：fn_dish_sku_summary 实时计算需 80+ 秒，物化后毫秒级
|
|
-- 用法：数据导入后执行 REFRESH MATERIALIZED VIEW 或调用刷新函数
|
|
-- ============================================================
|
|
|
|
-- 1. mv_dish_sku_summary_monthly — 按月物化菜品SKU汇总
|
|
DROP MATERIALIZED VIEW IF EXISTS analytics.mv_dish_sku_summary_monthly;
|
|
CREATE MATERIALIZED VIEW analytics.mv_dish_sku_summary_monthly AS
|
|
SELECT
|
|
date_trunc('month', b.closed_at)::date AS month_start,
|
|
d.dish_name,
|
|
min(d.category_level1) AS category_level1,
|
|
min(d.category_level2) AS category_level2,
|
|
count(*) AS detail_rows,
|
|
count(DISTINCT d.store_code || '|' || d.bill_no) AS bill_count,
|
|
count(DISTINCT d.store_code) AS store_count,
|
|
sum(d.sales_quantity) AS sales_quantity,
|
|
sum(d.gross_amount) AS gross_amount,
|
|
sum(d.received_amount) AS received_amount,
|
|
sum(d.gross_amount - d.received_amount) AS discount_amount,
|
|
round(sum(d.gross_amount - d.received_amount) / NULLIF(sum(d.gross_amount), 0) * 100, 2) AS discount_rate_pct,
|
|
round(sum(d.received_amount) / NULLIF(sum(d.sales_quantity), 0), 2) AS realized_unit_price
|
|
FROM public.dish_sales_details d
|
|
JOIN analytics.bill_fact b ON b.store_code = d.store_code AND b.bill_no = d.bill_no
|
|
WHERE d.dish_name IS NOT NULL
|
|
GROUP BY date_trunc('month', b.closed_at)::date, d.dish_name;
|
|
|
|
CREATE UNIQUE INDEX idx_mv_sku_summary_month_dish ON analytics.mv_dish_sku_summary_monthly (month_start, dish_name);
|
|
CREATE INDEX idx_mv_sku_summary_month ON analytics.mv_dish_sku_summary_monthly (month_start);
|
|
|
|
-- 2. mv_dish_sku_abc_monthly — 按月物化ABC分类
|
|
DROP MATERIALIZED VIEW IF EXISTS analytics.mv_dish_sku_abc_monthly;
|
|
CREATE MATERIALIZED VIEW analytics.mv_dish_sku_abc_monthly AS
|
|
WITH medians AS (
|
|
SELECT
|
|
month_start,
|
|
percentile_cont(0.5) WITHIN GROUP (ORDER BY sales_quantity::double precision) AS median_quantity,
|
|
percentile_cont(0.5) WITHIN GROUP (ORDER BY received_amount::double precision) AS median_revenue
|
|
FROM analytics.mv_dish_sku_summary_monthly
|
|
GROUP BY month_start
|
|
),
|
|
ranked AS (
|
|
SELECT
|
|
s.*,
|
|
sum(s.received_amount) OVER (PARTITION BY s.month_start ORDER BY s.received_amount DESC, s.dish_name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
/ NULLIF(sum(s.received_amount) OVER (PARTITION BY s.month_start), 0) AS cumulative_revenue_share,
|
|
m.median_quantity,
|
|
m.median_revenue
|
|
FROM analytics.mv_dish_sku_summary_monthly s
|
|
JOIN medians m ON s.month_start = m.month_start
|
|
)
|
|
SELECT
|
|
month_start,
|
|
dish_name,
|
|
category_level1,
|
|
category_level2,
|
|
detail_rows,
|
|
bill_count,
|
|
store_count,
|
|
sales_quantity,
|
|
gross_amount,
|
|
received_amount,
|
|
discount_amount,
|
|
discount_rate_pct,
|
|
realized_unit_price,
|
|
round(received_amount / NULLIF(sum(received_amount) OVER (PARTITION BY month_start), 0) * 100, 4) AS revenue_share_pct,
|
|
cumulative_revenue_share,
|
|
median_quantity,
|
|
median_revenue,
|
|
CASE
|
|
WHEN cumulative_revenue_share <= 0.70 THEN 'A-核心'
|
|
WHEN cumulative_revenue_share <= 0.90 THEN 'B-成长'
|
|
ELSE 'C-长尾'
|
|
END AS abc_class,
|
|
CASE
|
|
WHEN sales_quantity::double precision >= median_quantity AND received_amount::double precision >= median_revenue THEN '明星菜品'
|
|
WHEN sales_quantity::double precision >= median_quantity AND received_amount::double precision < median_revenue THEN '引流菜品'
|
|
WHEN sales_quantity::double precision < median_quantity AND received_amount::double precision >= median_revenue THEN '潜力菜品'
|
|
ELSE '淘汰观察品'
|
|
END AS sales_quadrant
|
|
FROM ranked;
|
|
|
|
CREATE UNIQUE INDEX idx_mv_sku_abc_month_dish ON analytics.mv_dish_sku_abc_monthly (month_start, dish_name);
|
|
CREATE INDEX idx_mv_sku_abc_month ON analytics.mv_dish_sku_abc_monthly (month_start);
|
|
CREATE INDEX idx_mv_sku_abc_class ON analytics.mv_dish_sku_abc_monthly (month_start, abc_class);
|
|
|
|
-- 3. 刷新函数 — 数据导入后调用
|
|
CREATE OR REPLACE FUNCTION analytics.refresh_sku_materialized_views(p_month date DEFAULT NULL)
|
|
RETURNS void
|
|
LANGUAGE plpgsql
|
|
AS $function$
|
|
BEGIN
|
|
IF p_month IS NULL THEN
|
|
REFRESH MATERIALIZED VIEW analytics.mv_dish_sku_summary_monthly;
|
|
REFRESH MATERIALIZED VIEW analytics.mv_dish_sku_abc_monthly;
|
|
ELSE
|
|
-- 全量刷新（物化视图不支持增量，但数据量小刷新快）
|
|
REFRESH MATERIALIZED VIEW analytics.mv_dish_sku_summary_monthly;
|
|
REFRESH MATERIALIZED VIEW analytics.mv_dish_sku_abc_monthly;
|
|
END IF;
|
|
END;
|
|
$function$;
|