SBrainCO/db/etl_batch1_fill_dims.sql

-- ============================================================
-- ETL Batch 1: 填充维度主数据表
-- 日期: 2026-08-02
-- 说明: 从 bill_fact / salary_detail_records / dish_sales_details 
--       聚合数据填充 dim_member / dim_employee / dim_channel / dim_sku
--       并创建 member_level / position 标准化映射表
-- 执行: psql -d bill_query -f db/etl_batch1_fill_dims.sql
-- ============================================================

-- ============================================================
-- 1. 填充 dim_member — 会员主数据
-- ============================================================
TRUNCATE TABLE analytics.dim_member;

INSERT INTO analytics.dim_member (
  member_id, phone_hash, register_channel, register_store, register_date,
  member_level, total_orders, total_revenue, last_order_date, status, tags,
  created_at, updated_at
)
WITH member_raw AS (
  SELECT 
    member_id,
    -- 首笔消费
    MIN(opened_at) AS first_order,
    MAX(opened_at) AS last_order,
    COUNT(DISTINCT bill_no) AS order_count,
    SUM(received_total) AS total_revenue,
    -- 最新等级（取最近一笔的member_level）
    (array_agg(member_level ORDER BY opened_at DESC))[1] AS latest_level,
    -- 首笔消费门店
    (array_agg(store_code ORDER BY opened_at ASC))[1] AS first_store,
    -- 首笔消费渠道
    (array_agg(
      CASE 
        WHEN meituan_delivery_received > 0 THEN '美团外卖'
        WHEN taobao_delivery_received > 0 THEN '淘宝外卖'
        WHEN jd_delivery_received > 0 THEN '京东外卖'
        WHEN meituan_received > 0 THEN '美团到店'
        WHEN douyin_received > 0 THEN '抖音'
        WHEN cash_received > 0 THEN '现金'
        WHEN alipay_received > 0 THEN '支付宝'
        WHEN wechat_received > 0 THEN '微信'
        WHEN unionpay_received > 0 THEN '银联'
        WHEN credit_received > 0 THEN '挂账'
        ELSE '堂食'
      END
      ORDER BY opened_at ASC
    ))[1] AS first_channel
  FROM analytics.bill_fact
  WHERE member_id IS NOT NULL AND member_id != ''
  GROUP BY member_id
),
max_date AS (
  SELECT MAX(opened_at)::date AS d FROM analytics.bill_fact WHERE opened_at IS NOT NULL
)
SELECT 
  mr.member_id,
  NULL AS phone_hash,  -- 无手机号数据
  mr.first_channel AS register_channel,
  mr.first_store AS register_store,
  (mr.first_order AT TIME ZONE 'Asia/Shanghai')::date AS register_date,
  -- 标准化会员等级
  CASE 
    WHEN mr.latest_level IN ('1') THEN '普通'
    WHEN mr.latest_level IN ('2','3') THEN '银卡'
    WHEN mr.latest_level IN ('4','5') THEN '金卡'
    WHEN mr.latest_level IN ('6','7','LV6','LV7') THEN '钻石'
    ELSE '普通'
  END AS member_level,
  mr.order_count::integer AS total_orders,
  round(mr.total_revenue::numeric, 2) AS total_revenue,
  (mr.last_order AT TIME ZONE 'Asia/Shanghai')::date AS last_order_date,
  -- 会员状态（基于最新账单日期）
  CASE 
    WHEN (md.d - (mr.last_order AT TIME ZONE 'Asia/Shanghai')::date) <= 30 THEN '活跃'
    WHEN (md.d - (mr.last_order AT TIME ZONE 'Asia/Shanghai')::date) <= 90 THEN '沉睡'
    ELSE '流失'
  END AS status,
  -- 标签
  CASE 
    WHEN mr.order_count = 1 THEN ARRAY['新客']
    WHEN mr.order_count >= 20 THEN ARRAY['高频']
    WHEN mr.total_revenue >= 500 THEN ARRAY['高价值']
    ELSE ARRAY[]::TEXT[]
  END AS tags,
  now(), now()
FROM member_raw mr
CROSS JOIN max_date md;

CREATE INDEX IF NOT EXISTS idx_dim_member_status ON analytics.dim_member(status);
CREATE INDEX IF NOT EXISTS idx_dim_member_level ON analytics.dim_member(member_level);
CREATE INDEX IF NOT EXISTS idx_dim_member_store ON analytics.dim_member(register_store);

-- ============================================================
-- 2. 填充 dim_employee — 员工主数据
-- ============================================================
TRUNCATE TABLE analytics.dim_employee;

INSERT INTO analytics.dim_employee (
  employee_id, employee_name, position, store_code, hire_date, leave_date, status,
  created_at, updated_at
)
SELECT DISTINCT ON (employee_code)
  employee_code,
  NULL AS employee_name,  -- 薪资表无姓名字段
  -- 标准化岗位
  CASE 
    WHEN position LIKE '店长%' OR position = '储备店长' OR position LIKE '见习经理%' OR position = '储备经理' THEN '店长'
    WHEN position LIKE '副店%' THEN '副店长'
    WHEN position LIKE '前厅经理%' OR position = '大堂经理' OR position = '服务主管' THEN '前厅经理'
    WHEN position = '区经理' OR position LIKE '营运经理%' OR position = '营运助理' OR position LIKE '营运总监%' THEN '区经理'
    WHEN position = '厨师长' OR position LIKE '厨师长%' OR position = '行政总厨' OR position LIKE '区厨%' OR position LIKE '大区总厨%' OR position LIKE '拉面区厨%' OR position LIKE '拉面总厨%' OR position = '研发总厨' OR position = '研发经理' OR position LIKE '配送中心%总厨%' OR position = '烤鸭总厨' OR position = '西餐总厨' THEN '厨师长'
    WHEN position LIKE '拉面师%' OR position = '拉面' OR position = '拉面主管%' OR position = '面工' OR position LIKE '面工%' OR position = '面点' OR position = '面点师' OR position = '打馕' THEN '拉面师'
    WHEN position LIKE '厨师%' OR position = '炒锅' OR position = '砧板' OR position = '打荷' OR position = '上什' OR position = '蒸箱' OR position = '出品' OR position = '副厨' OR position = '明档师傅' OR position = '西餐' OR position = '锅底' THEN '厨师'
    WHEN position LIKE '配菜师%' OR position = '配菜师' OR position = '切菜师' OR position LIKE '切菜师%' OR position = '切肉师' OR position = '砧板主管' THEN '配菜师'
    WHEN position LIKE '凉菜%' OR position = '凉菜' THEN '凉菜师'
    WHEN position LIKE '烧烤%' OR position = '烧烤师' OR position LIKE '烧烤工%' OR position = '烧烤师傅' OR position = '烤鸭师' THEN '烧烤师'
    WHEN position LIKE '服务员%' OR position = '传菜员' OR position = '迎宾员' OR position = '吧员' THEN '服务员'
    WHEN position = '收银员' OR position = '出纳主管' OR position LIKE '%出纳%' THEN '收银员'
    WHEN position = '训练员' OR position LIKE '训练员%' OR position = '训练经理' THEN '训练员'
    WHEN position LIKE '厨工%' OR position = '兼职工' OR position = '非全兼职工' OR position = '小时工' OR position = '计时工' THEN '厨工'
    WHEN position = '保洁员' OR position = '保洁' THEN '保洁'
    WHEN position = '洗碗' THEN '洗碗工'
    WHEN position LIKE '%组员%' OR position LIKE '%组长%' THEN '中央厨房工'
    WHEN position = '库房组员' OR position = '库房专员' OR position = '库房资深专员' THEN '库管'
    WHEN position LIKE '副总%' OR position LIKE '执行总裁%' OR position = '首席营销官%' OR position LIKE '%总监%' OR position LIKE '%经理%' OR position LIKE '%主管%' OR position LIKE '%专员%' OR position LIKE '%助理%' OR position = '主管' THEN '管理岗'
    ELSE '其他'
  END AS position_std,
  -- 门店编码：org_level3 中以人名命名的区域无法直接映射到门店，暂取NULL
  NULL AS store_code,
  -- hire_date: text转date
  CASE 
    WHEN hire_date ~ '^\d{4}-\d{2}-\d{2}$' THEN hire_date::date
    WHEN hire_date ~ '^\d{4}/\d{2}/\d{2}$' THEN to_date(hire_date, 'YYYY/MM/DD')
    ELSE NULL
  END AS hire_date,
  -- leave_date: '0' 表示在职
  CASE 
    WHEN leave_date = '0' OR leave_date IS NULL OR leave_date = '' THEN NULL
    WHEN leave_date ~ '^\d{4}-\d{2}-\d{2}$' THEN leave_date::date
    WHEN leave_date ~ '^\d{4}/\d{2}/\d{2}$' THEN to_date(leave_date, 'YYYY/MM/DD')
    ELSE NULL
  END AS leave_date,
  -- 状态
  CASE 
    WHEN leave_date = '0' OR leave_date IS NULL OR leave_date = '' THEN '在职'
    ELSE '离职'
  END AS status,
  now(), now()
FROM public.salary_detail_records
ORDER BY employee_code, leave_date DESC NULLS LAST;

CREATE INDEX IF NOT EXISTS idx_dim_employee_status ON analytics.dim_employee(status);
CREATE INDEX IF NOT EXISTS idx_dim_employee_position ON analytics.dim_employee(position);

-- ============================================================
-- 3. 填充 dim_channel — 渠道主数据
-- ============================================================
TRUNCATE TABLE analytics.dim_channel;

INSERT INTO analytics.dim_channel (channel_code, channel_name, channel_group, channel_type, platform, commission_rate, is_delivery, sort_order, status)
VALUES
  ('dinein',     '堂食',     '堂食', '堂食',       '自有',   0,     false, 1,  '启用'),
  ('meituan_dm', '美团到店', '堂食', '美团到店',   '美团',   0,     false, 2,  '启用'),
  ('meituan_wm', '美团外卖', '外卖', '美团外卖',   '美团',   15.0,  true,  3,  '启用'),
  ('taobao_wm',  '淘宝外卖', '外卖', '淘宝外卖',   '淘宝',   12.0,  true,  4,  '启用'),
  ('jd_wm',      '京东外卖', '外卖', '京东到家',   '京东',   10.0,  true,  5,  '启用'),
  ('douyin',     '抖音',     '支付', '抖音',       '抖音',   0,     false, 6,  '启用'),
  ('alipay',     '支付宝',   '支付', '支付宝',     '自有',   0.6,   false, 7,  '启用'),
  ('wechat',     '微信',     '支付', '微信',       '自有',   0.6,   false, 8,  '启用'),
  ('cash',       '现金',     '支付', '现金',       '自有',   0,     false, 9,  '启用'),
  ('unionpay',   '银联',     '支付', '银联',       '自有',   0.5,   false, 10, '启用'),
  ('credit',     '挂账',     '支付', '挂账',       '自有',   0,     false, 11, '启用');

-- ============================================================
-- 4. 填充 dim_sku — SKU主数据（从 dish_sales_details 聚合）
-- ============================================================
TRUNCATE TABLE analytics.dim_sku;

INSERT INTO analytics.dim_sku (
  sku_code, standard_name, pos_code, category_l1, category_l2, 
  status, abc_class, unit, created_at, updated_at
)
SELECT 
  -- SKU编码：DISH- + 序号（用dense_rank生成）
  'DISH-' || lpad(dense_rank() OVER (ORDER BY dish_name)::text, 5, '0'),
  dish_name,
  NULL AS pos_code,
  min(category_level1) AS category_l1,
  min(category_level2) AS category_l2,
  '在售' AS status,
  -- 取最新月度ABC分类
  (SELECT abc.abc_class FROM analytics.mv_dish_sku_abc_monthly abc 
   WHERE abc.dish_name = d.dish_name 
   ORDER BY abc.month_start DESC LIMIT 1) AS abc_class,
  min(unit) AS unit,
  now(), now()
FROM public.dish_sales_details d
WHERE d.dish_name IS NOT NULL AND d.dish_name != ''
GROUP BY d.dish_name;

-- ============================================================
-- 5. 创建 member_level_mapping 视图 — 会员等级标准化映射
-- ============================================================
CREATE OR REPLACE VIEW analytics.v_member_level_mapping AS
SELECT 
  member_level AS raw_level,
  CASE 
    WHEN member_level IN ('1') THEN '普通'
    WHEN member_level IN ('2','3') THEN '银卡'
    WHEN member_level IN ('4','5') THEN '金卡'
    WHEN member_level IN ('6','7','LV6','LV7') THEN '钻石'
    ELSE '普通'
  END AS standard_level,
  CASE 
    WHEN member_level IN ('1') THEN 1
    WHEN member_level IN ('2','3') THEN 2
    WHEN member_level IN ('4','5') THEN 3
    WHEN member_level IN ('6','7','LV6','LV7') THEN 4
    ELSE 1
  END AS level_sort
FROM (SELECT DISTINCT member_level FROM analytics.bill_fact WHERE member_level IS NOT NULL AND member_level != '') t;

-- ============================================================
-- 6. 创建 v_position_mapping 视图 — 岗位标准化映射
-- ============================================================
CREATE OR REPLACE VIEW analytics.v_position_mapping AS
SELECT DISTINCT
  position AS raw_position,
  CASE 
    WHEN position LIKE '店长%' OR position = '储备店长' OR position LIKE '见习经理%' OR position = '储备经理' THEN '店长'
    WHEN position LIKE '副店%' THEN '副店长'
    WHEN position LIKE '前厅经理%' OR position = '大堂经理' OR position = '服务主管' THEN '前厅经理'
    WHEN position = '区经理' OR position LIKE '营运经理%' OR position = '营运助理' OR position LIKE '营运总监%' THEN '区经理'
    WHEN position = '厨师长' OR position LIKE '厨师长%' OR position = '行政总厨' OR position LIKE '区厨%' OR position LIKE '大区总厨%' OR position LIKE '拉面区厨%' OR position LIKE '拉面总厨%' OR position = '研发总厨' OR position = '研发经理' OR position LIKE '配送中心%总厨%' OR position = '烤鸭总厨' OR position = '西餐总厨' THEN '厨师长'
    WHEN position LIKE '拉面师%' OR position = '拉面' OR position LIKE '拉面主管%' OR position = '面工' OR position LIKE '面工%' OR position = '面点' OR position = '面点师' OR position = '打馕' THEN '拉面师'
    WHEN position LIKE '厨师%' OR position = '炒锅' OR position = '砧板' OR position = '打荷' OR position = '上什' OR position = '蒸箱' OR position = '出品' OR position = '副厨' OR position = '明档师傅' OR position = '西餐' OR position = '锅底' THEN '厨师'
    WHEN position LIKE '配菜师%' OR position = '配菜师' OR position = '切菜师' OR position LIKE '切菜师%' OR position = '切肉师' OR position = '砧板主管' THEN '配菜师'
    WHEN position LIKE '凉菜%' OR position = '凉菜' THEN '凉菜师'
    WHEN position LIKE '烧烤%' OR position = '烧烤师' OR position LIKE '烧烤工%' OR position = '烧烤师傅' OR position = '烤鸭师' THEN '烧烤师'
    WHEN position LIKE '服务员%' OR position = '传菜员' OR position = '迎宾员' OR position = '吧员' THEN '服务员'
    WHEN position = '收银员' OR position = '出纳主管' OR position LIKE '%出纳%' THEN '收银员'
    WHEN position = '训练员' OR position LIKE '训练员%' OR position = '训练经理' THEN '训练员'
    WHEN position LIKE '厨工%' OR position = '兼职工' OR position = '非全兼职工' OR position = '小时工' OR position = '计时工' THEN '厨工'
    WHEN position = '保洁员' OR position = '保洁' THEN '保洁'
    WHEN position = '洗碗' THEN '洗碗工'
    WHEN position LIKE '%组员%' OR position LIKE '%组长%' THEN '中央厨房工'
    WHEN position = '库房组员' OR position = '库房专员' OR position = '库房资深专员' THEN '库管'
    WHEN position LIKE '副总%' OR position LIKE '执行总裁%' OR position = '首席营销官%' OR position LIKE '%总监%' OR position LIKE '%经理%' OR position LIKE '%主管%' OR position LIKE '%专员%' OR position LIKE '%助理%' OR position = '主管' THEN '管理岗'
    ELSE '其他'
  END AS standard_position
FROM public.salary_detail_records
WHERE position IS NOT NULL AND position != '';

-- ============================================================
-- 7. 补录 dim_store 缺失的 region
-- ============================================================
UPDATE analytics.dim_store SET region = '未知区域' WHERE region IS NULL OR region = '';

-- ============================================================
-- 验证结果
-- ============================================================
SELECT 'dim_member' AS table_name, count(*) AS row_count FROM analytics.dim_member
UNION ALL SELECT 'dim_employee', count(*) FROM analytics.dim_employee
UNION ALL SELECT 'dim_channel', count(*) FROM analytics.dim_channel
UNION ALL SELECT 'dim_sku', count(*) FROM analytics.dim_sku
UNION ALL SELECT 'v_member_level_mapping', count(*) FROM analytics.v_member_level_mapping
UNION ALL SELECT 'v_position_mapping', count(*) FROM analytics.v_position_mapping;

-- dim_member 状态分布
SELECT 'dim_member_status' AS check_name, status, count(*) AS cnt 
FROM analytics.dim_member GROUP BY status
UNION ALL
SELECT 'dim_member_level', member_level, count(*) 
FROM analytics.dim_member GROUP BY member_level
UNION ALL
SELECT 'dim_employee_status', status, count(*) 
FROM analytics.dim_employee GROUP BY status
UNION ALL
SELECT 'dim_employee_position', position, count(*) 
FROM analytics.dim_employee GROUP BY position
ORDER BY 1, 3 DESC;