生而为人

程序员的自我修养

0%

Posted on

被打了一巴掌

跟警察说3句话

  1. 对方当众殴打我，事实清楚，他已经违反了治安管理处罚法，我要求依法处理，并且立刻向我出具立案的回执
  2. 我现在已经有点儿恶心，并且视线模糊，我要求立刻调取现场监控，并依法对我的伤情进行鉴定
  3. 如果我的合法诉求不被受理，那我将通过12389热线向你们上级部门进行投诉，并追究相关的不作为责任。

Posted on

ExecutionPolicy

Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope MachinePolicy
Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope UserPolicy
Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope Process
Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope CurrentUser
Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope LocalMachine

Get-ExecutionPolicy -List

Set-ExecutionPolicy Bypass -Scope Process -Force; Copy-Item \cosbj-01\public\CosmosPowerShell\builds\install.ps1 $HOME\Downloads\install.ps1; & $HOME\Downloads\install.ps1 -Force

Posted on

timestamp to date

  1. second: =(A1+8*3600)/86400+70*365+19
  2. millsecond: =(A1+8*3600)/8640000+70*365+19

date to timestamp

  1. second: =(B1-70*365-19)*86400-8*3600

Posted on

原始sql:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
WITH sku_info AS
  (SELECT a.sku_id,
          d.bu_id,
          if(c.management_type = 3, 'pop', '自营') AS is_3p,
          b.cat1_id,
          b.cat1_name,
          b.cat2_id,
          b.cat2_name,
          max(on_shelf) AS is_onshelf
   FROM
     (SELECT sku_code AS sku_id,
             sales_grid_id,
             on_shelf,
             bu_id
      FROM mart_caterb2b.dim_prod_all_map_csu__grid_his
      WHERE dt = '20210317'
        AND channel_id = '1001'
      UNION ALL SELECT sku_code,
                       sales_grid_id,
                       on_shelf,
                       bu_id
      FROM mart_caterb2b.fact_caterb2b_csu_grid_onshelf_log
      WHERE update_time >='2021-03-18'
        AND update_time <= '2021-03-19'
        AND on_shelf = 1
        AND channel_id = '1001'
      GROUP BY 1,
               2,
               3,
               4) a
   LEFT JOIN
     (SELECT DISTINCT sku_id,
                      cat1_id,
                      cat1_name,
                      cat2_id,
                      cat2_name
      FROM mart_caterb2b.dim_caterb2b_csu_his
      WHERE dt = '20210318'
        AND channel_id = '1001') b ON a.sku_id = b.sku_id
   LEFT JOIN mart_caterb2b.dim_sm_business_entity c ON a.bu_id = c.bu_id
   LEFT JOIN mart_caterb2b.dim_sell_grid d ON a.sales_grid_id = d.sell_grid_id
   GROUP BY 1,
            2,
            3,
            4,
            5,
            6,
            7)
            
            
            
SELECT coalesce(f.bu_id, '-1') AS bu_id,
       coalesce(f.cat1_id, '-1') AS cat1_id,
       coalesce(f.cat1_name, '全部') AS cat1_name,
       coalesce(f.cat2_id, '-1') AS cat2_id,
       coalesce(f.cat2_name, '全部') AS cat2_name,
       coalesce(f.is_3p, '全部') AS is_3p,
       count(DISTINCT if(is_view = 1, f.sku_id, NULL)) AS view_sku_ct,
       count(DISTINCT if(is_view = 1, f.customer_id, NULL)) AS view_uv,
       count(DISTINCT if(is_view = 1, f.sku_id, NULL), if(is_view = 1, f.customer_id, NULL)) AS view_sku_uv,
       count(DISTINCT if(is_intention = 1, f.sku_id, NULL)) AS view_sku_ct,
       count(DISTINCT if(is_intention = 1, f.customer_id, NULL)) AS view_uv,
       count(DISTINCT if(is_intention = 1, f.sku_id, NULL), if(is_intention = 1, f.customer_id, NULL)) AS view_sku_uv
FROM
  (SELECT d.sku_id,
          d.bu_id,
          d.customer_id,
          coalesce(e.cat1_id, '-99') AS cat1_id,
          coalesce(e.cat1_name, '其他') AS cat1_name,
          coalesce(e.cat2_id, '-99') AS cat2_id,
          coalesce(e.cat2_name, '其他') AS cat2_name,
          coalesce(e.is_3p, '其他') AS is_3p,
          d.is_view,
          d.is_intention
   FROM
     (SELECT if(click.event_type = 'view', 1, 0) AS is_view,
             CASE WHEN (click.val_cid = 'page_csu_detail'
                        AND click.val_bid = 'b_htrmpzlb')
                    OR click.val_bid IN ('b_h856xuac',
                           'b_x6m8625m') THEN 1
                  ELSE 0
              END AS is_intention,
              c.sku_id,
              coalesce(cust.bu_id, '-99') AS bu_id,
              click.customer_id
        FROM mart_caterb2b.fact_flow_visit_click_kv_day click
        JOIN mart_caterb2b.dim_caterb2b_csu_his c
          ON click.csu_id = c.csu_id
        LEFT JOIN
          (SELECT DISTINCT customer_id,
                           bu_id
           FROM mart_caterb2b.dim_caterb2b_customer_his
           WHERE dt = '20210225'
             AND business_type = 2
             AND cat_type = 1
             AND channel_id = '1001') cust ON click.customer_id = cust.customer_id
        WHERE click.dt = '20210225'
          AND click.csu_id IS NOT NULL
          AND c.dt = '20210225' )d
  LEFT JOIN sku_info e ON d.sku_id = e.sku_id
  AND d.bu_id = e.bu_id)f
GROUP BY f.bu_id,
         f.cat1_id,
         f.cat1_name,
         f.cat2_id,
         f.cat2_name,
         f.is_3p
GROUPING sets((f.bu_id, f.cat1_id, f.cat1_name, f.cat2_id, f.cat2_name, f.is_3p),
            (f.bu_id, f.cat1_id, f.cat1_name, f.cat2_id, f.cat2_name),
            (f.bu_id, f.cat1_id, f.cat1_name, f.is_3p),
            (f.bu_id, f.cat1_id, f.cat1_name),
            (f.bu_id, f.is_3p),
            (f.bu_id),
            (f.cat1_id, f.cat1_name, f.cat2_id, f.cat2_name, f.is_3p),
            (f.cat1_id, f.cat1_name, f.cat2_id, f.cat2_name),
            (f.cat1_id, f.cat1_name, f.is_3p),
            (f.cat1_id, f.cat1_name),
            (f.is_3p),
            ())

添加参数：

SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.dynamic.partition=true;
– 分区
SET hive.exec.max.dynamic.partitions=1000;
– 触发spark合并小文件的阈值(1M)
SET spark.sql.mergeSmallFileSize=1048576;
– spark shuffle默认分区数
SET spark.sql.shuffle.partitions=500;
– 限制最大申请资源
set spark.dynamicAllocation.enabled=true;
set spark.dynamicAllocation.minExecutors=3;
set spark.dynamicAllocation.maxExecutors=300;
set spark.executor.cores=2;

如果不添加参数，在最后一步，资源急剧下降，导致执行时间很长

Posted on In
explode 与 lateral view 对比 

select user_coupon_id, explode(split('0,1', ',')) as tag
from mart_waimai.aggr_act_ord_use_coupon_dd
where dt='20200920'
limit 10

背景

统计端维度的日活、下单UV、成交UV等，由于端信息只能从流量表里获取，所以需要从流量表关联订单表得到成交信息。

开发过程

原始sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
SELECT b.region_id,
       b.region_name,
       b.management_city_id,
       b.management_city_name,
       a.device_type_id,
       count(DISTINCT a.customer_id) AS dau,
       count(DISTINCT IF (a.event_type = 'view'
                          AND a.val_cid = 'page_csu_list', a.customer_id, NULL)) AS home_expose_uv,
       count(DISTINCT IF (a.event_type = 'click'
                          AND a.val_cid = 'page_csu_list', a.customer_id, NULL)) AS home_click_uv,
       count(DISTINCT IF (a.val_bid = 'b_h8ds56xuac', a.customer_id, NULL)) AS add_cart_uv,
       count(DISTINCT IF (a.val_cid = 'page_order_confirm'
                          AND a.val_bid = 'b_drqbsnud', a.customer_id, NULL)) AS order_uv,
       count(DISTINCT IF (c.order_id IS NOT NULL, a.customer_id, NULL)) AS order_arrange_uv,
       sum(IF (c.order_id IS NOT NULL, a.arranged_amt, 0)) AS conversion_sales_amt
FROM mart_caterb2b.fact_flow_visit_click_kv_day a
JOIN
  (SELECT m.dt,
          m.customer_id,
          n.region_id,
          n.region_name,
          n.management_city_id,
          n.management_city_name
   FROM mart_caterb2b.dim_caterb2b_customer_his m
   JOIN mart_caterb2b.dim_sm_management_city_info n ON m.city_id = n.city_id
   WHERE m.dt = '20210307') b ON a.customer_id = b.customer_id
AND a.dt = b.dt
LEFT JOIN
  (SELECT DISTINCT dt,
                   order_id,
                   parent_order_id
   FROM mart_caterb2b.fact_biz_order_day
   WHERE dt = '20210307') d ON a.order_id = d.parent_order_id
AND a.dt = d.dt
LEFT JOIN
  (SELECT dt,
          order_id,
          sum(arranged_amt) AS arranged_amt
   FROM mart_caterb2b.mid_deal_order_item_withpop
   WHERE dt = '20210307'
     AND is_arranged = 1
     AND channel_id = 1001
     AND order_type NOT IN (3,
                            4)-- 排除补货换货

     AND spu_type != 5 -- 排除包装物

   GROUP BY dt,
            order_id) c ON d.dt = c.dt
AND d.order_id = c.order_id
WHERE a.dt = '20210307'
GROUP BY b.region_id,
         b.region_name,
         b.management_city_id,
         b.management_city_name,
         a.device_type_id

出现数据倾斜

查看出问题stage

定位sql

怀疑是因为d表dt很多关联不上为null，导致某些某些节点量特别大，所以考虑提前关联c表数据，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
SELECT b.region_id,
           b.region_name,
           b.management_city_id,
           b.management_city_name,
           a.device_type_id,
           count(DISTINCT a.customer_id) AS dau,
           count(DISTINCT IF (a.event_type = 'view'
                              AND a.val_cid = 'page_csu_list', a.customer_id, NULL)) AS home_expose_uv,
           count(DISTINCT IF (a.event_type = 'click'
                              AND a.val_cid = 'page_csu_list', a.customer_id, NULL)) AS home_click_uv,
           count(DISTINCT IF (a.val_bid = 'b_h856xuac', a.customer_id, NULL)) AS add_cart_uv,
           count(DISTINCT IF (a.val_cid = 'page_order_confirm'
                              AND a.val_bid = 'b_drqbsnud', a.customer_id, NULL)) AS order_uv,
           count(DISTINCT IF (a.val_cid = 'page_order_confirm'
                              AND a.val_bid = 'b_drqbsnud'
                              AND d.order_id IS NOT NULL, a.customer_id, NULL)) AS order_arrange_uv,
           sum(IF (a.val_cid = 'page_order_confirm'
                   AND a.val_bid = 'b_drqbsnud'
                   AND d.order_id IS NOT NULL, d.arranged_amt, 0)) AS conversion_sales_amt
    FROM mart_caterb2b.fact_flow_visit_click_kv_day a
    JOIN
      (SELECT m.dt,
              m.customer_id,
              n.region_id,
              n.region_name,
              n.management_city_id,
              n.management_city_name
       FROM mart_caterb2b.dim_caterb2b_customer_his m
       JOIN mart_caterb2b.dim_sm_management_city_info n ON m.city_id = n.city_id
       WHERE m.dt = '$now.datekey') b ON a.customer_id = b.customer_id
    AND a.dt = b.dt
    LEFT JOIN
      (SELECT e.dt,
              e.parent_order_id,
              c.order_id,
              c.arranged_amt
       FROM mart_caterb2b.fact_biz_order_day e
       left JOIN
         (SELECT dt,
                 order_id,
                 sum(arranged_amt) AS arranged_amt
          FROM mart_caterb2b.mid_deal_order_item_withpop
          WHERE dt = '$now.datekey'
            AND is_arranged = 1
            AND channel_id = 1001
            AND order_type NOT IN (3,
                                   4)-- 排除补货换货

            AND spu_type != 5 -- 排除包装物

          GROUP BY dt,
                   order_id) c ON e.dt = c.dt
       AND e.order_id = c.order_id
       WHERE e.dt = '$now.datekey') d ON a.order_id = d.parent_order_id
    AND a.dt = d.dt
    WHERE a.dt = '$now.datekey'
    GROUP BY b.region_id,
             b.region_name,
             b.management_city_id,
             b.management_city_name,
             a.device_type_id
    GROUPING SETS ((b.region_id, b.region_name, b.management_city_id, b.management_city_name, a.device_type_id),
               (b.region_id, b.region_name, a.device_type_id),
               (b.region_id, b.region_name, b.management_city_id, b.management_city_name),
               (b.region_id, b.region_name),
               (a.device_type_id),
               ())

但是并没有改善

说明a表与b表关联时候，由于能用order_id关联上是少部分的数据（下单数据），而不能关联上的还是被分配到两个节点上处理。

所以只能将数据拆分处理，sql如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
WITH flow_log_info AS
  (SELECT a.dt,
          b.region_id,
          b.region_name,
          b.management_city_id,
          b.management_city_name,
          a.device_type_id,
          a.customer_id,
          a.event_type,
          a.val_cid,
          a.val_bid,
          a.order_id
   FROM mart_caterb2b.fact_flow_visit_click_kv_day a
   JOIN
     (SELECT m.dt,
             m.customer_id,
             n.region_id,
             n.region_name,
             n.management_city_id,
             n.management_city_name
      FROM mart_caterb2b.dim_caterb2b_customer_his m
      JOIN mart_caterb2b.dim_sm_management_city_info n ON m.city_id = n.city_id
      WHERE m.dt = '$now.datekey') b ON a.customer_id = b.customer_id
   AND a.dt = b.dt
   WHERE a.dt = '$now.datekey')

INSERT OVERWRITE TABLE `${target.table}` PARTITION (dt)
SELECT coalesce(e.region_id, '-1') AS region_id,
       coalesce(e.region_name, '全部') AS region_name,
       coalesce(e.management_city_id, '-1') AS management_city_id,
       coalesce(e.management_city_name, '全部') AS management_city_name,
       coalesce(e.device_type_id, '-1') AS device_type_id,
       e.dau,
       e.home_expose_uv,
       e.home_click_uv,
       e.add_cart_uv,
       e.order_uv,
       e.order_arrange_uv,
       e.conversion_sales_amt,
       if(e.home_expose_uv = 0, 0, 1 - (e.home_click_uv / e.home_expose_uv)) AS bounce_rate,
       if(e.dau = 0, 0, e.order_arrange_uv / e.dau) AS dau_arrange_rate,
       if(e.dau = 0, 0, e.conversion_sales_amt / e.dau) AS sales_amt_per_uv,
       '$now.datekey' AS dt
FROM
  (SELECT c.region_id,
          c.region_name,
          c.management_city_id,
          c.management_city_name,
          c.device_type_id,
          sum(c.dau) AS dau,
          sum(c.home_expose_uv) AS home_expose_uv,
          sum(c.home_click_uv) AS home_click_uv,
          sum(c.add_cart_uv) AS add_cart_uv,
          sum(c.order_uv) AS order_uv,
          sum(c.order_arrange_uv) AS order_arrange_uv,
          sum(c.conversion_sales_amt) AS conversion_sales_amt
   FROM
     (SELECT a.region_id,
             a.region_name,
             a.management_city_id,
             a.management_city_name,
             a.device_type_id,
             count(DISTINCT a.customer_id) AS dau,
             count(DISTINCT IF (a.event_type = 'view'
                                AND a.val_cid = 'page_csu_list', a.customer_id, NULL)) AS home_expose_uv,
             count(DISTINCT IF (a.event_type = 'click'
                                AND a.val_cid = 'page_csu_list', a.customer_id, NULL)) AS home_click_uv,
             count(DISTINCT IF (a.val_bid = 'b_h856xuac', a.customer_id, NULL)) AS add_cart_uv,
             count(DISTINCT IF (a.val_cid = 'page_order_confirm'
                                AND a.val_bid = 'b_drqbsnud', a.customer_id, NULL)) AS order_uv,
             0 AS order_arrange_uv,
             0 AS conversion_sales_amt
      FROM flow_log_info a
      GROUP BY a.region_id,
               a.region_name,
               a.management_city_id,
               a.management_city_name,
               a.device_type_id
      GROUPING SETS ((a.region_id, a.region_name, a.management_city_id, a.management_city_name, a.device_type_id),
               (a.region_id, a.region_name, a.device_type_id),
               (a.region_id, a.region_name, a.management_city_id, a.management_city_name),
               (a.region_id, a.region_name),
               (a.device_type_id),
               ())

      UNION ALL

      SELECT a.region_id,
           a.region_name,
           a.management_city_id,
           a.management_city_name,
           a.device_type_id,
           0 AS dau,
           0 AS home_expose_uv,
           0 AS home_click_uv,
           0 AS add_cart_uv,
           0 AS order_uv,
           count(DISTINCT IF (a.val_cid = 'page_order_confirm'
                              AND a.val_bid = 'b_drqbsnud'
                              AND d.order_id IS NOT NULL, a.customer_id, NULL)) AS order_arrange_uv,
           sum(IF (a.val_cid = 'page_order_confirm'
                   AND a.val_bid = 'b_drqbsnud'
                   AND d.order_id IS NOT NULL, d.arranged_amt, 0)) AS conversion_sales_amt
      FROM flow_log_info a
      JOIN
        (SELECT e.dt,
                e.parent_order_id,
                c.order_id,
                c.arranged_amt
         FROM mart_caterb2b.fact_biz_order_day e
         LEFT JOIN
           (SELECT dt,
                   order_id,
                   sum(arranged_amt) AS arranged_amt
            FROM mart_caterb2b.mid_deal_order_item_withpop
            WHERE dt = '$now.datekey'
              AND is_arranged = 1
              AND channel_id = 1001
              AND order_type NOT IN (3,
                                     4)-- 排除补货换货

              AND spu_type != 5 -- 排除包装物

            GROUP BY dt,
                     order_id) c ON e.dt = c.dt
         AND e.order_id = c.order_id
         WHERE e.dt = '$now.datekey') d ON a.order_id = d.parent_order_id
      AND a.dt = d.dt
      GROUP BY a.region_id,
               a.region_name,
               a.management_city_id,
               a.management_city_name,
               a.device_type_id
      GROUPING SETS ((a.region_id, a.region_name, a.management_city_id, a.management_city_name, a.device_type_id),
               (a.region_id, a.region_name, a.device_type_id),
               (a.region_id, a.region_name, a.management_city_id, a.management_city_name),
               (a.region_id, a.region_name),
               (a.device_type_id),
               ()) )c
   GROUP BY c.region_id,
            c.region_name,
            c.management_city_id,
            c.management_city_name,
            c.device_type_id) e

总结

当用join关联时，一定要注意数据的量级分布，包括能关联上的和不能关联上的，提前计算一下！

原理探究

Posted on In

a、命名规范

1、使用有意义英文词汇；使用约定俗成的词汇；禁止中文拼音或拼音缩写

2、字段名由字母、下划线组成，禁止滥用阿拉伯数字，不同词汇之间用下划线分隔；字符长度不超过30位

3、为了意思清晰，加少混淆，并易于理解，应当使用 表名 ＋ id/name/type…的形式。例如，poi表里字段应该为poi_id、poi_type、 poi_name（不应该直接使用：id/name/type）

4、基础字段命名：用num, amt, cnt, id, type等简写做后缀，如”order_cnt”表示订单数量，“product_id”标示产品ID

5、聚集指标字段命名，用sum, avg, max, min等简写做后缀加在指标字段后，“consume_amt_sum“表示消费总额。

6、表示是否的字段，用is_含义， 例如，deal是否可预订，is_apt、is_prepay等。

  • 业务含义是动词时，尽量不用使用其分词命名,如is_paid，应为is_pay
  • 定义：是（1）非（0）或 肯（1）否（0）

7、列里面存的是多值，可以用复数命名，例如 markland_ids, hotel_types

8、DW内部：相同含义的字段名称、数据类型在正常情况下须保持一致，减少使用过程中的混淆，避免造成数据流转时不能被抽取或数据截断情况

b、命名建议

原则：字段名尽量详细，易于理解

命名规则：

字段名：[is]_[修饰词]＋字段描述词＋[后缀/度量]＋[时间周期词]，强烈建议保留后缀

中文名：[时间周期词]＋[修饰词]＋字段描述词＋[度量]

  • 字段描述词（名词、动词）

  • 修饰词（名词、动词、形容词、topN）

  • 名词修饰示例：dp_shop_id、mt_deal_id、business_area_name、province_location_id、hotel_star_name

  • 动词修饰示例：pay_order_cnt、refund_apply_cnt、locate_city_name、checkout_city_id

  • 形容词修饰示例：new_user_id、last_modify_operator_name、poi_first_cate_name

  • topN修饰（top\d）示例：top10_click_uv

  • 多个修饰词存在时，按主－谓－宾顺序、动作发生时间前后的顺序排列，例如：

主谓宾示例：biz_checkin_coupon_value（商家入住代金券补贴）

多动作示例：auto_pass_update_cnt（自动通过更新量）、response_add_time（回应添加时间）

  • 后缀（度量）

    常用基础后缀

    后缀 含义 备注
    id 唯一标示
    key 代理键
    cnt 数量
    no 批次、顺序号、编号
    code 代码
    type 类型
    name 名称
    amt 金额
    desc 描述
    effect_end_date 关链时间
    effect_begin_date 开链时间
    value 值
    pct 百分比
    score 得分
    level 等级
    comment 备注、说明
    status 状态
    time 时间
    date 日期
    datekey 日期键
    identifier 标识
    tag 标签
    goal 目标
    item 条目
    uv 访客数
    pv 浏览量
    platform 平台
    source 来源

    复合后缀：在基础后缀后使用

    后缀 含义 备注
    sum 汇总值
    avg 平均值 后可接by_[均分维度]，如人均、日均；缺省默认为按表的聚合粒度取均值
    max 最大值
    min 最小值
    var 方差
    covar 协方差
    stddev 标准差
    (\d)pctl 百分位数 \d为相应百分比
    interval 间隔 示例：[a]to[b]_time_interval
    [\d时间周期]diff 差异 示例：[a]to[b]_amt_diff与自身对比时：diff后加时间周期pay_order_cnt_diff_14days（与前14天的支付订单数差异），如果需要时间周期修饰，仍可以在字段后加时间周期词：pay_order_cnt_diff_14days_7days（当前日期前7天的支付订单数与前14天的差异）

  • 时间周期词（day、week、quarter、year、tenday、quarter、halfyear、period）

    时间周期词 含义 备注
    (\d)days 近n日 当前日期之前
    (\d)day 第n日 当前日期之前
    f(\d)days 未来n日内
    f(\d)day 未来第n日
    ……

c、设计原则

1、各列必须有明确注释

2、维度表：

  • 维度主键须在第一列，其余外键按重要性、相关性由高到低依次排列。
  • 维度表扁平化或设计层次维度表时，如需添加其他维度属性，应将其他维度主键和属性写到一块，方便浏览。如city维表中的province_id，province_name

3、事实表：

  • 如有标示唯一动作的ID时，应置为第一列，如订单表的order_id，评价表的review_id
  • 无唯一ID时，日期时间字段放在前列，相关的维度信息按重要性、相关性由高到低依次排列。如deal浏览日志：time，deal_id……

4、代理键设置：

模型涉及多系统整合时，需要添加代理键字段标示唯一主键，命名后缀为_key，代理键码值维护在1.4、公共代码空间

目前产品、交易主题需要设置代理键，因为这部分数据主要来自酒旅平台内部业务系统，我们作为数据的供给方是有权力制定码值的规范；

商户、用户主题的数据不建议整合，因为数据供给方在平台，我们制定代理键规则的话，会影响与平台和其他BG的数据互通

d、字段设计

1、新增字段应参照已定义的字段标准

2、字段类型严格遵循以下规范

数据类别 Hive类型 Mysql类型 长度 精度 词根 说明
日期类型 字符日期类 string varchar 10 date YYYY-MM-DD
整数日期类 int int 8 datekey YYYYMMDD
月类 int int 6 month YYYYMM
季类 int int 5 quarter YYYYQ
年类 int int 4 year YYYY
日期时间 string datetime time YYYY-MM-DD HH:MM:SS
数值类型 数量类 bigint bigint 10 0 cnt 间夜、券数等
金额类 Decimal Decimal 20 4 amt
浮点类 Double Decimal 20 4 avg 月均/年均等
指示器类 tinyint tinyint 1 is 值域个数为2。例如:(0,1)
级次类 int int 3 level 1.2..N
字符类型 文本类 String varchar type／name等
代码类 String varchar code 各维度表代码
键值类 String varchar id 非整形主键或唯一ID
复合类型 JSON类 String varchar json
MAP类 map varchar map
ARRAY类 array varchar array 类似[a,b,c,d]结构

e、主题关联键

现有字段名 字段中文名 字段类型 主题 规范字段名 规范字段类型
order_id 订单id bigint 交易、营销主题 order_id bigint
order_code 订单编码 string 交易、营销主题 order_code string
purchase_no 采购单号 string 采购主题 purchase_code string
id 退货单id bigint 交易主题 return_id bigint
return_code 退货单编码 string 交易主题 return_code string
after_sales_order_code 售后订单编号 string 交易主题
service_id 客服id bigint 客服主题 service_id bigint
creator_id 创建人id bigint
order_payment_id 支付id bigint 交易主题 payment_id bigint
csu_id 售卖单元id bigint 商品主题 csu_id bigint
sku_id 商品最小粒度id bigint 商品主题 sku_id bigint
spu_code spu编码 string 商品主题 spu_code string
spu_id spu_id bigint 商品主题 spu_id bigint
bd_id bdid bigint 营销管理主题 bd_id bigint
customer_id 商户id bigint 商户主题 customer_id bigint
poi_id 门店id bigint 商户主题 poi_id bigint
buyer_id 商家id？ string 商户主题 customer_id bigint
warehouse_id 仓库 bigint 仓储主题 wh_id bigint
inbound_no 入库单 string 仓储主题
brand_id 品牌id bigint 商品主题 brand_id bigint
sku_brand sku品牌 bigint 商品主题 brand_name bigint
supplier_id 供应商id bigint 采购主题 supplier_id bigint
supplier_code 供应商编码 string 采购主题 supplier_code string
channel_id 渠道id bigint 采购主题 channel_id bigint
cat1_id 一级品类id int 商品主题 cat1_id bigint
cat2_id 二级品类id int 商品主题 cat2_id bigint
cat3_id 三级品类id int 商品主题 cat3_id bigint
ba_cat1_id 商分一级品类id int 商品主题 ba_cat1_id bigint
ba_cat2_id 商分二级品类id int 商品主题 ba_cat2_id bigint
ba_cat3_id 商分三级品类id int 商品主题 ba_cat3_id bigint
receiving_bill_no 收货操作单号 string 仓储主题
putaway_bill_no 上架操作单号 string 仓储主题
allot_bill_no 调拨单号 string 仓储主题
creator_id 采购人mis_id bigint 采购主题
dealer_id 经销商id bigint 采购主题 dealer_id bigint
biz_unique_no 结算单号 string 采购主题
poi_address_id 配送地址id？ bigint 履约主题 poi_address_id bigint
service_provider_id 服务商id bigint 履约主题
route_id 路由id bigint 履约主题 route_id bigint
line_id 线路id bigint 履约主题 line_id bigint
creator_id 代下单人下单id bingint 营销管理主题
bd_misid、mis_name BD账号 string 销售管理 mis_name string
bu_id 事业部ID bigint 销售管理 bu_id bigint
city_id 城市ID bigint 销售管理 city_id bigint
bd_info_id、employee_id BD员工ID bigint 销售管理 employee_id bigint
org_id 销售组ID bigint 销售管理 org_id bigint

f、MAP类型key值

  1. key设计规则：

    1.1 时间key值

    today 当日

    (N)day 第N日之前

    (N)days 近N日

    f(N)day 未来第N日

    f(N)days 未来N日/次N天

    toweek 当周

    (N)week 第N周之前

    f(N)week 未来第N周/次N周

    tomonth 当月

    (N)month 第N月之前

    f(N)month 未来第N月/次N月

  1. key值枚举（只会新增、不会删减）

    key 含义 备注 应用场景
    today 当天 dt=’20190328’，代表20190328当天的数据 客户宽表、客户分群
    1day 1天前 dt=’20190328’，代表20190327的数据 客户宽表、客户分群
    7day 7天前(上周同期) dt=’20190328’，代表20190321的数据 客户宽表、客户分群
    1month 1月前(上月同期) dt=’20190328’，代表20190228的数据 客户宽表、客户分群
    7days 近7天 dt=’20190328’，代表20190322-20190328的数据 客户宽表、客户分群
    30days 近30天 dt=’20190328’，代表20190227-20190328的数据 客户宽表、客户分群
    total 历史累计 dt=’20190328’，代表截止到20190328的数据 客户宽表、客户分群
    2days 近2天 dt=’20190328’，代表20190327-20190328的数据 客户宽表、客户分群
    3days 近3天 以此类推 客户宽表、客户分群
    4days 近4天 客户宽表、客户分群
    5days 近5天 客户宽表、客户分群
    6days 近6天 客户宽表、客户分群
    7days 近7天 客户宽表、客户分群
    14days 近14天 客户宽表、客户分群
    21days 近21天 客户宽表、客户分群
    28days 近28天 客户宽表、客户分群
    29days 近29天 客户宽表、客户分群
    35days 近35天 客户宽表、客户分群
    60days 近60天 客户宽表、客户分群
    f1～30day 未来第1～30天/次1～30天 客户cohort分析
    toweek 当周（自然周） 客户cohort分析
    f1～12week 未来第1～12周/次1～12周 客户cohort分析
    tomonth 当月（自然月） 客户cohort分析
    f1～12month 未来第1～12月/次1～12月 客户cohort分析
    mtd 本月（截止到统计日期） 客户宽表、商品宽表
    待增

g. 多种日期维度字典值

日期含义 命名规则 应用场景
日 当天 例如：20191105 星辰
自然周 周一到周日，例如 20191028-20191103 星辰
汇报周（周三～周二） 周一到周日，例如20201104-20201110 星辰-自定义看板（品类运营看板）
自然月 月份：201911 星辰
周WTD W-1、W-2 星辰
汇报周WTD（周三～周二） W-1-WT、W-2-WT（WT的意思是Wednesday、Tuesday） 星辰-自定义看板（品类运营看板）
月MTD（只计算还没过完的当前月份的月MTD，该MTD只是截止到当日） M-1、M-2 星辰
滚动七天 7D- 和当前日期 组合 7D-20191105 星辰-经营结果
月MTD（每个日期的月度累计时间） MTD-20191028 星辰-经营结果

Posted on In

数据仓库建设中的数据建模方法

实体建模

数据建模-建模方法三：实体建模

实体建模法也有着自己先天的缺陷，由于实体说明法只是一种抽象客观世界的方法，因此，注定了该建模方法
只能局限在业务建模和领域概念建模阶段。因此，到了逻辑建模阶段和物理建模阶段，则是范式建模和维度建模发挥长处的阶段

实体是现实世界中存在的事物或发生的事件，是现实世界中任何可识别、可区分的事物。实体可以是人，可以是物，也可以是发生的某件事，比如一场篮球比赛。每一个实体都必须具备一定的特征，用来区分一个个实体，这些特征称为属性，每个实体可以用若干个属性来刻画，每个属性又有一定的取值类型和取值范围，属性是变量，其取值范围是属性的值域。实体建模法是根据客观世界中的一个个实体，以及实体之间的关系，在数据建模的过程中引入这种区分方法，将整个业务分成一个个实体，从而建立数据模型。实体建模可以比较容易的实现模型的划分，抽象出具体的业务概念，创建符合自己需要的数据库模型。实体建模是对客观世界的抽象，因此该方法建立的模型具有一定的局限性，适合于特定的领域。

维度建模法

维度在数学上指独立参数的数据，在数据分析领域，是指描述事物的角度和方面，是数据库当中，描述某一事物的方法和属性的数目。维度建模就是针对给定的事物，使用不同的描述方法，记录该事物在不同分类方法当中每个属性的值。如对人进行维度建模时，按照性别的维度可以分为男和女，按照年龄段可以分为儿童、少年、青年、中年、老年，按照收入水平可以分为3000以下、3000-6000、6000-10000，10000以上等集中类别。

维度建模法的好处是对于某个事物，可以在各个维度进行预处理，进行统计、分类、排序等，提高数据库操作性能，同时维度建模法建立的数据模型比较直观，可以紧密围绕业务需求建立模型，直观的反应业务中的问题，建模方法简单，不需要进行特别的抽象处理。但是维度建模法同时也存在缺点，一是在数据建模时需要进行预处理，存在大量的预处理工作，并且当业务需求发生变化时，需要重新定义维度，重新进行新维度的数据预处理，在数据预处理过程中往往存在大量数据冗余，另外就是在进行维度建模时，仅仅依靠维度进行建模，不能保证数据来源的准确性和一致性，不适合在数据库底层使用。

范式建模法

范式就是规则，是符合某一级别关系模式的集合，构造数据库时必须遵循一定的规则。关系数据库中的关系必须满足一定的规则，即满足不同的范式。

范式建模法是将原始数据信息根据一定的数据结构和转换模型，分解、转化为规范的格式，使数据遵守一定的约束条件，每一个数据项所表达的意思明确，不产生歧义，同时各条数据之间相互独立，不存在依赖关系。

建模过程

3NF范式建模，实体模型中的关系作为fact表，实体作为维度表