Featured image of post 电商用户行为分析
项目

电商用户行为分析

本文简要介绍了电商用户行为分析离线数仓的搭建及效果展示。

一、项目介绍

  • 项目概述

    本项目旨在构建一个完整的电商用户行为离线数据仓库系统，用于存储、处理和分析电商平台的用户行为数据及业务数据，为企业的经营分析、用户画像、推荐系统等提供数据支持。

  • 项目进度

    • 已完成数据采集架构搭建和HDFS存储设计
    • 正在进行数据仓库分层建模和ETL流程开发
    • 后续将完善数据质量监控和可视化展示

  • 项目价值

    • 用户行为分析：分析用户路径、转化漏斗、停留时长等，优化用户体验
    • 精准营销：基于用户画像实现个性化推荐和精准营销
    • 经营分析：监控核心业务指标(KPI)，辅助经营决策
    • 库存优化：分析商品销售趋势，优化库存管理

  • 项目效果

    warehouse_screen

二、项目规划

  • 集群规划

    四台虚拟机（centos7）、Termius（虚拟机连接）

    服务名称 子服务 服务器 hadoop102 服务器 hadoop103 服务器 hadoop104
    HDFS NameNode √
    DataNode √ √ √
    SecondaryNameNode √
    Yarn NodeManager √ √ √
    Resourcemanager √
    Zookeeper Zookeeper Server √ √ √
    Flume（采集日志） Flume √ √
    Kafka Kafka √ √ √
    Flume（消费Kafka日志） Flume √
    Flume（消费Kafka业务） Flume √
    Hive √ √ √
    MySQL MySQL √
    DataX √ √ √
    Spark √ √ √
    DolphinScheduler ApiApplicationServer √
    AlertServer √
    MasterServer √
    WorkerServer √ √ √
    LoggerServer √ √ √
    Superset Superset √
    Flink √
    ClickHouse √
    Redis √
    Hbase √

  • 项目流程

    data_warehouse_flow

  • 项目架构

    • 数据采集层（cluster.sh）

      用户行为日志采集：

      • 使用Flume构建日志收集系统，实时采集用户在前端的点击、浏览、搜索、加购、下单等行为日志
      • 日志数据以JSON格式存储到HDFS分布式文件系统
      • 自定义拦截器进行数据初步过滤、格式化以及零点漂移问题

      业务数据同步：

      • 使用Maxwell监控MySQL binlog，实时捕获业务数据库的变更
      • 使用Maxwell-bootstrap实现历史全量数据同步
      • 使用DataX进行批量数据同步，处理全量历史数据
      • 自定义拦截器解决零点漂移问题
      • 业务数据包括用户信息、商品信息、订单数据、支付数据等

    • 数据存储层

      • 采用HDFS作为底层分布式存储系统
      • 使用Hive构建数据仓库，实现结构化数据存储
      • 分区表设计按日期分区，优化查询性能

    • 数据处理层

      • 使用Hive SQL进行ETL处理，通过DataGrip实现
      • 实现数据清洗、转换、聚合等操作
      • 采用DolphinScheduler进行任务调度

    • 数据服务层

      • 使用DataX将分析结果导出到关系型数据库供业务系统使用
      • 通过Superset工具实现数据可视化

三、数仓开发

  • ER建模 or 维度建模

    data_warehouse_model

  • 维度建模

    • 事实表：用户行为事件、订单事实、支付事实等
    • 维度表：用户维度、商品维度、时间维度、地区维度等

  • 分层设计

    1. ODS层(原始数据层)：
      • ODS层的表结构设计依托于业务系统同步过来的数据结构
      • ODS层要保存全部历史数据，故其压缩格式应选择压缩比较高的，此处选择gzip
      • ODS层表名的命名规范为：ods_表名_单分区增量/全量标识（inc / full）
    2. DIM层（维度层）
      • DIM层的设计依据是维度建模理论，该层存储维度模型的维度表
      • DIM层的数据存储格式为orc列式存储+snappy压缩
      • DIM层表名的命名规范为dim_表名_全量表或者拉链表标识（full / zip）
    3. DWD层(明细数据层)：
      • DWD层的设计依据是维度建模理论，该层存储维度模型的事实表
      • DWD层的数据存储格式为orc列式存储+snappy压缩
      • DWD层表名的命名规范为dwd_数据域_表名_单分区增量/全量标识（inc / full）
    4. DWS层(汇总数据层)：

      • DWS层的设计参考指标体系

      • DWS层的数据存储格式为orc列式存储+snappy压缩

      • DWS层表名的命名规范为dws_数据域_统计粒度_业务过程_统计周期（1d/nd/td）

        1d表示最近1日，nd表示最近n日，td表示历史至今

    5. ADS层(应用数据层)：
      • 面向具体业务场景的高度聚合数据
      • 包含用户留存分析、转化漏斗、商品销量排行等主题

四、设计细节

  • ODS层 

      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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316

    -- ODS层
    -- Operate Data Store
        -- 存储从mysql业务数据库和日志服务器的日志文件中采集到的数据
            -- 日志数据
                -- 格式：JSON
            -- 业务数据
                -- 历史数据
                -- 格式：
                    -- 全量
                        -- DataX：TSV
                    -- 增量
                        -- Maxwell：JSON
        -- 汇总数据
            -- 希望用最少的资源存最多的数据
                -- 压缩：gzip
                    -- gzip：Hadoop默认支持的，压缩效率不高，压缩率极高
                    -- lzo：Hadoop默认不支持的，压缩效率一般，压缩率高
                    -- snappy：Hadoop默认不支持的，压缩效率极高，压缩率不高
                -- 数据格式尽可能不变
                -- 压缩格式尽可能不变
        -- 命名规范
            -- 在数据仓库中，表其实都是放置在一起的。从逻辑上进行区分，进行分层
            -- 表从名称上区分每一层
            -- 分层标记（ods_） + 同步数据的表名称 + 全量/增量标识（full/inc）

-- 日志表
    -- 表的数据就是同步过来的日志数据
        -- 页面浏览日志：JSON
        -- APP启动日志：JSON
    -- ods_log_inc
        -- 字段
    -- 建表语句
        -- EXTERNAL
            -- 外部表
        -- LOCATION
            -- 位置
    -- 日志数据格式：
        -- 页面浏览日志
            -- JSON中含有JSON
                -- JSON表中的JSON数据如果存在嵌套的情况
                    -- 一般会将最外层的JSON对象的属性作为JSON表的字段
                -- common
                -- actions
                -- displays
                -- page
                -- err
                -- ts
            -- 表的字段类型应该采用特殊类型
        -- APP启动日志
            -- JSON中含有JSON
                -- common
                -- start
                -- err
                -- ts

-- DROP TABLE IF EXISTS ods_log_inc;
-- CREATE EXTERNAL TABLE ods_log_inc
-- (
--     -- common：struct<ar:string, br:string ...>
--     -- actions：array<struct<action_id:string, item:string ...>>
--     -- displays：array<struct<action_id:string, item:string ...>>
--     -- page：struct
--     -- err：struct
--     -- ts：bigint
--     -- start：struct
-- ) COMMENT '活动信息表'
-- PARTITIONED BY (`dt` STRING)
-- ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
-- LOCATION '/warehouse/gmall/ods/ods_log_inc/';

DROP TABLE IF EXISTS ods_log_inc;
CREATE EXTERNAL TABLE ods_log_inc
(
    `common` STRUCT<ar :STRING,
        ba :STRING,
        ch :STRING,
        is_new :STRING,
        md :STRING,
        mid :STRING,
        os :STRING,
        sid :STRING,
        uid :STRING,
        vc :STRING> COMMENT '公共信息',
    `page` STRUCT<during_time :STRING,
        item :STRING,
        item_type :STRING,
        last_page_id :STRING,
        page_id :STRING,
        from_pos_id :STRING,
        from_pos_seq :STRING,
        refer_id :STRING> COMMENT '页面信息',
    `actions` ARRAY<STRUCT<action_id:STRING,
        item:STRING,
        item_type:STRING,
        ts:BIGINT>> COMMENT '动作信息',
    `displays` ARRAY<STRUCT<display_type :STRING,
        item :STRING,
        item_type :STRING,
        `pos_seq` :STRING,
        pos_id :STRING>> COMMENT '曝光信息',
    `start` STRUCT<entry :STRING,
        first_open :BIGINT,
        loading_time :BIGINT,
        open_ad_id :BIGINT,
        open_ad_ms :BIGINT,
        open_ad_skip_ms :BIGINT> COMMENT '启动信息',
    `err` STRUCT<error_code:BIGINT,
            msg:STRING> COMMENT '错误信息',
    `ts` BIGINT  COMMENT '时间戳'
) COMMENT '活动信息表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_log_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

-- 装载数据
    -- load：HDFS
    -- save：ods_log_inc
-- hive：file -> load -> table

-- load data：剪切
load data inpath '/origin_data/gmall/log/topic_log/2022-06-08' into table 
ods_log_inc partition(dt='2022-06-08');

-- 分区表
    -- hive中的表一般都包含分区
    -- hive中表的数据通常非常多，底层采用很多数据文件进行保存
        -- 查询时，会从数据文件中按照条件进行查询
            -- 一旦数据多、文件大、文件多，都会影响查询效率
        -- 通过建立分区的操作，让查询效率提高
            -- 将数据文件不是放置在一起，而是根据某些条件存储到不同的路径下
                -- 这里的条件就是分区
        -- 分区表，存在分区字段，这个字段不是数据字段，而是用于文件目录的划分，不会存储到数据文件中
            -- dt：date
DROP TABLE IF EXISTS test_part;
CREATE TABLE test_part
(
    id int
) COMMENT '活动信息表'
PARTITIONED BY (`dt` STRING)
LOCATION '/test/test_part';

insert into table test_part values (1, '2022-06-08'); -- 真实的数据字段 虚拟的分区字段
-- 静态分区：分区字段的值为固定值
insert into table test_part partition (dt = '2022-06-08') values (1);
-- 动态分区：分区字段取自于查询结果
    -- 分区字段不能赋值
    -- 查询字段应该在最后增加一个额外的字段用于分区操作，所以称之为分区字段
    -- 默认情况下 Hive 没有开启动态分区的处理
set hive.exec.dynamic.partition.mode=nonstrict;
insert into table test_part partition (dt)
select 2, '2022-06-09';

set hive.mapred.mode=nonstrict;
select
    *
from test_part
where dt = "2022-06-09";
--------------------------------------------------------
-- log：JSON
    -- 默认情况下，Hive的表无法解析JSON格式
    -- 如果 Hive 表可以解析JSON格式的数据，那么一般称之为JSON表
        -- 如果JSON属性和表的字段相同，那么可以正常解析
        -- 如果JSON属性少于表的字段，那么存在的属性可以正常解析，不存在的字段会被设定为null
        -- 如果JSON属性多于表的字段，那么多余属性不做解析
        -- 如果JSON属性和表的字段大小写有差异，会进行不区分大小写的解析
DROP TABLE IF EXISTS test_log;
CREATE TABLE test_log
(
    id bigint,
    name string,
    age bigint
) COMMENT '活动信息表'
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/test/test_log/';

-- map, array, struct
    -- array
        -- 使用 [] 表示数组，访问时就是采用中括号加索引的方式进行访问
        -- 如果索引超出范围，并不会发生错误，而是直接返回null
DROP TABLE IF EXISTS test_datatype;
CREATE TABLE test_datatype
(
    ids array<int>,
    dat map<string, string>,
    obj struct<id:int, name:string>
) COMMENT '活动信息表'
LOCATION '/test/test_datatype/';
/*
 子查询 (select array(1, 2, 3, 4) as ids) t
 创建了一个临时表 t，其中包含一列 ids，值为数组 [1, 2, 3, 4]
 外层查询 从临时表 t 中选择 ids 列作为结果
 */
select
    ids,
    ids[5],
    array( ids[1], ids[2] ),
    array_contains(ids, 5)
from
(select `array`(1, 2, 3, 4) ids )
t where array_contains(ids, 8)
;

-- map
    -- 显示效果类似于JSON对象
        -- map数据的访问，不能直接通过key来操作
        -- 访问数据时，采用类似于数组的方式，但是不是采用索引，而是key
        -- 如果key不存在，那么直接返回null
select
    dat,
    dat[0],
    dat['a'],
    map_keys(dat),
    map_values(dat),
    if (array_contains(map_keys(dat), 'c'), 3, 4)
from
    (select `map`('a', 'b', 'c', 1) dat ) -- <'a', 'b'> <'c', 1>
t;

-- struct：对象
    -- 显示效果类似于JSON对象
        -- struct：会将所有数据作为属性值存在
        -- 访问对象中存在的属性时，不能采用中括号，直接采用点的方式就可以
        -- 访问对象中不存在的属性时，会直接发生错误

select struct('a', 'b', 'c', 'd');

-- map和struct区别
    -- 泛型
    -- struct中的属性名称是固定的，只要约束好不能发生变化
    -- map中的key不是固定的，可以动态判断
select
    obj,
    obj.a,
    obj.e
from (select named_struct('a', 'b', 'c', 1) obj) t;

-- 业务表
    -- 全量表：DataX 表结构和业务表保持一致即可
        -- TSV
            -- mysql：column[id, name, age]
            -- data：1001    zhangsan    30
            -- hive：column[id, name, age]
    -- 增量表：Maxwell
        -- JSON
            -- 最外层JSON对象的属性作为表的字段

DROP TABLE IF EXISTS ods_activity_info_full;
CREATE EXTERNAL TABLE ods_activity_info_full
(
    `id`              STRING COMMENT '活动id',
    `activity_name` STRING COMMENT '活动名称',
    `activity_type` STRING COMMENT '活动类型',
    `activity_desc` STRING COMMENT '活动描述',
    `start_time`     STRING COMMENT '开始时间',
    `end_time`        STRING COMMENT '结束时间',
    `create_time`    STRING COMMENT '创建时间',
    `operate_time`   STRING COMMENT '修改时间'
) COMMENT '活动信息表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_activity_info_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

DROP TABLE IF EXISTS ods_activity_rule_full;
CREATE EXTERNAL TABLE ods_activity_rule_full
(
    `id`                  STRING COMMENT '编号',
    `activity_id`       STRING COMMENT '活动ID',
    `activity_type`     STRING COMMENT '活动类型',
    `condition_amount` DECIMAL(16, 2) COMMENT '满减金额',
    `condition_num`     BIGINT COMMENT '满减件数',
    `benefit_amount`    DECIMAL(16, 2) COMMENT '优惠金额',
    `benefit_discount` DECIMAL(16, 2) COMMENT '优惠折扣',
    `benefit_level`     STRING COMMENT '优惠级别',
    `create_time`       STRING COMMENT '创建时间',
    `operate_time`      STRING COMMENT '修改时间'
) COMMENT '活动规则表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_activity_rule_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');


DROP TABLE IF EXISTS ods_cart_info_inc;
CREATE EXTERNAL TABLE ods_cart_info_inc
(
    `type` STRING COMMENT '变动类型',
    `ts`   BIGINT COMMENT '变动时间',
    `data` STRUCT<id :STRING,
        user_id :STRING,
        sku_id :STRING,
        cart_price :DECIMAL(16, 2),
        sku_num :BIGINT,
        img_url :STRING,
        sku_name :STRING,
        is_checked :STRING,
        create_time :STRING,
        operate_time :STRING,
        is_ordered :STRING,
        order_time:STRING> COMMENT '数据',
    `old`  MAP<STRING,STRING> COMMENT '旧值' -- old中不确定字段的个数（修改字段）
) COMMENT '购物车增量表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_cart_info_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

DROP TABLE IF EXISTS test_loc;
CREATE TABLE test_loc
(
    id int
) COMMENT '活动信息表'
LOCATION '/test/test_loc';

  • DIM层 

      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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820

    -- DIM层
    -- Dimension：维度
        -- 所谓的维度其实就是分析数据的角度表
            -- 性别
            -- 年龄
            -- 品牌
            -- 品类
        -- 维度层保存维度表，所以建模理论应该遵循维度建模理论
            -- 维度层中的维度表主要用于统计分析
                -- 数据存储方式应该为列式存储：orc（行式存储不利于统计分析）
                -- 数据压缩效率越高越好（时间短）：snappy
            -- 数据源
                -- ODS层的数据为整个数据仓库做准备
                -- DIM层数据源就是ODS层
        --命名规范
            -- 分层标记 (dim_)_维度名称_全量/拉链（标识）
                -- 全量：维度表的全部数据
                    -- 状态数据为了避免数据出现问题，最好的方式就是每一天都保存全部数据
                -- 绝大多数的维度表都是全量表，特殊情况采用拉链的方式

    -- 建模理论
        -- ER模型
            -- ODS
        -- 维度模型
            -- 维度（状态）表
            -- 事实（行为）表
    -- 维度表
        -- 表
            -- 维度（角度），一个维度就是一张表
                -- t_order, t_sex, t_age
            -- 从实践来讲，一般会将有关联性的维度设置为一张表，不同的维度就是这张表的字段
                -- t_order, t_user(sex, age)
                -- t_order, t_tm, t_category -> t_order, t_sku(tm, category)
            -- 如果维度特别简单，特别独立，只在特殊场合用，可不创建，在事实表中直接使用，无需单独创建
                -- t_payment_type：微信支付、支付宝支付、银联支付
        -- 字段
            -- 维度：只要能用来进行分析的维度，都是字段（有些表的字段，如密码、头像无需分析，不能成为字段）
        -- 数据（字段）来源：参考业务数据库的表字段
            -- 主维表：业务数据库中主要用于分析维度字段的表
            -- 相关维表：业务数据库中相关用于分析维度字段的表

            -- 维度字段的确定
                -- 尽可能生成丰富的维度属性：字段越多越好
                -- 字段越多不会影响统计分析（列式存储），需要哪列取哪列，不用即不需要取
                -- 编码和文字共存
                -- 沉淀出（计算）通用的维度属性
                    -- 时间 2023-02-01
                    -- tel

-- 商品维度表
    -- dim_sku_full
        -- 全量维度表：以天为单位将数据全部同步到维度表的相同时间分区中
        -- 业务数据库的表
            -- 主维表：sku_info
            -- 相关维表：
            -- sku_attr_value
            -- sku_sale_attr_value

-- 建表语句
DROP TABLE IF EXISTS dim_sku_full;
CREATE EXTERNAL TABLE dim_sku_full
(
    `id`                   STRING COMMENT 'SKU_ID',
    `price`                DECIMAL(16, 2) COMMENT '商品价格',
    `sku_name`             STRING COMMENT '商品名称',
    `sku_desc`             STRING COMMENT '商品描述',
    `weight`               DECIMAL(16, 2) COMMENT '重量',
    `is_sale`              BOOLEAN COMMENT '是否在售',
    `spu_id`               STRING COMMENT 'SPU编号',
    `spu_name`             STRING COMMENT 'SPU名称',
    `category3_id`         STRING COMMENT '三级品类ID',
    `category3_name`       STRING COMMENT '三级品类名称',
    `category2_id`         STRING COMMENT '二级品类id',
    `category2_name`       STRING COMMENT '二级品类名称',
    `category1_id`         STRING COMMENT '一级品类ID',
    `category1_name`       STRING COMMENT '一级品类名称',
    `tm_id`                  STRING COMMENT '品牌ID',
    `tm_name`               STRING COMMENT '品牌名称',
    `sku_attr_values`      ARRAY<STRUCT<attr_id :STRING,
        value_id :STRING,
        attr_name :STRING,
        value_name:STRING>> COMMENT '平台属性',
    `sku_sale_attr_values` ARRAY<STRUCT<sale_attr_id :STRING,
        sale_attr_value_id :STRING,
        sale_attr_name :STRING,
        sale_attr_value_name:STRING>> COMMENT '销售属性',
    `create_time`          STRING COMMENT '创建时间'
) COMMENT '商品维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_sku_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');  -- 默认gzip


-- 装载数据
    -- load
        -- 补全数据
            -- 补全行：union
            -- 补全列：join
                -- join, left join, right join, full join
    -- save
        -- 以时间为单位（分区）进行保存
        -- 采集汇总哪一天的数据，就保存到哪一天的分区中
        -- insert into：插入
        -- insert overwrite：覆盖
        -- 大数据中没有事务的概念，如果执行到中间失败，下面不会再执行，需要retry整个脚本，使用insert into会导致已成功执行的重复
insert overwrite table dim_sku_full partition (dt='2022-06-08')
select
     sku.`id`                  ,--STRING COMMENT 'SKU_ID',
    `price`                ,--DECIMAL(16, 2) COMMENT '商品价格',
    `sku_name`             ,--STRING COMMENT '商品名称',
    `sku_desc`             ,--STRING COMMENT '商品描述',
    `weight`               ,--DECIMAL(16, 2) COMMENT '重量',
    `is_sale`              ,--BOOLEAN COMMENT '是否在售',
    `spu_id`               ,--STRING COMMENT 'SPU编号',
    `spu_name`             ,--STRING COMMENT 'SPU名称',
    `category3_id`         ,--STRING COMMENT '三级品类ID',
    `category3_name`       ,--STRING COMMENT '三级品类名称',
    `category2_id`         ,--STRING COMMENT '二级品类id',
    `category2_name`       ,--STRING COMMENT '二级品类名称',
    `category1_id`         ,--STRING COMMENT '一级品类ID',
    `category1_name`       ,--STRING COMMENT '一级品类名称',
    `tm_id`                ,--  STRING COMMENT '品牌ID',
    `tm_name`              ,-- STRING COMMENT '品牌名称',
    `sku_attr_values`      ,
    `sku_sale_attr_values` ,
    `create_time`           --STRING COMMENT '创建时间'
from (
    select
        `id`                   ,--STRING COMMENT 'SKU_ID',
        `price`                ,--DECIMAL(16, 2) COMMENT '商品价格',
        `sku_name`             ,--STRING COMMENT '商品名称',
        `sku_desc`             ,--STRING COMMENT '商品描述',
        `weight`               ,--DECIMAL(16, 2) COMMENT '重量',
        `is_sale`              ,--BOOLEAN COMMENT '是否在售',
        `spu_id`               ,--STRING COMMENT 'SPU编号',
        --`spu_name`             ,--STRING COMMENT 'SPU名称',
        `category3_id`         ,--STRING COMMENT '三级品类ID',
        --`category3_name`       ,--STRING COMMENT '三级品类名称',
        --`category2_id`         ,--STRING COMMENT '二级品类id',
        --`category2_name`       ,--STRING COMMENT '二级品类名称',
        --`category1_id`         ,--STRING COMMENT '一级品类ID',
        --`category1_name`       ,--STRING COMMENT '一级品类名称',
        `tm_id`                ,--STRING COMMENT '品牌ID',
        --`tm_name`              ,--STRING COMMENT '品牌名称',
        `create_time`           --STRING COMMENT '创建时间'
    from ods_sku_info_full
    where dt = '2022-06-08'
) sku
left join (
    select
        id,
        spu_name
    from ods_spu_info_full
    where dt = '2022-06-08'
) spu on sku.spu_id = spu.id
left join (
    select
        id,
        tm_name
    from ods_base_trademark_full
    where dt = '2022-06-08'
) tm on sku.tm_id = tm.id
left join (
    select
        id,
        name category3_name,
        category2_id
    from ods_base_category3_full
    where dt = '2022-06-08'
) c3 on sku.category3_id = c3.id
left join (
    select
        id,
        name category2_name,
        category1_id
    from ods_base_category2_full
    where dt = '2022-06-08'
) c2 on c3.category2_id = c2.id
left join (
    select
        id,
        name category1_name
    from ods_base_category1_full
    where dt = '2022-06-08'
) c1 on c2.category1_id = c1.id
left join (
    select
        sku_id,
        collect_list(named_struct("attr_id", attr_id, "value_id", value_id, "attr_name", attr_name, "value_name", value_name)) sku_attr_values
    from ods_sku_attr_value_full
    where dt = '2022-06-08'
    group by sku_id
) sav on sav.sku_id = sku.id
left join (
    select
        sku_id,
        collect_list(named_struct("sale_attr_id", sale_attr_id, "sale_attr_value_id", sale_attr_value_id, "sale_attr_name", sale_attr_name, "sale_attr_value_name", sale_attr_value_name)) sku_sale_attr_values
    from ods_sku_sale_attr_value_full
    where dt = '2022-06-08'
    group by sku_id
) ssav on ssav.sku_id = sku.id;

-- 1. struct
-- 2. 相同的sku n * struct -> array
    -- 聚合函数 -> array
select
    sku_id,
    collect_list(named_struct("attr_id", attr_id, "value_id", value_id, "attr_name", attr_name, "value_name", value_name))
from ods_sku_attr_value_full
where dt = '2022-06-08'
group by sku_id;

-- CTE
select
    *
from (
    select *
    from (
        select
            *
        from ods_user_info_inc
        where dt = '2022-06-08'
    ) t1
) t
join (
    select
        *
    from (
        select *
        from ods_user_info_inc
        where dt = '2022-06-08'
    ) t1
) t2;

with t as (select * from ods_user_info_inc where dt = '2022-06-08')
select
    *
from (
    select
        *
    from t
) t1
join (
    select
        *
    from t
) t2;


-- 优惠券维度表
    -- 主维表：coupon_info
    -- 相关维表
        -- coupon_range（冗余）
        -- coupon_use（行为）
DROP TABLE IF EXISTS dim_coupon_full;
CREATE EXTERNAL TABLE dim_coupon_full
(
    `id`                  STRING COMMENT '优惠券编号',
    `coupon_name`       STRING COMMENT '优惠券名称',
    `coupon_type_code` STRING COMMENT '优惠券类型编码', -- 编码与名称共存
    `coupon_type_name` STRING COMMENT '优惠券类型名称',
    `condition_amount` DECIMAL(16, 2) COMMENT '满额数',
    `condition_num`     BIGINT COMMENT '满件数',
    `activity_id`       STRING COMMENT '活动编号',
    `benefit_amount`   DECIMAL(16, 2) COMMENT '减免金额',
    `benefit_discount` DECIMAL(16, 2) COMMENT '折扣',
    `benefit_rule`     STRING COMMENT '优惠规则:满元*减*元，满*件打*折', -- 沉淀，通过计算得到
    `create_time`       STRING COMMENT '创建时间',
    `range_type_code`  STRING COMMENT '优惠范围类型编码',
    `range_type_name`  STRING COMMENT '优惠范围类型名称',
    `limit_num`         BIGINT COMMENT '最多领取次数',
    `taken_count`       BIGINT COMMENT '已领取次数',
    `start_time`        STRING COMMENT '可以领取的开始时间',
    `end_time`          STRING COMMENT '可以领取的结束时间',
    `operate_time`      STRING COMMENT '修改时间',
    `expire_time`       STRING COMMENT '过期时间'
) COMMENT '优惠券维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_coupon_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');


-- 数据装载
    -- load
        -- coupon_info
        -- base_dic
    -- save
insert overwrite table dim_coupon_full partition (dt='2022-06-08')
select
    `id`                  ,--STRING COMMENT '优惠券编号',
    `coupon_name`         ,--STRING COMMENT '优惠券名称',
    `coupon_type_code`    ,--STRING COMMENT '优惠券类型编码', -- 编码与名称共存
    `coupon_type_name`    ,--STRING COMMENT '优惠券类型名称',
    `condition_amount`    ,--DECIMAL(16, 2) COMMENT '满额数',
    `condition_num`       ,--BIGINT COMMENT '满件数',
    `activity_id`         ,--STRING COMMENT '活动编号',
    `benefit_amount`      ,--DECIMAL(16, 2) COMMENT '减免金额',
    `benefit_discount`    ,--DECIMAL(16, 2) COMMENT '折扣',
    `benefit_rule`        ,--STRING COMMENT '优惠规则:满元*减*元，满*件打*折', -- 沉淀，通过计算得到
    `create_time`         ,--STRING COMMENT '创建时间',
    `range_type_code`     ,--STRING COMMENT '优惠范围类型编码',
    `range_type_name`     ,--STRING COMMENT '优惠范围类型名称',
    `limit_num`           ,--BIGINT COMMENT '最多领取次数',
    `taken_count`         ,--BIGINT COMMENT '已领取次数',
    `start_time`          ,--STRING COMMENT '可以领取的开始时间',
    `end_time`            ,--STRING COMMENT '可以领取的结束时间',
    `operate_time`        ,--STRING COMMENT '修改时间',
    `expire_time`         --STRING COMMENT '过期时间'
from (
    select
        `id`                  ,--STRING COMMENT '优惠券编号',
        `coupon_name`         ,--STRING COMMENT '优惠券名称',
        coupon_type `coupon_type_code`    ,--STRING COMMENT '优惠券类型编码', -- 编码与名称共存
        --`coupon_type_name`    ,--STRING COMMENT '优惠券类型名称',
        `condition_amount`    ,--DECIMAL(16, 2) COMMENT '满额数',
        `condition_num`       ,--BIGINT COMMENT '满件数',
        `activity_id`         ,--STRING COMMENT '活动编号',
        `benefit_amount`      ,--DECIMAL(16, 2) COMMENT '减免金额',
        `benefit_discount`    ,--DECIMAL(16, 2) COMMENT '折扣',
        case coupon_type
            when '3201' then concat('满', condition_amount, '元减', benefit_amount, '元')
            when '3202' then concat('满', condition_num, '件打', benefit_discount, '折')
            when '3203' then concat('减', benefit_amount, '元')
        end `benefit_rule`        ,--STRING COMMENT '优惠规则:满元*减*元，满*件打*折', -- 沉淀，通过计算得到
        `create_time`         ,--STRING COMMENT '创建时间',
        range_type `range_type_code`     ,--STRING COMMENT '优惠范围类型编码',
        --`range_type_name`     ,--STRING COMMENT '优惠范围类型名称',
        `limit_num`           ,--BIGINT COMMENT '最多领取次数',
        `taken_count`         ,--BIGINT COMMENT '已领取次数',
        `start_time`          ,--STRING COMMENT '可以领取的开始时间',
        `end_time`            ,--STRING COMMENT '可以领取的结束时间',
        `operate_time`        ,--STRING COMMENT '修改时间',
        `expire_time`         --STRING COMMENT '过期时间'
    from ods_coupon_info_full
    where dt = '2022-06-08'
) cp
join (
    select
        dic_code,
        dic_name coupon_type_name
    from ods_base_dic_full
    where dt = '2022-06-08'
    and parent_code= '32'
) dic1 on dic1.dic_code = cp.coupon_type_code
join (
    select
        dic_code,
        dic_name range_type_name
    from ods_base_dic_full
    where dt = '2022-06-08'
    and parent_code= '33'
) dic2 on dic2.dic_code = cp.range_type_code;

-- 活动维度表
    -- activity_info：相关维表
    -- activity_rule：主维表
    -- activity_sku (x)

DROP TABLE IF EXISTS dim_activity_full;
CREATE EXTERNAL TABLE dim_activity_full
(
    `activity_rule_id`   STRING COMMENT '活动规则ID',
    `activity_id`         STRING COMMENT '活动ID',
    `activity_name`       STRING COMMENT '活动名称',
    `activity_type_code` STRING COMMENT '活动类型编码',
    `activity_type_name` STRING COMMENT '活动类型名称',
    `activity_desc`       STRING COMMENT '活动描述',
    `start_time`           STRING COMMENT '开始时间',
    `end_time`             STRING COMMENT '结束时间',
    `create_time`          STRING COMMENT '创建时间',
    `condition_amount`    DECIMAL(16, 2) COMMENT '满减金额',
    `condition_num`       BIGINT COMMENT '满减件数',
    `benefit_amount`      DECIMAL(16, 2) COMMENT '优惠金额',
    `benefit_discount`   DECIMAL(16, 2) COMMENT '优惠折扣',
    `benefit_rule`        STRING COMMENT '优惠规则',
    `benefit_level`       STRING COMMENT '优惠级别'
) COMMENT '活动维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_activity_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 数据装载
insert overwrite table dim_activity_full partition (dt='2022-06-08')
select
    `activity_rule_id`   ,--STRING COMMENT '活动规则ID',
    `activity_id`        ,-- STRING COMMENT '活动ID',
    `activity_name`      ,-- STRING COMMENT '活动名称',
    `activity_type_code` ,--STRING COMMENT '活动类型编码',
    `activity_type_name` ,--STRING COMMENT '活动类型名称',
    `activity_desc`      ,-- STRING COMMENT '活动描述',
    `start_time`         ,--  STRING COMMENT '开始时间',
    `end_time`           ,--  STRING COMMENT '结束时间',
    `create_time`        ,--  STRING COMMENT '创建时间',
    `condition_amount`   ,-- DECIMAL(16, 2) COMMENT '满减金额',
    `condition_num`      ,-- BIGINT COMMENT '满减件数',
    `benefit_amount`     ,-- DECIMAL(16, 2) COMMENT '优惠金额',
    `benefit_discount`   ,--DECIMAL(16, 2) COMMENT '优惠折扣',
    `benefit_rule`       ,-- STRING COMMENT '优惠规则',
    `benefit_level`      -- STRING COMMENT '优惠级别'
from (
    select
        `id` `activity_rule_id`, -- STRING COMMENT '活动规则ID',
        `activity_id`        ,-- STRING COMMENT '活动ID',
        --`activity_name`      ,-- STRING COMMENT '活动名称',
        activity_type `activity_type_code` ,-- STRING COMMENT '活动类型编码',
        --`activity_type_name` ,-- STRING COMMENT '活动类型名称',
        `create_time`        ,-- STRING COMMENT '创建时间',
        `condition_amount`   ,-- DECIMAL(16, 2) COMMENT '满减金额',
        `condition_num`      ,-- BIGINT COMMENT '满减件数',
        `benefit_amount`     ,-- DECIMAL(16, 2) COMMENT '优惠金额',
        `benefit_discount`   ,-- DECIMAL(16, 2) COMMENT '优惠折扣',
        case activity_type
            when '3101' then concat('满', condition_amount, '元减', benefit_amount, '元')
            when '3102' then concat('满', condition_num, '件打', benefit_discount, '折')
            when '3103' then concat('打', benefit_discount, '折')
        end `benefit_rule`  ,-- STRING COMMENT '优惠规则',
        `benefit_level`      -- STRING COMMENT '优惠级别'
    from ods_activity_rule_full
    where dt = '2022-06-08'
) rule
left join (
    select
        id,
        activity_name,
        `activity_desc`      ,-- STRING COMMENT '活动描述',
        `start_time`         ,-- STRING COMMENT '开始时间',
        `end_time`            -- STRING COMMENT '结束时间',
    from ods_activity_info_full
    where dt = '2022-06-08'

) info on rule.activity_id = info.id
left join (
    select
        dic_code,
        dic_name activity_type_name
    from ods_base_dic_full
    where dt = '2022-06-08'
    and parent_code = '31'
) dic on rule.activity_type_code = dic.dic_code;

-- 地区维度表
    -- 建表语句
DROP TABLE IF EXISTS dim_province_full;
CREATE EXTERNAL TABLE dim_province_full
(
    `id`              STRING COMMENT '省份ID',
    `province_name` STRING COMMENT '省份名称',
    `area_code`     STRING COMMENT '地区编码',
    `iso_code`      STRING COMMENT '旧版国际标准地区编码，供可视化使用',
    `iso_3166_2`    STRING COMMENT '新版国际标准地区编码，供可视化使用',
    `region_id`     STRING COMMENT '地区ID',
    `region_name`   STRING COMMENT '地区名称'
) COMMENT '地区维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_province_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 数据装载
insert overwrite table dim_province_full partition (dt='2022-06-08')
select
    prv.`id`              ,--STRING COMMENT '省份ID',
    `province_name`       ,--STRING COMMENT '省份名称',
    `area_code`           ,--STRING COMMENT '地区编码',
    `iso_code`            ,--STRING COMMENT '旧版国际标准地区编码，供可视化使用',
    `iso_3166_2`          ,--STRING COMMENT '新版国际标准地区编码，供可视化使用',
    `region_id`           ,--STRING COMMENT '地区ID',
    `region_name`         --STRING COMMENT '地区名称'
from (
    select
        `id`              ,--STRING COMMENT '省份ID',
        name `province_name`   ,--STRING COMMENT '省份名称',
        `area_code`       ,--STRING COMMENT '地区编码',
        `iso_code`        ,--STRING COMMENT '旧版国际标准地区编码，供可视化使用',
        `iso_3166_2`      ,--STRING COMMENT '新版国际标准地区编码，供可视化使用',
        `region_id`       --STRING COMMENT '地区ID',
        --`region_name`     --STRING COMMENT '地区名称'
    from ods_base_province_full
    where dt = '2022-06-08'
) prv
left join (
    select
        id,
        region_name
    from ods_base_region_full
    where dt = '2022-06-08'
) region on prv.region_id = region.id;

-- 营销坑位表
    -- 建表语句
DROP TABLE IF EXISTS dim_promotion_pos_full;
CREATE EXTERNAL TABLE dim_promotion_pos_full
(
    `id`                 STRING COMMENT '营销坑位ID',
    `pos_location`       STRING COMMENT '营销坑位位置',
    `pos_type`           STRING COMMENT '营销坑位类型 ',
    `promotion_type`     STRING COMMENT '营销类型',
    `create_time`        STRING COMMENT '创建时间',
    `operate_time`       STRING COMMENT '修改时间'
) COMMENT '营销坑位维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_promotion_pos_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

insert overwrite table dim_promotion_pos_full partition (dt='2022-06-08')
select
    `id`                ,--STRING COMMENT '营销坑位ID',
    `pos_location`      ,--STRING COMMENT '营销坑位位置',
    `pos_type`          ,--STRING COMMENT '营销坑位类型 ',
    `promotion_type`    ,--STRING COMMENT '营销类型',
    `create_time`       ,--STRING COMMENT '创建时间',
    `operate_time`       --STRING COMMENT '修改时间'
from ods_promotion_pos_full
where dt = '2022-06-08';

-- 营销渠道维度表
    -- 建表语句
DROP TABLE IF EXISTS dim_promotion_refer_full;
CREATE EXTERNAL TABLE dim_promotion_refer_full
(
    `id`                  STRING COMMENT '营销渠道ID',
    `refer_name`          STRING COMMENT '营销渠道名称',
    `create_time`         STRING COMMENT '创建时间',
    `operate_time`        STRING COMMENT '修改时间'
) COMMENT '营销渠道维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_promotion_refer_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

insert overwrite table dim_promotion_refer_full partition (dt='2022-06-08')
select
    `id`                  ,--STRING COMMENT '营销渠道ID',
    `refer_name`          ,--STRING COMMENT '营销渠道名称',
    `create_time`         ,--STRING COMMENT '创建时间',
    `operate_time`         --STRING COMMENT '修改时间'
from ods_promotion_refer_full
where dt = '2022-06-08';

-- 日期维度表
    -- 建表语句
DROP TABLE IF EXISTS dim_date;
CREATE EXTERNAL TABLE dim_date
(
    `date_id`    STRING COMMENT '日期ID',
    `week_id`    STRING COMMENT '周ID,一年中的第几周',
    `week_day`   STRING COMMENT '周几',
    `day`         STRING COMMENT '每月的第几天',
    `month`       STRING COMMENT '一年中的第几月',
    `quarter`    STRING COMMENT '一年中的第几季度',
    `year`        STRING COMMENT '年份',
    `is_workday` STRING COMMENT '是否是工作日',
    `holiday_id` STRING COMMENT '节假日'
) COMMENT '日期维度表'
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_date/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 数据装载
    -- 创建临时表
DROP TABLE IF EXISTS tmp_dim_date_info;
CREATE EXTERNAL TABLE tmp_dim_date_info (
    `date_id`       STRING COMMENT '日',
    `week_id`       STRING COMMENT '周ID',
    `week_day`      STRING COMMENT '周几',
    `day`            STRING COMMENT '每月的第几天',
    `month`          STRING COMMENT '第几月',
    `quarter`       STRING COMMENT '第几季度',
    `year`           STRING COMMENT '年',
    `is_workday`    STRING COMMENT '是否是工作日',
    `holiday_id`    STRING COMMENT '节假日'
) COMMENT '时间维度表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' -- tsv
LOCATION '/warehouse/gmall/tmp/tmp_dim_date_info/';

-- tsv（行式） -> orc（列式）
-- Hive -> SQL -> Spark
insert into table dim_date select * from tmp_dim_date_info; -- 自动转换格式

-- 用户维度表（用增量的方式来表示全量）
    -- user_info
    -- user_address
    -- 建表语句
DROP TABLE IF EXISTS dim_user_zip;
CREATE EXTERNAL TABLE dim_user_zip
(
    `id`           STRING COMMENT '用户ID',
    `name`         STRING COMMENT '用户姓名',
    `phone_num`    STRING COMMENT '手机号码',
    `email`        STRING COMMENT '邮箱',
    `user_level`   STRING COMMENT '用户等级',
    `birthday`     STRING COMMENT '生日',
    `gender`       STRING COMMENT '性别',
    `create_time`  STRING COMMENT '创建时间',
    `operate_time` STRING COMMENT '操作时间',
    `start_date`   STRING COMMENT '开始日期',  -- 类似于聚合
    `end_date`     STRING COMMENT '结束日期'
) COMMENT '用户维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_user_zip/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

insert overwrite table dim_user_zip partition (dt='2022-06-08')
select
    *
from ods_user_info_inc
where dt = '2022-06-08';

-- 数据的基数
    -- 每日全量数据
        -- 每天同步一份全部的数据
            -- 40 * 400 * 5 = 80000（行） * 1kb = 80Mb
            -- 40w * 400 * 5 = 800Gb
        -- 用户
            -- 2000w * 400 * 5 = 40Tb
    -- 将数据状态的变化记录下来，而不是全部数据，而是指定时间范围 (start, end)
        -- 在表的设计中，需要增加两个额外的字段，用于标识时间范围 (start, end)
        -- 表设计为拉链表(zip)即可
        -- 一般情况下，拉链表中截止到今天依然有效的状态的结束时间，为了不修改，所以设置为时间极大值 9999-12-31

-- 拉链表的数据源应该是ods层增量表
    -- 增量表
        -- maxwell
            -- 数据格式：json
            -- 同步方式：
                -- 首日：bootstrap(select)
                -- 每日：insert, update, delete(binlog)
-- 首日：全量
    -- 2022-06-08：数仓上线首日：user_info_all
        -- 首日获取的全量数据只有用户的最新状态数据，不存在历史数据，所以无法判断状态的开始
            -- 所以折中地认为首日就是当前最新状态的开始日期
            -- 将最新状态的结束时间设为时间极大值
    -- 拉链表的分区策略
        -- 将数据存储到哪一个分区，更方便我们的查询
            -- 假设将用户数据保存到用户的开始日期的分区中
                -- zhangsan 2022-06-08 9999-12-31 => 2022-06-08
                -- lisi     2022-06-08 2022-06-08 => 2022-06-08
                -- lisi     2022-06-09 9999-12-31 => 2022-06-09
                -- select * from t_user where dt = '2022-06-08';
            -- 假设将用户数据保存到用户的结束日期的分区中
                -- zhangsan 2022-06-08 9999-12-31 => 9999-12-31
                -- lisi     2022-06-08 2022-06-08 => 2022-06-08
                -- lisi     2022-06-09 9999-12-31 => 9999-12-31
                -- select * from t_user where dt = '2022-06-08';
        -- 将数据存储到结束时间所在分区
insert overwrite table dim_user_zip partition (dt='9999-12-31')
select
    type,
    data.`id`           ,--STRING COMMENT '用户ID',
    data.`name`         ,--STRING COMMENT '用户姓名',
    data.`phone_num`    ,--STRING COMMENT '手机号码',
    data.`email`        ,--STRING COMMENT '邮箱',
    data.`user_level`   ,--STRING COMMENT '用户等级',
    data.`birthday`     ,--STRING COMMENT '生日',
    data.`gender`       ,--STRING COMMENT '性别',
    data.`create_time`  ,--STRING COMMENT '创建时间',
    data.`operate_time` ,--STRING COMMENT '操作时间',
    '2022-06-08'   ,--STRING COMMENT '开始日期',
    '9999-12-31'     --STRING COMMENT '结束日期'
from ods_user_info_inc
where dt = '2022-06-08'
and type = 'bootstrap-insert';

-- 每日：增量
    -- 增加的用户信息
        -- type：insert
        -- start：当天（2022-06-09）
        -- end：9999-12-31
        -- partition：9999-12-31
    -- 修改的用户信息
        -- 修改后
            -- type：update
            -- start：当天（2022-06-09）
            -- end：9999-12-31
            -- partition：9999-12-31
        -- 修改前
            -- dim_user_zip (dt=9999-12-31)
            -- start：start
            -- end：9999-12-31 -> 2022-06-08
            -- partition：9999-12-31 -> 2022-06-08

-- 修改
-- insert overwrite table  xxx
-- select
--     '2022-06-08' dt
-- from (
--     select '9999-12-31' dt
-- ) t

-- 每日数据装载
    -- union：去重
    -- union all：不去重
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dim_user_zip partition (dt) -- 动态分区
select
    `id`           ,--STRING COMMENT '用户ID',
    `name`         ,--STRING COMMENT '用户姓名',
    `phone_num`    ,--STRING COMMENT '手机号码',
    `email`        ,--STRING COMMENT '邮箱',
    `user_level`   ,--STRING COMMENT '用户等级',
    `birthday`     ,--STRING COMMENT '生日',
    `gender`       ,--STRING COMMENT '性别',
    `create_time`  ,--STRING COMMENT '创建时间',
    `operate_time` ,--STRING COMMENT '操作时间',
    `start_date`   ,--STRING COMMENT '开始日期',
    if ( rn == 2, date_sub('2022-06-09', 1), '9999-12-31'),
    if ( rn == 2, date_sub('2022-06-09', 1), '9999-12-31')
from (
    select
        *,
        row_number() over ( partition by id order by start_date desc ) rn
    from (
        select
            `id`           ,--STRING COMMENT '用户ID',
            `name`         ,--STRING COMMENT '用户姓名',
            `phone_num`    ,--STRING COMMENT '手机号码',
            `email`        ,--STRING COMMENT '邮箱',
            `user_level`   ,--STRING COMMENT '用户等级',
            `birthday`     ,--STRING COMMENT '生日',
            `gender`       ,--STRING COMMENT '性别',
            `create_time`  ,--STRING COMMENT '创建时间',
            `operate_time` ,--STRING COMMENT '操作时间',
            `start_date`   ,--STRING COMMENT '开始日期',
            `end_date`      --STRING COMMENT '结束日期'
        from dim_user_zip
        where dt = '9999-12-31'
        union all
        select
            data.`id`           ,--STRING COMMENT '用户ID',
            data.`name`         ,--STRING COMMENT '用户姓名',
            data.`phone_num`    ,--STRING COMMENT '手机号码',
            data.`email`        ,--STRING COMMENT '邮箱',
            data.`user_level`   ,--STRING COMMENT '用户等级',
            data.`birthday`     ,--STRING COMMENT '生日',
            data.`gender`       ,--STRING COMMENT '性别',
            data.`create_time`  ,--STRING COMMENT '创建时间',
            data.`operate_time` ,--STRING COMMENT '操作时间',
            '2022-06-09'   ,--STRING COMMENT '开始日期',  -- 类似于聚合
            '9999-12-31'      --STRING COMMENT '结束日期'
        from ods_user_info_inc
        where dt = '2022-06-09'
        and type in ('insert', 'update')
    ) t
) t1;

-- 比对方式
    -- 将数据集合并（union）在一起进行比对，需要查找数据的规律
    -- 将数据集连接（join）在一起进行比对
        -- full join + inner join

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dim_user_zip partition (dt) -- 动态分区
select
    `id`           ,--STRING COMMENT '用户ID',
    `name`         ,--STRING COMMENT '用户姓名',
    `phone_num`    ,--STRING COMMENT '手机号码',
    `email`        ,--STRING COMMENT '邮箱',
    `user_level`   ,--STRING COMMENT '用户等级',
    `birthday`     ,--STRING COMMENT '生日',
    `gender`       ,--STRING COMMENT '性别',
    `create_time`  ,--STRING COMMENT '创建时间',
    `operate_time` ,--STRING COMMENT '操作时间',
    `start_date`   ,--STRING COMMENT '开始日期',
    if ( rn == 2, date_sub('2022-06-09', 1), '9999-12-31'),
    if ( rn == 2, date_sub('2022-06-09', 1), '9999-12-31')
from (
    select
        *,
        row_number() over ( partition by id order by start_date desc ) rn
    from (
        select
            `id`           ,--STRING COMMENT '用户ID',
            `name`         ,--STRING COMMENT '用户姓名',
            `phone_num`    ,--STRING COMMENT '手机号码',
            `email`        ,--STRING COMMENT '邮箱',
            `user_level`   ,--STRING COMMENT '用户等级',
            `birthday`     ,--STRING COMMENT '生日',
            `gender`       ,--STRING COMMENT '性别',
            `create_time`  ,--STRING COMMENT '创建时间',
            `operate_time` ,--STRING COMMENT '操作时间',
            `start_date`   ,--STRING COMMENT '开始日期',
            `end_date`      --STRING COMMENT '结束日期'
        from dim_user_zip
        where dt = '9999-12-31'
        union all
        select
            `id`           ,--STRING COMMENT '用户ID',
            `name`         ,--STRING COMMENT '用户姓名',
            `phone_num`    ,--STRING COMMENT '手机号码',
            `email`        ,--STRING COMMENT '邮箱',
            `user_level`   ,--STRING COMMENT '用户等级',
            `birthday`     ,--STRING COMMENT '生日',
            `gender`       ,--STRING COMMENT '性别',
            `create_time`  ,--STRING COMMENT '创建时间',
            `operate_time` ,--STRING COMMENT '操作时间',
            '2022-06-09'   ,--STRING COMMENT '开始日期',
            '9999-12-31'      --STRING COMMENT '结束日期'
        from (
            select
                data.`id`           ,--STRING COMMENT '用户ID',
                data.`name`         ,--STRING COMMENT '用户姓名',
                data.`phone_num`    ,--STRING COMMENT '手机号码',
                data.`email`        ,--STRING COMMENT '邮箱',
                data.`user_level`   ,--STRING COMMENT '用户等级',
                data.`birthday`     ,--STRING COMMENT '生日',
                data.`gender`       ,--STRING COMMENT '性别',
                data.`create_time`  ,--STRING COMMENT '创建时间',
                data.`operate_time` ,--STRING COMMENT '操作时间',
                row_number() over (partition by data.id order by ts desc) num
            from ods_user_info_inc
            where dt = '2022-06-09'
            and type in ('insert', 'update')
        ) a where num = 1
    ) t
) t1

  • DWD层 

       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
 145
 146
 147
 148
 149
 150
 151
 152
 153
 154
 155
 156
 157
 158
 159
 160
 161
 162
 163
 164
 165
 166
 167
 168
 169
 170
 171
 172
 173
 174
 175
 176
 177
 178
 179
 180
 181
 182
 183
 184
 185
 186
 187
 188
 189
 190
 191
 192
 193
 194
 195
 196
 197
 198
 199
 200
 201
 202
 203
 204
 205
 206
 207
 208
 209
 210
 211
 212
 213
 214
 215
 216
 217
 218
 219
 220
 221
 222
 223
 224
 225
 226
 227
 228
 229
 230
 231
 232
 233
 234
 235
 236
 237
 238
 239
 240
 241
 242
 243
 244
 245
 246
 247
 248
 249
 250
 251
 252
 253
 254
 255
 256
 257
 258
 259
 260
 261
 262
 263
 264
 265
 266
 267
 268
 269
 270
 271
 272
 273
 274
 275
 276
 277
 278
 279
 280
 281
 282
 283
 284
 285
 286
 287
 288
 289
 290
 291
 292
 293
 294
 295
 296
 297
 298
 299
 300
 301
 302
 303
 304
 305
 306
 307
 308
 309
 310
 311
 312
 313
 314
 315
 316
 317
 318
 319
 320
 321
 322
 323
 324
 325
 326
 327
 328
 329
 330
 331
 332
 333
 334
 335
 336
 337
 338
 339
 340
 341
 342
 343
 344
 345
 346
 347
 348
 349
 350
 351
 352
 353
 354
 355
 356
 357
 358
 359
 360
 361
 362
 363
 364
 365
 366
 367
 368
 369
 370
 371
 372
 373
 374
 375
 376
 377
 378
 379
 380
 381
 382
 383
 384
 385
 386
 387
 388
 389
 390
 391
 392
 393
 394
 395
 396
 397
 398
 399
 400
 401
 402
 403
 404
 405
 406
 407
 408
 409
 410
 411
 412
 413
 414
 415
 416
 417
 418
 419
 420
 421
 422
 423
 424
 425
 426
 427
 428
 429
 430
 431
 432
 433
 434
 435
 436
 437
 438
 439
 440
 441
 442
 443
 444
 445
 446
 447
 448
 449
 450
 451
 452
 453
 454
 455
 456
 457
 458
 459
 460
 461
 462
 463
 464
 465
 466
 467
 468
 469
 470
 471
 472
 473
 474
 475
 476
 477
 478
 479
 480
 481
 482
 483
 484
 485
 486
 487
 488
 489
 490
 491
 492
 493
 494
 495
 496
 497
 498
 499
 500
 501
 502
 503
 504
 505
 506
 507
 508
 509
 510
 511
 512
 513
 514
 515
 516
 517
 518
 519
 520
 521
 522
 523
 524
 525
 526
 527
 528
 529
 530
 531
 532
 533
 534
 535
 536
 537
 538
 539
 540
 541
 542
 543
 544
 545
 546
 547
 548
 549
 550
 551
 552
 553
 554
 555
 556
 557
 558
 559
 560
 561
 562
 563
 564
 565
 566
 567
 568
 569
 570
 571
 572
 573
 574
 575
 576
 577
 578
 579
 580
 581
 582
 583
 584
 585
 586
 587
 588
 589
 590
 591
 592
 593
 594
 595
 596
 597
 598
 599
 600
 601
 602
 603
 604
 605
 606
 607
 608
 609
 610
 611
 612
 613
 614
 615
 616
 617
 618
 619
 620
 621
 622
 623
 624
 625
 626
 627
 628
 629
 630
 631
 632
 633
 634
 635
 636
 637
 638
 639
 640
 641
 642
 643
 644
 645
 646
 647
 648
 649
 650
 651
 652
 653
 654
 655
 656
 657
 658
 659
 660
 661
 662
 663
 664
 665
 666
 667
 668
 669
 670
 671
 672
 673
 674
 675
 676
 677
 678
 679
 680
 681
 682
 683
 684
 685
 686
 687
 688
 689
 690
 691
 692
 693
 694
 695
 696
 697
 698
 699
 700
 701
 702
 703
 704
 705
 706
 707
 708
 709
 710
 711
 712
 713
 714
 715
 716
 717
 718
 719
 720
 721
 722
 723
 724
 725
 726
 727
 728
 729
 730
 731
 732
 733
 734
 735
 736
 737
 738
 739
 740
 741
 742
 743
 744
 745
 746
 747
 748
 749
 750
 751
 752
 753
 754
 755
 756
 757
 758
 759
 760
 761
 762
 763
 764
 765
 766
 767
 768
 769
 770
 771
 772
 773
 774
 775
 776
 777
 778
 779
 780
 781
 782
 783
 784
 785
 786
 787
 788
 789
 790
 791
 792
 793
 794
 795
 796
 797
 798
 799
 800
 801
 802
 803
 804
 805
 806
 807
 808
 809
 810
 811
 812
 813
 814
 815
 816
 817
 818
 819
 820
 821
 822
 823
 824
 825
 826
 827
 828
 829
 830
 831
 832
 833
 834
 835
 836
 837
 838
 839
 840
 841
 842
 843
 844
 845
 846
 847
 848
 849
 850
 851
 852
 853
 854
 855
 856
 857
 858
 859
 860
 861
 862
 863
 864
 865
 866
 867
 868
 869
 870
 871
 872
 873
 874
 875
 876
 877
 878
 879
 880
 881
 882
 883
 884
 885
 886
 887
 888
 889
 890
 891
 892
 893
 894
 895
 896
 897
 898
 899
 900
 901
 902
 903
 904
 905
 906
 907
 908
 909
 910
 911
 912
 913
 914
 915
 916
 917
 918
 919
 920
 921
 922
 923
 924
 925
 926
 927
 928
 929
 930
 931
 932
 933
 934
 935
 936
 937
 938
 939
 940
 941
 942
 943
 944
 945
 946
 947
 948
 949
 950
 951
 952
 953
 954
 955
 956
 957
 958
 959
 960
 961
 962
 963
 964
 965
 966
 967
 968
 969
 970
 971
 972
 973
 974
 975
 976
 977
 978
 979
 980
 981
 982
 983
 984
 985
 986
 987
 988
 989
 990
 991
 992
 993
 994
 995
 996
 997
 998
 999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136

    -- DWD层
    -- Data Warehouse Detail
        -- detail：详细、明细
        -- 对ODS层的数据进行加工，为统计分析做准备
        -- DIM层主要功能其实是分析数据（面向状态）
        -- DWD层主要功能其实是统计分析（面向行为）
        -- DWD层的表中主要保存的就是业务行为数据，表的设计需要遵循建模理论 - 维度建模 - 事实（行为）表
        -- 数据存储格式：列式存储
        -- 数据压缩格式：snappy
        -- 业务过程其实指代的就是业务行为
    -- 命名规范
        -- 分层标记（dwd_） + 数据域（分类） + 原子行为（login_success） + 全量 / 增量
            -- 原则上来讲，所有的行为都应该是增量数据
                -- 特殊情况下，会采用全量方式实现行为统计
    -- 事实表
        -- 包含维度
            -- 维度越多，行为越详细；维度越少，行为越简单
        -- 包含度量值
            -- 所有的行为必须可以用于统计，这里用于统计的值（字段）就是度量值
    -- 事实表分类
        -- 事务事实表
            -- 绝大多数的事实表都是事务型事实表
                -- 事务：原子性
                -- 原子操作（行为）
                    -- login success (table)
                    -- login fail (table)
            -- 粒度：行为描述的详细程度，称之为粒度
                -- 描述的越详细，粒度越细，称之为细粒度
                    -- 维度越多，粒度越细
                -- 描述的越简单，粒度越粗，称之为粗粒度
                    -- 维度越少，粒度越粗
            -- 建立表的时候，尽可能让粒度越细
            -- 创建表的步骤：
                -- 选择业务过程：确定创建什么表
                -- 声明粒度：确定行
                -- 确认维度：确定列
                -- 确认事实：确定度量值
        -- 周期快照事实表
        -- 累计快照事实表

-- 交易域加购事务事实表
    -- 交易域
    -- 加购
        -- 购物车中没有这个商品，新增商品
        -- 购物车中有这个商品，增加购买商品的数量
    -- 事务事实表：至少将行为描述清楚
        -- 表：dwd_trade_add_cart_inc
        -- 行：用户 + 时间 + 商品 + 商品数量
        -- 列：user + date + sku + num
        -- 度量值：num + 次数

    -- 建表语句
DROP TABLE IF EXISTS dwd_trade_cart_add_inc;
CREATE EXTERNAL TABLE dwd_trade_cart_add_inc
(
    `id`                  STRING COMMENT '编号',
    `user_id`            STRING COMMENT '用户ID',
    `sku_id`             STRING COMMENT 'SKU_ID',
    `date_id`            STRING COMMENT '日期ID',  -- yyyy-MM-dd 关联 dim_date
    `create_time`        STRING COMMENT '加购时间', -- yyyy-MM-dd hh:mm:ss
    `sku_num`            BIGINT COMMENT '加购物车件数'
) COMMENT '交易域加购事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_cart_add_inc/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 数据装载
    -- load
        -- 获取加购行为的数据
            -- maxwell：增量数据（insert, update）
    -- save
-- 从ods层的增量表中获取数据，能想到什么？
    -- 数据格式：JSON
    -- 首日：全量
        -- 历史数据（4、5、6、7、8）
        -- 无法判断行为
            -- 折中地认为当前的数据全部都是新增购物
    -- 每日：增量
        -- 当天新增及变化
    -- 分区策略
        -- 哪一天的行为数据就存放到哪一天的分区中
insert overwrite table dwd_trade_cart_add_inc partition (dt)
select
    data.`id`                 ,--STRING COMMENT '编号',
    data.`user_id`            ,--STRING COMMENT '用户ID',
    data.`sku_id`             ,--STRING COMMENT 'SKU_ID',
    date_format(data.`create_time`, 'yyyy-MM-dd') `date_id`            ,--STRING COMMENT '日期ID',  -- yyyy-MM-dd 关联 dim_date
    data.`create_time`        ,--STRING COMMENT '加购时间', -- yyyy-MM-dd HH:mm:ss
    data.`sku_num`            ,--BIGINT COMMENT '加购物车件数'
    date_format(data.`create_time`, 'yyyy-MM-dd')
from ods_cart_info_inc
where dt = '2022-06-08'
and type = 'bootstrap-insert';

-- 每日数据装载：不包含历史数据，主要当天新增及变化的数据
    -- 首日：2022-06-08
    -- 每日：2022-06-09，10，11 ……
    -- maxwell：{update, old : Map}
insert overwrite table dwd_trade_cart_add_inc partition (dt='2022-06-09')
select
    data.id,
    data.user_id,
    data.sku_id,
    date_format(if( type = 'insert', data.create_time, data.operate_time), 'yyyy-MM-dd'),
    if( type = 'insert', data.create_time, data.operate_time),
    if( type = 'insert', data.sku_num, data.sku_num - old['sku_num']) -- 自动转换
from ods_cart_info_inc
where dt = '2022-06-09'
and (
    type = 'insert'
    or
    (
        type = 'update'
            and
        array_contains(map_keys(old), 'sku_num')
            and
        data.sku_num > cast(old['sku_num'] as bigint)
    )
);

-- long -> string(date) -> date_format(string, date)
insert overwrite table dwd_trade_cart_add_inc partition (dt='2022-06-09')
select
    data.id,
    data.user_id,
    data.sku_id,
    date_format(ts, 'yyyy-MM-dd'),
    ts,
    if( type = 'insert', data.sku_num, data.sku_num - old['sku_num']) -- 自动转换
from ods_cart_info_inc
where dt = '2022-06-09'
and (
    type = 'insert'
    or
    (
        type = 'update'
            and
        array_contains(map_keys(old), 'sku_num')
            and
        data.sku_num > cast(old['sku_num'] as bigint)
    )
);

-- from_unixtime：0时区的时间
select
    ts,
    from_unixtime(ts, 'yyyy-MM-dd HH:mm:ss'),
    date_format(from_utc_timestamp(ts * 1000, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss')
from ods_cart_info_inc
where dt = '2022-06-08'
and type = 'bootstrap-insert';

-- 交易域下单事务事实表
    -- 交易域
    -- 下单
        -- 用户 + 时间 + 商品 + 订单 + 商品数量 + 商品金额
    -- 事务事实表
    -- 建表语句
DROP TABLE IF EXISTS dwd_trade_order_detail_inc;
CREATE EXTERNAL TABLE dwd_trade_order_detail_inc
(
    `id`                     STRING COMMENT '编号',
    `order_id`              STRING COMMENT '订单ID',
    `user_id`               STRING COMMENT '用户ID',
    `sku_id`                STRING COMMENT '商品ID',
    `province_id`          STRING COMMENT '省份ID',
    `activity_id`          STRING COMMENT '参与活动ID',
    `activity_rule_id`    STRING COMMENT '参与活动规则ID',
    `coupon_id`             STRING COMMENT '使用优惠券ID',
    `date_id`               STRING COMMENT '下单日期ID',
    `create_time`           STRING COMMENT '下单时间',
    `sku_num`                BIGINT COMMENT '商品数量',
    `split_original_amount` DECIMAL(16, 2) COMMENT '原始价格',
    `split_activity_amount` DECIMAL(16, 2) COMMENT '活动优惠分摊',
    `split_coupon_amount`   DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
    `split_total_amount`    DECIMAL(16, 2) COMMENT '最终价格分摊'
) COMMENT '交易域下单事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_order_detail_inc/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 数据装载
    -- load
    -- save

-- 首日：历史数据
insert overwrite table dwd_trade_order_detail_inc partition (dt)
select
    od.`id`                  ,--STRING COMMENT '编号',
    `order_id`               ,--STRING COMMENT '订单ID',
    `user_id`                ,--STRING COMMENT '用户ID',
    `sku_id`                 ,--STRING COMMENT '商品ID',
    `province_id`            ,--STRING COMMENT '省份ID',
    `activity_id`            ,--STRING COMMENT '参与活动ID',
    `activity_rule_id`       ,--STRING COMMENT '参与活动规则ID',
    `coupon_id`              ,--STRING COMMENT '使用优惠券ID',
    `date_id`                ,--STRING COMMENT '下单日期ID',
    `create_time`            ,--STRING COMMENT '下单时间',
    `sku_num`                ,--BIGINT COMMENT '商品数量',
    `split_original_amount`  ,--DECIMAL(16, 2) COMMENT '原始价格',
    `split_activity_amount`  ,--DECIMAL(16, 2) COMMENT '活动优惠分摊',
    `split_coupon_amount`    ,--DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
    `split_total_amount`     ,--DECIMAL(16, 2) COMMENT '最终价格分摊'
    date_id
from (
    select
        data.`id`                    ,--STRING COMMENT '编号',
        data.`order_id`              ,--STRING COMMENT '订单ID',
        --data.`user_id`               ,--STRING COMMENT '用户ID',
        data.`sku_id`                ,--STRING COMMENT '商品ID',
        --data.`province_id`           ,--STRING COMMENT '省份ID',
        --data.`activity_id`           ,--STRING COMMENT '参与活动ID',
        --data.`activity_rule_id`      ,--STRING COMMENT '参与活动规则ID',
        --data.`coupon_id`             ,--STRING COMMENT '使用优惠券ID',
        --data.`date_id`               ,--STRING COMMENT '下单日期ID',
        --data.`create_time`           ,--STRING COMMENT '下单时间',
        data.`sku_num`               ,--BIGINT COMMENT '商品数量',
        data.`sku_num` * data.`order_price` `split_original_amount` ,--DECIMAL(16, 2) COMMENT '原始价格',
        nvl(data.`split_activity_amount`, 0) split_activity_amount ,--DECIMAL(16, 2) COMMENT '活动优惠分摊',
        nvl(data.`split_coupon_amount`, 0) split_coupon_amount ,--DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
        data.`split_total_amount`     --DECIMAL(16, 2) COMMENT '最终价格分摊'
    from ods_order_detail_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) od
left join (
    select
        data.id,
        data.user_id,
        data.province_id,
        date_format(data.create_time, 'yyyy-MM-dd') date_id,
        data.create_time
    from ods_order_info_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) oi on od.order_id = oi.id
left join (
    select
        data.order_detail_id,
        data.activity_id,
        data.activity_rule_id
    from ods_order_detail_activity_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) act on od.id = act.order_detail_id
left join (
    select
        data.order_detail_id,
        data.coupon_id
    from ods_order_detail_coupon_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) cp on od.id = cp.order_detail_id;


-- 每日：当天数据
insert overwrite table dwd_trade_order_detail_inc partition (dt='2022-06-09')
select
    od.`id`                  ,--STRING COMMENT '编号',
    `order_id`               ,--STRING COMMENT '订单ID',
    `user_id`                ,--STRING COMMENT '用户ID',
    `sku_id`                 ,--STRING COMMENT '商品ID',
    `province_id`            ,--STRING COMMENT '省份ID',
    `activity_id`            ,--STRING COMMENT '参与活动ID',
    `activity_rule_id`       ,--STRING COMMENT '参与活动规则ID',
    `coupon_id`              ,--STRING COMMENT '使用优惠券ID',
    `date_id`                ,--STRING COMMENT '下单日期ID',
    `create_time`            ,--STRING COMMENT '下单时间',
    `sku_num`                ,--BIGINT COMMENT '商品数量',
    `split_original_amount`  ,--DECIMAL(16, 2) COMMENT '原始价格',
    `split_activity_amount`  ,--DECIMAL(16, 2) COMMENT '活动优惠分摊',
    `split_coupon_amount`    ,--DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
    `split_total_amount`      --DECIMAL(16, 2) COMMENT '最终价格分摊'
from (
    select
        data.`id`                    ,--STRING COMMENT '编号',
        data.`order_id`              ,--STRING COMMENT '订单ID',
        --data.`user_id`               ,--STRING COMMENT '用户ID',
        data.`sku_id`                ,--STRING COMMENT '商品ID',
        --data.`province_id`           ,--STRING COMMENT '省份ID',
        --data.`activity_id`           ,--STRING COMMENT '参与活动ID',
        --data.`activity_rule_id`      ,--STRING COMMENT '参与活动规则ID',
        --data.`coupon_id`             ,--STRING COMMENT '使用优惠券ID',
        --data.`date_id`               ,--STRING COMMENT '下单日期ID',
        --data.`create_time`           ,--STRING COMMENT '下单时间',
        data.`sku_num`               ,--BIGINT COMMENT '商品数量',
        data.`sku_num` * data.`order_price` `split_original_amount` ,--DECIMAL(16, 2) COMMENT '原始价格',
        nvl(data.`split_activity_amount`, 0) split_activity_amount ,--DECIMAL(16, 2) COMMENT '活动优惠分摊',
        nvl(data.`split_coupon_amount`, 0) split_coupon_amount ,--DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
        data.`split_total_amount`     --DECIMAL(16, 2) COMMENT '最终价格分摊'
    from ods_order_detail_inc
    where dt = '2022-06-09'
    and type = 'insert' -- 只有insert
) od
left join (
    select
        data.id,
        data.user_id,
        data.province_id,
        date_format(data.create_time, 'yyyy-MM-dd') date_id,
        data.create_time
    from ods_order_info_inc
    where dt = '2022-06-09'
    and type = 'insert' -- 此处假设 update操作不会修改我们想要的字段
) oi on od.order_id = oi.id
left join (
    select
        data.order_detail_id,
        data.activity_id,
        data.activity_rule_id
    from ods_order_detail_activity_inc
    where dt = '2022-06-09'
    and type = 'insert' -- 只有insert
) act on od.id = act.order_detail_id
left join (
    select
        data.order_detail_id,
        data.coupon_id
    from ods_order_detail_coupon_inc
    where dt = '2022-06-09'
    and type = 'insert'
) cp on od.id = cp.order_detail_id;

-- 交易域支付成功事务事实表：最细粒度
    -- 交易域
    -- 支付成功
        -- 用户 + 时间 + 订单 + 商品 + 支付金额
    -- 事务事实表
    -- 建表语句
    -- 维度表是否应该创建
        -- 原则上来讲，每一个维度都应该创建一张表
            -- 但是如果维度比较简单，数据少，应用场景少，那么无需创建维度表，可以直接将维度声明在事实表，一般称之为'维度退化'
DROP TABLE IF EXISTS dwd_trade_pay_detail_suc_inc;
CREATE EXTERNAL TABLE dwd_trade_pay_detail_suc_inc
(
    `id`                      STRING COMMENT '编号',
    `order_id`               STRING COMMENT '订单ID',
    `user_id`                STRING COMMENT '用户ID',
    `sku_id`                 STRING COMMENT 'SKU_ID',
    `province_id`           STRING COMMENT '省份ID',
    `activity_id`           STRING COMMENT '参与活动ID',
    `activity_rule_id`     STRING COMMENT '参与活动规则ID',
    `coupon_id`              STRING COMMENT '使用优惠券ID',
    `payment_type_code`     STRING COMMENT '支付类型编码',
    `payment_type_name`     STRING COMMENT '支付类型名称',
    `date_id`                STRING COMMENT '支付日期ID',
    `callback_time`         STRING COMMENT '支付成功时间',
    `sku_num`                 BIGINT COMMENT '商品数量',
    `split_original_amount` DECIMAL(16, 2) COMMENT '应支付原始金额',
    `split_activity_amount` DECIMAL(16, 2) COMMENT '支付活动优惠分摊',
    `split_coupon_amount`   DECIMAL(16, 2) COMMENT '支付优惠券优惠分摊',
    `split_payment_amount`  DECIMAL(16, 2) COMMENT '支付金额' -- 支付金额与下单金额不是一回事
) COMMENT '交易域支付成功事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_pay_detail_suc_inc/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 首日数据装载
insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt)
select
    od.`id`                    ,--   STRING COMMENT '编号',
    od.`order_id`              ,--  STRING COMMENT '订单ID',
    `user_id`               ,--  STRING COMMENT '用户ID',
    `sku_id`                ,--  STRING COMMENT 'SKU_ID',
    `province_id`           ,-- STRING COMMENT '省份ID',
    `activity_id`           ,-- STRING COMMENT '参与活动ID',
    `activity_rule_id`      ,--STRING COMMENT '参与活动规则ID',
    `coupon_id`             ,--  STRING COMMENT '使用优惠券ID',
    `payment_type_code`     ,-- STRING COMMENT '支付类型编码',
    `payment_type_name`     ,-- STRING COMMENT '支付类型名称',
    `date_id`               ,--  STRING COMMENT '支付日期ID',
    `callback_time`         ,-- STRING COMMENT '支付成功时间',
    `sku_num`               ,--   BIGINT COMMENT '商品数量',
    `split_original_amount` ,-- DECIMAL(16, 2) COMMENT '应支付原始金额',
    `split_activity_amount` ,-- DECIMAL(16, 2) COMMENT '支付活动优惠分摊',
    `split_coupon_amount`   ,-- DECIMAL(16, 2) COMMENT '支付优惠券优惠分摊',
    `split_payment_amount`  ,-- DECIMAL(16, 2) COMMENT '支付金额'
    date_id
from (
    select
        data.`id`                    ,--   STRING COMMENT '编号',
        data.`order_id`              ,--  STRING COMMENT '订单ID',
        --data.`user_id`               ,--  STRING COMMENT '用户ID',
        data.`sku_id`                ,--  STRING COMMENT 'SKU_ID',
        --data.`province_id`           ,-- STRING COMMENT '省份ID',
        --data.`activity_id`           ,-- STRING COMMENT '参与活动ID',
        --data.`activity_rule_id`      ,--STRING COMMENT '参与活动规则ID',
        --data.`coupon_id`             ,--  STRING COMMENT '使用优惠券ID',
        --data.`payment_type_code`     ,-- STRING COMMENT '支付类型编码',
        --data.`payment_type_name`     ,-- STRING COMMENT '支付类型名称',
        --data.`date_id`               ,--  STRING COMMENT '支付日期ID',
        --data.`callback_time`         ,-- STRING COMMENT '支付成功时间',
        data.`sku_num`               ,--   BIGINT COMMENT '商品数量',
        data.sku_num * data.order_price `split_original_amount` ,-- DECIMAL(16, 2) COMMENT '应支付原始金额',
        data.`split_activity_amount` ,-- DECIMAL(16, 2) COMMENT '支付活动优惠分摊',
        data.`split_coupon_amount`    -- DECIMAL(16, 2) COMMENT '支付优惠券优惠分摊',
        --data.`split_payment_amount`   -- DECIMAL(16, 2) COMMENT '支付金额'
    from ods_order_detail_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) od
join (  --  join !!! 有订单详情但不一定有支付信息，没有支付信息的不要
    select
        data.order_id,
        data.payment_type payment_type_code,
        date_format(data.callback_time, 'yyyy-MM-dd') date_id,
        data.callback_time,
        data.total_amount split_payment_amount
    from ods_payment_info_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
    and data.payment_status = '1602'
) pay on od.order_id = pay.order_id
left join (
    select
        data.id,
        data.user_id,
        data.province_id
    from ods_order_info_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) oi on od.order_id = oi.id
left join (
    select
        data.order_detail_id,
        data.activity_id,
        data.activity_rule_id
    from ods_order_detail_activity_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) act on od.id = act.order_detail_id
left join (
    select
        data.order_detail_id,
        data.coupon_id
    from ods_order_detail_coupon_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) cp on od.id = cp.order_detail_id
left join (
    select
        dic_code,
        dic_name payment_type_name
    from ods_base_dic_full
    where dt = '2022-06-08'
    and parent_code = '11'
) dic on pay.payment_type_code = dic.dic_code;


-- 每日数据装载
insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt='2022-06-09')
select
    od.`id`                    ,--   STRING COMMENT '编号',
    od.`order_id`              ,--  STRING COMMENT '订单ID',
    `user_id`               ,--  STRING COMMENT '用户ID',
    `sku_id`                ,--  STRING COMMENT 'SKU_ID',
    `province_id`           ,-- STRING COMMENT '省份ID',
    `activity_id`           ,-- STRING COMMENT '参与活动ID',
    `activity_rule_id`      ,--STRING COMMENT '参与活动规则ID',
    `coupon_id`             ,--  STRING COMMENT '使用优惠券ID',
    `payment_type_code`     ,-- STRING COMMENT '支付类型编码',
    `payment_type_name`     ,-- STRING COMMENT '支付类型名称',
    `date_id`               ,--  STRING COMMENT '支付日期ID',
    `callback_time`         ,-- STRING COMMENT '支付成功时间',
    `sku_num`               ,--   BIGINT COMMENT '商品数量',
    `split_original_amount` ,-- DECIMAL(16, 2) COMMENT '应支付原始金额',
    `split_activity_amount` ,-- DECIMAL(16, 2) COMMENT '支付活动优惠分摊',
    `split_coupon_amount`   ,-- DECIMAL(16, 2) COMMENT '支付优惠券优惠分摊',
    `split_payment_amount`   -- DECIMAL(16, 2) COMMENT '支付金额'
from (
    select
        data.`id`                    ,--   STRING COMMENT '编号',
        data.`order_id`              ,--  STRING COMMENT '订单ID',
        --data.`user_id`               ,--  STRING COMMENT '用户ID',
        data.`sku_id`                ,--  STRING COMMENT 'SKU_ID',
        --data.`province_id`           ,-- STRING COMMENT '省份ID',
        --data.`activity_id`           ,-- STRING COMMENT '参与活动ID',
        --data.`activity_rule_id`      ,--STRING COMMENT '参与活动规则ID',
        --data.`coupon_id`             ,--  STRING COMMENT '使用优惠券ID',
        --data.`payment_type_code`     ,-- STRING COMMENT '支付类型编码',
        --data.`payment_type_name`     ,-- STRING COMMENT '支付类型名称',
        --data.`date_id`               ,--  STRING COMMENT '支付日期ID',
        --data.`callback_time`         ,-- STRING COMMENT '支付成功时间',
        data.`sku_num`               ,--   BIGINT COMMENT '商品数量',
        data.sku_num * data.order_price `split_original_amount` ,-- DECIMAL(16, 2) COMMENT '应支付原始金额',
        data.`split_activity_amount` ,-- DECIMAL(16, 2) COMMENT '支付活动优惠分摊',
        data.`split_coupon_amount`    -- DECIMAL(16, 2) COMMENT '支付优惠券优惠分摊',
        --data.`split_payment_amount`   -- DECIMAL(16, 2) COMMENT '支付金额'
    from ods_order_detail_inc
    where (dt = '2022-06-09' or dt = date_sub('2022-06-09', 1))
    and (type = 'insert' or type = 'bootstrap-insert')
) od
join (  --  join !!! 有订单详情但不一定有支付信息，没有支付信息的不要
    select
        data.order_id,
        data.payment_type payment_type_code,
        date_format(data.callback_time, 'yyyy-MM-dd') date_id,
        data.callback_time,
        data.total_amount split_payment_amount
    from ods_payment_info_inc
    where dt = '2022-06-09'
    and type = 'update'
    and array_contains(map_keys(old), 'payment_status')
    and data.payment_status = '1602'
) pay on od.order_id = pay.order_id
left join (
    select
        data.id,
        data.user_id,
        data.province_id
    from ods_order_info_inc
    where (dt = '2022-06-09' or dt = date_sub('2022-06-09', 1))
    and (type = 'insert' or type = 'bootstrap-insert')
) oi on od.order_id = oi.id
left join (
    select
        data.order_detail_id,
        data.activity_id,
        data.activity_rule_id
    from ods_order_detail_activity_inc
    where (dt = '2022-06-09' or dt = date_sub('2022-06-09', 1))
    and (type = 'insert' or type = 'bootstrap-insert')
) act on od.id = act.order_detail_id
left join (
    select
        data.order_detail_id,
        data.coupon_id
    from ods_order_detail_coupon_inc
    where (dt = '2022-06-09' or dt = date_sub('2022-06-09', 1))
    and (type = 'insert' or type = 'bootstrap-insert')
) cp on od.id = cp.order_detail_id
left join (
    select
        dic_code,
        dic_name payment_type_name
    from ods_base_dic_full
    where dt = '2022-06-09'
    and parent_code = '11'
) dic on pay.payment_type_code = dic.dic_code;

-- 交易域购物车周期快照事实表
    -- 交易域
    -- 购物车
    -- 周期快照事实表
        -- 特殊的需求：各品类商品购物车存量(Top3)
            -- zhangsan： 鞋  生活用品  5
            -- zhangsan：手机 电子产品  1
            -- lisi：    衣服 生活用品  1
        ---------------------------------
            -- 生活用品[6]
            -- 电子产品[1]
        -- 实现思路：行为数据（事实表） + 状态数据（维度表）
            -- dwd_trade_cart_add_inc(加购) + dim_sku_full
            -- dwd_trade_cart_sub_inc(减购) + dim_sku_full
                -- 1001 [1, 2, 5, 6]
                -- 1001 [3, 2, 1]
                -- [join] 1001 add(14) sub(6) => [1001 storage(8)]
        -- 特殊的需求不需要特殊计算，因为效率太低，那么可以直接从业务数据中获取特殊字段，比如存量字段（库存、余额……）
            -- 特殊字段不需要进行多张表的关联计算，直接从业务数据库中周期性地获取即可
    -- 建表语句
DROP TABLE IF EXISTS dwd_trade_cart_full;
CREATE EXTERNAL TABLE dwd_trade_cart_full
(
    `id`         STRING COMMENT '编号',
    `user_id`   STRING COMMENT '用户ID',
    `sku_id`    STRING COMMENT 'SKU_ID',
    `sku_name`  STRING COMMENT '商品名称',
    `sku_num`   BIGINT COMMENT '现存商品件数'
) COMMENT '交易域购物车周期快照事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_cart_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 度量值的类型
    -- 可加事实：度量值可加
    -- 半可加事实：度量值在某些场景中可加，在某些场景不可加
    -- 不可加事实：比率型

-- 数据装载(full - tsv)
insert overwrite table dwd_trade_cart_full partition (dt = '2022-06-08')
select
    `id`        ,--STRING COMMENT '编号',
    `user_id`   ,--STRING COMMENT '用户ID',
    `sku_id`    ,--STRING COMMENT 'SKU_ID',
    `sku_name`  ,--STRING COMMENT '商品名称',
    `sku_num`    --BIGINT COMMENT '现存商品件数'
from ods_cart_info_full
where dt = '2022-06-08'
and is_ordered = '0';

-- 交易域交易流程累积快照事实表
    -- 交易域
    -- 交易流程
    -- 累积快照事实表
        -- 特殊的需求：下单到支付时间间隔平均值
        -- 将一个流程中的多个行为的状态数据累积到一张表中
    -- 建表语句
        -- 行为和行为之间的关系，粒度不是越细越好
DROP TABLE IF EXISTS dwd_trade_trade_flow_acc;
CREATE EXTERNAL TABLE dwd_trade_trade_flow_acc
(
    `order_id`               STRING COMMENT '订单ID',
    `user_id`                STRING COMMENT '用户ID',
    `province_id`           STRING COMMENT '省份ID',
    `order_date_id`         STRING COMMENT '下单日期ID',
    `order_time`             STRING COMMENT '下单时间',
    `payment_date_id`        STRING COMMENT '支付日期ID',
    `payment_time`           STRING COMMENT '支付时间',
    `finish_date_id`         STRING COMMENT '确认收货日期ID',
    `finish_time`             STRING COMMENT '确认收货时间',
    `order_original_amount` DECIMAL(16, 2) COMMENT '下单原始价格',
    `order_activity_amount` DECIMAL(16, 2) COMMENT '下单活动优惠分摊',
    `order_coupon_amount`   DECIMAL(16, 2) COMMENT '下单优惠券优惠分摊',
    `order_total_amount`    DECIMAL(16, 2) COMMENT '下单最终价格分摊',
    `payment_amount`         DECIMAL(16, 2) COMMENT '支付金额'
) COMMENT '交易域交易流程累积快照事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_trade_flow_acc/'
TBLPROPERTIES ('orc.compress' = 'snappy');

-- 加载数据
-- 首日
    -- 保存数据时，分区策略
        -- 哪天的数据存放到哪天的分区
            -- 使用下单日期 order_date_id 作为分区
    -- 如果表中存在多个和业务相关的时间字段，一般会选择其中的一个业务时间作为分区字段
        -- 一般选择时间靠后的字段作为分区字段
            -- DIM_USER_ZIP
        -- 存在一个问题，最后一个时间字段可能没值，一般不会采用null来表示分区，会采用时间极大值 9999-12-31
insert overwrite table dwd_trade_trade_flow_acc partition (dt)
select
    oi.id `order_id`        ,--  STRING COMMENT '订单ID',
    `user_id`               ,--  STRING COMMENT '用户ID',
    `province_id`           ,-- STRING COMMENT '省份ID',
    `order_date_id`         ,-- STRING COMMENT '下单日期ID',
    `order_time`            ,--  STRING COMMENT '下单时间',
    `payment_date_id`       ,--  STRING COMMENT '支付日期ID',
    `payment_time`          ,--  STRING COMMENT '支付时间',
    `finish_date_id`        ,--  STRING COMMENT '确认收货日期ID',
    `finish_time`           ,--   STRING COMMENT '确认收货时间',
    `order_original_amount` ,-- DECIMAL(16, 2) COMMENT '下单原始价格',
    `order_activity_amount` ,-- DECIMAL(16, 2) COMMENT '下单活动优惠分摊',
    `order_coupon_amount`   ,-- DECIMAL(16, 2) COMMENT '下单优惠券优惠分摊',
    `order_total_amount`    ,-- DECIMAL(16, 2) COMMENT '下单最终价格分摊',
    `payment_amount`        ,--  DECIMAL(16, 2) COMMENT '支付金额'
    nvl(finish_date_id, '9999-12-31')
from (
    select
        data.id,
        data.user_id,
        data.province_id,
        date_format(data.create_time, 'yyyy-MM-dd') order_date_id,
        data.create_time order_time,
        data.original_total_amount order_original_amount,
        data.activity_reduce_amount order_activity_amount,
        data.coupon_reduce_amount order_coupon_amount,
        data.total_amount order_total_amount
    from ods_order_info_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) oi
left join (
    select
        data.order_id,
        date_format(data.callback_time, 'yyyy-MM-dd') payment_date_id,
        data.callback_time payment_time,
        data.total_amount payment_amount
    from ods_payment_info_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
    and data.payment_status = '1602'
) pay on oi.id = pay.order_id
left join (
    -- 订单状态流水表
    select
        data.order_id,
        date_format(data.create_time, 'yyyy-MM-dd') finish_date_id,
        data.create_time finish_time
    from ods_order_status_log_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
    and data.order_status = '1004'
) log on oi.id = log.order_id;

-- 分区策略
    -- 查询的数据如何存储到表的分区中
        -- 查询数据的效率，数据有效

    -- ODS层
        -- 一天采集到的数据就存储到表的一天的分区

    -- DIM层
        -- 全量：每天一份全量数据
        -- 拉链：会采用结束时间作为分区字段（null -> 9999-12-31）

    -- DWD层
        -- 事务型事实表：一天的业务行为数据存到一天的分区中
        -- 周期型快照事实表：将每一天的全部状态数据保存到这一天的分区中（行为 -> 状态）
        -- 累计型快照事实表：将业务流程中最后的时间字段作为分区字段

-- 每日
    -- [insert order] * [update payment] * [update finish]
        -- union all
    -- [old order] * [update payment] * [update finish]
    -- (insert order union all old order) * payment * finish

insert overwrite table dwd_trade_trade_flow_acc partition (dt)
select
    oi.`order_id`              ,-- STRING COMMENT '订单ID',
    `user_id`               ,-- STRING COMMENT '用户ID',
    `province_id`           ,--STRING COMMENT '省份ID',
    `order_date_id`         ,--STRING COMMENT '下单日期ID',
    `order_time`            ,-- STRING COMMENT '下单时间',
    if(pay.`payment_time` is not null, pay.`payment_date_id`, oi.`payment_date_id`)      ,--  STRING COMMENT '支付日期ID',
    if(pay.`payment_time` is not null, pay.`payment_time`, oi.`payment_time`)         ,--  STRING COMMENT '支付时间',
    if(log.finish_time is not null, log.finish_date_id, null)        ,-- STRING COMMENT '确认收货日期ID',
    if(log.finish_time is not null, log.finish_time, null)           ,--  STRING COMMENT '确认收货时间',
    `order_original_amount` ,--DECIMAL(16, 2) COMMENT '下单原始价格',
    `order_activity_amount` ,--DECIMAL(16, 2) COMMENT '下单活动优惠分摊',
    `order_coupon_amount`   ,--DECIMAL(16, 2) COMMENT '下单优惠券优惠分摊',
    `order_total_amount`    ,--DECIMAL(16, 2) COMMENT '下单最终价格分摊',
    if(pay.`payment_time` is not null, pay.`payment_amount`, oi.payment_amount)         ,-- DECIMAL(16, 2) COMMENT '支付金额'
    if(log.finish_time is not null, log.finish_date_id, '9999-12-31')
from (
    select
        `order_id`             ,--  STRING COMMENT '订单ID',
        `user_id`              ,--  STRING COMMENT '用户ID',
        `province_id`          ,-- STRING COMMENT '省份ID',
        `order_date_id`        ,-- STRING COMMENT '下单日期ID',
        `order_time`           ,--  STRING COMMENT '下单时间',
        `payment_date_id`      ,-- STRING COMMENT '支付日期ID',
        `payment_time`         ,-- STRING COMMENT '支付时间',
        `finish_date_id`       ,--  STRING COMMENT '确认收货日期ID',
        `finish_time`          ,--   STRING COMMENT '确认收货时间',
        `order_original_amount`,-- DECIMAL(16, 2) COMMENT '下单原始价格',
        `order_activity_amount`,-- DECIMAL(16, 2) COMMENT '下单活动优惠分摊',
        `order_coupon_amount`  ,-- DECIMAL(16, 2) COMMENT '下单优惠券优惠分摊',
        `order_total_amount`   ,-- DECIMAL(16, 2) COMMENT '下单最终价格分摊',
        `payment_amount`        --  DECIMAL(16, 2) COMMENT '支付金额'
    from dwd_trade_trade_flow_acc
    where dt = '9999-12-31'
    union all
    select
        data.id,
        data.user_id,
        data.province_id,
        date_format(data.create_time, 'yyyy-MM-dd') order_date_id,
        data.create_time order_time,
        null,
        null,
        null,
        null,
        data.original_total_amount order_original_amount,
        data.activity_reduce_amount order_activity_amount,
        data.coupon_reduce_amount order_coupon_amount,
        data.total_amount order_total_amount,
        null
    from ods_order_info_inc
    where dt = '2022-06-09'
    and type = 'insert'
) oi
left join (
    select
        data.order_id,
        date_format(data.callback_time, 'yyyy-MM-dd') payment_date_id,
        data.callback_time payment_time,
        data.total_amount payment_amount
    from ods_payment_info_inc
    where dt = '2022-06-09'
    and type = 'update'
    and array_contains(map_keys(old), 'payment')
) pay on oi.order_id = pay.order_id
left join (
    select
        data.order_id,
        date_format(data.create_time, 'yyyy-MM-dd') finish_date_id,
        data.create_time finish_time
    from ods_order_status_log_inc
    where dt = '2022-06-09'
    and type = 'insert'
    and data.order_status = '1004'
) log;


-- 工具域优惠券使用(支付)事务事实表
    -- 工具域
    -- 优惠券使用(支付)
        -- 用户 + 时间 + 订单 + 优惠券 + （次数）
    -- 事务事实表
DROP TABLE IF EXISTS dwd_tool_coupon_used_inc;
CREATE EXTERNAL TABLE dwd_tool_coupon_used_inc
(
    `id`           STRING COMMENT '编号',
    `coupon_id`    STRING COMMENT '优惠券ID',
    `user_id`      STRING COMMENT '用户ID',
    `order_id`     STRING COMMENT '订单ID',
    `date_id`      STRING COMMENT '日期ID',
    `payment_time` STRING COMMENT '使用(支付)时间'
) COMMENT '优惠券使用（支付）事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_tool_coupon_used_inc/'
    TBLPROPERTIES ("orc.compress" = "snappy");

-- 数据装载
-- 首日
insert overwrite table dwd_tool_coupon_used_inc partition (dt)
select
    data.`id`           ,--STRING COMMENT '编号',
    data.`coupon_id`    ,--STRING COMMENT '优惠券ID',
    data.`user_id`      ,--STRING COMMENT '用户ID',
    data.`order_id`     ,--STRING COMMENT '订单ID',
    date_format(data.used_time, 'yyyy-MM-dd') `date_id`      ,--STRING COMMENT '日期ID',
    data.used_time `payment_time` ,--STRING COMMENT '使用(支付)时间'
    date_format(data.used_time, 'yyyy-MM-dd')
from ods_coupon_use_inc
where dt = '2022-06-08'
and type = 'bootstrap-insert'
and data.used_time is not null;

-- 每日
insert overwrite table dwd_tool_coupon_used_inc partition (dt = '2022-06-09')
select
    data.`id`           ,--STRING COMMENT '编号',
    data.`coupon_id`    ,--STRING COMMENT '优惠券ID',
    data.`user_id`      ,--STRING COMMENT '用户ID',
    data.`order_id`     ,--STRING COMMENT '订单ID',
    date_format(data.used_time, 'yyyy-MM-dd') `date_id`      ,--STRING COMMENT '日期ID',
    data.used_time `payment_time` --STRING COMMENT '使用(支付)时间'
from ods_coupon_use_inc
where dt = '2022-06-09'
and type = 'update'
-- and array_contains(map_keys(old), 'used_time') -- 同下
and data.used_time is not null;

-- 互动域收藏商品事务事实表
    -- 互动域
    -- 收藏商品
        -- 用户 + 时间 + 商品
    -- 事务事实表
    -- 建表语句
DROP TABLE IF EXISTS dwd_interaction_favor_add_inc;
CREATE EXTERNAL TABLE dwd_interaction_favor_add_inc
(
    `id`          STRING COMMENT '编号',
    `user_id`     STRING COMMENT '用户ID',
    `sku_id`      STRING COMMENT 'SKU_ID',
    `date_id`     STRING COMMENT '日期ID',
    `create_time` STRING COMMENT '收藏时间'
) COMMENT '互动域收藏商品事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_interaction_favor_add_inc/'
    TBLPROPERTIES ("orc.compress" = "snappy");

-- 数据装载
insert overwrite table dwd_interaction_favor_add_inc partition (dt)
select
    data.id,
    data.user_id,
    data.sku_id,
    date_format(data.create_time, 'yyyy-MM-dd') date_id,
    data.create_time,
    date_format(data.create_time, 'yyyy-MM-dd')
from ods_favor_info_inc
where dt = '2022-06-08'
and type = 'bootstrap-insert';
--and data.is_cancel = '0' -- 曾经收藏过也是收藏行为
-- id user_id ... is_cancel
-- 1   1001           0
-- 2   1002           1    -- 这条数据曾经被收藏过

insert overwrite table dwd_interaction_favor_add_inc partition (dt = '2022-06-09')
select
    data.id,
    data.user_id,
    data.sku_id,
    date_format(data.create_time, 'yyyy-MM-dd') date_id,
    data.create_time
from ods_favor_info_inc
where dt = '2022-06-09'
and type = 'insert';

-- 流量域页面浏览事务事实表
    -- 流量域
    -- 页面浏览
        -- 用户 + 时间 + 上一个页面 + 当前页面 + 停留时间
        -- 日志
    -- 事务事实表
    -- 建表语句
DROP TABLE IF EXISTS dwd_traffic_page_view_inc;
CREATE EXTERNAL TABLE dwd_traffic_page_view_inc
(
    `province_id`    STRING COMMENT '省份ID',
    `brand`           STRING COMMENT '手机品牌',
    `channel`         STRING COMMENT '渠道',
    `is_new`          STRING COMMENT '是否首次启动',
    `model`           STRING COMMENT '手机型号',
    `mid_id`          STRING COMMENT '设备ID', -- 用户
    `operate_system` STRING COMMENT '操作系统',
    `user_id`         STRING COMMENT '会员ID',  -- 登录后的ID
    `version_code`   STRING COMMENT 'APP版本号',
    `page_item`       STRING COMMENT '目标ID',
    `page_item_type` STRING COMMENT '目标类型',
    `last_page_id`    STRING COMMENT '上页ID',
    `page_id`          STRING COMMENT '页面ID ',
    `from_pos_id`     STRING COMMENT '点击坑位ID',
    `from_pos_seq`    STRING COMMENT '点击坑位位置',
    `refer_id`         STRING COMMENT '营销渠道ID',
    `date_id`          STRING COMMENT '日期ID',
    `view_time`       STRING COMMENT '跳入时间',
    `session_id`      STRING COMMENT '所属会话ID',
    `during_time`     BIGINT COMMENT '持续时间毫秒'
) COMMENT '流量域页面浏览事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_traffic_page_view_inc'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 数据装载
    -- 日志表的数据分为两大类
        -- 页面浏览日志
        -- APP启动日志
    -- Hive Bug
        -- 升级（扩展）
set hive.cbo.enable=false;
insert overwrite table dwd_traffic_page_view_inc partition (dt='2022-06-08')
select
    common.ar `province_id`     ,--STRING COMMENT '省份ID',
    common.ba `brand`           ,--STRING COMMENT '手机品牌',
    common.ch `channel`         ,--STRING COMMENT '渠道',
    common.`is_new`          ,--STRING COMMENT '是否首次启动',
    common.md `model`           ,--STRING COMMENT '手机型号',
    common.mid `mid_id`          ,--STRING COMMENT '设备ID', -- 用户
    common.os `operate_system`  ,--STRING COMMENT '操作系统',
    common.uid `user_id`         ,--STRING COMMENT '会员ID',  -- 登录后的ID
    common.vc `version_code`     ,--STRING COMMENT 'APP版本号',
    page.item `page_item`       ,--STRING COMMENT '目标ID',
    page.item_type `page_item_type`  ,--STRING COMMENT '目标类型',
    page.`last_page_id`    ,--STRING COMMENT '上页ID',
    page.`page_id`         ,--STRING COMMENT '页面ID ',
    page.`from_pos_id`     ,--STRING COMMENT '点击坑位ID',
    page.`from_pos_seq`    ,--STRING COMMENT '点击坑位位置',
    page.`refer_id`        ,--STRING COMMENT '营销渠道ID',
    date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd') `date_id`          ,--STRING COMMENT '日期ID',
    date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') `view_time`        ,--STRING COMMENT '跳入时间',
    common.sid `session_id`       ,--STRING COMMENT '所属会话ID',
    page.`during_time`       --BIGINT COMMENT '持续时间毫秒'
from ods_log_inc
where dt = '2022-06-08'
and page is not null;
set hive.cbo.enable=true;

-- 用户域用户注册事务事实表
    -- 用户域
    -- 用户注册成功
        -- 用户（会员）+ 时间
        -- user_info(insert)
    -- 事务事实表
    -- 建表语句
DROP TABLE IF EXISTS dwd_user_register_inc;
CREATE EXTERNAL TABLE dwd_user_register_inc
(
    `user_id`          STRING COMMENT '用户ID',
    `date_id`          STRING COMMENT '日期ID',
    `create_time`     STRING COMMENT '注册时间',
    `channel`          STRING COMMENT '应用下载渠道',
    `province_id`     STRING COMMENT '省份ID',
    `version_code`    STRING COMMENT '应用版本',
    `mid_id`           STRING COMMENT '设备ID',
    `brand`            STRING COMMENT '设备品牌',
    `model`            STRING COMMENT '设备型号',
    `operate_system` STRING COMMENT '设备操作系统'
) COMMENT '用户域用户注册事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_user_register_inc/'
    TBLPROPERTIES ("orc.compress" = "snappy");

-- 数据装载
    -- 页面浏览日志：离开页面的时候
-- insert overwrite table dwd_user_register_inc partition (dt='2022-06-08')
-- select
--     common.uid `user_id`         ,-- STRING COMMENT '用户ID',
--     date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd') `date_id`         ,-- STRING COMMENT '日期ID',
--     date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') `create_time`     ,--STRING COMMENT '注册时间',
--     common.ch `channel`         ,-- STRING COMMENT '应用下载渠道',
--     common.ar `province_id`     ,--STRING COMMENT '省份ID',
--     common.vc `version_code`    ,--STRING COMMENT '应用版本',
--     common.mid `mid_id`          ,-- STRING COMMENT '设备ID',
--     common.ba `brand`           ,-- STRING COMMENT '设备品牌',
--     common.md `model`           ,-- STRING COMMENT '设备型号',
--     common.os `operate_system`   --STRING COMMENT '设备操作系统'
-- from ods_log_inc
-- where dt = '2022-06-08'
-- and page.page_id = 'register'
-- and common.uid is not null;

-- 首日
insert overwrite table dwd_user_register_inc partition (dt)
select
    `user_id`        ,--  STRING COMMENT '用户ID',
    `date_id`        ,--  STRING COMMENT '日期ID',
    `create_time`    ,-- STRING COMMENT '注册时间',
    `channel`        ,--  STRING COMMENT '应用下载渠道',
    `province_id`    ,-- STRING COMMENT '省份ID',
    `version_code`   ,-- STRING COMMENT '应用版本',
    `mid_id`         ,--  STRING COMMENT '设备ID',
    `brand`          ,--  STRING COMMENT '设备品牌',
    `model`          ,--  STRING COMMENT '设备型号',
    `operate_system` ,--STRING COMMENT '设备操作系统'
    date_id
from (
    select
        data.id,
        date_format(data.create_time, 'yyyy-MM-dd') date_id,
        data.create_time
    from ods_user_info_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) ui
left join (
    select
        common.uid `user_id`         ,-- STRING COMMENT '用户ID',
        common.ch `channel`         ,-- STRING COMMENT '应用下载渠道',
        common.ar `province_id`     ,--STRING COMMENT '省份ID',
        common.vc `version_code`    ,--STRING COMMENT '应用版本',
        common.mid `mid_id`          ,-- STRING COMMENT '设备ID',
        common.ba `brand`           ,-- STRING COMMENT '设备品牌',
        common.md `model`           ,-- STRING COMMENT '设备型号',
        common.os `operate_system`   --STRING COMMENT '设备操作系统'
    from ods_log_inc
    where dt = '2022-06-08'
    and page.page_id = 'register'
    and common.uid is not null
) log on ui.id = log.user_id;

-- 每日
insert overwrite table dwd_user_register_inc partition (dt = '2022-06-09')
select
    `user_id`        ,--  STRING COMMENT '用户ID',
    `date_id`        ,--  STRING COMMENT '日期ID',
    `create_time`    ,-- STRING COMMENT '注册时间',
    `channel`        ,--  STRING COMMENT '应用下载渠道',
    `province_id`    ,-- STRING COMMENT '省份ID',
    `version_code`   ,-- STRING COMMENT '应用版本',
    `mid_id`         ,--  STRING COMMENT '设备ID',
    `brand`          ,--  STRING COMMENT '设备品牌',
    `model`          ,--  STRING COMMENT '设备型号',
    `operate_system`  --STRING COMMENT '设备操作系统'
from (
    select
        data.id,
        date_format(data.create_time, 'yyyy-MM-dd') date_id,
        data.create_time
    from ods_user_info_inc
    where dt = '2022-06-09'
    and type = 'insert'
) ui
left join (  -- 日志有丢失
    select
        common.uid `user_id`         ,-- STRING COMMENT '用户ID',
        common.ch `channel`         ,-- STRING COMMENT '应用下载渠道',
        common.ar `province_id`     ,--STRING COMMENT '省份ID',
        common.vc `version_code`    ,--STRING COMMENT '应用版本',
        common.mid `mid_id`          ,-- STRING COMMENT '设备ID',
        common.ba `brand`           ,-- STRING COMMENT '设备品牌',
        common.md `model`           ,-- STRING COMMENT '设备型号',
        common.os `operate_system`   --STRING COMMENT '设备操作系统'
    from ods_log_inc
    where dt = '2022-06-09'
    and page.page_id = 'register'
    and common.uid is not null
) log on ui.id = log.user_id;

-- 用户域用户登录事务事实表
    -- 用户域
    -- 用户登录成功
        -- 用户 + 时间 + （次数）
    -- 事务事实表
    -- 建表语句
DROP TABLE IF EXISTS dwd_user_login_inc;
CREATE EXTERNAL TABLE dwd_user_login_inc
(
    `user_id`         STRING COMMENT '用户ID',
    `date_id`         STRING COMMENT '日期ID',
    `login_time`     STRING COMMENT '登录时间',
    `channel`         STRING COMMENT '应用下载渠道',
    `province_id`    STRING COMMENT '省份ID',
    `version_code`   STRING COMMENT '应用版本',
    `mid_id`          STRING COMMENT '设备ID',
    `brand`           STRING COMMENT '设备品牌',
    `model`           STRING COMMENT '设备型号',
    `operate_system` STRING COMMENT '设备操作系统'
) COMMENT '用户域用户登录事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_user_login_inc/'
    TBLPROPERTIES ("orc.compress" = "snappy");

-- 数据装载
-- 会话内第一个uid不为null的页面就对应一次登录操作
insert overwrite table dwd_user_login_inc partition (dt = '2022-06-08')
select
    `user_id`         ,--STRING COMMENT '用户ID',
    date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd') `date_id`         ,--STRING COMMENT '日期ID',
    date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') `login_time`      ,--STRING COMMENT '登录时间',
    `channel`         ,--STRING COMMENT '应用下载渠道',
    `province_id`     ,--STRING COMMENT '省份ID',
    `version_code`    ,--STRING COMMENT '应用版本',
    `mid_id`          ,--STRING COMMENT '设备ID',
    `brand`           ,--STRING COMMENT '设备品牌',
    `model`           ,--STRING COMMENT '设备型号',
    `operate_system`   --STRING COMMENT '设备操作系统'
from (
    select
        ts,
        common.uid `user_id`         ,-- STRING COMMENT '用户ID',
        common.ch `channel`         ,-- STRING COMMENT '应用下载渠道',
        common.ar `province_id`     ,--STRING COMMENT '省份ID',
        common.vc `version_code`    ,--STRING COMMENT '应用版本',
        common.mid `mid_id`          ,-- STRING COMMENT '设备ID',
        common.ba `brand`           ,-- STRING COMMENT '设备品牌',
        common.md `model`           ,-- STRING COMMENT '设备型号',
        common.os `operate_system`  ,--STRING COMMENT '设备操作系统'
        row_number() over (partition by common.sid order by ts) rn  -- 开窗
    from ods_log_inc
    where dt = '2022-06-08'
    and page is not null
    and common.uid is not null
) t where rn = 1;

  • DWS层 

      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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994

    -- DWS
    -- Data Warehouse Summary
        -- Data Warehouse : 数据仓库
        -- Summary : 汇总（预聚合）
            -- 用于将DIM、DWD的数据进行提前统计，将统计结果保存到当前的表中（中间计算表）
            -- 当前的表不是最终的统计结果表
                -- 数据量可能比较多，表的设计中应该添加分区
                -- 当前表需要进一步聚合处理，所以表的设计中应该是列式存储，采用snappy压缩
            -- 表的分类：根据数据范围进行分类
                -- 1d:1天数据的统计
                    -- 数据来源为DIM、DWD
                -- nd:N天数据的统计（不包括 1）
                    -- 数据来源必须为1d表
                -- td:所有数据的统计
                    -- 数据来源可以为1d表
                    -- 数据来源可以为DIM、DWD
        -- 表的设计
            -- 参考ADS层表的设计
                -- 指标体系：
                    -- 原子指标（拆分指标）
                        -- 行为、统计字段、统计逻辑
                    -- 派生指标（增加条件）
                        -- 统计周期（数据范围）、业务限定（筛选条件）、统计粒度（分组维度）
                    -- 衍生指标（比率、比例）
            -- 表名
                -- 分层标记 (dws_) + 数据域 + 统计粒度 + 业务过程 + 统计周期（1d/nd/td）
                    -- 指标：客户想要的一个统计结果（数值）
                        -- 业务过程相同：数据来源相同
                        -- 统计周期相同：数据范围相同
                        -- 统计粒度相同：数据含义相同

-- 交易域用户商品粒度订单最近1日汇总表
    -- 交易域
    -- 用户商品粒度
        -- user + sku
    -- 订单
        -- 下单（行为）
    -- 最近1日汇总表
        -- 数据范围

-- 建表语句
DROP TABLE IF EXISTS dws_trade_user_sku_order_1d;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_1d
(
    `user_id`                   STRING COMMENT '用户ID',
    `sku_id`                    STRING COMMENT 'SKU_ID',
    `sku_name`                  STRING COMMENT 'SKU名称',
    `category1_id`              STRING COMMENT '一级品类ID',
    `category1_name`            STRING COMMENT '一级品类名称',
    `category2_id`              STRING COMMENT '二级品类ID',
    `category2_name`            STRING COMMENT '二级品类名称',
    `category3_id`              STRING COMMENT '三级品类ID',
    `category3_name`            STRING COMMENT '三级品类名称',
    `tm_id`                      STRING COMMENT '品牌ID',
    `tm_name`                    STRING COMMENT '品牌名称',
    `order_count_1d`            BIGINT COMMENT '最近1日下单次数',
    `order_num_1d`              BIGINT COMMENT '最近1日下单件数',
    `order_original_amount_1d`  DECIMAL(16, 2) COMMENT '最近1日下单原始金额',
    `activity_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日活动优惠金额',
    `coupon_reduce_amount_1d`   DECIMAL(16, 2) COMMENT '最近1日优惠券优惠金额',
    `order_total_amount_1d`     DECIMAL(16, 2) COMMENT '最近1日下单最终金额'
) COMMENT '交易域用户商品粒度订单最近1日汇总表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_trade_user_sku_order_1d'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 数据装载
    -- 1d表存储的数据为1天的行为数据的统计结果，存放到这一天的分区中
    -- dwd的数据其实是包含历史行为数据（7，6，5，4），历史行为也需要统计
    -- 1d表的数据装载分为首日装载和每日装载
        -- 首日装载：包含历史数据
        -- 每日装载：包含当天数据

-- 首日数据装载
insert overwrite table dws_trade_user_sku_order_1d partition (dt)
select
    `user_id`                   ,--STRING COMMENT '用户ID',
    `sku_id`                    ,--STRING COMMENT 'SKU_ID',
    `sku_name`                  ,--STRING COMMENT 'SKU名称',
    `category1_id`              ,--STRING COMMENT '一级品类ID',
    `category1_name`            ,--STRING COMMENT '一级品类名称',
    `category2_id`              ,--STRING COMMENT '二级品类ID',
    `category2_name`            ,--STRING COMMENT '二级品类名称',
    `category3_id`              ,--STRING COMMENT '三级品类ID',
    `category3_name`            ,--STRING COMMENT '三级品类名称',
    `tm_id`                     ,-- STRING COMMENT '品牌ID',
    `tm_name`                   ,-- STRING COMMENT '品牌名称',
    count(distinct order_id) `order_count_1d`            ,--BIGINT COMMENT '最近1日下单次数',
    sum(sku_num) `order_num_1d`              ,--BIGINT COMMENT '最近1日下单件数',
    sum(split_original_amount) `order_original_amount_1d`  ,--DECIMAL(16, 2) COMMENT '最近1日下单原始金额',
    sum(split_activity_amount) `activity_reduce_amount_1d` ,--DECIMAL(16, 2) COMMENT '最近1日活动优惠金额',
    sum(split_coupon_amount) `coupon_reduce_amount_1d`   ,--DECIMAL(16, 2) COMMENT '最近1日优惠券优惠金额',
    sum(split_total_amount) `order_total_amount_1d`      ,--DECIMAL(16, 2) COMMENT '最近1日下单最终金额'
    dt
from  (
    select
        `user_id`                   ,--STRING COMMENT '用户ID',
        `sku_id`                    ,--STRING COMMENT 'SKU_ID'
        `order_id`,
        `sku_num`,
        split_original_amount,
        split_activity_amount,
        split_coupon_amount,
        split_total_amount,
        dt
    from dwd_trade_order_detail_inc
) od
left join (
    select
        `id`,
        `sku_name`                  ,--STRING COMMENT 'SKU名称',
        `category1_id`              ,--STRING COMMENT '一级品类ID',
        `category1_name`            ,--STRING COMMENT '一级品类名称',
        `category2_id`              ,--STRING COMMENT '二级品类ID',
        `category2_name`            ,--STRING COMMENT '二级品类名称',
        `category3_id`              ,--STRING COMMENT '三级品类ID',
        `category3_name`            ,--STRING COMMENT '三级品类名称',
        `tm_id`                     ,-- STRING COMMENT '品牌ID',
        `tm_name`                    -- STRING COMMENT '品牌名称',
    from dim_sku_full
    where dt = '2022-06-08'
) sku on od.sku_id = sku.id
group by `user_id`,
         `sku_id`,
         `sku_name`,
         `category1_id`,
         `category1_name`,
         `category2_id`,
         `category2_name`,
         `category3_id`,
         `category3_name`,
         `tm_id`,
         `tm_name`,
         `dt`;

-- 每日数据装载
insert overwrite table dws_trade_user_sku_order_1d partition (dt = '2022-06-09')
select
    `user_id`                   ,--STRING COMMENT '用户ID',
    `sku_id`                    ,--STRING COMMENT 'SKU_ID',
    `sku_name`                  ,--STRING COMMENT 'SKU名称',
    `category1_id`              ,--STRING COMMENT '一级品类ID',
    `category1_name`            ,--STRING COMMENT '一级品类名称',
    `category2_id`              ,--STRING COMMENT '二级品类ID',
    `category2_name`            ,--STRING COMMENT '二级品类名称',
    `category3_id`              ,--STRING COMMENT '三级品类ID',
    `category3_name`            ,--STRING COMMENT '三级品类名称',
    `tm_id`                     ,-- STRING COMMENT '品牌ID',
    `tm_name`                   ,-- STRING COMMENT '品牌名称',
    count(distinct order_id) `order_count_1d`            ,--BIGINT COMMENT '最近1日下单次数',
    sum(sku_num) `order_num_1d`              ,--BIGINT COMMENT '最近1日下单件数',
    sum(split_original_amount) `order_original_amount_1d`  ,--DECIMAL(16, 2) COMMENT '最近1日下单原始金额',
    sum(split_activity_amount) `activity_reduce_amount_1d` ,--DECIMAL(16, 2) COMMENT '最近1日活动优惠金额',
    sum(split_coupon_amount) `coupon_reduce_amount_1d`   ,--DECIMAL(16, 2) COMMENT '最近1日优惠券优惠金额',
    sum(split_total_amount) `order_total_amount_1d`      --DECIMAL(16, 2) COMMENT '最近1日下单最终金额'
from  (
    select
        `user_id`                   ,--STRING COMMENT '用户ID',
        `sku_id`                    ,--STRING COMMENT 'SKU_ID'
        `order_id`,
        `sku_num`,
        split_original_amount,
        split_activity_amount,
        split_coupon_amount,
        split_total_amount
    from dwd_trade_order_detail_inc
    where dt = '2022-06-09'
) od
left join (
    select
        `id`,
        `sku_name`                  ,--STRING COMMENT 'SKU名称',
        `category1_id`              ,--STRING COMMENT '一级品类ID',
        `category1_name`            ,--STRING COMMENT '一级品类名称',
        `category2_id`              ,--STRING COMMENT '二级品类ID',
        `category2_name`            ,--STRING COMMENT '二级品类名称',
        `category3_id`              ,--STRING COMMENT '三级品类ID',
        `category3_name`            ,--STRING COMMENT '三级品类名称',
        `tm_id`                     ,-- STRING COMMENT '品牌ID',
        `tm_name`                    -- STRING COMMENT '品牌名称',
    from dim_sku_full
    where dt = '2022-06-09'
) sku on od.sku_id = sku.id
group by `user_id`,
         `sku_id`,
         `sku_name`,
         `category1_id`,
         `category1_name`,
         `category2_id`,
         `category2_name`,
         `category3_id`,
         `category3_name`,
         `tm_id`,
         `tm_name`;

-- 思路：补全数据 => 统计（效率低、逻辑简单：缺什么补什么）
    --  统计 => 补全数据（效率高，连接之前先减少数据）
            -- 统计结果和不全数据没有关系

-- 交易域用户商品粒度订单最近n日汇总表
    -- 交易域
    -- 用户商品粒度
        -- user + sku
    -- 订单
        -- 下单：order
    -- 最近n日汇总表
        -- nd
            -- 表设计：参考1d表
            -- 数据来源：1d表

-- 建表语句
DROP TABLE IF EXISTS dws_trade_user_sku_order_nd;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_nd
(
    `user_id`                   STRING COMMENT '用户ID',
    `sku_id`                    STRING COMMENT 'SKU_ID',
    `sku_name`                  STRING COMMENT 'SKU名称',
    `category1_id`              STRING COMMENT '一级品类ID',
    `category1_name`            STRING COMMENT '一级品类名称',
    `category2_id`              STRING COMMENT '二级品类ID',
    `category2_name`            STRING COMMENT '二级品类名称',
    `category3_id`              STRING COMMENT '三级品类ID',
    `category3_name`            STRING COMMENT '三级品类名称',
    `tm_id`                      STRING COMMENT '品牌ID',
    `tm_name`                    STRING COMMENT '品牌名称',
    `order_count_7d`            BIGINT COMMENT '最近1日下单次数',
    `order_num_7d`              BIGINT COMMENT '最近1日下单件数',
    `order_original_amount_7d`  DECIMAL(16, 2) COMMENT '最近1日下单原始金额',
    `activity_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近1日活动优惠金额',
    `coupon_reduce_amount_7d`   DECIMAL(16, 2) COMMENT '最近1日优惠券优惠金额',
    `order_total_amount_7d`     DECIMAL(16, 2) COMMENT '最近1日下单最终金额',
    `order_count_30d`            BIGINT COMMENT '最近1日下单次数',
    `order_num_30d`              BIGINT COMMENT '最近1日下单件数',
    `order_original_amount_30d`  DECIMAL(16, 2) COMMENT '最近1日下单原始金额',
    `activity_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近1日活动优惠金额',
    `coupon_reduce_amount_30d`   DECIMAL(16, 2) COMMENT '最近1日优惠券优惠金额',
    `order_total_amount_30d`     DECIMAL(16, 2) COMMENT '最近1日下单最终金额'
) COMMENT '交易域用户商品粒度订单最近n日汇总表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_trade_user_sku_order_nd'
    TBLPROPERTIES ('orc.compress' = 'snappy');


-- nd表的数据装载基本思路
    -- 1. 读取最大范围i的数据
        -- 30d
    -- 2. 同时计算不同时间范围的数据
        -- sum(if)：有条件求和
insert overwrite table dws_trade_user_sku_order_nd partition (dt = '2022-06-08')
select
    `user_id`                   ,--STRING COMMENT '用户ID',
    `sku_id`                    ,--STRING COMMENT 'SKU_ID',
    `sku_name`                  ,--STRING COMMENT 'SKU名称',
    `category1_id`              ,--STRING COMMENT '一级品类ID',
    `category1_name`            ,--STRING COMMENT '一级品类名称',
    `category2_id`              ,--STRING COMMENT '二级品类ID',
    `category2_name`            ,--STRING COMMENT '二级品类名称',
    `category3_id`              ,--STRING COMMENT '三级品类ID',
    `category3_name`            ,--STRING COMMENT '三级品类名称',
    `tm_id`                     ,-- STRING COMMENT '品牌ID',
    `tm_name`                   ,-- STRING COMMENT '品牌名称',

    sum(if (dt >= date_sub('2022-06-08', 6), order_count_1d, 0)),
    sum(if (dt >= date_sub('2022-06-08', 6), order_num_1d, 0)),
    sum(if (dt >= date_sub('2022-06-08', 6), order_original_amount_1d, 0)),
    sum(if (dt >= date_sub('2022-06-08', 6), activity_reduce_amount_1d, 0)),
    sum(if (dt >= date_sub('2022-06-08', 6), coupon_reduce_amount_1d, 0)),
    sum(if (dt >= date_sub('2022-06-08', 6), order_total_amount_1d, 0)),

    sum(order_count_1d),
    sum(order_num_1d),
    sum(order_original_amount_1d),
    sum(activity_reduce_amount_1d),
    sum(coupon_reduce_amount_1d),
    sum(order_total_amount_1d)
from dws_trade_user_sku_order_1d
where dt >= date_sub('2022-06-08', 29) and dt <= '2022-06-08'
group by `user_id`,
         `sku_id`,
         `sku_name`,
         `category1_id`,
         `category1_name`,
         `category2_id`,
         `category2_name`,
         `category3_id`,
         `category3_name`,
         `tm_id`, `tm_name`;

-- 1d：交易域用户粒度订单最近1日汇总表
    -- 交易域
    -- 用户粒度
        -- user
    -- 订单
        -- 下单：
    -- 最近1日汇总表
        -- 1d

-- 建表语句
DROP TABLE IF EXISTS dws_trade_user_order_1d;
CREATE EXTERNAL TABLE dws_trade_user_order_1d
(
    `user_id`                   STRING COMMENT '用户ID',
    `order_count_1d`            BIGINT COMMENT '最近1日下单次数',
    `order_num_1d`              BIGINT COMMENT '最近1日下单商品件数',
    `order_original_amount_1d`  DECIMAL(16, 2) COMMENT '最近1日下单原始金额',
    `activity_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单活动优惠金额',
    `coupon_reduce_amount_1d`   DECIMAL(16, 2) COMMENT '最近1日下单优惠券优惠金额',
    `order_total_amount_1d`     DECIMAL(16, 2) COMMENT '最近1日下单最终金额'
) COMMENT '交易域用户粒度订单最近1日汇总表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_trade_user_order_1d'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 首日数据装载
insert overwrite table dws_trade_user_order_1d partition (dt)
select
    user_id,
    count(distinct order_id),
    sum(sku_num),
    sum(split_original_amount),
    sum(split_activity_amount),
    sum(split_coupon_amount),
    sum(split_total_amount),
    dt
from dwd_trade_order_detail_inc
where dt = '2022-06-08'
group by user_id, dt;

-- 每日数据装载
insert overwrite table dws_trade_user_order_1d partition (dt = '2022-06-09')
select
    user_id,
    count(distinct order_id),
    sum(sku_num),
    sum(split_original_amount),
    sum(split_activity_amount),
    sum(split_coupon_amount),
    sum(split_total_amount)
from dwd_trade_order_detail_inc
where dt = '2022-06-09'
group by user_id;

-- 粒度的变化问题
    -- DWS层的表不是最终的表，还需要进一步计算
        -- dws：user + sku (tm)
        -- ads：tm

-- 三种情况
--------------------------------------------------------------------------
    -- dws：user + sku
    -- ads：sku
    -- 当前的统计粒度减少，并且就是对减少的那个粒度做统计，此时无需判重
    -- 当前的统计粒度减少，对其他字段进行进一步统计，需要聚合数据
---------------------------------------------------------------------------
    -- dws：user + sku
    -- ads：tm
    -- 当前的统计粒度减少，此时必须判重
    -- 当前的统计粒度减少，对其他字段进行进一步统计，需要聚合数据
---------------------------------------------------------------------------
    -- dws：user + sku
    -- ads：user + sku
    -- 粒度没有变化的情况下，可以直接将中间表的数据获取后使用

-- 交易域用户粒度订单历史至今汇总表
    -- 交易域
    -- 用户粒度
    -- 订单
        -- 下单
    -- 历史至今汇总表

-- 建表语句
DROP TABLE IF EXISTS dws_trade_user_order_td;
CREATE EXTERNAL TABLE dws_trade_user_order_td
(
    `user_id`                   STRING COMMENT '用户ID',
    `order_date_first`          STRING COMMENT '历史至今首次下单日期',
    `order_date_last`           STRING COMMENT '历史至今末次下单日期',
    `order_count_td`            BIGINT COMMENT '历史至今下单次数',
    `order_num_td`              BIGINT COMMENT '历史至今购买商品件数',
    `original_amount_td`        DECIMAL(16, 2) COMMENT '历史至今下单原始金额',
    `activity_reduce_amount_td` DECIMAL(16, 2) COMMENT '历史至今下单活动优惠金额',
    `coupon_reduce_amount_td`   DECIMAL(16, 2) COMMENT '历史至今下单优惠券优惠金额',
    `total_amount_td`           DECIMAL(16, 2) COMMENT '历史至今下单最终金额'
) COMMENT '交易域用户粒度订单历史至今汇总表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_trade_user_order_td'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- td表的数据装载一般为了考虑效率，会分成首日装载和每日装载
    -- 首日装载：直接获取所有的数据做聚合
    -- 每日装载：装载的数据只比前一天多了一天的数据，而前一天的数据已经统计过了，所以存在重复计算
        -- 改善装载的思路：获取昨天的统计结果 + 今天新的数据 => 做进一步的聚合
-- 首日数据装载
insert overwrite table dws_trade_user_order_td partition (dt = '2022-06-08')
select
    user_id,
    min(dt) order_date_first,
    max(dt) order_date_last,
    sum(order_count_1d),
    sum(order_num_1d),
    sum(order_original_amount_1d),
    sum(activity_reduce_amount_1d),
    sum(coupon_reduce_amount_1d),
    sum(order_total_amount_1d)
from dws_trade_user_order_1d
group by user_id;
-- 每日数据装载
insert overwrite table dws_trade_user_order_td partition (dt = '2022-06-09')
select
    user_id,
    min(order_date_first),
    max(order_date_last),
    sum(order_count_td),
    sum(order_num_td),
    sum(original_amount_td),
    sum(activity_reduce_amount_td),
    sum(coupon_reduce_amount_td),
    sum(total_amount_td)
from (
    select
        `user_id`                   ,--STRING COMMENT '用户ID',
        `order_date_first`          ,--STRING COMMENT '历史至今首次下单日期',
        `order_date_last`           ,--STRING COMMENT '历史至今末次下单日期',
        `order_count_td`            ,--BIGINT COMMENT '历史至今下单次数',
        `order_num_td`              ,--BIGINT COMMENT '历史至今购买商品件数',
        `original_amount_td`        ,--DECIMAL(16, 2) COMMENT '历史至今下单原始金额',
        `activity_reduce_amount_td` ,--DECIMAL(16, 2) COMMENT '历史至今下单活动优惠金额',
        `coupon_reduce_amount_td`   ,--DECIMAL(16, 2) COMMENT '历史至今下单优惠券优惠金额',
        `total_amount_td`            --DECIMAL(16, 2) COMMENT '历史至今下单最终金额'
    from dws_trade_user_order_td
    where dt = date_sub('2022-06-09', 1)
    union all
    select
        `user_id`                   ,--STRING COMMENT '用户ID',
        '2022-06-09'           ,--STRING COMMENT '历史至今首次下单日期',
        '2022-06-09'           ,--STRING COMMENT '历史至今末次下单日期',
        `order_count_1d`            ,--BIGINT COMMENT '历史至今下单次数',
        `order_num_1d`              ,--BIGINT COMMENT '历史至今购买商品件数',
        `order_original_amount_1d`        ,--DECIMAL(16, 2) COMMENT '历史至今下单原始金额',
        `activity_reduce_amount_1d` ,--DECIMAL(16, 2) COMMENT '历史至今下单活动优惠金额',
        `coupon_reduce_amount_1d`   ,--DECIMAL(16, 2) COMMENT '历史至今下单优惠券优惠金额',
        `order_total_amount_1d`            --DECIMAL(16, 2) COMMENT '历史至今下单最终金额'
    from dws_trade_user_order_1d
    where dt = '2022-06-09'
) t group by user_id

-- 交易域用户粒度加购最近1日汇总表
DROP TABLE IF EXISTS dws_trade_user_cart_add_1d;
CREATE EXTERNAL TABLE dws_trade_user_cart_add_1d
(
    `user_id`           STRING COMMENT '用户ID',
    `cart_add_count_1d` BIGINT COMMENT '最近1日加购次数',
    `cart_add_num_1d`   BIGINT COMMENT '最近1日加购商品件数'
) COMMENT '交易域用户粒度加购最近1日汇总表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_trade_user_cart_add_1d'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 1d表首日包含历史数据
    -- user + sku
insert overwrite table dws_trade_user_cart_add_1d partition (dt)
select
    user_id,
    count(*),
    sum(sku_num),
    dt
from dwd_trade_cart_add_inc
group by dt, user_id;

-- 每日数据装载
insert overwrite table dws_trade_user_cart_add_1d partition (dt = '2022-06-09')
select
    user_id,
    count(*),
    sum(sku_num)
from dwd_trade_cart_add_inc
where dt = '2022-06-09'
group by user_id;

-- 交易域用户粒度支付最近1日汇总表
DROP TABLE IF EXISTS dws_trade_user_payment_1d;
CREATE EXTERNAL TABLE dws_trade_user_payment_1d
(
    `user_id`           STRING COMMENT '用户ID',
    `payment_count_1d`  BIGINT COMMENT '最近1日支付次数',
    `payment_num_1d`    BIGINT COMMENT '最近1日支付商品件数',
    `payment_amount_1d` DECIMAL(16, 2) COMMENT '最近1日支付金额'
) COMMENT '交易域用户粒度支付最近1日汇总表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_trade_user_payment_1d'
    TBLPROPERTIES ('orc.compress' = 'snappy');
-- 首日数据装载
-- 一次支付三个商品，就有三条数据
insert overwrite table dws_trade_user_payment_1d partition (dt)
select
    user_id,
    count(distinct order_id),
    sum(sku_num),
    sum(split_payment_amount),
    dt
from dwd_trade_pay_detail_suc_inc
group by user_id, dt;

-- 每日数据装载
insert overwrite table dws_trade_user_payment_1d partition (dt = '2022-06-09')
select
    user_id,
    count(distinct order_id),
    sum(sku_num),
    sum(split_payment_amount)
from dwd_trade_pay_detail_suc_inc
where dt = '2022-06-09'
group by user_id;

-- 交易域省份粒度订单最近1日汇总表
DROP TABLE IF EXISTS dws_trade_province_order_1d;
CREATE EXTERNAL TABLE dws_trade_province_order_1d
(
    `province_id`               STRING COMMENT '省份ID',
    `province_name`             STRING COMMENT '省份名称',
    `area_code`                 STRING COMMENT '地区编码',
    `iso_code`                  STRING COMMENT '旧版国际标准地区编码',
    `iso_3166_2`                STRING COMMENT '新版国际标准地区编码',
    `order_count_1d`            BIGINT COMMENT '最近1日下单次数',
    `order_original_amount_1d`  DECIMAL(16, 2) COMMENT '最近1日下单原始金额',
    `activity_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单活动优惠金额',
    `coupon_reduce_amount_1d`   DECIMAL(16, 2) COMMENT '最近1日下单优惠券优惠金额',
    `order_total_amount_1d`     DECIMAL(16, 2) COMMENT '最近1日下单最终金额'
) COMMENT '交易域省份粒度订单最近1日汇总表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_trade_province_order_1d'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 首日数据装载
insert overwrite table dws_trade_province_order_1d partition (dt)
select
    `province_id`               ,--STRING COMMENT '省份ID',
    `province_name`             ,--STRING COMMENT '省份名称',
    `area_code`                 ,--STRING COMMENT '地区编码',
    `iso_code`                  ,--STRING COMMENT '旧版国际标准地区编码',
    `iso_3166_2`                ,--STRING COMMENT '新版国际标准地区编码',
    `order_count_1d`            ,--BIGINT COMMENT '最近1日下单次数',
    `order_original_amount_1d`  ,--DECIMAL(16, 2) COMMENT '最近1日下单原始金额',
    `activity_reduce_amount_1d` ,--DECIMAL(16, 2) COMMENT '最近1日下单活动优惠金额',
    `coupon_reduce_amount_1d`   ,--DECIMAL(16, 2) COMMENT '最近1日下单优惠券优惠金额',
    `order_total_amount_1d`     ,--DECIMAL(16, 2) COMMENT '最近1日下单最终金额'
    dt
from (
    select
        province_id,
        count(distinct order_id) order_count_1d,
        sum(split_original_amount) order_original_amount_1d,
        sum(split_activity_amount) activity_reduce_amount_1d,
        sum(split_coupon_amount) coupon_reduce_amount_1d,
        sum(split_total_amount) order_total_amount_1d,
        dt
    from dwd_trade_order_detail_inc
    group by province_id, dt
) od
left join (
    select
        id,
        province_name,
        area_code,
        iso_code,
        iso_3166_2
    from dim_province_full
    where dt = '2022-06-08'
) prv on od.province_id = prv.id;

-- 每日数据装载
insert overwrite table dws_trade_province_order_1d partition (dt = '2022-06-09')
select
    `province_id`               ,--STRING COMMENT '省份ID',
    `province_name`             ,--STRING COMMENT '省份名称',
    `area_code`                 ,--STRING COMMENT '地区编码',
    `iso_code`                  ,--STRING COMMENT '旧版国际标准地区编码',
    `iso_3166_2`                ,--STRING COMMENT '新版国际标准地区编码',
    `order_count_1d`            ,--BIGINT COMMENT '最近1日下单次数',
    `order_original_amount_1d`  ,--DECIMAL(16, 2) COMMENT '最近1日下单原始金额',
    `activity_reduce_amount_1d` ,--DECIMAL(16, 2) COMMENT '最近1日下单活动优惠金额',
    `coupon_reduce_amount_1d`   ,--DECIMAL(16, 2) COMMENT '最近1日下单优惠券优惠金额',
    `order_total_amount_1d`      --DECIMAL(16, 2) COMMENT '最近1日下单最终金额'
from (
    select
        province_id,
        count(distinct order_id) order_count_1d,
        sum(split_original_amount) order_original_amount_1d,
        sum(split_activity_amount) activity_reduce_amount_1d,
        sum(split_coupon_amount) coupon_reduce_amount_1d,
        sum(split_total_amount) order_total_amount_1d
    from dwd_trade_order_detail_inc
    where dt = '2022-06-09'
    group by province_id
) od
left join (
    select
        id,
        province_name,
        area_code,
        iso_code,
        iso_3166_2
    from dim_province_full
    where dt = '2022-06-09'
) prv on od.province_id = prv.id;

-- 交易域省份粒度订单最近n日汇总表
DROP TABLE IF EXISTS dws_trade_province_order_nd;
CREATE EXTERNAL TABLE dws_trade_province_order_nd
(
    `province_id`                STRING COMMENT '省份ID',
    `province_name`              STRING COMMENT '省份名称',
    `area_code`                  STRING COMMENT '地区编码',
    `iso_code`                   STRING COMMENT '旧版国际标准地区编码',
    `iso_3166_2`                 STRING COMMENT '新版国际标准地区编码',
    `order_count_7d`             BIGINT COMMENT '最近7日下单次数',
    `order_original_amount_7d`   DECIMAL(16, 2) COMMENT '最近7日下单原始金额',
    `activity_reduce_amount_7d`  DECIMAL(16, 2) COMMENT '最近7日下单活动优惠金额',
    `coupon_reduce_amount_7d`    DECIMAL(16, 2) COMMENT '最近7日下单优惠券优惠金额',
    `order_total_amount_7d`      DECIMAL(16, 2) COMMENT '最近7日下单最终金额',
    `order_count_30d`            BIGINT COMMENT '最近30日下单次数',
    `order_original_amount_30d`  DECIMAL(16, 2) COMMENT '最近30日下单原始金额',
    `activity_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单活动优惠金额',
    `coupon_reduce_amount_30d`   DECIMAL(16, 2) COMMENT '最近30日下单优惠券优惠金额',
    `order_total_amount_30d`     DECIMAL(16, 2) COMMENT '最近30日下单最终金额'
) COMMENT '交易域省份粒度订单最近n日汇总表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_trade_province_order_nd'
    TBLPROPERTIES ('orc.compress' = 'snappy');

insert overwrite table dws_trade_province_order_nd partition (dt = '2022-06-08')
select
    `province_id`                ,--STRING COMMENT '省份ID',
    `province_name`              ,--STRING COMMENT '省份名称',
    `area_code`                  ,--STRING COMMENT '地区编码',
    `iso_code`                   ,--STRING COMMENT '旧版国际标准地区编码',
    `iso_3166_2`                 ,--STRING COMMENT '新版国际标准地区编码',
    sum(if(dt >= date_sub('2022-06-08', 6), order_count_1d, 0))             ,--BIGINT COMMENT '最近7日下单次数',
    sum(if(dt >= date_sub('2022-06-08', 6), order_original_amount_1d, 0))   ,--DECIMAL(16, 2) COMMENT '最近7日下单原始金额',
    sum(if(dt >= date_sub('2022-06-08', 6), activity_reduce_amount_1d, 0))  ,--DECIMAL(16, 2) COMMENT '最近7日下单活动优惠金额',
    sum(if(dt >= date_sub('2022-06-08', 6), coupon_reduce_amount_1d, 0))    ,--DECIMAL(16, 2) COMMENT '最近7日下单优惠券优惠金额',
    sum(if(dt >= date_sub('2022-06-08', 6), order_total_amount_1d, 0))      ,--DECIMAL(16, 2) COMMENT '最近7日下单最终金额',
    sum(order_count_1d)            ,--BIGINT COMMENT '最近30日下单次数',
    sum(order_original_amount_1d)  ,--DECIMAL(16, 2) COMMENT '最近30日下单原始金额',
    sum(activity_reduce_amount_1d) ,--DECIMAL(16, 2) COMMENT '最近30日下单活动优惠金额',
    sum(coupon_reduce_amount_1d)   ,--DECIMAL(16, 2) COMMENT '最近30日下单优惠券优惠金额',
    sum(order_total_amount_1d)     --DECIMAL(16, 2) COMMENT '最近30日下单最终金额'
from dws_trade_province_order_1d
where dt >= date_sub('2022-06-08', 29) and dt <= '2022-06-08'
group by `province_id`,
         `province_name`,
         `area_code`,
         `iso_code`,
         `iso_3166_2`;

-- 工具域用户优惠券粒度优惠券使用(支付)最近1日汇总表
DROP TABLE IF EXISTS dws_tool_user_coupon_coupon_used_1d;
CREATE EXTERNAL TABLE dws_tool_user_coupon_coupon_used_1d
(
    `user_id`          STRING COMMENT '用户ID',
    `coupon_id`        STRING COMMENT '优惠券ID',
    `coupon_name`      STRING COMMENT '优惠券名称',
    `coupon_type_code` STRING COMMENT '优惠券类型编码',
    `coupon_type_name` STRING COMMENT '优惠券类型名称',
    `benefit_rule`     STRING COMMENT '优惠规则',
    `used_count_1d`    STRING COMMENT '使用(支付)次数'
) COMMENT '工具域用户优惠券粒度优惠券使用(支付)最近1日汇总表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_tool_user_coupon_coupon_used_1d'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 首日数据装载
insert overwrite table dws_tool_user_coupon_coupon_used_1d partition (dt)
select
    `user_id`          ,--STRING COMMENT '用户ID',
    `coupon_id`        ,--STRING COMMENT '优惠券ID',
    `coupon_name`      ,--STRING COMMENT '优惠券名称',
    `coupon_type_code` ,--STRING COMMENT '优惠券类型编码',
    `coupon_type_name` ,--STRING COMMENT '优惠券类型名称',
    `benefit_rule`     ,--STRING COMMENT '优惠规则',
    `used_count_1d`    ,--STRING COMMENT '使用(支付)次数'
    dt
from (
    select
        user_id,
        coupon_id,
        count(*) used_count_1d,
        dt
    from dwd_tool_coupon_used_inc
    group by user_id, coupon_id, dt
) cu
left join (
    select
        id,
        coupon_name,
        coupon_type_code,
        coupon_type_name,
        benefit_rule
    from dim_coupon_full
    where dt = '2022-06-08'
) cp on cu.coupon_id = cp.id;

-- 每日数据装载
insert overwrite table dws_tool_user_coupon_coupon_used_1d partition (dt = '2022-06-09')
select
    `user_id`          ,--STRING COMMENT '用户ID',
    `coupon_id`        ,--STRING COMMENT '优惠券ID',
    `coupon_name`      ,--STRING COMMENT '优惠券名称',
    `coupon_type_code` ,--STRING COMMENT '优惠券类型编码',
    `coupon_type_name` ,--STRING COMMENT '优惠券类型名称',
    `benefit_rule`     ,--STRING COMMENT '优惠规则',
    `used_count_1d`     --STRING COMMENT '使用(支付)次数'
from (
    select
        user_id,
        coupon_id,
        count(*) used_count_1d
    from dwd_tool_coupon_used_inc
    where dt = '2022-06-09'
    group by user_id, coupon_id
) cu
left join (
    select
        id,
        coupon_name,
        coupon_type_code,
        coupon_type_name,
        benefit_rule
    from dim_coupon_full
    where dt = '2022-06-09'
) cp on cu.coupon_id = cp.id;

-- 互动域商品粒度收藏商品最近1日汇总表
DROP TABLE IF EXISTS dws_interaction_sku_favor_add_1d;
CREATE EXTERNAL TABLE dws_interaction_sku_favor_add_1d
(
    `sku_id`             STRING COMMENT 'SKU_ID',
    `sku_name`           STRING COMMENT 'SKU名称',
    `category1_id`       STRING COMMENT '一级品类ID',
    `category1_name`     STRING COMMENT '一级品类名称',
    `category2_id`       STRING COMMENT '二级品类ID',
    `category2_name`     STRING COMMENT '二级品类名称',
    `category3_id`       STRING COMMENT '三级品类ID',
    `category3_name`     STRING COMMENT '三级品类名称',
    `tm_id`              STRING COMMENT '品牌ID',
    `tm_name`            STRING COMMENT '品牌名称',
    `favor_add_count_1d` BIGINT COMMENT '商品被收藏次数'
) COMMENT '互动域商品粒度收藏商品最近1日汇总表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_interaction_sku_favor_add_1d'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 首日数据装载
insert overwrite table dws_interaction_sku_favor_add_1d partition (dt)
select
    `sku_id`             ,
    `sku_name`           ,
    `category1_id`       ,
    `category1_name`     ,
    `category2_id`       ,
    `category2_name`     ,
    `category3_id`       ,
    `category3_name`     ,
    `tm_id`              ,
    `tm_name`            ,
    `favor_add_count_1d` ,
    dt
from (
    select
        sku_id,
        count(*) favor_add_count_1d,
        dt
    from dwd_interaction_favor_add_inc
    group by sku_id, dt
) fa
left join (
    select
        `id`,
        `sku_name`           ,--STRING COMMENT 'SKU名称',
        `category1_id`       ,--STRING COMMENT '一级品类ID',
        `category1_name`     ,--STRING COMMENT '一级品类名称',
        `category2_id`       ,--STRING COMMENT '二级品类ID',
        `category2_name`     ,--STRING COMMENT '二级品类名称',
        `category3_id`       ,--STRING COMMENT '三级品类ID',
        `category3_name`     ,--STRING COMMENT '三级品类名称',
        `tm_id`              ,--STRING COMMENT '品牌ID',
        `tm_name`             --STRING COMMENT '品牌名称',
    from dim_sku_full
    where dt = '2022-06-08'
) sku on fa.sku_id = sku.id;

-- 每日数据装载
insert overwrite table dws_interaction_sku_favor_add_1d partition (dt = '2022-06-09')
select
    `sku_id`             ,
    `sku_name`           ,
    `category1_id`       ,
    `category1_name`     ,
    `category2_id`       ,
    `category2_name`     ,
    `category3_id`       ,
    `category3_name`     ,
    `tm_id`              ,
    `tm_name`            ,
    `favor_add_count_1d`
from (
    select
        sku_id,
        count(*) favor_add_count_1d
    from dwd_interaction_favor_add_inc
    where dt = '2022-06-09'
    group by sku_id
) fa
left join (
    select
        `id`,
        `sku_name`           ,--STRING COMMENT 'SKU名称',
        `category1_id`       ,--STRING COMMENT '一级品类ID',
        `category1_name`     ,--STRING COMMENT '一级品类名称',
        `category2_id`       ,--STRING COMMENT '二级品类ID',
        `category2_name`     ,--STRING COMMENT '二级品类名称',
        `category3_id`       ,--STRING COMMENT '三级品类ID',
        `category3_name`     ,--STRING COMMENT '三级品类名称',
        `tm_id`              ,--STRING COMMENT '品牌ID',
        `tm_name`             --STRING COMMENT '品牌名称',
    from dim_sku_full
    where dt = '2022-06-09'
) sku on fa.sku_id = sku.id;

-- 流量域会话粒度页面浏览最近1日汇总表
DROP TABLE IF EXISTS dws_traffic_session_page_view_1d;
CREATE EXTERNAL TABLE dws_traffic_session_page_view_1d
(
    `session_id`     STRING COMMENT '会话ID',
    `mid_id`         string comment '设备ID',
    `brand`          string comment '手机品牌',
    `model`          string comment '手机型号',
    `operate_system` string comment '操作系统',
    `version_code`   string comment 'APP版本号',
    `channel`        string comment '渠道',
    `during_time_1d` BIGINT COMMENT '最近1日浏览时长',
    `page_count_1d`  BIGINT COMMENT '最近1日浏览页面数'
) COMMENT '流量域会话粒度页面浏览最近1日汇总表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_traffic_session_page_view_1d'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 日志无历史数据
insert overwrite table dws_traffic_session_page_view_1d partition (dt = '2022-06-08')
select
    session_id,
    `mid_id`         ,--string comment '设备ID',
    `brand`          ,--string comment '手机品牌',
    `model`          ,--string comment '手机型号',
    `operate_system` ,--string comment '操作系统',
    `version_code`   ,--string comment 'APP版本号',
    `channel`        ,--string comment '渠道',
    sum(during_time),
    count(page_id)
from dwd_traffic_page_view_inc
where dt = '2022-06-08'
group by session_id,
         `mid_id`,
         `brand`,
         `model`,
         `operate_system`,
         `version_code`,
         `channel`;

-- 流量域访客页面粒度页面浏览最近1日汇总表
DROP TABLE IF EXISTS dws_traffic_page_visitor_page_view_1d;
CREATE EXTERNAL TABLE dws_traffic_page_visitor_page_view_1d
(
    `mid_id`         STRING COMMENT '访客ID',
    `brand`          string comment '手机品牌',
    `model`          string comment '手机型号',
    `operate_system` string comment '操作系统',
    `page_id`        STRING COMMENT '页面ID',
    `during_time_1d` BIGINT COMMENT '最近1日浏览时长',
    `view_count_1d`  BIGINT COMMENT '最近1日访问次数'
) COMMENT '流量域访客页面粒度页面浏览最近1日汇总表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_traffic_page_visitor_page_view_1d'
    TBLPROPERTIES ('orc.compress' = 'snappy');

insert overwrite table dws_traffic_page_visitor_page_view_1d partition (dt = '2022-06-08')
select
    mid_id,
    brand,
    model,
    operate_system,
    page_id,
    sum(during_time),
    count(*)
from dwd_traffic_page_view_inc
where dt = '2022-06-08'
group by mid_id,
         page_id,
         brand,
         model,
         operate_system;

-- 用户域用户粒度登录历史至今汇总表
    -- 活跃
DROP TABLE IF EXISTS dws_user_user_login_td;
CREATE EXTERNAL TABLE dws_user_user_login_td
(
    `user_id`          STRING COMMENT '用户ID',
    `login_date_last`  STRING COMMENT '历史至今末次登录日期',
    `login_date_first` STRING COMMENT '历史至今首次登录日期',
    `login_count_td`   BIGINT COMMENT '历史至今累计登录次数'
) COMMENT '用户域用户粒度登录历史至今汇总表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_user_user_login_td'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 登录信息来自于日志表，但是日志表只有8号以后的数据，没有历史数据
    -- MySQL数据库中不会保存行为数据，也就是说不会保存登录数据
    -- 折中地认为用户注册的时间就是用户首次登录时间，也就是末次登录时间
-- 首日数据装载
insert overwrite table dws_user_user_login_td partition (dt = '2022-06-08')
select
    user_id,
    max(dt) login_date_last,
    min(dt) login_date_first,
    count(*) login_count_td
from dwd_user_login_inc
group by user_id;

-- 每日数据装载
insert overwrite table dws_user_user_login_td partition (dt = '2022-06-09')
select
    user_id,
    max(login_date_last),
    min(login_date_first),
    sum(login_count_td)
from (
    select
        user_id,
        login_date_last,
        login_date_first,
        login_count_td
    from dws_user_user_login_td
    where dt = date_sub('2022-06-09', 1)
    union all
    select
        user_id,
        '2022-06-09',
        '2022-06-09',
        count(*)
    from dwd_user_login_inc
    where dt = '2022-06-09'
    group by user_id
) t group by user_id;
----------------------------------------------------------------------------
-- 首日数据使用注册数据
-- ODS层数据是整个数据仓库的数据源，不能作为统计分析的数据源
    -- 统计分析的数据源一般是DIM、DWD层
select
    user_id,
    max(login_date_last),
    min(login_date_first),
    sum(login_count_td)
from (
    select
        id user_id,
        date_format(create_time, 'yyyy-MM-dd') login_date_last,
        date_format(create_time, 'yyyy-MM-dd') login_date_first,
        1 login_count_td
    from dim_user_zip
    where dt = '9999-12-31' and date_format(create_time, 'yyyy-MM-dd') != '2022-06-08'
    union all
    select
        user_id,
        '2022-06-08',
        '2022-06-08',
        count(*) login_count_td
    from dwd_user_login_inc
    where dt = '2022-06-08'
    group by user_id
) t group by user_id

  • ADS层 

       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
 145
 146
 147
 148
 149
 150
 151
 152
 153
 154
 155
 156
 157
 158
 159
 160
 161
 162
 163
 164
 165
 166
 167
 168
 169
 170
 171
 172
 173
 174
 175
 176
 177
 178
 179
 180
 181
 182
 183
 184
 185
 186
 187
 188
 189
 190
 191
 192
 193
 194
 195
 196
 197
 198
 199
 200
 201
 202
 203
 204
 205
 206
 207
 208
 209
 210
 211
 212
 213
 214
 215
 216
 217
 218
 219
 220
 221
 222
 223
 224
 225
 226
 227
 228
 229
 230
 231
 232
 233
 234
 235
 236
 237
 238
 239
 240
 241
 242
 243
 244
 245
 246
 247
 248
 249
 250
 251
 252
 253
 254
 255
 256
 257
 258
 259
 260
 261
 262
 263
 264
 265
 266
 267
 268
 269
 270
 271
 272
 273
 274
 275
 276
 277
 278
 279
 280
 281
 282
 283
 284
 285
 286
 287
 288
 289
 290
 291
 292
 293
 294
 295
 296
 297
 298
 299
 300
 301
 302
 303
 304
 305
 306
 307
 308
 309
 310
 311
 312
 313
 314
 315
 316
 317
 318
 319
 320
 321
 322
 323
 324
 325
 326
 327
 328
 329
 330
 331
 332
 333
 334
 335
 336
 337
 338
 339
 340
 341
 342
 343
 344
 345
 346
 347
 348
 349
 350
 351
 352
 353
 354
 355
 356
 357
 358
 359
 360
 361
 362
 363
 364
 365
 366
 367
 368
 369
 370
 371
 372
 373
 374
 375
 376
 377
 378
 379
 380
 381
 382
 383
 384
 385
 386
 387
 388
 389
 390
 391
 392
 393
 394
 395
 396
 397
 398
 399
 400
 401
 402
 403
 404
 405
 406
 407
 408
 409
 410
 411
 412
 413
 414
 415
 416
 417
 418
 419
 420
 421
 422
 423
 424
 425
 426
 427
 428
 429
 430
 431
 432
 433
 434
 435
 436
 437
 438
 439
 440
 441
 442
 443
 444
 445
 446
 447
 448
 449
 450
 451
 452
 453
 454
 455
 456
 457
 458
 459
 460
 461
 462
 463
 464
 465
 466
 467
 468
 469
 470
 471
 472
 473
 474
 475
 476
 477
 478
 479
 480
 481
 482
 483
 484
 485
 486
 487
 488
 489
 490
 491
 492
 493
 494
 495
 496
 497
 498
 499
 500
 501
 502
 503
 504
 505
 506
 507
 508
 509
 510
 511
 512
 513
 514
 515
 516
 517
 518
 519
 520
 521
 522
 523
 524
 525
 526
 527
 528
 529
 530
 531
 532
 533
 534
 535
 536
 537
 538
 539
 540
 541
 542
 543
 544
 545
 546
 547
 548
 549
 550
 551
 552
 553
 554
 555
 556
 557
 558
 559
 560
 561
 562
 563
 564
 565
 566
 567
 568
 569
 570
 571
 572
 573
 574
 575
 576
 577
 578
 579
 580
 581
 582
 583
 584
 585
 586
 587
 588
 589
 590
 591
 592
 593
 594
 595
 596
 597
 598
 599
 600
 601
 602
 603
 604
 605
 606
 607
 608
 609
 610
 611
 612
 613
 614
 615
 616
 617
 618
 619
 620
 621
 622
 623
 624
 625
 626
 627
 628
 629
 630
 631
 632
 633
 634
 635
 636
 637
 638
 639
 640
 641
 642
 643
 644
 645
 646
 647
 648
 649
 650
 651
 652
 653
 654
 655
 656
 657
 658
 659
 660
 661
 662
 663
 664
 665
 666
 667
 668
 669
 670
 671
 672
 673
 674
 675
 676
 677
 678
 679
 680
 681
 682
 683
 684
 685
 686
 687
 688
 689
 690
 691
 692
 693
 694
 695
 696
 697
 698
 699
 700
 701
 702
 703
 704
 705
 706
 707
 708
 709
 710
 711
 712
 713
 714
 715
 716
 717
 718
 719
 720
 721
 722
 723
 724
 725
 726
 727
 728
 729
 730
 731
 732
 733
 734
 735
 736
 737
 738
 739
 740
 741
 742
 743
 744
 745
 746
 747
 748
 749
 750
 751
 752
 753
 754
 755
 756
 757
 758
 759
 760
 761
 762
 763
 764
 765
 766
 767
 768
 769
 770
 771
 772
 773
 774
 775
 776
 777
 778
 779
 780
 781
 782
 783
 784
 785
 786
 787
 788
 789
 790
 791
 792
 793
 794
 795
 796
 797
 798
 799
 800
 801
 802
 803
 804
 805
 806
 807
 808
 809
 810
 811
 812
 813
 814
 815
 816
 817
 818
 819
 820
 821
 822
 823
 824
 825
 826
 827
 828
 829
 830
 831
 832
 833
 834
 835
 836
 837
 838
 839
 840
 841
 842
 843
 844
 845
 846
 847
 848
 849
 850
 851
 852
 853
 854
 855
 856
 857
 858
 859
 860
 861
 862
 863
 864
 865
 866
 867
 868
 869
 870
 871
 872
 873
 874
 875
 876
 877
 878
 879
 880
 881
 882
 883
 884
 885
 886
 887
 888
 889
 890
 891
 892
 893
 894
 895
 896
 897
 898
 899
 900
 901
 902
 903
 904
 905
 906
 907
 908
 909
 910
 911
 912
 913
 914
 915
 916
 917
 918
 919
 920
 921
 922
 923
 924
 925
 926
 927
 928
 929
 930
 931
 932
 933
 934
 935
 936
 937
 938
 939
 940
 941
 942
 943
 944
 945
 946
 947
 948
 949
 950
 951
 952
 953
 954
 955
 956
 957
 958
 959
 960
 961
 962
 963
 964
 965
 966
 967
 968
 969
 970
 971
 972
 973
 974
 975
 976
 977
 978
 979
 980
 981
 982
 983
 984
 985
 986
 987
 988
 989
 990
 991
 992
 993
 994
 995
 996
 997
 998
 999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163

    -- ADS层
    -- Application Data Service
        -- Application：（数据仓库）应用
        -- Data：用户需求的统计结果数据
        -- Service：对外服务
        -- 1. ADS层保存的数据是最终的统计结果，无需做进一步的计算
            -- 不需要列式存储，也不需要snappy压缩
        -- 2. 统计结果的目的是对外提供服务，所以表不是最终数据存储的位置
            -- 需要将表中的数据同步到第三方存储（mysql）
                -- ADS层的表最好是行式存储：tsv (DataX)
                -- 压缩格式采用gzip
        -- 3. 统计结果的数据量，不会很多
            -- ADS层的表无需分区（分区是因为数据量大，分区作为提前筛选，但是ADS层数据量小，无需分区）
        -- 4. 表设计
            -- ODS层：表的结构依托于数据源的数据结构（ER模型）
            -- DIM层：遵循维度模型的维度表的设计理念（维度越丰富越好）（表、字段（丰富）、编码与文字共存、沉淀）
            -- DWD层：遵循维度模型的事实表的设计理念（粒度越细越好）（描述行为、度量值）
            -- ADS层：客户需求（不要额外添加）
    --基础概念
        -- 维度：分析数据的角度
        -- 粒度：描述数据的详细程度
        -- 统计周期：统计的时候，数据统计时间范围
            -- 2022-06-08（最近一周）：02 03 04 05 06 07 08
            -- 2022-06-09（最近一周）：03 04 05 06 07 08 09
            -- 2022-06-10（最近一周）：04 05 06 07 08 09 10
        -- 统计粒度：分析数据的角度（站在哪一个角度统计数据）
            -- 维度越丰富，粒度越细
        -- 指标：客户想要的一个结果数值

-- 各品牌商品下单统计
    -- 统计的行为：下单
    -- 分析的角度：品牌
    -- 指标：下单数量、下单人数
    -- 建表语句
        -- 统计日期：以获取数据那一天为准（比如获取8号数据（9号跑代码））
DROP TABLE IF EXISTS ads_order_stats_by_tm;
CREATE EXTERNAL TABLE ads_order_stats_by_tm
(
    `dt`                      STRING COMMENT '统计日期',
    `recent_days`             BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `tm_id`                   STRING COMMENT '品牌ID',
    `tm_name`                 STRING COMMENT '品牌名称',
    `order_count`             BIGINT COMMENT '下单数',
    `order_user_count`        BIGINT COMMENT '下单人数'
) COMMENT '各品牌商品下单统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_order_stats_by_tm/';

-- 数据装载
    -- 统计指标分析
        -- 将负责的指标分解成小的，简单的指标
        -- 指标分析
            -- 1. 原子指标（最基础的统计值，不可分割）
                -- 业务过程（业务行为） + 度量值 + 聚合逻辑
                -- 下单 + （order_id）次数 + 聚合逻辑
/*
    zs 2022-06-08 AAAA  鞋  1000
    zs 2022-06-08 AAAA 衣服 1500
    zs 2022-06-08 AAAA 帽子 500
*/
-- select
--     count(distinct order_id)
-- from dwd_trade_order_detail_inc;

            -- 2. 派生指标
                -- 基于原子指标，增加其他的条件、角度之类
                    -- 派生指标 = 原子指标 + 统计周期(最近1天) + 业务限定 + 统计粒度
                        -- 统计周期和业务限定其实都是数据筛选条件，但是不一样
                            -- 统计周期一般指的就是数据时间范围（分区字段的过滤：过滤文件夹）
                            -- 业务限定一般指的就是数据约束条件（数据字段的过滤：过滤文件）
-- select
--     count(distinct order_id)
-- from dwd_trade_order_detail_inc
-- where dt >= date_sub('2022-06-08', 0) and dt <= '2022-06-08'
-- group by 品牌
--
-- select
--     count(distinct order_id)
-- from dwd_trade_order_detail_inc
-- where dt >= date_sub('2022-06-08', 6) and dt <= '2022-06-08'
-- group by 品牌
--
-- select
--     count(distinct order_id)
-- from dwd_trade_order_detail_inc
-- where dt >= date_sub('2022-06-08', 29) and dt <= '2022-06-08'
-- group by 品牌

            -- 3. 衍生指标
                -- 将多个派生指标通过计算获取
-- 最近 1 7 30 天
    -- 2022-06-08   1   结果
    -- 2022-06-08   7   结果
    -- 2022-06-08   30  结果
-- 预期统计结果 <= 11 * 3 = 33
-- 预期统计结果：最近1天（5），最近7天（8），最近10天（10）

-- 最近1天 各个品牌 下单数 下单人数统计
    -- 统计周期其实就是数据的时间范围，一般会在where子句中添加dt条件
    -- 业务限定其实就是数据的约束条件，一般会在where子句中添加业务条件
    -- 统计粒度其实就是数据的分析维度，一般会在group by子句中添加维度字段

-- 分组聚合的场合下，哪些字段可以出现在select子句中
    -- 1. 常量
    -- 2. 聚合函数内的字段
    -- 3. 参与分组的字段
-- 多个字段参与分组的时候，统计值的含义
    -- 1. 如果多个字段存在上下级，所属关系，那么统计结果和下级字段相关，上级字段参与分组纯粹是用于补全数据（不参与分组，不能出现在查询结果中）
    -- 2. 如果多个字段存在关联关系，那么统计结果和具有唯一性字段相关，其他字段用于补全数据
    -- 3. 如果多个字段没有任何关系，那么统计结果和所有的字段相关
        -- group by (userId, skuId)
select
    '2022-06-08'                     ,-- STRING COMMENT '统计日期',
    1            ,-- BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `tm_id`                  ,-- STRING COMMENT '品牌ID',
    `tm_name`                ,-- STRING COMMENT '品牌名称',
    count(distinct order_id) `order_count`            ,-- BIGINT COMMENT '下单数',
    count(distinct user_id) `order_user_count`       -- BIGINT COMMENT '下单人数'
from (
    select
        order_id,
        user_id,
        sku_id
    from dwd_trade_order_detail_inc
    where dt = '2022-06-08'
) od left join (
    select
        id,
        tm_id,
        tm_name
    from dim_sku_full
    where dt = '2022-06-08'
) sku on od.sku_id = sku_id
group by tm_id, tm_name;

-- 最近7天
select
    '2022-06-08'                     ,-- STRING COMMENT '统计日期',
    7            ,-- BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `tm_id`                  ,-- STRING COMMENT '品牌ID',
    `tm_name`                ,-- STRING COMMENT '品牌名称',
    count(distinct order_id) `order_count`            ,-- BIGINT COMMENT '下单数',
    count(distinct user_id) `order_user_count`       -- BIGINT COMMENT '下单人数'
from (
    select
        order_id,
        user_id,
        sku_id
    from dwd_trade_order_detail_inc
    where dt >= date_sub('2022-06-08', 6) and dt <= '2022-06-08'
) od left join (
    select
        id,
        tm_id,
        tm_name
    from dim_sku_full
    where dt >= date_sub('2022-06-08', 6) and dt <= '2022-06-08'
    group by id, tm_id, tm_name
) sku on od.sku_id = sku_id
group by tm_id, tm_name;

-----------------------------------

select
    '2022-06-08'                     ,-- STRING COMMENT '统计日期',
    7            ,-- BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `tm_id`                  ,-- STRING COMMENT '品牌ID',
    `tm_name`                ,-- STRING COMMENT '品牌名称',
    count(distinct order_id) `order_count`            ,-- BIGINT COMMENT '下单数',
    count(distinct user_id) `order_user_count`       -- BIGINT COMMENT '下单人数'
from (
    select
        order_id,
        user_id,
        sku_id
    from dwd_trade_order_detail_inc
    where dt >= date_sub('2022-06-08', 6) and dt <= '2022-06-08'
) od left join (
    select
        id,
        tm_id,
        tm_name
    from dim_sku_full
    where dt = '2022-06-08'
    -- 查不出来但是能关联（最后一天一定是最全的数据）
) sku on od.sku_id = sku_id
group by tm_id, tm_name;

-- 最近30天
select
    '2022-06-08'                     ,-- STRING COMMENT '统计日期',
    30            ,-- BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `tm_id`                  ,-- STRING COMMENT '品牌ID',
    `tm_name`                ,-- STRING COMMENT '品牌名称',
    count(distinct order_id) `order_count`            ,-- BIGINT COMMENT '下单数',
    count(distinct user_id) `order_user_count`       -- BIGINT COMMENT '下单人数'
from (
    select
        order_id,
        user_id,
        sku_id
    from dwd_trade_order_detail_inc
    where dt >= date_sub('2022-06-08', 29) and dt <= '2022-06-08'
) od left join (
    select
        id,
        tm_id,
        tm_name
    from dim_sku_full
    where dt = '2022-06-08'
    -- 查不出来但是能关联（最后一天一定是最全的数据）
) sku on od.sku_id = sku_id
group by tm_id, tm_name;

--------------------------------------------------
-- 保留旧的，插入新的（全表）
insert overwrite table ads_order_stats_by_tm
select * from ads_order_stats_by_tm
union -- 去重
select * from (select '2022-06-08',-- STRING COMMENT '统计日期',
                      1,-- BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
                      `tm_id`,-- STRING COMMENT '品牌ID',
                      `tm_name`,-- STRING COMMENT '品牌名称',
                      count(distinct order_id) `order_count`,-- BIGINT COMMENT '下单数',
                      count(distinct user_id)  `order_user_count` -- BIGINT COMMENT '下单人数'
               from (select order_id,
                            user_id,
                            sku_id
                     from dwd_trade_order_detail_inc
                     where dt = '2022-06-08') od
                        left join (select id,
                                          tm_id,
                                          tm_name
                                   from dim_sku_full
                                   where dt = '2022-06-08') sku on od.sku_id = sku_id
               group by tm_id, tm_name
               union all
               select '2022-06-08',-- STRING COMMENT '统计日期',
                      7,-- BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
                      `tm_id`,-- STRING COMMENT '品牌ID',
                      `tm_name`,-- STRING COMMENT '品牌名称',
                      count(distinct order_id) `order_count`,-- BIGINT COMMENT '下单数',
                      count(distinct user_id)  `order_user_count` -- BIGINT COMMENT '下单人数'
               from (select order_id,
                            user_id,
                            sku_id
                     from dwd_trade_order_detail_inc
                     where dt >= date_sub('2022-06-08', 6)
                       and dt <= '2022-06-08') od
                        left join (select id,
                                          tm_id,
                                          tm_name
                                   from dim_sku_full
                                   where dt = '2022-06-08'
                   -- 查不出来但是能关联（最后一天一定是最全的数据）
               ) sku on od.sku_id = sku_id
               group by tm_id, tm_name
               union all
               select '2022-06-08',-- STRING COMMENT '统计日期',
                      30,-- BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
                      `tm_id`,-- STRING COMMENT '品牌ID',
                      `tm_name`,-- STRING COMMENT '品牌名称',
                      count(distinct order_id) `order_count`,-- BIGINT COMMENT '下单数',
                      count(distinct user_id)  `order_user_count` -- BIGINT COMMENT '下单人数'
               from (select order_id,
                            user_id,
                            sku_id
                     from dwd_trade_order_detail_inc
                     where dt >= date_sub('2022-06-08', 29)
                       and dt <= '2022-06-08') od
                        left join (select id,
                                          tm_id,
                                          tm_name
                                   from dim_sku_full
                                   where dt = '2022-06-08'
                   -- 查不出来但是能关联（最后一天一定是最全的数据）
               ) sku on od.sku_id = sku_id
               group by tm_id, tm_name) t;

-- 各品类商品下单统计
    -- 建表语句
DROP TABLE IF EXISTS ads_order_stats_by_cate;
CREATE EXTERNAL TABLE ads_order_stats_by_cate
(
    `dt`                      STRING COMMENT '统计日期',
    `recent_days`             BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `category1_id`            STRING COMMENT '一级品类ID',
    `category1_name`          STRING COMMENT '一级品类名称',
    `category2_id`            STRING COMMENT '二级品类ID',
    `category2_name`          STRING COMMENT '二级品类名称',
    `category3_id`            STRING COMMENT '三级品类ID',
    `category3_name`          STRING COMMENT '三级品类名称',
    `order_count`             BIGINT COMMENT '下单数',
    `order_user_count`        BIGINT COMMENT '下单人数'
) COMMENT '各品类商品下单统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_order_stats_by_cate/';

insert overwrite table ads_order_stats_by_cate
select * from ads_order_stats_by_cate
union
select * from (
-- 最近1天
                  select '2022-06-08',
                         1,
                         `category1_id`,
                         `category1_name`,
                         `category2_id`,
                         `category2_name`,
                         `category3_id`,
                         `category3_name`,
                         count(distinct order_id) order_count,
                         count(distinct user_id)  order_user_count
                  from (select order_id,
                               user_id,
                               sku_id
                        from dwd_trade_order_detail_inc
                        where dt = '2022-06-08') od
                           left join (select id,
                                             category1_id,
                                             category1_name,
                                             category2_id,
                                             category2_name,
                                             category3_id,
                                             category3_name
                                      from dim_sku_full
                                      where dt = '2022-06-08') sku on od.sku_id = sku.id
                  group by
                      -- 关联关系（3_id）
                      `category1_id`,
                      `category1_name`,
                      `category2_id`,
                      `category2_name`,
                      `category3_id`,
                      `category3_name`
                  union all
-- 最近7天
                  select '2022-06-08',
                         7,
                         `category1_id`,
                         `category1_name`,
                         `category2_id`,
                         `category2_name`,
                         `category3_id`,
                         `category3_name`,
                         count(distinct order_id) order_count,
                         count(distinct user_id)  order_user_count
                  from (select order_id,
                               user_id,
                               sku_id
                        from dwd_trade_order_detail_inc
                        where dt >= date_sub('2022-06-08', 6)
                          and dt <= '2022-06-08') od
                           left join (select id,
                                             category1_id,
                                             category1_name,
                                             category2_id,
                                             category2_name,
                                             category3_id,
                                             category3_name
                                      from dim_sku_full
                                      where dt = '2022-06-08') sku on od.sku_id = sku.id
                  group by
                      -- 关联关系（3_id）
                      `category1_id`,
                      `category1_name`,
                      `category2_id`,
                      `category2_name`,
                      `category3_id`,
                      `category3_name`
                  union all
-- 最近30天
                  select '2022-06-08',
                         30,
                         `category1_id`,
                         `category1_name`,
                         `category2_id`,
                         `category2_name`,
                         `category3_id`,
                         `category3_name`,
                         count(distinct order_id) order_count,
                         count(distinct user_id)  order_user_count
                  from (select order_id,
                               user_id,
                               sku_id
                        from dwd_trade_order_detail_inc
                        where dt >= date_sub('2022-06-08', 29)
                          and dt <= '2022-06-08') od
                           left join (select id,
                                             category1_id,
                                             category1_name,
                                             category2_id,
                                             category2_name,
                                             category3_id,
                                             category3_name
                                      from dim_sku_full
                                      where dt = '2022-06-08') sku on od.sku_id = sku.id
                  group by
                      -- 关联关系（3_id）
                      `category1_id`,
                      `category1_name`,
                      `category2_id`,
                      `category2_name`,
                      `category3_id`,
                      `category3_name`) t;

-- 性能如何提升？
    -- join(X)
    -- 可以减少数据量(X)
    -- 可以重复计算(OK)
    -- 数据重复读取(OK)
-- 优化思路
    -- 将最近1天的数据保存到一张表中
        -- 最近7天和最近30天数据从最近1天的统计表中获取数据，进行进一步的计算

-- ODS(X) 对接数据源
-- DIM(X) 分析数据作为维度
-- DWD(X) 存储业务行为数据
-- DWS(OK) 预聚合，保存中间计算结果
    -- 中间计算结果不是最终结果表
        -- 数据量：增加分区
        -- 需要进一步的计算
            -- 存储方式：列式存储
            -- 压缩语句：snappy
-- ADS(X) 无需再做进一步计算的统计结果
DROP TABLE IF EXISTS dws_order_stats_by_tm_1d;
CREATE EXTERNAL TABLE dws_order_stats_by_tm_1d
(
    `tm_id`                   STRING COMMENT '品牌ID',
    `tm_name`                 STRING COMMENT '品牌名称',
    `order_count_1d`             BIGINT COMMENT '下单数',
    `order_user_count_1d`        BIGINT COMMENT '下单人数'
) COMMENT '各品牌商品最近1天下单统计'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_order_stats_by_tm_1d/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 将最近1天的统计结果保存到1d表中
insert overwrite table dws_order_stats_by_tm_1d partition (dt = '2022-06-08')
select
    `tm_id`                  ,-- STRING COMMENT '品牌ID',
    `tm_name`                ,-- STRING COMMENT '品牌名称',
    count(distinct order_id) `order_count_1d`            ,-- BIGINT COMMENT '下单数',
    count(distinct user_id) `order_user_count_1d`       -- BIGINT COMMENT '下单人数'
from (
    select
        order_id,
        user_id,
        sku_id
    from dwd_trade_order_detail_inc
    where dt = '2022-06-08'
) od left join (
    select
        id,
        tm_id,
        tm_name
    from dim_sku_full
    where dt = '2022-06-08'
) sku on od.sku_id = sku_id
group by tm_id, tm_name;

-- 从1d表中获取最近1天，最近7天，最近30天数据，保存到ADS层的表中
insert overwrite table ads_order_stats_by_tm
select * from ads_order_stats_by_tm
union
select * from (
-- 最近1天
select
    '2022-06-08',
    1,
    tm_id,
    tm_name,
    order_count_1d,
    order_user_count_1d
from dws_order_stats_by_tm_1d
where dt = '2022-06-08'
union all
-- 最近7天
select
    '2022-06-08',
    7,
    tm_id,
    tm_name,
    sum(order_count_1d),
    sum(order_user_count_1d)
from dws_order_stats_by_tm_1d
where dt >= date_sub('2022-06-08', 6) and dt <= '2022-06-08'
group by tm_id, tm_name
union all
-- 最近30天
select
    '2022-06-08',
    30,
    tm_id,
    tm_name,
    sum(order_count_1d),
    sum(order_user_count_1d)
from dws_order_stats_by_tm_1d
where dt >= date_sub('2022-06-08', 29) and dt <= '2022-06-08'
group by tm_id, tm_name) t;

-- 当前的问题：读三次相同的数据，使用在三个不同的场景
-- 解决思路：读一次数据，使用在三个不同的场景
    -- 使用炸裂函数
select
    *
from (
    select 'a' name
) t lateral view explode(array(1, 7, 30)) tmp as name1
where name1 != 7;

/*
        -- 1d表中的数据
        zhangsan    2022-06-08    order
        zhangsan    2022-06-07    order
        zhangsan    2022-06-06    order
        zhangsan    2022-06-05    order
        zhangsan    2022-06-04    order
        zhangsan    2022-06-03    order
        zhangsan    2022-06-02    order
        zhangsan    2022-06-01    order

        -- 最近1天
        zhangsan    2022-06-08    order
        -- 最近7天
        zhangsan    2022-06-08    order
        zhangsan    2022-06-07    order
        zhangsan    2022-06-06    order
        zhangsan    2022-06-05    order
        zhangsan    2022-06-04    order
        zhangsan    2022-06-03    order
        zhangsan    2022-06-02    order
        -- 最近30天
        zhangsan    2022-06-08    order
        zhangsan    2022-06-07    order
        zhangsan    2022-06-06    order
        zhangsan    2022-06-05    order
        zhangsan    2022-06-04    order
        zhangsan    2022-06-03    order
        zhangsan    2022-06-02    order
        zhangsan    2022-06-01    order

        1. 获取时间范围最大的数据集
        zhangsan    2022-06-08    order
        zhangsan    2022-06-07    order
        zhangsan    2022-06-06    order
        zhangsan    2022-06-05    order
        zhangsan    2022-06-04    order
        zhangsan    2022-06-03    order
        zhangsan    2022-06-02    order
        zhangsan    2022-06-01    order
        2. 奖查询的数据集在内存中进行炸裂操作(3)，变成多份
        zhangsan    2022-06-08    order     1
        zhangsan    2022-06-07    order     1
        zhangsan    2022-06-06    order     1
        zhangsan    2022-06-05    order     1
        zhangsan    2022-06-04    order     1
        zhangsan    2022-06-03    order     1
        zhangsan    2022-06-02    order     1
        zhangsan    2022-06-01    order     1
        zhangsan    2022-06-08    order     7
        zhangsan    2022-06-07    order     7
        zhangsan    2022-06-06    order     7
        zhangsan    2022-06-05    order     7
        zhangsan    2022-06-04    order     7
        zhangsan    2022-06-03    order     7
        zhangsan    2022-06-02    order     7
        zhangsan    2022-06-01    order     7
        zhangsan    2022-06-08    order     30
        zhangsan    2022-06-07    order     30
        zhangsan    2022-06-06    order     30
        zhangsan    2022-06-05    order     30
        zhangsan    2022-06-04    order     30
        zhangsan    2022-06-03    order     30
        zhangsan    2022-06-02    order     30
        zhangsan    2022-06-01    order     30
        3. 将炸裂后的数据进行筛选过滤，保留有效数据
        zhangsan    2022-06-08    order     1     (OK)
        zhangsan    2022-06-07    order     1     (X)
        zhangsan    2022-06-06    order     1     (X)
        zhangsan    2022-06-05    order     1     (X)
        zhangsan    2022-06-04    order     1     (X)
        zhangsan    2022-06-03    order     1     (X)
        zhangsan    2022-06-02    order     1     (X)
        zhangsan    2022-06-01    order     1     (X)
        zhangsan    2022-06-08    order     7     (OK)
        zhangsan    2022-06-07    order     7     (OK)
        zhangsan    2022-06-06    order     7     (OK)
        zhangsan    2022-06-05    order     7     (OK)
        zhangsan    2022-06-04    order     7     (OK)
        zhangsan    2022-06-03    order     7     (OK)
        zhangsan    2022-06-02    order     7     (OK)
        zhangsan    2022-06-01    order     7     (X)
        zhangsan    2022-06-08    order     30    (OK)
        zhangsan    2022-06-07    order     30    (OK)
        zhangsan    2022-06-06    order     30    (OK)
        zhangsan    2022-06-05    order     30    (OK)
        zhangsan    2022-06-04    order     30    (OK)
        zhangsan    2022-06-03    order     30    (OK)
        zhangsan    2022-06-02    order     30    (OK)
        zhangsan    2022-06-01    order     30    (OK)
        4.将过滤后的数据按照标记进行分组，然后统计
        zhangsan    2022-06-08    order     1     (OK)

        zhangsan    2022-06-08    order     7     (OK)
        zhangsan    2022-06-07    order     7     (OK)
        zhangsan    2022-06-06    order     7     (OK)
        zhangsan    2022-06-05    order     7     (OK)
        zhangsan    2022-06-04    order     7     (OK)
        zhangsan    2022-06-03    order     7     (OK)
        zhangsan    2022-06-02    order     7     (OK)

        zhangsan    2022-06-08    order     30    (OK)
        zhangsan    2022-06-07    order     30    (OK)
        zhangsan    2022-06-06    order     30    (OK)
        zhangsan    2022-06-05    order     30    (OK)
        zhangsan    2022-06-04    order     30    (OK)
        zhangsan    2022-06-03    order     30    (OK)
        zhangsan    2022-06-02    order     30    (OK)
        zhangsan    2022-06-01    order     30    (OK)
 */
insert overwrite table ads_order_stats_by_tm
select * from ads_order_stats_by_tm
union
select
    '2022-06-08',
    days,
    tm_id,
    tm_name,
    sum(order_count_1d),
    sum(order_user_count_1d)
from dws_order_stats_by_tm_1d lateral view explode(`array`(1, 7, 30)) tmp as days
where dt >= date_sub('2022-06-08', days - 1) and dt <= '2022-06-08'
group by days, tm_id, tm_name

DROP TABLE IF EXISTS dws_order_stats_by_tm_1d;
CREATE EXTERNAL TABLE dws_order_stats_by_tm_1d
(
    `tm_id`                   STRING COMMENT '品牌ID',
    `tm_name`                 STRING COMMENT '品牌名称',
    `order_count_1d`             BIGINT COMMENT '下单数',
    `order_user_count_1d`        BIGINT COMMENT '下单人数'
) COMMENT '各品牌商品最近1天下单统计'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_order_stats_by_tm_1d/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

insert overwrite table dws_order_stats_by_tm_1d partition (dt = '2022-06-08')
select
    `tm_id`                  ,-- STRING COMMENT '品牌ID',
    `tm_name`                ,-- STRING COMMENT '品牌名称',
    count(distinct order_id) `order_count_1d`            ,-- BIGINT COMMENT '下单数',
    count(distinct user_id) `order_user_count_1d`       -- BIGINT COMMENT '下单人数'
from (
    select
        order_id,
        user_id,
        sku_id
    from dwd_trade_order_detail_inc
    where dt = '2022-06-08'
) od left join (
    select
        id,
        tm_id,
        tm_name
    from dim_sku_full
    where dt = '2022-06-08'
) sku on od.sku_id = sku_id
group by tm_id, tm_name;


DROP TABLE IF EXISTS dws_order_stats_by_tm_7d;
CREATE EXTERNAL TABLE dws_order_stats_by_tm_7d
(
    `tm_id`                   STRING COMMENT '品牌ID',
    `tm_name`                 STRING COMMENT '品牌名称',
    `order_count_7d`             BIGINT COMMENT '下单数',
    `order_user_count_7d`        BIGINT COMMENT '下单人数'
) COMMENT '各品牌商品最近7天下单统计'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_order_stats_by_tm_7d/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

insert overwrite table dws_order_stats_by_tm_7d partition (dt = '2022-06-08')
select
    tm_id,
    tm_name,
    sum(order_count_1d) order_count_7d,
    sum(order_user_count_1d) order_user_count_7d
from dws_order_stats_by_tm_1d
where dt >= date_sub('2022-06-08', 6) and dt <= '2022-06-08'
group by tm_id, tm_name;

DROP TABLE IF EXISTS dws_order_stats_by_tm_30d;
CREATE EXTERNAL TABLE dws_order_stats_by_tm_30d
(
    `tm_id`                   STRING COMMENT '品牌ID',
    `tm_name`                 STRING COMMENT '品牌名称',
    `order_count_30d`             BIGINT COMMENT '下单数',
    `order_user_count_30d`        BIGINT COMMENT '下单人数'
) COMMENT '各品牌商品最近30天下单统计'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_order_stats_by_tm_30d/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

insert overwrite table dws_order_stats_by_tm_30d partition (dt = '2022-06-08')
select
    tm_id,
    tm_name,
    sum(order_count_1d) order_count_30d,
    sum(order_user_count_1d) order_user_count_30d
from dws_order_stats_by_tm_1d
where dt >= date_sub('2022-06-08', 29) and dt <= '2022-06-08'
group by tm_id, tm_name;

--------------------------------------------------------------------------------
-- nd表
    -- 就是封装了7、30天的统计结果
DROP TABLE IF EXISTS dws_order_stats_by_tm_nd;
CREATE EXTERNAL TABLE dws_order_stats_by_tm_nd
(
    `tm_id`                   STRING COMMENT '品牌ID',
    `tm_name`                 STRING COMMENT '品牌名称',
    `order_count_7d`             BIGINT COMMENT '下单数',
    `order_user_count_7d`        BIGINT COMMENT '下单人数',
    `order_count_30d`             BIGINT COMMENT '下单数',
    `order_user_count_30d`        BIGINT COMMENT '下单人数'
) COMMENT '各品牌商品最近n天下单统计'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_order_stats_by_tm_nd/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

insert overwrite table dws_order_stats_by_tm_nd partition (dt = '2022-06-08')
select
    tm_id,
    tm_name,
    sum(if (dt >= date_sub('2022-06-08', 6), order_count_1d, 0)) order_count_7d,
    sum(if (dt >= date_sub('2022-06-08', 6), order_user_count_1d, 0)) order_user_count_7d,
    sum(order_count_1d) order_count_30d,
    sum(order_user_count_1d) order_user_count_30d
from dws_order_stats_by_tm_1d
where dt >= date_sub('2022-06-08', 29) and dt <= '2022-06-08'
group by tm_id, tm_name;

------------------------------------------------------------------------------------
-- 假设已经将数据封装为1d表和nd表，如何计算最终结果
DROP TABLE IF EXISTS ads_order_stats_by_tm;
CREATE EXTERNAL TABLE ads_order_stats_by_tm
(
    `dt`                      STRING COMMENT '统计日期',
    `recent_days`             BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `tm_id`                   STRING COMMENT '品牌ID',
    `tm_name`                 STRING COMMENT '品牌名称',
    `order_count`             BIGINT COMMENT '下单数',
    `order_user_count`        BIGINT COMMENT '下单人数'
) COMMENT '各品牌商品下单统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_order_stats_by_tm/';

insert overwrite table ads_order_stats_by_tm
select * from ads_order_stats_by_tm
union
    select * from (
    select
        '2022-06-08',
        1,
        tm_id,
        tm_name,
        order_count_1d,
        order_user_count_1d
    from dws_order_stats_by_tm_1d
    where dt = '2022-06-08'
    union all
    select
        '2022-06-08',
        7,
        tm_id,
        tm_name,
        order_count_7d,
        order_user_count_7d
    from dws_order_stats_by_tm_nd
    where dt = '2022-06-08'
    union all
    select
    '2022-06-08',
    30,
    tm_id,
    tm_name,
    order_count_30d,
    order_user_count_30d
from dws_order_stats_by_tm_nd
where dt = '2022-06-08'
) t;

-- 新增下单用户统计
    -- 以前这个用户没有下过订单，最近1天，第一回下订单，称之为新增下单用户
    -- 以前这个用户没有下过订单，最近7天，第一回下订单，称之为新增下单用户
    -- 以前这个用户没有下过订单，最近30天，第一回下订单，称之为新增下单用户
    -- 建表语句
DROP TABLE IF EXISTS ads_new_order_user_stats;
CREATE EXTERNAL TABLE ads_new_order_user_stats
(
    `dt`                   STRING COMMENT '统计日期',
    `recent_days`          BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `new_order_user_count` BIGINT COMMENT '新增下单人数'
) COMMENT '新增下单用户统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_new_order_user_stats/';

-- 新增：以前没做过，今天是第一回
    -- 判断1天前用户没有出现过
insert overwrite table ads_new_order_user_stats
select * from ads_new_order_user_stats
union
    select * from (
    select
        '2022-06-08',
        1,
        count(distinct  user_id)
    from dwd_trade_order_detail_inc
    where dt = '2022-06-08'
    and user_id not in (
        select
            *
        from dwd_trade_order_detail_inc
        where dt < '2022-06-08'
    )
    union all
    select
        '2022-06-08',
        7,
        count(distinct  user_id)
    from dwd_trade_order_detail_inc
    where dt >= date_sub('2022-06-08', 6) and dt <= '2022-06-08'
    and user_id not in (
        select
            *
        from dwd_trade_order_detail_inc
        where dt < date_sub('2022-06-08', 6)
    )
    union all
    select
    '2022-06-08',
    30,
    count(distinct  user_id)
from dwd_trade_order_detail_inc
where dt >= date_sub('2022-06-08', 29) and dt <= '2022-06-08'
and user_id not in (
    select
        *
    from dwd_trade_order_detail_inc
    where dt < date_sub('2022-06-08', 29)
)
) t;

-- 需求：统计最近1天，7天，30天新增注册用户
DROP TABLE IF EXISTS ads_new_reg_user_stats;
CREATE EXTERNAL TABLE ads_new_reg_user_stats
(
    `dt`                   STRING COMMENT '统计日期',
    `recent_days`          BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `new_reg_user_count` BIGINT COMMENT '新增下单人数'
) COMMENT '新增注册用户统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_new_reg_user_stats/';

insert overwrite table ads_new_reg_user_stats
select * from ads_new_reg_user_stats
union
select * from (
    select '2022-06-08',
                      1,
                      count(*)
               from dwd_user_register_inc
               where dt = '2022-06-08'
    union all
    select '2022-06-08',
          7,
          count(*)
   from dwd_user_register_inc
   where dt >= date_sub('2022-06-08', 6)
     and dt <= '2022-06-08'
    union all
    select '2022-06-08',
                      30,
                      count(*)
               from dwd_user_register_inc
               where dt >= date_sub('2022-06-08', 29)
                 and dt <= '2022-06-08'
) t;

-- 需求：统计最近1天，7天，30天新增下单用户（难）
    -- 新增下单（用户第一次下单）
-- 需求：统计最近1天，7天，30天新增注册用户（简单）
    -- 新增注册（用户第一次注册）

---------------------------------------------------------------------------------
-- 如果将一个表的所有数据获取之后再进行统计，那么这张表称为历史至今表，一般称之为td表
DROP TABLE IF EXISTS dws_first_order_stats;
CREATE EXTERNAL TABLE dws_first_order_stats
(
    `user_id`          BIGINT COMMENT '用户ID',
    `first_order_date` BIGINT COMMENT '用户首次下单时间'
) COMMENT '用户首次下单统计'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_first_order_stats/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 首日
-- all table data
insert overwrite table dws_first_order_stats partition (dt = '2022-06-08')
select
    user_id,
    min(date_id) first_order_date
from dwd_trade_order_detail_inc
group by user_id;

-- 2022-06-08 result + 2022-06-09 data => final result
-- insert overwrite table dws_first_order_stats partition (dt = '2022-06-09')
-- select
--     user_id,
--     min(date_id) first_order_date
-- from dwd_trade_order_detail_inc
-- group by user_id

-- 每日
insert overwrite table dws_first_order_stats partition (dt = '2022-06-09')
select
    user_id,
    min(first_order_date)
from (
    select
        user_id,
        first_order_date
    from dws_first_order_stats
    where dt = date_sub('2022-06-09', 1)
    union all
    select
        user_id,
        '2022-06-09'
    from dwd_trade_order_detail_inc
    where dt = '2022-06-09'
) t group by user_id;

---------------------------------------------------------------------------
insert overwrite table ads_new_order_user_stats
select * from ads_new_order_user_stats
union
select * from (
    select '2022-06-08',
                      1,
                      count(*)
               from dws_first_order_stats
               where dt = '2022-06-08'
                 and first_order_date = '2022-06-08'
    union all
    -- dt表示统计时间，而不是业务时间
    -- 统计周期其实指的是业务时间范围，所以条件判断中不能使用dt字段进行范围的查询
    select '2022-06-08',
          7,
          count(*)
   from dws_first_order_stats
   where dt = '2022-06-08'
     and first_order_date >= date_sub('2022-06-08', 6)
     and first_order_date <= '2022-06-08'
    union all
    select '2022-06-08',
                      30,
                      count(*)
               from dws_first_order_stats
               where dt = '2022-06-08'
                 and first_order_date >= date_sub('2022-06-08', 29)
                 and first_order_date <= '2022-06-08'
) t;

-------------------------------------------------------------------
-- 之前设计的表是有问题的
    -- 1d表
    -- nd表
    -- td表
-------------------------------------------------------------------
/*
        zhangsan    2022-06-08      order
        zhangsan    2022-06-07      order
        zhangsan    2022-06-06      order
        zhangsan    2022-06-05      order
        zhangsan    2022-06-04      order
        zhangsan    2022-06-03      order
        zhangsan    2022-06-02      order
        zhangsan    2022-06-01      order

        1d:
        2022-06-08  1   1
        2022-06-07  1   1
        2022-06-06  1   1
        2022-06-05  1   1
        2022-06-04  1   1
        2022-06-03  1   1
        2022-06-02  1   1
        2022-06-01  1   1

        nd(7d):
        2022-06-(02~08)  sum(order_id)7   sum(user_id)7

        dws层表的字段在预统计时，如果字段可以跨越天，那么就不能在每天中统计
        因为最终统计需要指定的字段，但是提前聚合不能对这个字段做统计，为了避免数据丢失，需要在我们的表中增加这个字段，而不是统计这个字段

 */
DROP TABLE IF EXISTS dws_order_stats_by_tm_1d_a;
CREATE EXTERNAL TABLE dws_order_stats_by_tm_1d_a
(
    `tm_id`                   STRING COMMENT '品牌ID',
    `tm_name`                 STRING COMMENT '品牌名称',
    `user_id`                 BIGINT COMMENT '用户ID',
    `order_count_1d`          BIGINT COMMENT '下单数'
) COMMENT '各品牌商品最近1天下单统计'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_order_stats_by_tm_1d_a/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

insert overwrite table dws_order_stats_by_tm_1d_a partition (dt = '2022-06-08')
select
    tm_id,
    tm_name,
    user_id,
    count(distinct order_id)
from (
    select
        order_id,
        sku_id,
        user_id
    from dwd_trade_order_detail_inc
    where dt = '2022-06-08'
) od
left join (
    select
        id,
        tm_id,
        tm_name
    from dim_sku_full
    where dt = '2022-06-08'
) sku on od.sku_id = sku.id
group by user_id, tm_id, tm_name;

select
    '2022-06-08',
    1,
    tm_id,
    tm_name,
    sum(order_count_1d),
    count(user_id)
from dws_order_stats_by_tm_1d_a
where dt = '2022-06-08'
group by tm_id, tm_name;

-- dws层设计目的就是简化计算，提前进行预聚合的操作
    -- 底层实现依然是将数据写入文件，再读取文件，性能一定会受到影响
    -- 如果表的设计可以在多个地方使用，那么就可以提高效率

DROP TABLE IF EXISTS dws_order_stats_by_tm_1d_a;
CREATE EXTERNAL TABLE dws_order_stats_by_tm_1d_a
(
    `tm_id`                   STRING COMMENT '品牌ID',
    `tm_name`                 STRING COMMENT '品牌名称',
    `user_id`                 BIGINT COMMENT '用户ID',
    `order_count_1d`          BIGINT COMMENT '下单数'
) COMMENT '各品牌商品最近1天下单统计'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_order_stats_by_tm_1d_a/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

DROP TABLE IF EXISTS dws_order_stats_by_category_1d_a;
CREATE EXTERNAL TABLE dws_order_stats_by_category_1d_a
(
    `category1_id`                   STRING COMMENT '品牌ID',
    `category1_name`                 STRING COMMENT '品牌名称',
    `user_id`                 BIGINT COMMENT '用户ID',
    `order_count_1d`          BIGINT COMMENT '下单数'
) COMMENT '各品牌商品最近1天下单统计'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_order_stats_by_category_1d_a/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

DROP TABLE IF EXISTS dws_order_stats_by_sku_1d_a;
CREATE EXTERNAL TABLE dws_order_stats_by_sku_1d_a
(
    `sku_id`                  STRING COMMENT '品牌ID',
    `tm_id`                   STRING COMMENT '品牌ID',
    `tm_name`                 STRING COMMENT '品牌名称',
    `category1_id`                   STRING COMMENT '品牌ID',
    `category1_name`                 STRING COMMENT '品牌名称',
    `user_id`                 BIGINT COMMENT '用户ID',
    `order_count_1d`          BIGINT COMMENT '下单数'
) COMMENT '各品牌商品最近1天下单统计'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_order_stats_by_sku_1d_a/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- ADS层
    -- Application Data Service
        -- 统计结果数据量比较少，所以建表语句没有分区
        -- 统计结果为最终的结果，无需进一步的分析，所以无需列式存储和snappy压缩
        -- 因为存储的数据结果需要同步到MySQL给第三方可视化平台使用，所以一般采用tsv格式（行式存储）
        -- 表的字段不需要很多，满足客户需求即可

-- 各渠道流量统计
    -- 数据源：dws_traffic_session_page_view_1d
    -- 建表语句：
DROP TABLE IF EXISTS ads_traffic_stats_by_channel;
CREATE EXTERNAL TABLE ads_traffic_stats_by_channel
(
    `dt`               STRING COMMENT '统计日期',
    `recent_days`      BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `channel`          STRING COMMENT '渠道',
    `uv_count`         BIGINT COMMENT '访客人数',
    `avg_duration_sec` BIGINT COMMENT '会话平均停留时长，单位为秒',
    `avg_page_count`   BIGINT COMMENT '会话平均浏览页面数',
    `sv_count`         BIGINT COMMENT '会话数',
    `bounce_rate`      DECIMAL(16, 2) COMMENT '跳出率'
) COMMENT '各渠道流量统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_traffic_stats_by_channel/';

insert overwrite table ads_traffic_stats_by_channel
select *
from ads_traffic_stats_by_channel
union
select * from (
    -- 最近1天
    select
        '2022-06-08',
        1,
        channel,
        count(distinct mid_id),
        avg(during_time_1d),
        avg(page_count_1d/1000),
        count(session_id),
    --     count(if(page_count_1d = 1, session_id, null)) / count(session_id)
        sum(if(page_count_1d = 1, 1, 0)) / count(session_id)
    from dws_traffic_session_page_view_1d
    where dt = '2022-06-08'
    group by channel
    union all
    -- 最近7天
    select
        '2022-06-08',
        7,
        channel,
        count(distinct mid_id),
        avg(during_time_1d),
        avg(page_count_1d/1000),
        count(session_id),
    --     count(if(page_count_1d = 1, session_id, null)) / count(session_id)
        sum(if(page_count_1d = 1, 1, 0)) / count(session_id)
    from dws_traffic_session_page_view_1d
    where dt >= date_sub('2022-06-08', 6) and dt <= '2022-06-08'
    group by channel
    union all
    -- 最近30天
    select
        '2022-06-08',
        30,
        channel,
        count(distinct mid_id),
        avg(during_time_1d),
        avg(page_count_1d/1000),
        count(session_id),
    --     count(if(page_count_1d = 1, session_id, null)) / count(session_id)
        sum(if(page_count_1d = 1, 1, 0)) / count(session_id)
    from dws_traffic_session_page_view_1d
    where dt >= date_sub('2022-06-08', 29) and dt <= '2022-06-08'
    group by channel
) t;

-- 炸裂函数
    -- 1. 将最大范围的数据获取到
    -- 2. 将数据炸裂成多条数据，具体的条数取决于数据应用场景，而且需要给不同的场景增加标记
    -- 3. 将炸裂后的数据根据条件进行筛选过滤
    -- 4. 根据数据增加的标记，对数据进行分组聚合
insert overwrite table ads_traffic_stats_by_channel
select *
from ads_traffic_stats_by_channel
union
select
        '2022-06-08',
        days,
        channel,
        count(distinct mid_id),
        avg(during_time_1d),
        avg(page_count_1d/1000),
        count(session_id),
        sum(if(page_count_1d = 1, 1, 0)) / count(session_id)
    from dws_traffic_session_page_view_1d lateral view explode(array(1, 7, 30)) tmp as days
    where dt >= date_sub('2022-06-08', days - 1) and dt <= '2022-06-08'
    group by channel, days;

-- 路径分析
    -- 数据源：dwd_traffic_page_view_inc
    -- 建表语句
DROP TABLE IF EXISTS ads_page_path;
CREATE EXTERNAL TABLE ads_page_path
(
    `dt`          STRING COMMENT '统计日期',
    `source`      STRING COMMENT '跳转起始页面ID',
    `target`      STRING COMMENT '跳转终到页面ID',
    `path_count`  BIGINT COMMENT '跳转次数'
) COMMENT '页面浏览路径分析'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_page_path/';

-- 问题
    -- 1. 桑基图(Sankey)不允许出现环状图形
        -- 在页面前增加流程标记，区分页面跳转顺序
    -- 2. 需要统计页面离开的情况
        -- 2.1 当前统计的是页面路径跳转，前提条件是同一个会话
        -- 2.2 在同一个会话中给每一个页面额外增加一个标记，用于表示跳转后的页面，如果跳转后的页面为null，表示离开
/*
                                      source     target      num
        zhangsan    AAAA    null      home1      list2        1
        zhangsan    AAAA    home1     list2      detail3      2
        zhangsan    AAAA    list2     detail3    cart4        3
        zhangsan    AAAA    detail3   cart4      login5       4
        zhangsan    AAAA    cart4     login5     cart6        5
        zhangsan    AAAA    login5    cart6      order7       6
        zhangsan    AAAA    cart6     order7     payment8     7
        zhangsan    AAAA    order7    payment8   out9         8
 */
insert overwrite table ads_page_path
select * from ads_page_path
union
select
    '2022-06-08',
    last_page_id source,
    page_id target,
    count(*) path_count
from dwd_traffic_page_view_inc
where dt = '2022-06-08'
group by last_page_id, page_id;
-------------------------------------------------------
insert overwrite table ads_page_path
select * from ads_page_path
union
select
    '2022-06-08',
    source,
    target,
    count(*)
from (
    select
        concat('step-', rn, ':', source) source,
        concat('step-', (rn+1), ':', target) target
    from (
        select
            page_id source,
            -- 开窗函数
            lead(page_id, 1, 'out') over (partition by session_id order by view_time) target,
            row_number() over (partition by session_id order by view_time) rn
        from dwd_traffic_page_view_inc
        where dt = '2022-06-08'
    ) t1
) t group by source, target;

-- 用户变动统计
/*
    流失用户数量：2022-06-08(1)
        zhangsan: 08 07 06 05 04 03 02 01(login) 31
        lisi    : 08 07 06 05 04 03 02(login) 01 31
        wangwu  : 08 07 06 05 04 03 02 01 31(login)
        逻辑：用户的末次登录时间是7天前当天 = 8 - 7
    回流用户数：2022-06-08(2)
        zhangsan: 08(login) 07 06 05 04 03 02 01(login) 31
        lisi    : 08 07 06 05 04 03 02(login) 01 31
        wangwu  : 08(login) 07 06 05 04 03 02 01 31(login)
        zhaoliu : 08(login) 07 06 05 04 03 02 01 31 30(login)
        逻辑：今天登录 & 上一次登录时间为7天前 < 8 - 7
            上一次登录其实就是昨天统计的末次登录
 */

DROP TABLE IF EXISTS ads_user_change;
CREATE EXTERNAL TABLE ads_user_change
(
    `dt`               STRING COMMENT '统计日期',
    `user_churn_count` BIGINT COMMENT '流失用户数',
    `user_back_count`  BIGINT COMMENT '回流用户数'
) COMMENT '用户变动统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_user_change/';

-- 统计日期和业务日期的区别
    -- 统计日期(dt)：数据在哪一天统计的
-- 8号统计的，1号登录的
insert overwrite table ads_user_change
select * from ads_user_change
union
select
    '2022-06-08',
    user_churn_count,
    user_back_count
from (
    select
        1 a,
        count(*) user_churn_count
    from dws_user_user_login_td
    where dt = '2022-06-08'
    and login_date_last = date_sub('2022-06-08', 7)
) churn
join (
    select
        1 b,
        count(*) user_back_count
    from (
        select
            user_id,
            login_date_last
        from dws_user_user_login_td
        where dt = '2022-06-08'
        and login_date_last = '2022-06-08'
    ) new
    join (
        select
            user_id,
            login_date_last
        from dws_user_user_login_td
        where dt = date_sub('2022-06-08', 1)
    ) old on new.user_id = old.user_id
    where datediff(new.login_date_last, old.login_date_last) > 7
) back on churn.a = back.b;

-- 用户留存率
/*
        2022-06-08
            zhangsan  reg
            lisi      reg
            wangwu    reg
        2022-06-09 (1日留存率 1/3 33.3%)
            zhangsan  login
            zhaoliu   login
        2022-06-10 (2日留存率 2/3 66.6%)
            zhangsan  login
            lisi      login
            zhaoliu   login
        2022-06-11 (3日留存率 3/3 100%)
            zhangsan  login
            lisi      login
            zhaoliu   login
        ...

        06-xx(login) / 06-08(reg)
        ---------------------------------------------------
        2022-06-08(1日留存率 100%; 2日留存率 100%; 3日留存率 0%)
            zhangsan  login
            lisi      login
        2022-06-07
            zhansan   reg
        2022-06-06
            lisi      reg
        2022-06-05
            wangwu    reg
            zhaoliu   reg

        规律：
            获取1天前注册用户数量 + 获取1天前注册且当天登录用户数量
            获取2天前注册用户数量 + 获取2天前注册且当天登录用户数量
            获取3天前注册用户数量 + 获取3天前注册且当天登录用户数量
        ...
        用户登录历史至今表(td)
            -- user_id + first_date + last_date
 */
DROP TABLE IF EXISTS ads_user_retention;
CREATE EXTERNAL TABLE ads_user_retention
(
    `dt`              STRING COMMENT '统计日期',
    `create_date`     STRING COMMENT '用户新增日期',
    `retention_day`   INT COMMENT '截至当前日期留存天数',
    `retention_count` BIGINT COMMENT '留存用户数量',
    `new_user_count`  BIGINT COMMENT '新增用户数量',
    `retention_rate`  DECIMAL(16, 2) COMMENT '留存率'
) COMMENT '用户留存率'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_user_retention/';

-- 1日留存率
select
    '2022-06-08',
    date_sub('2022-06-08', 1),
    1,
    sum(if(login_date_last = '2022-06-08', 1, 0)) retention_count,
    count(*) new_user_count,
    sum(if(login_date_last = '2022-06-08', 1, 0)) / count(*) * 100
from dws_user_user_login_td
where dt = '2022-06-08'
and login_date_first = date_sub('2022-06-08', 1);

-- 2日留存率
select
    '2022-06-08',
    date_sub('2022-06-08', 2),
    2,
    sum(if(login_date_last = '2022-06-08', 1, 0)) retention_count,
    count(*) new_user_count,
    sum(if(login_date_last = '2022-06-08', 1, 0)) / count(*) * 100
from dws_user_user_login_td
where dt = '2022-06-08'
and login_date_first = date_sub('2022-06-08', 2);

-- 3日留存率
select
    '2022-06-08',
    date_sub('2022-06-08', 3),
    3,
    sum(if(login_date_last = '2022-06-08', 1, 0)) retention_count,
    count(*) new_user_count,
    sum(if(login_date_last = '2022-06-08', 1, 0)) / count(*) * 100
from dws_user_user_login_td
where dt = '2022-06-08'
and login_date_first = date_sub('2022-06-08', 3);
------------------------------------------------------------------
insert overwrite table ads_user_retention
select * from ads_user_retention
union
select
    '2022-06-08',
    login_date_first,
    datediff('2022-06-08', login_date_first),
    sum(if(login_date_last = '2022-06-08', 1, 0)) retention_count,
    count(*) new_user_count,
    sum(if(login_date_last = '2022-06-08', 1, 0)) / count(*) * 100
from dws_user_user_login_td
where dt = '2022-06-08'
and login_date_first >= date_sub('2022-06-08', 7) and login_date_first < '2022-06-08'
group by login_date_first;

-- 用户新增活跃统计
DROP TABLE IF EXISTS ads_user_stats;
CREATE EXTERNAL TABLE ads_user_stats
(
    `dt`                STRING COMMENT '统计日期',
    `recent_days`       BIGINT COMMENT '最近n日,1:最近1日,7:最近7日,30:最近30日',
    `new_user_count`    BIGINT COMMENT '新增用户数',
    `active_user_count` BIGINT COMMENT '活跃用户数'
) COMMENT '用户新增活跃统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_user_stats/';

insert overwrite table ads_user_stats
select * from ads_user_stats
union
select * from (
    -- 最近1日
    select
        '2022-06-08',
        1,
        sum(if(login_date_first = '2022-06-08', 1, 0)) new_user_count,
        count(*) active_user_count
    from dws_user_user_login_td
    where dt = '2022-06-08'
    and login_date_last = date_sub('2022-06-08', 1)
    union all
    -- 最近7日
    select
        '2022-06-08',
        7,
        sum(if(login_date_first >= date_sub('2022-06-08', 6), 1, 0)) new_user_count,
        count(*) active_user_count
    from dws_user_user_login_td
    where dt = '2022-06-08'
    and login_date_last >= date_sub('2022-06-08', 6) and  login_date_last <= '2022-06-08'
    union all
    -- 最近30日
    select
        '2022-06-08',
        30,
        sum(if(login_date_first >= date_sub('2022-06-08', 29), 1, 0)) new_user_count,
        count(*) active_user_count
    from dws_user_user_login_td
    where dt = '2022-06-08'
    and login_date_last >= date_sub('2022-06-08', 29) and  login_date_last <= '2022-06-08'
) t;

-- 炸裂优化
insert overwrite table ads_user_stats
select * from ads_user_stats
union
select
    '2022-06-08',
    days,
    sum(if(login_date_first >= date_sub('2022-06-08', days-1), 1, 0)) new_user_count,
    count(*) active_user_count
from dws_user_user_login_td lateral view explode(array(1, 7, 30)) tmp as days
where dt = '2022-06-08'
and login_date_last >= date_sub('2022-06-08', days-1) and  login_date_last <= '2022-06-08'
group by days;

-- 用户行为漏斗分析
DROP TABLE IF EXISTS ads_user_action;
CREATE EXTERNAL TABLE ads_user_action
(
    `dt`                STRING COMMENT '统计日期',
    `home_count`        BIGINT COMMENT '浏览首页人数',
    `good_detail_count` BIGINT COMMENT '浏览商品详情页人数',
    `cart_count`        BIGINT COMMENT '加购人数',
    `order_count`       BIGINT COMMENT '下单人数',
    `payment_count`     BIGINT COMMENT '支付人数'
) COMMENT '用户行为漏斗分析'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_user_action/';
/*
        dwd
            zhangsan home
            zhangsan home
            zhangsan home
        dws
            zhangsan home 3
            lisi     home 3
 */
 insert overwrite table ads_user_action
 select * from ads_user_action
 union
 select * from (
    select
         '2022-06-08'               ,--STRING COMMENT '统计日期',
        `home_count`        ,--BIGINT COMMENT '浏览首页人数',
        `good_detail_count` ,--BIGINT COMMENT '浏览商品详情页人数',
        `cart_count`        ,--BIGINT COMMENT '加购人数',
        `order_count`       ,--BIGINT COMMENT '下单人数',
        `payment_count`      --BIGINT COMMENT '支付人数'
     from (
        select
            1 a,
            sum(if(page_id = 'home'), 1, 0) home_count,
            sum(if(page_id = 'good_detail'), 1, 0) good_detail_count
        from dws_traffic_page_visitor_page_view_1d
        where dt = '2022-06-08'
        and (page_id = 'home' or page_id = 'good_detail')
    ) pv
    join (
        select
            1 b,
            count(user_id) cart_count
        from dws_trade_user_cart_add_1d
        where dt = '2022-06-08'
     ) cart on pv.a = cart.b
     join (
        select
            1 c,
            count(user_id) order_count
        from dws_trade_user_order_1d
        where dt = '2022-06-08'
     ) oi on cart.b = oi.c
     join (
         select
            1 d,
            count(user_id) payment_count
        from dws_trade_user_payment_1d
        where dt = '2022-06-08'
     ) pay on oi.c = pay.d
) t;

 -- 新增下单用户统计
 DROP TABLE IF EXISTS ads_new_order_user_stats;
CREATE EXTERNAL TABLE ads_new_order_user_stats
(
    `dt`                   STRING COMMENT '统计日期',
    `recent_days`          BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `new_order_user_count` BIGINT COMMENT '新增下单人数'
) COMMENT '新增下单用户统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_new_order_user_stats/';

insert overwrite table ads_new_order_user_stats
select * from ads_new_order_user_stats
union
select * from (
    -- 最近1天
    select
        '2022-06-08',
        1,
        count(*) new_order_user_count
    from dws_trade_user_order_td
    where dt = '2022-06-08'
    and order_date_first = '2022-06-08'
    union all
    -- 最近7天
    select
        '2022-06-08',
        7,
        count(user_id) new_order_user_count
    from dws_trade_user_order_td
    where dt = '2022-06-08'
    and order_date_first >= date_sub('2022-06-08', 6) and order_date_first <= '2022-06-08'
    union all
    -- 最近30天
    select
        '2022-06-08',
        30,
        count(user_id) new_order_user_count
    from dws_trade_user_order_td
    where dt = '2022-06-08'
    and order_date_first >= date_sub('2022-06-08', 29) and order_date_first <= '2022-06-08'
) t;

-- 优化版
insert overwrite table ads_new_order_user_stats
select * from ads_new_order_user_stats
union
select
    '2022-06-08',
    days,
    count(user_id) new_order_user_count
from dws_trade_user_order_td lateral view explode(array(1, 7, 30)) tmp as days
where dt = '2022-06-08'
and order_date_first >= date_sub('2022-06-08', days-1) and order_date_first <= '2022-06-08'
group by days;

-- 最近7日内连续3日下单用户数
    -- 数据源：dws_trade_user_order_1d
/*
        数据的规律：
            1. 连续三天的基本条件：数据量 >= 3
            2. 连续的第三条数据的时间减去当前时间 = 2
            3. 一个用户可能会重复出现连续3天，所以统计时需要去重

        zhangsan(1)
            2022-06-02 order 2022-06-04 → 2
            2022-06-03 order 2022-06-06 → 3
            2022-06-04 order 2022-06-07 → 3
            2022-06-06 order 2022-06-08 → 2
            2022-06-07 order 9999-12-31 → N
            2022-06-08 order 9999-12-31 → N
        lisi(1)
            2022-06-02 order 2022-06-04 → 2
            2022-06-03 order 2022-06-05 → 2
            2022-06-04 order 2022-06-07 → 3
            2022-06-05 order 2022-06-08 → 3
            2022-06-07 order 9999-12-31 → N
            2022-06-08 order 9999-12-31 → N
        wangwu(0)
            2022-06-02 order 2022-06-05 → 3
            2022-06-03 order 2022-06-06 → 3
            2022-06-05 order 2022-06-08 → 3
            2022-06-06 order 9999-12-31 → N
            2022-06-08 order 9999-12-31 → N
 */
DROP TABLE IF EXISTS ads_order_continuously_user_count;
CREATE EXTERNAL TABLE ads_order_continuously_user_count
(
    `dt`                            STRING COMMENT '统计日期',
    `recent_days`                   BIGINT COMMENT '最近天数,7:最近7天',
    `order_continuously_user_count` BIGINT COMMENT '连续3日下单用户数'
) COMMENT '最近7日内连续3日下单用户数统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_order_continuously_user_count/';

insert overwrite table ads_order_continuously_user_count
select * from ads_order_continuously_user_count
union
select
    '2022-06-08',
    7,
    count(distinct user_id)
from (
    select
        user_id,
        datediff(lead(dt, 2, '9999-12-31') over (partition by user_id order by dt), dt) diff
    from dws_trade_user_order_1d
    where dt >= date_sub('2022-06-08', 6) and dt <= '2022-06-08'
) t where diff = 2;

-- 最近30日各品牌复购率
    -- 复购率
    -- 衍生指标（比例）
        -- N派生指标
            -- 重复购买人数（下单（次数 > 1）人数） / 购买人数（下单人数）
    -- 数据来源
        -- DWS(dws_trade_user_sku_order_1d) (dws_trade_user_sku_order_nd)
        -- DWD(下单) + DIM(SKU)
        -- ODS(X)

DROP TABLE IF EXISTS ads_repeat_purchase_by_tm;
CREATE EXTERNAL TABLE ads_repeat_purchase_by_tm
(
    `dt`                  STRING COMMENT '统计日期',
    `recent_days`       BIGINT COMMENT '最近天数,30:最近30天',
    `tm_id`              STRING COMMENT '品牌ID',
    `tm_name`            STRING COMMENT '品牌名称',
    `order_repeat_rate` DECIMAL(16, 2) COMMENT '复购率'
) COMMENT '最近30日各品牌复购率统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_repeat_purchase_by_tm/';

/*
          user       tm       sku    count
        zhangsan    华为      手环      100
        zhangsan    华为      手机      200
 */

-- 粒度的变化
    -- 数据源粒度：user + sku
    -- 统计粒度：sku
        -- 此时对另外一个粒度的数量统计不需要去重操作

    -- tm > sku
    -- 数据源粒度：user + sku
    -- 统计粒度：user + tm
    -- 如果粒度变粗(sku -> tm)，那么数据就需要进一步聚合

    -- 数据源粒度：user + sku
    -- 统计粒度：tm

-- nd表的问题
    -- nd表计算时可能出现的问题：30天内有数据，7天内没有数据，但是7天和30天会融合成一条
insert overwrite table ads_repeat_purchase_by_tm
select * from ads_repeat_purchase_by_tm
union
select
    '2022-06-08',
    30,
    tm_id,
    tm_name,
    sum(if(order_count > 1, 1, 0)) / count(user_id) * 100
from (
    select
        user_id,
        tm_id,
        tm_name,
        sum(order_count_30d) order_count -- 某一个用户在某一个品牌下单多少次
    from dws_trade_user_sku_order_nd
    where dt = '2022-06-08'
    group by user_id, tm_id, tm_name
) t
group by tm_id, tm_name;

-- 各品牌商品下单统计
    -- DWD + DIM -> 1d
    -- 数据源：dws_trade_user_sku_order_1d dws_trade_user_sku_order_nd
DROP TABLE IF EXISTS ads_order_stats_by_tm;
CREATE EXTERNAL TABLE ads_order_stats_by_tm
(
    `dt`                      STRING COMMENT '统计日期',
    `recent_days`             BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `tm_id`                   STRING COMMENT '品牌ID',
    `tm_name`                 STRING COMMENT '品牌名称',
    `order_count`             BIGINT COMMENT '下单数',
    `order_user_count`        BIGINT COMMENT '下单人数'
) COMMENT '各品牌商品下单统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_order_stats_by_tm/';

-- 最近1天
    -- 数据源粒度：user + sku
    -- 统计粒度：sku
        -- user数量的统计不需要去重
    -- 统计粒度：tm
insert overwrite table ads_order_stats_by_tm
select * from ads_order_stats_by_tm
union
select * from (
    select
        '2022-06-08',
        1,
        tm_id,
        tm_name,
        sum(order_count_1d),
        count(distinct user_id)
    from dws_trade_user_sku_order_1d
    where dt = '2022-06-08'
    group by tm_id, tm_name
    union all
    -- 最近7天
    select
        '2022-06-08',
        7,
        tm_id,
        tm_name,
        sum(order_count_7d),
        count(distinct user_id)
    from dws_trade_user_sku_order_nd
    where dt = '2022-06-08'
    and order_count_7d > 0
    group by tm_id, tm_name
    union all
    -- 最近30天
    select
        '2022-06-08',
        30,
        tm_id,
        tm_name,
        sum(order_count_30d),
        count(distinct user_id)
    from dws_trade_user_sku_order_nd
    where dt = '2022-06-08'
    and order_count_30d > 0
    group by tm_id, tm_name
) t;

insert overwrite table ads_order_stats_by_tm
select * from ads_order_stats_by_tm
union
select * from (
    select '2022-06-08',
                      1,
                      tm_id,
                      tm_name,
                      sum(order_count_1d),
                      count(distinct user_id)
               from dws_trade_user_sku_order_1d
               where dt = '2022-06-08'
               group by tm_id, tm_name
    union all
    select
'2022-06-08',
          days,
          tm_id,
          tm_name,
          sum(order_count),
          count(distinct user_id)
           from (
                 select
                     days,
                     tm_id,
                     tm_name,
                     user_id,
                     if(days = 7, order_count_7d, order_count_30d) order_count
                 from dws_trade_user_sku_order_nd lateral view explode(array(7, 30)) tmp as days
                 where dt = '2022-06-08') t
           where order_count > 0
           group by days, tm_id, tm_name
) t;

-- 各品类商品下单统计
DROP TABLE IF EXISTS ads_order_stats_by_cate;
CREATE EXTERNAL TABLE ads_order_stats_by_cate
(
    `dt`                      STRING COMMENT '统计日期',
    `recent_days`             BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `category1_id`            STRING COMMENT '一级品类ID',
    `category1_name`          STRING COMMENT '一级品类名称',
    `category2_id`            STRING COMMENT '二级品类ID',
    `category2_name`          STRING COMMENT '二级品类名称',
    `category3_id`            STRING COMMENT '三级品类ID',
    `category3_name`          STRING COMMENT '三级品类名称',
    `order_count`             BIGINT COMMENT '下单数',
    `order_user_count`        BIGINT COMMENT '下单人数'
) COMMENT '各品类商品下单统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_order_stats_by_cate/';

insert overwrite table ads_order_stats_by_cate
select * from ads_order_stats_by_cate
union
select * from (
    select
        '2022-06-08',
        1,
        category1_id,
        category1_name,
        category2_id,
        category2_name,
        category3_id,
        category3_name,
        sum(order_count_1d),
        count(distinct user_id)
   from dws_trade_user_sku_order_1d
   where dt = '2022-06-08'
   group by
        category1_id,
        category1_name,
        category2_id,
        category2_name,
        category3_id
    union all
    select
        '2022-06-08',
        days,
        category1_id,
        category1_name,
        category2_id,
        category2_name,
        category3_id,
        category3_name,
        sum(order_count),
        count(distinct user_id)
        from (
             select
                 days,
                 category1_id,
                 category1_name,
                 category2_id,
                 category2_name,
                 category3_id,
                 category3_name,
                 user_id,
                 if(days = 7, order_count_7d, order_count_30d) order_count
             from dws_trade_user_sku_order_nd lateral view explode(array(7, 30)) tmp as days
             where dt = '2022-06-08') t
        where order_count > 0
        group by
            days,
            category1_id,
            category1_name,
            category2_id,
            category2_name,
            category3_id,
            category3_name
) t;

-- 各品类商品购物车存量Top3
    -- 各品类商品
    -- 购物车存量
        -- 周期快照事实表
    -- Top3
    -- 组内排序取TopN
        -- 实现思路
            -- 1. 统计粒度 + 维度 -> 统计销量
            -- 2. 将相同的统计粒度数据分在一个组中（数据不会减少 -> 开窗）
            -- 3. 将组内的统计结果排序（开窗带排序）
            -- 4. 将组内排序结果取前N条（row_number <= N）

/*
        手机  小米13    300
        手机  苹果13    200
        手环  小米9     100
 */

DROP TABLE IF EXISTS ads_sku_cart_num_top3_by_cate;
CREATE EXTERNAL TABLE ads_sku_cart_num_top3_by_cate
(
    `dt`             STRING COMMENT '统计日期',
    `category1_id`   STRING COMMENT '一级品类ID',
    `category1_name` STRING COMMENT '一级品类名称',
    `category2_id`   STRING COMMENT '二级品类ID',
    `category2_name` STRING COMMENT '二级品类名称',
    `category3_id`   STRING COMMENT '三级品类ID',
    `category3_name` STRING COMMENT '三级品类名称',
    `sku_id`         STRING COMMENT 'SKU_ID',
    `sku_name`       STRING COMMENT 'SKU名称',
    `cart_num`       BIGINT COMMENT '购物车中商品数量',
    `rk`             BIGINT COMMENT '排名'
) COMMENT '各品类商品购物车存量Top3'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_sku_cart_num_top3_by_cate/';

insert overwrite table ads_sku_cart_num_top3_by_cate
select * from ads_sku_cart_num_top3_by_cate
union
select
    '2022-06-08',
    category1_id,
    category1_name,
    category2_id,
    category2_name,
    category3_id,
    category3_name,
    sku_id,
    sku_name,
    cart_num,
    rk
from (
    select
        category1_id,
        category1_name,
        category2_id,
        category2_name,
        category3_id,
        category3_name,
        sku_id,
        sku_name,
        cart_num,
        rank() over (partition by category1_id, category2_id, category3_id order by cart_num desc) rk
    from (
        select
            category1_id,
            category1_name,
            category2_id,
            category2_name,
            category3_id,
            category3_name,
            sku_id,
            sku_name,
            cart_num
        from (
            select
                sku_id,
                sum(sku_num) cart_num
            from dwd_trade_cart_full
            where dt = '2022-06-08'
            group by sku_id
        ) cart
        left join (
            select
                id,
                sku_name,
                category1_id,
                category1_name,
                category2_id,
                category2_name,
                category3_id,
                category3_name
            from dim_sku_full
            where dt = '2022-06-08'
        ) sku on cart.sku_id = sku.id
    ) t
) t1 where rk <= 3;

-- 各品牌商品收藏次数Top3
DROP TABLE IF EXISTS ads_sku_favor_count_top3_by_tm;
CREATE EXTERNAL TABLE ads_sku_favor_count_top3_by_tm
(
    `dt`          STRING COMMENT '统计日期',
    `tm_id`       STRING COMMENT '品牌ID',
    `tm_name`     STRING COMMENT '品牌名称',
    `sku_id`      STRING COMMENT 'SKU_ID',
    `sku_name`    STRING COMMENT 'SKU名称',
    `favor_count` BIGINT COMMENT '被收藏次数',
    `rk`          BIGINT COMMENT '排名'
) COMMENT '各品牌商品收藏次数Top3'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_sku_favor_count_top3_by_tm/';

-- 实现思路
    -- 1. （品牌 + 商品） -> 统计收藏次数
    -- 2. 按照品牌分组
    -- 3. 按照次数进行排序（降序）
    -- 4. 取排序后的前3名
-- 粒度的变化
    -- 数据源粒度：sku
    -- 统计粒度：(tm) + sku

insert overwrite table ads_sku_favor_count_top3_by_tm
select * from ads_sku_favor_count_top3_by_tm
union
select
    '2022-06-08',
    tm_id,
    tm_name,
    sku_id,
    sku_name,
    favor_add_count_1d,
    rk
from (
    select
        sku_id,
        sku_name,
        tm_id,
        tm_name,
        favor_add_count_1d,
        rank() over (partition by tm_id order by favor_add_count_1d desc) rk
    from dws_interaction_sku_favor_add_1d
    where dt = '2022-06-08'
) t where rk <= 3;

-- 下单到支付时间间隔平均值
DROP TABLE IF EXISTS ads_order_to_pay_interval_avg;
CREATE EXTERNAL TABLE ads_order_to_pay_interval_avg
(
    `dt`                        STRING COMMENT '统计日期',
    `order_to_pay_interval_avg` BIGINT COMMENT '下单到支付时间间隔平均值,单位为秒'
) COMMENT '下单到支付时间间隔平均值统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_order_to_pay_interval_avg/';

-- 用户年龄平均值：avg(age)
-- 订单下单到支付时间间隔平均值：avg(datediff(支付时间, 下单时间))
-- 累积快照事实表
    -- 1. 时间差问题
        -- datediff：用于天的时间差，不能用于时分秒的差值计算
        -- 时间戳相减 sub -> (string -> long) - (string -> long)
        -- long -> timestamp(string, date) -> date_format
    -- 2. 分区策略
        -- 事实表会以流程中最后一个行为的时间作为分区字段值（收货时间）
insert overwrite table ads_order_to_pay_interval_avg
select * from ads_order_to_pay_interval_avg
union
select
    '2022-06-08',
    avg(to_unix_timestamp(payment_time) - to_unix_timestamp(order_time))
from dwd_trade_trade_flow_acc
where (dt = '2022-06-08' or dt = '9999-12-31') and payment_date_id = '2022-06-08';

-- 各省份交易统计
DROP TABLE IF EXISTS ads_order_by_province;
CREATE EXTERNAL TABLE ads_order_by_province
(
    `dt`                 STRING COMMENT '统计日期',
    `recent_days`        BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `province_id`        STRING COMMENT '省份ID',
    `province_name`      STRING COMMENT '省份名称',
    `area_code`          STRING COMMENT '地区编码',
    `iso_code`           STRING COMMENT '旧版国际标准地区编码，供可视化使用',
    `iso_code_3166_2`    STRING COMMENT '新版国际标准地区编码，供可视化使用',
    `order_count`        BIGINT COMMENT '订单数',
    `order_total_amount` DECIMAL(16, 2) COMMENT '订单金额'
) COMMENT '各省份交易统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_order_by_province/';
-- 粒度没有变化
insert overwrite table ads_order_by_province
select * from ads_order_by_province
union
select
        `dt`                 ,--STRING COMMENT '统计日期',
        1        ,--BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
        `province_id`        ,--STRING COMMENT '省份ID',
        `province_name`      ,--STRING COMMENT '省份名称',
        `area_code`          ,--STRING COMMENT '地区编码',
        `iso_code`           ,--STRING COMMENT '旧版国际标准地区编码，供可视化使用',
        iso_3166_2 `iso_code_3166_2`    ,--STRING COMMENT '新版国际标准地区编码，供可视化使用',
        `order_count_1d`        ,--BIGINT COMMENT '订单数',
        `order_total_amount_1d`  --DECIMAL(16, 2) COMMENT '订单金额'
    from dws_trade_province_order_1d
    where dt = '2022-06-08'
union -- all 报错
select
        `dt`                 ,--STRING COMMENT '统计日期',
        days        ,--BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
        `province_id`        ,--STRING COMMENT '省份ID',
        `province_name`      ,--STRING COMMENT '省份名称',
        `area_code`          ,--STRING COMMENT '地区编码',
        `iso_code`           ,--STRING COMMENT '旧版国际标准地区编码，供可视化使用',
        iso_3166_2 `iso_code_3166_2`    ,--STRING COMMENT '新版国际标准地区编码，供可视化使用',
        if(days = 7, order_count_7d, order_count_30d)        ,--BIGINT COMMENT '订单数',
        if(days = 7, order_total_amount_7d, order_total_amount_30d)  --DECIMAL(16, 2) COMMENT '订单金额'
    from dws_trade_province_order_nd lateral view explode(array(7, 30)) tmp as days
    where dt = '2022-06-08';

-- 优惠券使用统计
    -- 数据源：dws_tool_user_coupon_coupon_used_1d
        -- 粒度：user + coupon
    -- 粒度：coupon
DROP TABLE IF EXISTS ads_coupon_stats;
CREATE EXTERNAL TABLE ads_coupon_stats
(
    `dt`              STRING COMMENT '统计日期',
    `coupon_id`       STRING COMMENT '优惠券ID',
    `coupon_name`     STRING COMMENT '优惠券名称',
    `used_count`      BIGINT COMMENT '使用次数',
    `used_user_count` BIGINT COMMENT '使用人数'
) COMMENT '优惠券使用统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_coupon_stats/';

insert overwrite table ads_coupon_stats
select * from ads_coupon_stats
union
select
    '2022-06-08',
    coupon_id,
    coupon_name,
    sum(used_count_1d),
    count(user_id)
from dws_tool_user_coupon_coupon_used_1d
where dt = '2022-06-08'
group by coupon_id, coupon_name;

五、知识get

  • 维度分析

    data_dim_analysis

  • Hive中的数据转换

    hive_SerDe

  • Hive BUG

    • cbo优化会导致hive struct 结构体判空操作失效，执行计划缺少对应的 filter
    • 解决思路：set hive.cbo.enable=false;
    • 参考issue：https://issues.apache.org/jira/browse/HIVE-21778
© 2020 - 2025 © 2023-2025 洋洋 | 原创不易，转载请注明出处~
✨ 本站由 Hugo + Stack 主题搭建 | 不忘初心，慢慢成长 ✨
使用 Hugo 构建
主题 Stack 由 Jimmy 设计