【电商数仓】数仓搭建之服务数据（data warehouse service-- DWS）层（DWS层概述、几个系统函数和用户主题的建立与数据导入）

零 DWS层概述

DWD层：将数据重新建模，以粒度最细的方式将所有的明细数据放入DWD层。在一个公司中，会计或者财务会关心明细，将明细总结形成一张资产负债表交给老板。

数仓最终形成的表格不是明细层，数据量十分大，可读性差。

最终呈现的数据是汇总过的数据，所以在DWS层需要将数据以感兴趣的角度进行汇总，如下sql，为分组汇总

select XXX,sum(cost) from tbl group by XXX;

按照维度进行汇总，六个维度表（商品、优惠券、活动、地区、时间、用户）。

所有能够汇总的事实按照用户维度进行汇总，就得到了一张用户主题汇总表，所有能够汇总的事实如下表所示：

	时间	用户	地区	商品	优惠券	活动	度量值
订单	√	√	√				运费/优惠金额/原始金额/最终金额
订单详情	√	√	√	√	√	√	件数/优惠金额/原始金额/最终金额
支付	√	√	√				支付金额
加购	√	√		√			件数/金额
收藏	√	√		√			次数
评价	√	√		√			次数
退单	√	√	√	√			件数/金额
退款	√	√	√	√			件数/金额
优惠券领用	√	√			√		次数

DWS层共有访客、用户、商品、优惠券、活动、地区六个主题。

DWS层需要进行汇总，DWT层同样需要进行汇总，区别在于时间维度的差别，DWS层汇总当天的数据，DWT层汇总累积的数据。

一 系统函数

1 nvl函数

基本语法：NVL（表达式1，表达式2）

如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。

该函数的目的是把一个空值（null）转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。

hive (gmall)> select nvl(1,0);
1
hive (gmall)> select nvl(null,"hello");
hello

2 日期处理函数

-- date_format函数（根据格式整理日期）
hive (gmall)> select date_format('2020-06-14','yyyy-MM');
2020-06

-- date_add函数（加减日期）
hive (gmall)> select date_add('2020-06-14',-1);
2020-06-13
hive (gmall)> select date_add('2020-06-14',1);
2020-06-15

-- next_day函数
-- 取当前天的下一个周一
hive (gmall)> select next_day('2020-06-14','MO');
2020-06-15
-- 说明：星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）

-- 取当前周的周一
hive (gmall)> select date_add(next_day('2020-06-14','MO'),-7);
2020-06-8

-- last_day函数（求当月最后一天日期）
hive (gmall)> select last_day('2020-06-14');
2020-06-30

3 复杂数据类型定义

-- map结构数据定义
map<string,string>

-- array结构数据定义
array<string>

-- struct结构数据定义
struct<id:int,name:string,age:int>

-- struct和array嵌套定义
array<struct<id:int,name:string,age:int>>

二 用户主题

在用户维度上，将所有能够统计的数据进行汇总，汇总完成的数据全部放在一张结果表中

用户维度能够统计的所有数据来源如下

	用户	度量值
订单	√	运费/优惠金额/原始金额/最终金额
订单详情	√	件数/优惠金额/原始金额/最终金额
支付	√	支付金额
加购	√	件数/金额
收藏	√	次数
评价	√	次数
退单	√	件数/金额
退款	√	件数/金额
优惠券领用	√	次数

1 建表语句

DROP TABLE IF EXISTS dws_user_action_daycount;
CREATE EXTERNAL TABLE dws_user_action_daycount
(
    `user_id` STRING COMMENT '用户id',
    `login_count` BIGINT COMMENT '登录次数',
    `cart_count` BIGINT COMMENT '加入购物车次数',
    `favor_count` BIGINT COMMENT '收藏次数',
    `order_count` BIGINT COMMENT '下单次数',
    `order_activity_count` BIGINT COMMENT '订单参与活动次数',
    `order_activity_reduce_amount` DECIMAL(16,2) COMMENT '订单减免金额(活动)',
    `order_coupon_count` BIGINT COMMENT '订单用券次数',
    `order_coupon_reduce_amount` DECIMAL(16,2) COMMENT '订单减免金额(优惠券)',
    `order_original_amount` DECIMAL(16,2)  COMMENT '订单单原始金额',
    `order_final_amount` DECIMAL(16,2) COMMENT '订单总金额',
    `payment_count` BIGINT COMMENT '支付次数',
    `payment_amount` DECIMAL(16,2) COMMENT '支付金额',
    `refund_order_count` BIGINT COMMENT '退单次数',
    `refund_order_num` BIGINT COMMENT '退单件数',
    `refund_order_amount` DECIMAL(16,2) COMMENT '退单金额',
    `refund_payment_count` BIGINT COMMENT '退款次数',
    `refund_payment_num` BIGINT COMMENT '退款件数',
    `refund_payment_amount` DECIMAL(16,2) COMMENT '退款金额',
    `coupon_get_count` BIGINT COMMENT '优惠券领取次数',
    `coupon_using_count` BIGINT COMMENT '优惠券使用(下单)次数',
    `coupon_used_count` BIGINT COMMENT '优惠券使用(支付)次数',
    `appraise_good_count` BIGINT COMMENT '好评数',
    `appraise_mid_count` BIGINT COMMENT '中评数',
    `appraise_bad_count` BIGINT COMMENT '差评数',
    `appraise_default_count` BIGINT COMMENT '默认评价数',
    `order_detail_stats` array<struct<sku_id:string,sku_num:bigint,order_count:bigint,activity_reduce_amount:decimal(16,2),coupon_reduce_amount:decimal(16,2),original_amount:decimal(16