人人草人人看人人摸,人妻AV在线观看,又大又爽,精品中字幕久久久人妻,A片免费在线,国产婷婷色一区二区在线,精品国产成人a在线观看,91免费三级片

數(shù)倉（一）簡介數(shù)倉，OLTP和OLAP

數(shù)倉（二）關(guān)系建模和維度建模

數(shù)倉（三）簡析阿里、美團(tuán)、網(wǎng)易、恒豐銀行、馬蜂窩5家數(shù)倉分層架構(gòu)

數(shù)倉（四）數(shù)據(jù)倉庫分層

數(shù)倉 (五) 元數(shù)據(jù)管理系統(tǒng)解析

數(shù)倉（六）從0到1簡單搭建數(shù)倉ODS層(埋點日志 + 業(yè)務(wù)數(shù)據(jù))

數(shù)倉（七）從0到1簡單搭建加載數(shù)倉DIM層以及拉鏈表處理

數(shù)倉（八）從0到1簡單搭建加載數(shù)倉DWD層（用戶行為日志數(shù)據(jù)解析）

上一次我們講解了DWD層（關(guān)于用戶交易等業(yè)務(wù)數(shù)據(jù)）的搭建、解析加載。這節(jié)我們講解DWS層關(guān)于各個主題的加工和使用，這層是寬表聚合值，是各個事實表的聚合值。

一、DWS層概念回顧

在第四章我們對DWS層做了概述，現(xiàn)在我們再來回顧一下。

DWS(Data Warehouse Service)

使輕度匯總層，從ODS層中對用戶的行為做一個初步的匯總，抽象出來一些通用的維度：時間、ip、id，并根據(jù)這些維度做一些統(tǒng)計值。
這里做輕度的匯總會讓以后的計算更加的高效，如：統(tǒng)計各個主題對象計算7天、30天、90天的行為，應(yīng)對特殊需求（例如，購買行為，統(tǒng)計商品復(fù)購率）會快很多不必走ODS層反復(fù)拿數(shù)據(jù)做加工。
這層以分析的主題對象作為建模驅(qū)動，基于上層的應(yīng)用和產(chǎn)品的指標(biāo)需求，構(gòu)建公共粒度的匯總指標(biāo)事實表，以寬表化手段物理化模型。構(gòu)建命名規(guī)范、口徑一致的統(tǒng)計指標(biāo)，為上層提供公共指標(biāo)，建立匯總寬表、明細(xì)事實表。
服務(wù)于 DWT 層的主題寬表，以及一些業(yè)務(wù)明細(xì)數(shù)據(jù)。

涉及的主題包括：訪客主題、用戶主題、商品主題、優(yōu)惠券主題、活動主題、地區(qū)主題等。

二、DWS層用戶主題加工

用戶主題記錄某一個用戶在某一天的匯總行為。

1、首先我們看一下我們需要統(tǒng)計或者說加工的指標(biāo)，一共是25個左右；

login_count`?BIGINT?COMMENT?'登錄次數(shù)'，`cart_count` BIGINT COMMENT '加入購物車次數(shù)',`favor_count` BIGINT COMMENT '收藏次數(shù)',`order_count`?BIGINT?COMMENT?'下單次數(shù)',`order_activity_count` BIGINT COMMENT '訂單參與活動次數(shù)',`order_activity_reduce_amount` DECIMAL(16,2) COMMENT '訂單減免金額(活動)',`order_coupon_count` BIGINT COMMENT '訂單用券次數(shù)',`order_coupon_reduce_amount` DECIMAL(16,2) COMMENT '訂單減免金額(使用優(yōu)惠券)',`order_original_amount` DECIMAL(16,2)  COMMENT '訂單單原始金額',`order_final_amount` DECIMAL(16,2) COMMENT '訂單總金額',`payment_count` BIGINT COMMENT '支付次數(shù)',`payment_amount` DECIMAL(16,2) COMMENT '支付金額',`refund_order_count` BIGINT COMMENT '退單次數(shù)',`refund_order_num` BIGINT COMMENT '退單件數(shù)',`refund_order_amount` DECIMAL(16,2) COMMENT '退單金額',`refund_payment_count` BIGINT COMMENT '退款次數(shù)',`refund_payment_num` BIGINT COMMENT '退款件數(shù)',`refund_payment_amount` DECIMAL(16,2) COMMENT '退款金額',`coupon_get_count` BIGINT COMMENT '優(yōu)惠券領(lǐng)取次數(shù)',`coupon_using_count` BIGINT COMMENT '優(yōu)惠券使用(下單)次數(shù)',`coupon_used_count` BIGINT COMMENT '優(yōu)惠券使用(支付)次數(shù)',`appraise_good_count`?BIGINT?COMMENT?'好評數(shù)'，`appraise_mid_count` BIGINT COMMENT '中評數(shù)',`appraise_bad_count` BIGINT COMMENT '差評數(shù)',`appraise_default_count` BIGINT COMMENT '默認(rèn)評價數(shù)',`order_detail_stats`?array<structstring,sku_num:bigint,order_count:bigint,activity_reduce_amount:decimal(16,2),coupon_reduce_amount:decimal(16,2),original_amount:decimal(16,2),final_amount:decimal(16,2)>>?COMMENT?'下單明細(xì)統(tǒng)計'

2、根據(jù)指標(biāo)建表

這里我們搭建DWS層級表，XXXX就是上面指標(biāo)的字段。

DROP TABLE IF EXISTS dws_user_action_daycount;CREATE EXTERNAL TABLE dws_user_action_daycount(    XXXXX    ??????......) COMMENT '每日用戶行為'PARTITIONED BY (`dt` STRING)STORED AS PARQUETLOCATION'/warehouse/gmall/dws/dws_user_action_daycount/'TBLPROPERTIES?("parquet.compression"="lzo");

3、逐個計算指標(biāo)

3.1 login_count 登錄次數(shù)

這個指標(biāo)簡單，根據(jù)dwd層，dwd_page_log表可以直接計算；

select      dt,    ??user_id,???  count(*) login_count    from dwd_page_logwhere user_id is not nulland last_page_id is nullgroup?by?dt,user_id

3.2?cart_count 加入購物車次數(shù);

????? favor_count 收藏次數(shù);

這兩個指標(biāo)也是很簡單，直接根據(jù)dwd層，dwd_action_log表中獲取

SQL實現(xiàn)

select      dt,      user_id,      sum(if(action_id='cart_add',1,0)) cart_count,  sum(if(action_id='favor_add',1,0))?favor_countfrom?dwd_action_logwhere?user_id?is?not?nulland?action_id?in?('cart_add','favor_add')group?by?dt,user_id

3.3 order_count 下單次數(shù)；

????order_activity_count 訂單參與活動次數(shù)；

????order_activity_reduce_amount 訂單減免金額(活動)；

????order_coupon_count 訂單用券次數(shù)；

????order_coupon_reduce_amount 訂單減免金額(使用優(yōu)惠券)；

????order_original_amount ?訂單單原始金額；

????order_final_amount ?訂單總金額；

訂單表的幾個指標(biāo)相對也很簡單，直接根據(jù)dwd層，dwd_order_info表中獲取

SQL實現(xiàn)

select    ??date_format(create_time,'yyyy-MM-dd')?dt,???  user_id,      count(*) order_count,      sum(if(activity_reduce_amount>0,1,0)) order_activity_count,      sum(if(coupon_reduce_amount>0,1,0)) order_coupon_count,      sum(activity_reduce_amount) order_activity_reduce_amount,      sum(coupon_reduce_amount) order_coupon_reduce_amount,????  sum(original_amount)?order_original_amount,      sum(final_amount) order_final_amountfrom dwd_order_infogroup by date_format(create_time,'yyyy-MM-dd'),user_id

3.4?payment_count'支付次數(shù)',

????? payment_amount'支付金額',

SQL實現(xiàn)

select??date_format(callback_time,'yyyy-MM-dd')?dt,??user_id,??count(*)?payment_count,??sum(payment_amount)?payment_amountfrom?dwd_payment_infogroup by date_format(callback_time,'yyyy-MM-dd'),user_id

3.5?refund_order_count 退單次數(shù)',

????refund_order_num 退單件數(shù)',

????refund_order_amount '退單金額',

SQL實現(xiàn)

select    date_format(create_time,'yyyy-MM-dd') dt,    user_id,    count(*) refund_order_count,????sum(refund_num)?refund_order_num,    sum(refund_amount) refund_order_amountfrom dwd_order_refund_infogroup by date_format(create_time,'yyyy-MM-dd'),user_id

3.6refund_payment_count '退款次數(shù)',

????refund_payment_num '退款件數(shù)',

????refund_payment_amount '退款金額',

ri表示上述退單中間表，rp表示退款中間表；然后做左外鏈接取交集；

SQL實現(xiàn)

select      date_format(callback_time,'yyyy-MM-dd') dt,      rp.user_id,      count(*) refund_payment_count,      sum(ri.refund_num) refund_payment_num,      sum(rp.refund_amount) refund_payment_amount  from  (      select          user_id,??????????order_id,??????????sku_id,          refund_amount,          callback_time      from dwd_refund_payment  )rp  left join  (      select          user_id,          order_id,          sku_id,          refund_num      from dwd_order_refund_info  )ri  on rp.order_id=ri.order_id  and rp.sku_id=rp.sku_id??group?by?date_format(callback_time,'yyyy-MM-dd'),rp.user_id??

3.7coupon_get_count '優(yōu)惠券領(lǐng)取次數(shù)',

coupon_using_count '優(yōu)惠券使用(下單)次數(shù)',

coupon_used_count '優(yōu)惠券使用(支付)次數(shù)',

SQL實現(xiàn)

select    coalesce(coupon_get.dt,coupon_using.dt,coupon_used.dt) dt,    coalesce(coupon_get.user_id,coupon_using.user_id,coupon_used.user_id) user_id,    nvl(coupon_get_count,0) coupon_get_count,    nvl(coupon_using_count,0) coupon_using_count,    nvl(coupon_used_count,0) coupon_used_countfrom(    select        date_format(get_time,'yyyy-MM-dd') dt,????????user_id，        count(*) coupon_get_count    from dwd_coupon_use    where get_time is not null    group by user_id,date_format(get_time,'yyyy-MM-dd'))coupon_getfull outer join(    select        date_format(using_time,'yyyy-MM-dd') dt,        user_id,        count(*) coupon_using_count    from dwd_coupon_use    where using_time is not null    group by user_id,date_format(using_time,'yyyy-MM-dd'))coupon_usingon coupon_get.dt=coupon_using.dtand coupon_get.user_id=coupon_using.user_idfull outer join(    select        date_format(used_time,'yyyy-MM-dd') dt,        user_id,        count(*) coupon_used_count    from dwd_coupon_use    where used_time is not null    group by user_id,date_format(used_time,'yyyy-MM-dd'))coupon_usedon?nvl(coupon_get.dt,coupon_using.dt)=coupon_used.dtand nvl(coupon_get.user_id,coupon_using.user_id)=coupon_used.user_id)

3.8appraise_good_count '好評數(shù)',

appraise_mid_count? '中評數(shù)',

appraise_bad_count? '差評數(shù)',

這個數(shù)值直接從dwd層，dwd_comment_info表里面取sum值

SQL實現(xiàn)

select    date_format(create_time,'yyyy-MM-dd') dt,????user_id    sum(if(appraise='1201',1,0)) appraise_good_count,    sum(if(appraise='1202',1,0)) appraise_mid_count,    sum(if(appraise='1203',1,0)) appraise_bad_count,????sum(if(appraise='1204',1,0))?appraise_default_countfrom dwd_comment_infogroup?by?date_format(create_time,'yyyy-MM-dd'),user_id

4、完成首日和每日加載

匯總SQL后做首日裝載，分區(qū)是動態(tài)分區(qū)dt

insert overwrite table dws_user_action_daycount partition(dt)select    coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id,tmp_ri.user_id,tmp_rp.user_id,tmp_comment.user_id,tmp_coupon.user_id,tmp_od.user_id),    nvl(login_count,0),????nvl(cart_count,0),    nvl(favor_count,0),????XXXX,????

每日裝載,dt = 當(dāng)前時間分區(qū);

insert overwrite table dws_user_action_daycount partition(dt='2020-06-15')select    coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id,tmp_ri.user_id,tmp_rp.user_id,tmp_comment.user_id,tmp_coupon.user_id,tmp_od.user_id),    nvl(login_count,0),    nvl(cart_count,0),????nvl(favor_count,0),????nvl(order_count,0),

這樣我們就完成了關(guān)于用戶主題的DWS層的匯聚值的計算。

請讀者參考后完成以下主題：

訪客主題、商品主題、優(yōu)惠券主題、活動主題、地區(qū)主題等，每日裝載和首裝載的SQL。

總結(jié)：

? 數(shù)倉DWS層的概念、作用；
DWS層的寬表聚合值是各個事實表的聚合值，怎么樣加工和計算？

數(shù)倉（十）從0到1簡單搭建加載數(shù)倉DWS層