数据仓库建设

公共维度汇总层（DIM）

CREATE TABLE
IF
  NOT EXISTS cdm_dim_marketchannel (
      subjectId VARCHAR(255) COMMENT '渠道ID',
    label VARCHAR(255) COMMENT '渠道',
    brand VARCHAR(255) COMMENT '投放主体',
    category VARCHAR(255) COMMENT '类别',
    one_level_channel CHAR COMMENT '一级渠道',
    two_level_channel CHAR COMMENT '二级渠道',
    three_level_channel CHAR COMMENT '三级渠道'
  ) COMMENT '公共营销渠道维表'

明细粒度事实层（DWD）

CREATE TABLE
IF
  NOT EXISTS cdm_dwd_zydconcume (
    riqi DATE COMMENT '日期',
    kehubianma VARCHAR(255) COMMENT '客户编码',
    xingming VARCHAR(255) COMMENT '客户姓名',
    accountId CHAR COMMENT 'ID',
    fenyuan VARCHAR(255) COMMENT '收费分院名称',
    shijishoukuanfenyuan VARCHAR(255) COMMENT '实际收款院店',
    ifkuanyuan VARCHAR(255) COMMENT '是否跨院消耗',
    taocan VARCHAR(255) COMMENT '套餐类目名称',
    chanpin VARCHAR(255) COMMENT '产品类目名称',
    jine DOUBLE COMMENT '金额',
    rank BIGINT COMMENT '消费频次',
  biaoshi CHAR COMMENT '消费标识'
  ) COMMENT '直营店消费事实明细表'<br>

CREATE TABLE
IF
  NOT EXISTS cmd_dwd_concume (
        fenyuan VARCHAR(255) COMMENT '收费分院名称',
        accountId CHAR COMMENT 'ID',
        kehubianma VARCHAR(255) COMMENT '客户编码',
        xingming VARCHAR(255) COMMENT '客户姓名',
        riqi DATE COMMENT '日期',
        shoushufei DOUBLE COMMENT '手术费',
        zhuanjiafei DOUBLE COMMENT '专家费',
        cailiaofei DOUBLE COMMENT '材料费',
        jianchafei DOUBLE COMMENT '检查费',
        yaofei DOUBLE COMMENT '药费',
        jianfa DOUBLE COMMENT '健发',
        meifa DOUBLE COMMENT '美发',
        zengfa DOUBLE COMMENT '增发', 
        yimei DOUBLE COMMENT '医美', 
        yonghe DOUBLE COMMENT '雍禾', 
        zhifachanpin DOUBLE COMMENT '植发产品', 
        shiyunxunchanpin DOUBLE COMMENT '史云逊产品', 
        jiafachanpin DOUBLE COMMENT '健发产品', 
        yuyuejin DOUBLE COMMENT '预约金', 
        qita DOUBLE COMMENT '其他', 
        guahaofei DOUBLE COMMENT '挂号费',
        shouka  DOUBLE COMMENT '售卡',
        rank DOUBLE COMMENT '消费频次',
        biaoshi CHAR COMMENT '消费标识',
        zjine  DOUBLE COMMENT '总金额',  
    zhongpei DOUBLE COMMENT '中胚'
  ) COMMENT '雍禾消费事实明细表'

  CREATE TABLE
IF
  NOT EXISTS cdm_dwd_hairfollicle (
    OutId VARCHAR(50) COMMENT '客户ID',
    TouPiYouZhi VARCHAR(255) COMMENT '头皮油脂',
    LaZhuangWu VARCHAR(255) COMMENT '蜡状物/头屑',
    MinGanYanZheng VARCHAR(255) COMMENT '头皮敏感/炎症/红/肿/脓',
    CuiMao_Rate VARCHAR(255) COMMENT '毳毛占比',
    HairLossLevel VARCHAR(255) COMMENT '脱发等级',
    LoseDegree VARCHAR(255) COMMENT '脱发程度',
    HairLineHeight VARCHAR(50) COMMENT '发际线测量值',
    HairLineHeight_text  VARCHAR(255) COMMENT '发际线高'
  ) COMMENT '毛囊检测结果表'
  

CREATE TABLE
IF
  NOT EXISTS cdm_dwd_adspend (
    quyu VARCHAR(50) COMMENT '区域',
    date DATE COMMENT '日期',
    qudao VARCHAR(255) COMMENT '开发渠道',
    province VARCHAR(255) COMMENT '省',
    city VARCHAR(255) COMMENT '市',
    cost DOUBLE COMMENT '花费',
    fenyuan VARCHAR(255) COMMENT '分院'
  ) COMMENT '广告花费表'