技術文章 » 資料倉儲

資料倉儲

2023-07-03 12:01:28

SQL

  1. 給一張城市和交易額表，一張城市對應省份表， 取出 省份 總 交易額大於 500 的 省份 的名字
select max(tmp.province_name) from  

(select bt.city_num, bt.gmv, pt.province_num, pt.province_name from business_table bt  

left join province_table pt  on bt.city_num  = pt.city_num) tmp

GROUP BY (tmp.province_num) HAVING  sum(tmp.gmv) > 500
  1. 基於上面1問題， 得出 省份 總 交易額 [0,500 ] , [500,1000 ] , [1000,+oo ] 在以下三個區間的 省份 的 數量
select 
count(case when tmp1.pro_gmv >=0 and tmp1.pro_gmv <500 then tmp1.pro_name  else NULL END ) as gmv_0_500,
count(case when tmp1.pro_gmv  >=500 and tmp1.pro_gmv  <1000 then tmp1.pro_name  else NULL END ) as gmv_500_1000,
count(case when tmp1.pro_gmv  >=1000 then tmp1.pro_name  else NULL END ) as gmv_1000_
from 
(select  max(tmp.province_name) as pro_name , sum(gmv) as pro_gmv from 

(select bt.city_num, bt.gmv, pt.province_num, pt.province_name from business_table bt  

left join province_table pt  on bt.city_num  = pt.city_num) tmp

group by  tmp.province_num) tmp1
  1. 還是基於剛才， 按從小到大的順序得出每個城市的累計交易額，可以用視窗
-- group by 
select city_num, sum(gmv) as c_gmv  from business_table bt group by city_num   order by c_gmv

-- window
select bt.city_num, bt.c_gmv from 

(select DISTINCT city_num, sum(gmv) over (PARTITION by city_num) as c_gmv from business_table) bt 

order by  bt.c_gmv

指標

指標的價值

海盜指標法(AARRR海盜模型)
它反映了增長是系統性地貫穿於使用者生命週期各個階段的：使用者拉新(Acquisition)、使用者啟用(Activation)、使用者留存(Retention)、商業變現(Revenue)、使用者推薦(Referral)

指標如何做到精準

選指標常用方法是指標分級方法和OSM模型。

  • 指標分級方法
    T1 公司戰略層面指標
    T2 業務策略層面指標
    T3 業務執行層面指標

  • OSM模型
    O 業務目標
    S 業務策略
    M 業務度量

建模

維度建模的模式:

  1. 星型模型: 以事實表為中心，所有的維度表直接連在事實表上
  2. 雪花模式：雪花模式的維度表可以擁有其他的維度表，這種表不易維護，一般不推薦使用
  3. 星座模型: 基於多張事實表，而且共用維度資訊，即事實表之間可以共用某些維度表

維度建模步驟:

事實表種類:

  1. 事物事實表: 表中的一行對應空間或時間上某點的度量事件
  2. 週期快照事實表: 單個週期內資料, 每行都帶有時間值欄位，代表週期
  3. 累計快照事實表: 由多個週期資料組成，每行彙總了過程開始到結束之間的度量
  4. 無事實的事實表: 有少量的沒有數位化的值但是還很有價值的欄位，無事實的事實表就是為這種資料準備的，利用這種事實表可以分析發生了什麼。
  5. 聚集事實表: 原子粒度的資料進行簡單的聚合操作，目的就是為了提高查詢效能
  6. 合併事實表: 屬於相同粒度，就可以合併為一個事實表

維度表技術

  1. 維度表結構
    維度表謹記一條原則，包含單一主鍵列

  2. 跨表鑽取
    使不同的查詢能夠針對兩個或更多的事實表進行查詢

    上鑽（roll-up）：上卷是沿著維的層次向上聚集彙總資料。例如，對產品銷售資料，沿著時間維上卷，可以求出所有產品在所有地區每月（或季度或年或全部）的銷售額。

    下鑽（drill-down）：下鑽是上鑽的逆操作，它是沿著維的層次向下，檢視更詳細的資料。

  3. 退化維度
    退化維度就是將維度退回到事實表中。因為有時維度除了主鍵沒有其他內容，雖然也是合法維度鍵，但是一般都會退回到事實表中，減少關聯次數，提高查詢效能

  4. 多層次維度
    多數維度包含不止一個自然層次，如日期維度可以從天的層次到周到月到年的層次。所以在有些情況下，在同一維度中存在不同的層次。

  5. 維度表空值屬性
    推薦採用描述性字串代替空值

  6. 日曆日期維度
    在日期維度表中，主鍵的設定不要使用順序生成的id來表示，可以使用更有意義的資料表示，比如將年月日合併起來表示，即YYYYMMDD，或者更加詳細的精度。

資料抽取

業務資料 -- Sqoop
紀錄檔資料 -- Flume
其他資料 -- 通用第三方介面