freedakgmail
226 lines
9.2 KiB
SQL
226 lines
9.2 KiB
SQL
-- ============================================================
|
|
-- 03_create_views.sql
|
|
-- 周度检查视图 + 月度验收视图 + 闭环健康度视图 + 数据质量视图
|
|
-- ============================================================
|
|
|
|
-- 周度检查视图
|
|
CREATE OR REPLACE VIEW analytics.v_task_weekly_check AS
|
|
SELECT
|
|
w.id, w.task_id, w.store_code, w.store_name, w.problem_indicator,
|
|
w.iso_week,
|
|
round(w.this_week_value, 2) AS this_week_value,
|
|
round(w.last_week_value, 2) AS last_week_value,
|
|
w.change_direction,
|
|
w.consecutive_no_improve_weeks,
|
|
w.check_comment,
|
|
w.checked_by,
|
|
w.checked_at,
|
|
t.plan_month,
|
|
t.priority,
|
|
t.status AS task_status,
|
|
t.target_value,
|
|
CASE
|
|
WHEN w.consecutive_no_improve_weeks >= 2 THEN '需重新判断原因'
|
|
WHEN w.change_direction = 'up' AND t.problem_indicator IN ('优惠率','异常率','实际成本率','平台加权成本率') THEN '改善中'
|
|
WHEN w.change_direction = 'down' AND t.problem_indicator IN ('优惠率','异常率','实际成本率','平台加权成本率') THEN '需关注'
|
|
WHEN w.change_direction = 'up' AND t.problem_indicator IN ('理论毛利率','会员复购率','饮品搭售率','经营稳定性') THEN '改善中'
|
|
WHEN w.change_direction = 'down' AND t.problem_indicator IN ('理论毛利率','会员复购率','饮品搭售率','经营稳定性') THEN '需关注'
|
|
ELSE '观察中'
|
|
END AS improvement_status
|
|
FROM analytics.task_weekly_check w
|
|
JOIN analytics.store_task t ON w.task_id = t.task_id;
|
|
|
|
-- 月度验收视图
|
|
CREATE OR REPLACE VIEW analytics.v_task_monthly_review AS
|
|
SELECT
|
|
r.id, r.task_id, r.store_code, r.store_name, r.plan_month,
|
|
r.problem_indicator,
|
|
round(r.baseline_value, 2) AS baseline_value,
|
|
round(r.target_value, 2) AS target_value,
|
|
round(r.actual_value, 2) AS actual_value,
|
|
r.review_result,
|
|
r.revenue_stable,
|
|
r.margin_improved,
|
|
r.customer_stable,
|
|
r.anomaly_decreased,
|
|
CASE
|
|
WHEN r.review_result = '达标' THEN true
|
|
WHEN r.review_result = '改善中' AND r.revenue_stable AND r.customer_stable THEN true
|
|
ELSE false
|
|
END AS can_promote,
|
|
t.priority,
|
|
t.owner,
|
|
t.action_required,
|
|
t.process_evidence,
|
|
t.incomplete_reason,
|
|
t.next_step
|
|
FROM analytics.task_monthly_review r
|
|
JOIN analytics.store_task t ON r.task_id = t.task_id;
|
|
|
|
-- 闭环健康度视图
|
|
CREATE OR REPLACE VIEW analytics.v_loop_health AS
|
|
WITH task_stats AS (
|
|
SELECT
|
|
count(DISTINCT store_code) AS total_stores,
|
|
count(DISTINCT CASE WHEN status != '待启动' THEN store_code END) AS executed_stores,
|
|
count(*) AS total_tasks,
|
|
count(*) FILTER (WHERE status = '已验收') AS verified_tasks,
|
|
count(*) FILTER (WHERE status = '已回滚') AS rolled_back_tasks
|
|
FROM analytics.store_task
|
|
WHERE plan_month = date_trunc('month', COALESCE(
|
|
(SELECT max(plan_month) FROM analytics.store_task), CURRENT_DATE))
|
|
),
|
|
weekly_stats AS (
|
|
SELECT
|
|
count(DISTINCT t.store_code) AS stores_with_weekly_check
|
|
FROM analytics.task_weekly_check w
|
|
JOIN analytics.store_task t ON w.task_id = t.task_id
|
|
WHERE t.plan_month = date_trunc('month', COALESCE(
|
|
(SELECT max(plan_month) FROM analytics.store_task), CURRENT_DATE))
|
|
AND w.checked_at >= date_trunc('month', COALESCE(
|
|
(SELECT max(plan_month) FROM analytics.store_task), CURRENT_DATE))
|
|
),
|
|
practice_stats AS (
|
|
SELECT
|
|
count(*) AS total_practices,
|
|
count(*) FILTER (WHERE status = '已推广') AS promoted_practices
|
|
FROM analytics.standardized_practice
|
|
),
|
|
store_count AS (
|
|
SELECT count(DISTINCT store_code) AS total FROM analytics.v_store_scorecard
|
|
)
|
|
SELECT
|
|
round(COALESCE(ts.total_stores::numeric / NULLIF(sc.total, 0) * 100, 0), 1) AS task_generation_rate,
|
|
round(COALESCE(ts.executed_stores::numeric / NULLIF(ts.total_stores, 0) * 100, 0), 1) AS store_execution_rate,
|
|
round(COALESCE(ws.stores_with_weekly_check::numeric / NULLIF(ts.total_stores, 0) * 100, 0), 1) AS weekly_check_rate,
|
|
round(COALESCE(ts.verified_tasks::numeric / NULLIF(ts.total_tasks, 0) * 100, 0), 1) AS monthly_review_rate,
|
|
round(COALESCE(ps.promoted_practices::numeric / NULLIF(ps.total_practices, 0) * 100, 0), 1) AS practice_promotion_rate
|
|
FROM task_stats ts
|
|
CROSS JOIN weekly_stats ws
|
|
CROSS JOIN practice_stats ps
|
|
CROSS JOIN store_count sc;
|
|
|
|
-- 数据质量检查视图
|
|
CREATE OR REPLACE VIEW analytics.v_data_quality_check AS
|
|
WITH bill_stats AS (
|
|
SELECT
|
|
count(*) AS total_bills,
|
|
count(*) FILTER (WHERE NULLIF(c005, '') IS NULL) AS missing_bill_no,
|
|
count(*) FILTER (WHERE NULLIF(c002, '') IS NULL) AS missing_store_code,
|
|
count(*) FILTER (WHERE c009::numeric <= 0 OR c009 IS NULL) AS zero_consumption,
|
|
count(*) FILTER (WHERE c114::numeric < 0) AS negative_received,
|
|
count(DISTINCT NULLIF(c002, '')) AS store_count,
|
|
min(c175::timestamp) AS min_date,
|
|
max(c176::timestamp) AS max_date
|
|
FROM bill_records
|
|
),
|
|
dish_stats AS (
|
|
SELECT
|
|
count(*) AS total_dish_records,
|
|
count(*) FILTER (WHERE store_code IS NULL OR store_code = '') AS missing_store,
|
|
count(*) FILTER (WHERE dish_name IS NULL OR dish_name = '') AS missing_dish
|
|
FROM dish_sales_details
|
|
)
|
|
SELECT
|
|
b.total_bills,
|
|
b.missing_bill_no,
|
|
b.missing_store_code,
|
|
b.zero_consumption,
|
|
b.negative_received,
|
|
b.store_count,
|
|
b.min_date,
|
|
b.max_date,
|
|
d.total_dish_records,
|
|
d.missing_store AS dish_missing_store,
|
|
d.missing_dish AS dish_missing_dish,
|
|
CASE
|
|
WHEN b.missing_bill_no > 0 THEN '有账单缺失单号'
|
|
WHEN b.missing_store_code > 0 THEN '有账单缺失门店编码'
|
|
WHEN b.negative_received > 0 THEN '有负实收账单'
|
|
ELSE '数据完整性正常'
|
|
END AS bill_quality_status,
|
|
CASE
|
|
WHEN d.missing_store > 0 OR d.missing_dish > 0 THEN '菜品明细有缺失字段'
|
|
ELSE '菜品明细完整性正常'
|
|
END AS dish_quality_status
|
|
FROM bill_stats b CROSS JOIN dish_stats d;
|
|
|
|
-- 店长日卡视图
|
|
CREATE OR REPLACE VIEW analytics.v_store_daily_card AS
|
|
WITH latest_date AS (
|
|
SELECT max(closed_at)::date AS business_date FROM analytics.bill_fact WHERE closed_at IS NOT NULL
|
|
),
|
|
-- 收入模块
|
|
revenue AS (
|
|
SELECT bf.store_code, bf.store_name,
|
|
'收入' AS module,
|
|
jsonb_build_array(
|
|
jsonb_build_object('metric', '实收', 'value', round(sum(bf.received_total), 2),
|
|
'baseline', round(avg(sw.received), 2), 'is_anomaly',
|
|
sum(bf.received_total) < avg(sw.received) * 0.8),
|
|
jsonb_build_object('metric', '账单数', 'value', count(*),
|
|
'baseline', round(avg(sw.bill_count), 0), 'is_anomaly',
|
|
count(*) < avg(sw.bill_count) * 0.8),
|
|
jsonb_build_object('metric', '客单价', 'value', round(sum(bf.received_total)/count(*), 2),
|
|
'baseline', round(avg(sw.avg_bill), 2), 'is_anomaly',
|
|
sum(bf.received_total)/count(*) < avg(sw.avg_bill) * 0.9)
|
|
) AS anomalies
|
|
FROM analytics.bill_fact bf
|
|
CROSS JOIN latest_date ld
|
|
LEFT JOIN LATERAL (
|
|
SELECT sum(r.received_total) AS received, count(*) AS bill_count,
|
|
sum(r.received_total)/count(*) AS avg_bill
|
|
FROM analytics.bill_fact r
|
|
WHERE r.store_code = bf.store_code
|
|
AND r.closed_at::date >= ld.business_date - 28
|
|
AND r.closed_at::date < ld.business_date
|
|
AND extract(isodow FROM r.closed_at) = extract(isodow FROM ld.business_date)
|
|
) sw ON true
|
|
WHERE bf.closed_at::date = ld.business_date
|
|
GROUP BY bf.store_code, bf.store_name
|
|
),
|
|
-- 优惠模块
|
|
discount AS (
|
|
SELECT bf.store_code, bf.store_name,
|
|
'优惠' AS module,
|
|
jsonb_build_array(
|
|
jsonb_build_object('metric', '优惠率', 'value',
|
|
round(sum(bf.discount_total)/nullif(sum(bf.consumption), 0) * 100, 2),
|
|
'baseline', 20.23, 'is_anomaly',
|
|
sum(bf.discount_total)/nullif(sum(bf.consumption), 0) * 100 > 25),
|
|
jsonb_build_object('metric', '异常优惠账单', 'value',
|
|
count(*) FILTER (WHERE bf.discount_total > bf.consumption AND bf.consumption > 0),
|
|
'baseline', 0, 'is_anomaly',
|
|
count(*) FILTER (WHERE bf.discount_total > bf.consumption AND bf.consumption > 0) > 5)
|
|
) AS anomalies
|
|
FROM analytics.bill_fact bf
|
|
CROSS JOIN latest_date ld
|
|
WHERE bf.closed_at::date = ld.business_date
|
|
GROUP BY bf.store_code, bf.store_name
|
|
),
|
|
-- 风险模块
|
|
risk AS (
|
|
SELECT bf.store_code, bf.store_name,
|
|
'风险' AS module,
|
|
jsonb_build_array(
|
|
jsonb_build_object('metric', '零实收账单', 'value',
|
|
count(*) FILTER (WHERE bf.received_total = 0), 'baseline', 0, 'is_anomaly',
|
|
count(*) FILTER (WHERE bf.received_total = 0) > 3),
|
|
jsonb_build_object('metric', '撤单/退款', 'value',
|
|
count(*) FILTER (WHERE bf.bill_status IN ('撤单', '退款')), 'baseline', 0, 'is_anomaly',
|
|
count(*) FILTER (WHERE bf.bill_status IN ('撤单', '退款')) > 2)
|
|
) AS anomalies
|
|
FROM analytics.bill_fact bf
|
|
CROSS JOIN latest_date ld
|
|
WHERE bf.closed_at::date = ld.business_date
|
|
GROUP BY bf.store_code, bf.store_name
|
|
)
|
|
SELECT
|
|
COALESCE(r.store_code, d.store_code, rk.store_code) AS store_code,
|
|
COALESCE(r.store_name, d.store_name, rk.store_name) AS store_name,
|
|
COALESCE(r.module, d.module, rk.module) AS module,
|
|
COALESCE(r.anomalies, d.anomalies, rk.anomalies) AS anomalies
|
|
FROM revenue r
|
|
FULL JOIN discount d USING (store_code, store_name)
|
|
FULL JOIN risk rk USING (store_code, store_name);
|