数据仓库dws层，DWS层搭建--商品主题宽表，md，review第1遍,220622，

知识点03：DWS层搭建--商品主题宽表--需求分析

• 确定指标字段与表的关系

  当需求提出指标和维度之后，我们需要做的就是确定通过哪些表能够提供支撑。

  思考：是不是意味着数仓分层之后，上一层的表只能查询下一层的，能不能跨层？

  答案是不一定的。

  #下单次数、下单件数、下单金额
  dwb_order_detail
       order_id: 下单次数相当于下了多少订单（有多少个包含这个商品的订单）
       buy_num : 下单件数相当于下了多少商品
       total_price  每个商品下单金额指的是订单金额还是商品金额？应该是商品金额(订单中可能会包含其他商品)

  #被支付次数、被支付件数、被支付金额
  dwb_order_detail
      #支付状态的判断
          order_state: 只要不是1和7  就是已经支付状态的订单
          is_pay: 这个字段也可以 0表示未支付，1表示已支付。#推荐使用这个字段来判断
      #次数 件数 金额
          order_id
          buy_num
          total_price

  #被退款次数、被退款件数、被退款金额
  dwb_order_detail
      #退款的判断
          refund_id: 退款单号 is not null的就表明有退款
      #次数 件数 金额    
          order_id
          buy_num
          total_price

  #被加入购物车次数、被加入购物车件数
  yp_dwd.fact_shop_cart(能够提供购物车相关信息的只有这张表)
      id: 次数
      buy_num: 件数

  #被收藏次数
  yp_dwd.fact_goods_collect
      id: 次数

  #好评数、中评数、差评数
  yp_dwd.fact_goods_evaluation_detail
      geval_scores_goods:商品评分0-10分
      
      #如何判断 好  中  差 （完全业务指定）
          得分: >= 9      好
          得分: >6  <9   中
          得分：<= 6      差

  #维度
      时间、商品

概况起来，计算商品主题宽表，需要参与计算的表有：

yp_dwb.dwb_order_detail 订单明细宽表

yp_dwd.fact_shop_cart 购物车表

yp_dwd.fact_goods_collect 商品收藏表

yp_dwd.fact_goods_evaluation_detail 商品评价表

知识点04：DWS层搭建--商品主题宽表--step1--下单、支付、退款统计

大前提：使用row_number对数据进行去重

基于dwb_order_detail表根据商品（goods_id）进行统计各个指标的时候；

为了避免同一笔订单下有多个重复的商品出现（正常来说重复的应该合并在一起了）；

应该使用row_number对order_id和goods_id进行去重。

--订单明细表抽取字段，并且进行去重，作为后续的base基础数据
--体现：列裁剪  尽量不要select *
with order_base as (select
    dt,
    order_id, --订单id
    goods_id, --商品id
    goods_name,--商品名称
    buy_num,--购买商品数量
    total_price,--商品总金额（数量*单价）
    is_pay,--支付状态（1表示已经支付）
    row_number() over(partition by order_id,goods_id) as rn
from yp_dwb.dwb_order_detail),

-- 后面跟,表示后面继续使用CTE,语法如下
with t1 as (select....),
     t2 as (select....)
select * from t1 join t2;    

下单次数、件数、金额统计

基于上述的order_base进行查询

--下单次数、件数、金额统计
order_count as (select
    dt,goods_id as sku_id,goods_name as sku_name,
    count(order_id) order_count,
    sum(buy_num) order_num,
    sum(total_price) order_amount
from order_base where rn =1
group by dt,goods_id,goods_name),

支付次数、件数、金额统计

计算支付相关指标之前，可以先使用is_pay进行订单状态过滤

然后基于过滤后的数据进行统计

可以继续使用CTE引导

--订单状态,已支付
pay_base as(
    select *,
    row_number() over(partition by order_id, goods_id) rn
    from yp_dwb.dwb_order_detail
    where is_pay=1
),

--支付次数、件数、金额统计
payment_count as(
    select dt, goods_id sku_id, goods_name sku_name,
       count(order_id) payment_count,
       sum(buy_num) payment_num,
       sum(total_price) payment_amount
    from pay_base
    where rn=1
    group by dt, goods_id, goods_name
),

退款次数、件数、金额统计

可以先使用refund_id is not null查询出退款订单，然后进行统计

 --先查询出退款订单
refund_base as(
    select *,
       row_number() over(partition by order_id, goods_id) rn
    from yp_dwb.dwb_order_detail
    where refund_id is not null
),
-- 退款次数、件数、金额
refund_count as (
    select dt, goods_id sku_id, goods_name sku_name,
       count(order_id) refund_count,
       sum(buy_num) refund_num,
       sum(total_price) refund_amount
    from refund_base
    where rn=1
    group by dt, goods_id, goods_name
),

知识点05：DWS层搭建--商品主题宽表--step2--购物车、收藏统计

思考：为什么下面这两个查询需要考虑拉链的状态