--事务型事实表
--事务事实表
--绝大多数的事实表都是事务型事实表
--事务：原子性
--原子操作（行为）
--                 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',
    `create_time` STRING COMMENT '加购时间',
    `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');
select count(*)
from dwd_trade_cart_add_inc;
set hive.exec.dynamic.partition.mode=nonstrict;
--首日装载
insert overwrite table dwd_trade_cart_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,
       data.sku_num,
       date_format(data.create_time, 'yyyy-MM-dd')
from ods_cart_info_inc
where dt = '2022-06-08'
  and type = 'bootstrap-insert';
--每日装载
insert overwrite table dwd_trade_cart_add_inc partition (dt = '2022-06-09')
select data.id,
       data.user_id,
       data.sku_id,
       date_format(from_utc_timestamp(ts * 1000, 'GMT+8'), 'yyyy-MM-dd')                          date_id,
       date_format(from_utc_timestamp(ts * 1000, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss')                 create_time,
       if(type = 'insert', data.sku_num, cast(data.sku_num as int) - cast(old['sku_num'] as int)) sku_num
from ods_cart_info_inc
where dt = '2022-06-09'
  and (type = 'insert'
    or (type = 'update' and old['sku_num'] is not null and cast(data.sku_num as int) > cast(old['sku_num'] as int)));

--下单事实事务表
--建表语句
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');

-- 首日数据装载
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_order_detail_inc partition (dt)
select od.id,
       order_id,
       user_id,
       sku_id,
       province_id,
       activity_id,
       activity_rule_id,
       coupon_id,
       date_format(create_time, 'yyyy-MM-dd') date_id,
       create_time,
       sku_num,
       split_original_amount,
       nvl(split_activity_amount, 0.0),
       nvl(split_coupon_amount, 0.0),
       split_total_amount,
       date_format(create_time, 'yyyy-MM-dd')
from (select data.id,
             data.order_id,
             data.sku_id,
             data.create_time,
             data.sku_num,
             data.sku_num * data.order_price split_original_amount,
             data.split_total_amount,
             data.split_activity_amount,
             data.split_coupon_amount
      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
      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') cou
     on od.id = cou.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');
--首日数据装载
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt)
select od.id,
       od.order_id,
       user_id,
       sku_id,
       province_id,
       activity_id,
       activity_rule_id,
       coupon_id,
       payment_type,
       pay_dic.dic_name,
       date_format(callback_time, 'yyyy-MM-dd') date_id,
       callback_time,
       sku_num,
       split_original_amount,
       nvl(split_activity_amount, 0.0),
       nvl(split_coupon_amount, 0.0),
       split_total_amount,
       date_format(callback_time, 'yyyy-MM-dd')
from (select data.id,
             data.order_id,
             data.sku_id,
             data.sku_num,
             data.sku_num * data.order_price split_original_amount,
             data.split_total_amount,
             data.split_activity_amount,
             data.split_coupon_amount
      from ods_order_detail_inc
      where dt = '2022-06-08'
        and type = 'bootstrap-insert') od
         --注意！！！
         join
     (select data.user_id,
             data.order_id,
             data.payment_type,
             data.callback_time
      from ods_payment_info_inc
      where dt = '2022-06-08'
        and type = 'bootstrap-insert'
        and data.payment_status = '1602') pi
     on od.order_id = pi.order_id
         left join
     (select data.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') cou
     on od.id = cou.order_detail_id
         left join
     (select dic_code,
             dic_name
      from ods_base_dic_full
      where dt = '2022-06-08'
        and parent_code = '11') pay_dic
     on pi.payment_type = pay_dic.dic_code;
select count(*)
from dwd_trade_cart_add_inc;
--交易域购物车周期快照事实表】
--建表语句
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');
insert overwrite table dwd_trade_cart_full partition (dt = '2022-06-08')
select id,
       user_id,
       sku_id,
       sku_name,
       sku_num
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');
--数据装载
--以收货时间作为分区依据若是没有收货则时间为”9999-12-31“
--首日
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_trade_flow_acc partition (dt)
select oi.id,
       user_id,
       province_id,
       date_format(create_time, 'yyyy-MM-dd'),
       create_time,
       date_format(callback_time, 'yyyy-MM-dd'),
       callback_time,
       date_format(finish_time, 'yyyy-MM-dd'),
       finish_time,
       original_total_amount,
       activity_reduce_amount,
       coupon_reduce_amount,
       total_amount,
       nvl(payment_amount, 0.0),
       nvl(date_format(finish_time, 'yyyy-MM-dd'), '9999-12-31')
from (select data.id,
             data.user_id,
             data.province_id,
             data.create_time,
             data.original_total_amount,
             data.activity_reduce_amount,
             data.coupon_reduce_amount,
             data.total_amount
      from ods_order_info_inc
      where dt = '2022-06-08'
        and type = 'bootstrap-insert') oi
         left join
     (select data.order_id,
             data.callback_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') pi
     on oi.id = pi.order_id
         left join
     (select data.order_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;
-----每日数据装载
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_trade_flow_acc partition (dt)
select oi.order_id,
       user_id,
       province_id,
       order_date_id,
       order_time,
       nvl(oi.payment_date_id, pi.payment_date_id),
       nvl(oi.payment_time, pi.payment_time),
       nvl(oi.finish_date_id, log.finish_date_id),
       nvl(oi.finish_time, log.finish_time),
       order_original_amount,
       order_activity_amount,
       order_coupon_amount,
       order_total_amount,
       nvl(oi.payment_amount, pi.payment_amount),
       nvl(nvl(oi.finish_time, log.finish_time), '9999-12-31')
from (select order_id,
             user_id,
             province_id,
             order_date_id,
             order_time,
             payment_date_id,
             payment_time,
             finish_date_id,
             finish_time,
             order_original_amount,
             order_activity_amount,
             order_coupon_amount,
             order_total_amount,
             payment_amount
      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,
             null                                        payment_date_id,
             null                                        payment_time,
             null                                        finish_date_id,
             null                                        finish_time,
             data.original_total_amount,
             data.activity_reduce_amount,
             data.coupon_reduce_amount,
             data.total_amount,
             null                                        payment_amount
      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_status')
        and data.payment_status = '1602') pi
     on oi.order_id = pi.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
     on oi.order_id = log.order_id;
--工具域优惠券使用事务事实表
--建表语句
-- 用户+优惠券+订单+支付成功
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");
-- ① 首日装载
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_tool_coupon_used_inc partition (dt)
select data.id,
       data.coupon_id,
       data.user_id,
       data.order_id,
       date_format(data.used_time, 'yyyy-MM-dd') date_id,
       data.used_time,
       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,
       data.coupon_id,
       data.user_id,
       data.order_id,
       date_format(data.used_time, 'yyyy-MM-dd') date_id,
       data.used_time
from ods_coupon_use_inc
where dt = '2022-06-09'
  and type = 'update'

  and array_contains(map_keys(old), 'used_time');
--互动域收藏商品事务事实表
--用户+时间+商品
--建表语句
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");
--数据装载
--统计的为商品收藏事务，所以取消的也算，infer to 不需要where进行状态判断
set hive.exec.dynamic.partition.mode=nonstrict;
select count(*)from dwd_trade_cart_add_inc;
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';
--每日数据装载
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',
    `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');
--数据装载
--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,
    common.ba brand,
    common.ch channel,
    common.is_new is_new,
    common.md model,
    common.mid mid_id,
    common.os operate_system,
    common.uid user_id,
    common.vc version_code,
    page.item page_item,
    page.item_type page_item_type,
    page.last_page_id,
    page.page_id,
    page.from_pos_id,
    page.from_pos_seq,
    page.refer_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,
    common.sid session_id,
    page.during_time
from ods_log_inc
where dt='2022-06-08'
and page is not null;
set hive.cbo.enable=true;
--用户域用户注册事务事实表
    --用户域
    --用户注册成功
        --用户（会员）+时间
    --事务事实表
--建表语句
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");
--数据装载
--使用页面浏览记录
--首日装载
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_user_register_inc partition(dt)
select
    ui.user_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system,
    date_format(create_time,'yyyy-MM-dd')
from
(
    select
        data.id user_id,
        data.create_time
    from ods_user_info_inc
    where dt='2022-06-08'
    and type='bootstrap-insert'
)ui
left join
    --注册时日志
(
    select
        common.ar province_id,
        common.ba brand,
        common.ch channel,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code
    from ods_log_inc
    where dt='2022-06-08'
    and page.page_id='register'
    and common.uid is not null
)log
on ui.user_id=log.user_id;
--每日数据
insert overwrite table dwd_user_register_inc partition(dt='2022-06-09')
select
    ui.user_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system
from
(
    select
        data.id user_id,
        data.create_time
    from ods_user_info_inc
    where dt='2022-06-09'
    and type='insert'
)ui
left join
(
    select
        common.ar province_id,
        common.ba brand,
        common.ch channel,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code
    from ods_log_inc
    where dt='2022-06-09'
    and page.page_id='register'
    and common.uid is not null
)log
on ui.user_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,
       date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd')          date_id,
       date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') login_time,
       channel,
       province_id,
       version_code,
       mid_id,
       brand,
       model,
       operate_system
from (
         select user_id,
                channel,
                province_id,
                version_code,
                mid_id,
                brand,
                model,
                operate_system,
                ts
         from (select common.uid user_id,
                      common.ch  channel,
                      common.ar  province_id,
                      common.vc  version_code,
                      common.mid mid_id,
                      common.ba  brand,
                      common.md  model,
                      common.os  operate_system,
                      ts,
                      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) t1
         where rn = 1
     ) t2;