text: -- 3.5.1 创建用户行为宽表
  drop table if exists dws_user_action;
  create external table dws_user_action
  (
      user_id        string comment '用户 id',
      order_count    bigint comment '下单次数 ',
      order_amount   decimal(16, 2) comment '下单金额 ',
      payment_count  bigint comment '支付次数',
      payment_amount decimal(16, 2) comment '支付金额 ',
      comment_count  bigint comment '评论次数'
  ) comment '每日用户行为宽表'
      partitioned by (`dt` string)
      stored as parquet
      location '/warehouse/gmall/dws/dws_user_action/';
  -- 3.5.2 向用户行为宽表导入数据
  -- 1）导入数据with tmp_order as (
      select
          user_id,
          count(*) order_count,
          sum(oi.total_amount) order_amount
      from
          dwd_order_info oi
      where
          date_format(oi.create_time, 'yyyy-MM-dd') = '2019-08-28'
      group by
          user_id
  ),
  tmp_payment as (
      select
          user_id,
          sum(pi.total_amount) payment_amount,
          count(*) payment_count
      from
          dwd_payment_info pi
      where
          date_format(pi.payment_time, 'yyyy-MM-dd') = '2019-08-28'
      group by
          user_id
  ),
  tmp_comment as (
      select
          user_id,
          count(*) comment_count
      from
          dwd_comment_log c
      where
          date_format(c.dt, 'yyyy-MM-dd') = '2019-08-28'
      group by
          user_id
  )
  insert
      overwrite table dws_user_action partition (dt = '2019-08-28')
  select
      user_actions.user_id,
      sum(user_actions.order_count),
      sum(user_actions.order_amount),
      sum(user_actions.payment_count),
      sum(user_actions.payment_amount),
      sum(user_actions.comment_count)
  from
      (
          select
              user_id,
              order_count,
              order_amount,
              0 payment_count,
              0 payment_amount,
              0 comment_count
          from
              tmp_order
          union
          all
          select
              user_id,
              0,
              0,
              payment_count,
              payment_amount,
              0
          from
              tmp_payment
          union
          all
          select
              user_id,
              0,
              0,
              0,
              0,
              comment_count
          from
              tmp_comment
      ) user_actions
  group by
      user_id;
  -- 2）查询导入结果
  select *
  from dws_user_action;
  