數據倉庫開發 SQL 使用技巧總結

作者：dcguo

使用 sql 做數倉開發有一段時間了，現做一下梳理覆盤，主要內容包括 sql 語法、特性、函數、優化、特殊業務表實現等。

mysql 數據結構

常用 innodb 存儲爲 B+ 樹

特點

優勢

索引

優化索引是提升性能主要手段，主要用到的索引基本爲以下三種：

  1. 聚簇索引

一般表會用自增主鍵做聚集索引，沒有的話 mysql 會默認創建，但是一旦確定之後這個主建得更改代價就會很高，所以建表時候要考慮自增主建不能頻繁 update

  1. 非聚簇索引

根據實際情況自行添加得索引都是輔助索引，就是一個爲了尋找主鍵索引得二級索引，就是先找到主鍵索引再通過主鍵索引找數據。

輔助索引可能沒有我們需要的 select 列，這就引出 innodb 非聚集索引獨有得耗時操作 回表，sql 重要得優化操作 索引覆蓋。

覆蓋索引: 

回表:

比如開發人員最喜愛得 select * ... 就經常會回表

回表理解： select * 導致業務覆蓋不到索引，那麼優化器決策後很可能就不走輔助索引了，因爲輔助索引上拿到的 key 太多了，隨機回表開銷太大，還不如走聚集索引，經常出現再 範圍查詢, join 操作 上，但是現在磁盤都是 ssd, 不怕隨機讀，所以我們又可以用 force index() 操作強制優化器走輔助索引。

demo: 

-- 輔助索引
select * from t where a > '1999-08-01';

如果從輔助索引取，根據一些過濾條件得到 50w 行，此時我還需要回表 50w 次，50w*3 層 = 150w 次 i/o，而且因爲回表時主鍵是無序的，所以是隨機 io。

如果我不走輔助索引，直接順序掃描這 150w 行的數據，需要 (50w*3 層) / (16KB/100B) 約 1w 次 i/o，而且是順序 io 。

優化方案

mrr 將二級索引上查詢出來的主鍵排序之後在回表，explain 得 extra 有一列 using mrr。

  1. 複合索引

其實還是一個 b+ 樹，每個節點是幾個字段值 concat 起來的元組，比如複合索引 (a, b) 的 b+ 樹上，對 (a) 列是有序的，對 (a, b) 組合列也是有序的，但是對 (b) 列卻不一定是有序的，對其葉子節點上帶的 pk 列也是無序的。

聚簇索引 / 非聚簇索引

sql 語法

case when

--demo1 case 搜索函數 case when xxx then xxx else end
select
 id,
 case
 when score < 60
 then 'low'
 when score < 90
 then 'middle'
    else 'high'
    end as rank
from
 test

--demo2 簡單 case 函數 case field when xxx then xxx else end
select
 id
 case score
 when 0
 then 'bad'
 when 100
 then 'good'
 else 'middle'
    end

with as

-- 定義臨時表
with tmp as (select * from test)
-- 使用臨時表
select * from tmp

-- 定義臨時表
with tmp as (
    select id from test where score > 60
)
-- 使用臨時表
select distinct id from tmp;

group by/with rollup

group by

主要是用來做數據聚合

需要選擇字段作爲聚合維度後，然後通過聚合函數得到彙總值的過程。

  • count，sum，avg，...

  • max/min，std，variance，...

  • rank，first/last_value，row_number，...

demo:

select
 score,
 count(distinct id)
from
 test
group by
 score

優化: 

  1. 分組是一個相對耗時的操作，我們可以先通過 where 縮小數據的範圍之後，再分組；

  2. 也可以將分組拆分，如果是大表多維度分組，可以使用 with as 語法先計算一部分得到臨時表然後再利用臨時表進行計算，sql 也可以簡化 。

with rollup

rollup 是 group by 子句的擴展，rollup 選項允許包含表示小計的額外行，通常稱爲超級聚合行，以及總計行。

-- demo
-- 下圖結果第三行就是超級聚合行
select
    name, sum(score)
from
    students
group by rollup(name)

FKPMfw

tag:

如何區分超級聚合行得 NULL 和 普通 NULL？

使用 grouping 函數可以識別超級聚合形成的 NULL, 避免和普通的 NULL 混淆。

union/union all/intersect/except

用法基本類似，只舉例部分

  • union 並集

  • intersect 交集

  • except 差集

-- union 去重, union all 不去重
select column_name(s) from table_name1
union
select column_name(s) from table_name2

limit

limit 分頁查詢使用

  1. 使用

    select
 id
from
 test
where
 id > 1000000 limit 20;

in/not in/exists/not exists/between

in/not in:  作用不用多說

exists/not exists： 強調的是是否返回結果集

exists 用於檢查子查詢是否至少會返回一行數據，該子查詢實際上並不返回任何數據，而是返回值 true 或 false；exist 指定一個子查詢，檢測行的存在。

**in/exists 對比 ** in 是把外表和內表作 hash 連接，而 exists 是對外表作 loop 循環，每次 loop 循環再對內表進行查詢；如果查詢語句使用了 not in 那麼內外表都進行全表掃描，沒有用到索引；而 not extsts 的子查詢依然能用到表上的索引；所以無論那個表大，用 not exists 都比 not in 要快。

between: 如果表達式大於或等於 >= low 值且小於或等於 <=) high 值，則 between 運算符返回 true

in/between 對比 連續數據使用 between 比用 in 好

-- in/notin
-- 略

-- exists/not exists(略)
-- 子查詢是相關的, 對於 scores 表中的每一行，子查詢檢查 class 表中是否有對應的行。 如果有匹配行，則子查詢返回一個使外部查詢包含 scores 表中的當前行的子查詢。 如果沒有相應的行，則子查詢不返回導致外部查詢不包括結果集中 scores 表中的當前行的行
select
    id, score
from
    scores
where
    exists(
        select
            1
        from
            class
        where
            class.id = scores.id
    );

-- between/not between(略)
select
    id, score
from
 scores
where
    score between 2500 and 3000

join(inner join)/left join/right join/full join / 隱式連接 / cross join/left semi join / 不等值連接

having

  1. 使用聚合函數進行計算

  2. 使用 having 子句篩選分組

where/on

join 時候 where/on 不可以混用

inner join 中 where 可以代替 on 但是 on 不能代替 where

on 是 using on 的簡便寫法

explain(mysql)

bZXvlT

hive/spark/mysql 8.0

之前也沒有接觸過大數據相關知識，簡單瞭解這幾種 sql 其實大差不差，主要區別就是 hive/spark 操作的數據可以很大很大，單機存不下，所以數據文件位於分佈式文件系統 HDFS。

hive: sql 解析引擎，將 sql 轉譯成 map/reduce job 然後再 hadoop 執行，相當於 hadoop 的客戶端工具。

hive 的表其實就是 hdfs 的目錄，按照表名分開文件夾，就是分區表，分區值就是子文件夾，可以直接再 map/reduce job 裏面使用。

hive 數據存儲格式

textfile, sequencefile, avro, rcfile, orcfile, parquet

內部表 / 外部表

hive 默認創建的是內部表

外部表沒有辦法直接 truncate table

創建外部表的 sql

CREATE EXTERNAL TABLE IF NOT EXISTS test (
    `quota`          STRING COMMENT '',
    `package`          INT    COMMENT '',
    `all_sys`         INT    COMMENT ''
) COMMENT 'test'
PARTITIONED BY (timeline STRING COMMENT '時間分區')
STORED AS PARQUET

關於刪數據 ？

在刪除內部表的時候，hive 將會把屬於表的元數據和數據全部刪掉；而刪除外部表的時候，hive 僅僅刪除外部表的元數據，數據是不會刪除的，也就是說，外部表的數據其實不是 hive 自己管理的。

如何選擇創建內部或者外部表 ？

但是作爲一個經驗，如果所有處理都需要由 hive 完成，應該創建表，否則使用外部表，基於此，我們使用數倉都是基於 hive 完成，所以應該創建內部表。

內置函數

函數主要有取值函數和變換函數等

  • round，abs，ceil

  • sin，cos，sqrt

時間

隨機數

字符串

-- 函數用於將多行數據聚合爲單行，從而提供與特定值關聯的數據列表，它將以逗號來分割列表
-- 結果
-- 1 python,c
-- 2 r,java
-- 3 swift
select id,wm_concat(',',name) as language from tmp_test group by id;

-- 字符串截取
-- CDE
select substring("ABCDE", 3)

-- json 解析
select get_json_object({"score":3}, '$.score')

統計函數

-- coalesce(expression_1, expression_2, ...,expression_n) 依次參考各參數表達式，遇到非 null 值即停止並返回該值。如果所有的表達式都是空值，最終將返回一個空值
select coalesce(null, null, '200')

判斷語句

-- 條件判斷，滿足第一個表達式返回 1, 否則返回 0
select if(1>0, 1, 0)

列轉行

-- 結果: a#b#c
select concat_ws('#', 'a', 'b', 'c', null)
-- 特性: 它們都是將分組中的某列轉爲一個數組返回，不同的是 collect_list 不去重而 collect_set 去重
-- collect_set 去重, collect_list 不去重
-- 還可以利用 collect 來突破 group by 的限制, hive 中在 group by 查詢的時候要求出現在 select 後面的列都必須是出現在 group by 後面的，即 select 列必須是作爲分組依據的列
select username, collect_list(video_name)[0] from t_visit_video group by username;
-- 結果
-- +----------+--------+
-- | lastname |  name  |
-- +----------+--------+
-- |     a    |  aa,ab |
-- |     b    |  ba,bb |
-- +----------+--------+
select last_name, group_concat(name) as name from test where lastname in ('a', 'b')
-- 數據準備
-- id score
-- 1  100,96,98,100,96,85
-- 2  98,97,100,85,99,100

-- 計算
-- 找到具備 100 的 id
select
 *
from
 socre
 lateral view explode(spilt(scores, ',')) score AS each_score
where
 each_score in ("100")
udf 函數

其實就是一個簡單的函數，執行過程就是在 hive 轉換成 mapreduce 程序後，執行 java 方法，類似於像 mapreduce 執行過程中加入一個插件，方便擴展。udf 只能實現一進一出的操作，如果需要實現多進一出，則需要實現 udaf。hive 可以允許用戶編寫自己定義的函數 udf，來在查詢中使用。

使用 udf 函數進行業務內複雜結構字段提取

-- parse_components 就是業務種自定義的 udf 函數，用來解析一個複雜得動態字段，此字段根據不同的模板可能出現得字段枚舉超過百種
select
    test...,
    parse_components(doc.components, '100', '101').test as template_field
from
 test
order by
    test

窗口函數

ps: 以前一直不知道爲啥叫窗口函數，總聯想到滑動窗口，但是其實不是這樣

partition by 的結果是分組的結果，每個組都是不同的範圍，窗口的意思就是範圍

它可以做什麼？

有些需求可以使用窗口函數完成，如下：

  • 排名問題，根據科目進行分組

  • top n 問題，找出每個科目前三的同學進行獎勵

  • 增量表問題

  • 某字段某段時間移動平均

  • 某種行爲前百分之多少的數據

此時可以引入窗口函數

該函數也叫 olap 函數（online anallytical processing，聯機分析處理），可以對數據庫數據進行實時分析處理

它的執行順序 where 之後

demo

就下面圖 2 這個查詢不用窗口該咋寫？各種子查詢想想都難受，so 使用窗口。

我們做到了，在一個單個 sql 中計算列的綜合，以及每一列佔總數得比例，以及當前列，這就是窗口函數得一個突破。

基本語法
-- partition by 用於給結果集分組，另外如果不指定那麼會默認把整個結果集作爲分組
-- partition by 需要分組的列名
-- order by 需要排序的列名
-- rows between 參與計算的行起始位置 and 參與計算的行終止位置
-- over括號中的那些如果不需要可以省略
<窗口函數> over (partition by <用於分組的列名> order by <用於排序的列名> rows between <起始位置> and <終止位置>)
-- rows between 指定計算起始行和結束行的方法
-- rows between 3 preceding and current row 包括本行和前三行
-- rows between current row and 3 following 包括本行和後三行
-- rows between unbounded preceding and current row 包括本行和該分組內之前所有的行
-- rows between current row and unbounded following 包括本行和該分組內之後所有行
-- rows between 5 preceding and 1 following 包括前五行和後一行

-- over (order by x) 相當於 over(order by x rows between unbounded preceding and current now) 從前方無界到當前行

-- over () 相當於從前方無界到後方無界，整組內容

-- 另, partition 子句可省略省略就是不指定分組
-- 例:
select *, rank() over (order by scores desc) as ranking from students
哪些函數可以使用窗口函數
專用函數 rank/dens_rank/row_number

rank/dens_rank/row_number

這三個函數得區別是分組排序後得到的虛擬 rank 列不同

實際上此函數可以爲查出來的每一行增加 rank 序號

注意

rank() 函數中得到的 rank 值可能是會出現重複值，如果要取 1 條，需要 sql 查到的數據不重複，rank = 1 不能保證僅取 1 條, 除非你使用得函數是 row_number()：

demo

大概場景就是，我們需要查詢一張表，要按照某個字段 a 去排序另一個字段 b，並且每個 c 字段只取前 n 條數據

select a.id, a.a, a.b, a.c, a.d
  from (select t.id,
               t.a,
               t.b,
               t.c,
               t.d,
               rank() over(partition by t.a order by t.b desc) rk
          from test t) a
where rk < 4;

demo：尋找企業下第一個入住企業

--distinct_org_id
select
 *
from (
 select
  org.*,
  row_number() over (partition by org.id, org.name order by org.creat_time asc) rk
 from
  org_test as org
) as temp
where
 rk = 1
平均分組 ntile

它將有序分區的行分配到指定數量的大致相等的組或桶中

可用場景

  • 求成績再前百分之 20 的分數

demo:

-- 求成績再前百分之 20 的分數
select
 score,
 ntile (5) over (order by score) buckets
from
 scores.ntile_demo
where
 buckets = 1;
錯位 lag/lead

定義

  • lag 提供對當前行之前的給定物理偏移的行的訪問

  • lead 提供對當前行之後的給定物理偏移量的行的訪問

通過這兩個函數可以在一次查詢中取出同一字段的前 n 行的數據 lag 和後 n 行的數據 lead 作爲獨立的列, 更方便地進行進行數據過濾

可用場景

  • 在比較同一個相鄰的記錄集內兩條相鄰記錄

  • 計算今日電錶消耗 (需計算今日電錶度數和昨日差值)

demo:

-- 語法
-- lag(field, num, defaultvalue)
-- 函數可以在一次查詢中取出當前行的同一字段 field 的前面第 num 行的數據，如果沒有用 defaultvalue 代替
-- lead(field, num, defaultvalue)
-- 函數可以在一次查詢中取出當前行的同一字段 field 的後面第 num 行的數據，如果沒有用 defaultvalue 代替

-- 計算今日材料消耗(需計算今日材料損耗和昨日差值)
select name,
       data,
    --今日抄表值
       use as use_today,
    --前一天抄表值
       lag(use,1,0) over(partition BY name order by data desc) as use_yesday
from test
分組取最大最小 first_value/last_value

first_value() : 取分組內排序後，截止到當前行，第一個值

last_value(): 取分組內排序後，截止到當前行，最後一個值

簡單地說你可以得到一列值中某字段上下其他行得字段值，和 lag/lead 函數有些近似

demo:

-- 數據準備
-- 計算每個月銷售額，上一月銷售額，下月銷售額，相鄰月銷售額平均值
insert into dc_test_order values
(100, 1), (200, 2),
(300, 3), (400, 4),
(500, 5), (600, 6),
(700, 7), (800, 8),
(900, 9)

-- 計算
select month,
  first_value(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) prev_month,
  sum(tot_sales) monthly_sales,
  last_value(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) next_month,
  avg(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) rolling_avg
from dc_test_order
group by month
order by month;
-- 結果
|month|prev_month|monthly_sales|next_month|rolling_avg|
+-----+----------+-------------+----------+-----------+
|    1|     100.0|        100.0|     200.0|      150.0|
|    2|     100.0|        200.0|     300.0|      200.0|
|    3|     200.0|        300.0|     400.0|      300.0|
|    4|     300.0|        400.0|     500.0|      400.0|
|    5|     400.0|        500.0|     600.0|      500.0|
|    6|     500.0|        600.0|     700.0|      600.0|
|    7|     600.0|        700.0|     800.0|      700.0|
|    8|     700.0|        800.0|     900.0|      800.0|
|    9|     800.0|        900.0|     900.0|      850.0|
+-----+----------+-------------+----------+-----------+
分析函數 cume_dist，perent_rank

這兩個函數使用較少，不支持 windows 字句

  • cume_dist 小於等於當前值的行數 / 分組內總行數

  • percent_rank 計算給定行的百分比排名

demo1:

-- 數據準備
-- 計算小於等於當前成績的人數佔總人數佔比
insert into dc_test_scores values
(1, 100), (2, 200),
(3, 300), (4, 400),
(5, 500)

-- 計算
select
    id,
    score,
    cume_dist() over(order by score) as rn1
from
 test

-- 結果
|id|score|rn1|
+--+---------+
| 1|100.0|0.2|
| 2|200.0|0.4|
| 3|300.0|0.6|
| 4|400.0|0.8|
| 5|500.0|1.0|
+--+-----+---+

demo2: 

-- 數據準備
-- 給定行的百分比排名
insert into dc_test_scores values
(1, 100), (2, 200),
(3, 300), (4, 400),
(5, 500)

--計算
select
 id,
 score,
 percent_rank() over (partition by id order by score) as rn1
from
 test

--結果
|id|score|rn1 |
+--+----------+
| 1|100.0|0.0 |
| 2|200.0|0.25|
| 3|300.0|0.5 |
| 4|400.0|0.75|
| 5|500.0|1.0 |
+--+-----+----+
聚合函數 sum, avg, count, max, min

場景

demo

疑問

給 mysql 字段指定寬度

對證數類型指定寬度，比如 Int(11) 不會起到作用，int 使用 16 爲存儲空間，他的表示範圍是確定的，int(1) 和 int(20) 沒有區別

代碼分表和分區表有什麼區別？

兩個維度，第一個是業務規則的變化對分表變化影響，分區表能否簡單實現。第二個是數據量以及查詢處理方面分區表能否滿足

第一個很顯然，如果一般的業務規則變化可能性不大，或者變化後，依然可以通過分區表簡單處理就能實現，這一個維度是沒什麼問題

第二個方面，首先分區表並不能加速訪問，而且一旦沒有命中帶上分區字段的唯一類索引，將會全表掃描，當然代碼分表也一樣，沒法定位數據所在表也是多表全部掃描。
而當數據量上來了之後，特別是涉及到分佈式管理，分區表就顯得力不從心，即使能滿足業務分離數據，分佈式的特點也使得分區表用不上

分區表這個特性，主要是用於做數據的劃分管理，不能優化性能。所以綜上所述，當對於一些是適用於數據管理的業務，而且量沒有大到要擴展存儲的情況下，譬如一些日誌表，七天或者一個月後就刪除那種，就可以用分區表做。涉及到較爲複雜或者數據較大的業務，適合業務代碼分表，而且更加靈活

count(distinct) 和 group by 和 partition by(窗口函數) 區別

count(distinct) 對比 group by

count(distinct) 喫內存，查詢快；group by 空間複雜度小，在時間複雜度允許的情況下，可以發揮他的空間複雜度優勢

group by 對比 partition by

group by 分組彙總後改變了表的行數，一行只有一個類別。而 partiition by 和 rank 函數不會減少原表中的行數

數據湖是什麼

關於什麼是雲原生架構，衆說紛紜。尋找到比較好的答案是以下三點特徵：

存儲和計算分離，計算能力和存儲能力均可獨立擴展；

多模態計算引擎支持，SQL、批處理、流式計算、機器學習等；

提供 serverless 態服務，確保足夠的彈性以及支持按需付費。

大屏實時展示數據方案

對於這一類實時數據場景來說，一般做法都比較簡單

數通過 fink 計算或者聚合之後將結果寫入 myslq/es/hbase/druid/kudu 等，然後提供查詢和分析，一般就是 kafka+flink 的架構

數據技術生態圈分類

視圖

相當於在統計邏輯和實際庫表之間提供了一種折中的方案。完成這個功能，邏輯上是必須有這麼幾道工序的，但又不想在每一道工序裏都落地一張實際的數據表，顯得繁瑣而臃腫，那麼就用視圖把這些中間的工序用視圖的形式去實現和替代。

遞歸

以前寫遞歸的 sql 記得是用的 find_in_set 函數，這邊最近又看到一種使用 with as 語法的辦法看起來也更簡單，看情況選擇即可

使用 mysql 遞歸 cte 完成。

demo 1

with RECURSIVE T as
(
select '1' as id,'10' as parent_id union all
select '2', '20' union all
select '3', '30' union all
select '20', '200' union all
select '10', '100' union all
select '100', '1000' union all
select '200', '2000' union all
select '1000', '10000'
)
, A as (
select id, parent_id from T where id = '1'
union all
select T.id,T.parent_id from T
inner join A on T.id=A.parent_id
)

select * from A;
-- 結果
-- id parent_id
-- 1 10
-- 10 100
-- 100 1000

demo 2: 遞歸一個連續時間表

--遞歸一個連續時間表
with t as
(
    select
        date_add( to_date('2022-04-14'), i) as timeline -- 基礎日期，起始時間
    from
        (select 80 as days) t
    lateral view
    posexplode(split(repeat(',', days), ',')) pe as i, x
)

診斷

 -- 查看成本
show status like 'last_query_cost'

優化

禁止負向條件查詢

禁止使用負向查詢 not、!=、<>、!<、!>、not in、not like 等，會導致全表掃描。

這條規定想滿足其實很難，有些業務必不可免需要用到，那麼可以考慮如果數據量大的情況使用以下用法：

select oid from order where uid = 1 and status != 1;

這條 sql 只要 uid 有索引，就可以先走索引縮小數據範圍，此時再接上一個負向查詢也沒什麼性能影響了。

拆分大的 insert/delete

子查詢

往往一個不合適的子查詢用法會形成一個性能瓶頸。如果子查詢的條件中使用了其外層的表的字段，這種子查詢就叫作相關子查詢。

分頁優化

-- demo1
select id, content from news order by title limit 50,5
--> 優化後
select news.id, news.content
from news inner join (
    select id from news order by title limit 50,5
) as tmp using(id)

-- demo2
select id from t limit 10000, 10
--> 優化後
select id from t where id > 10000 limit 10

group by/order by 優化

確保任何 group by 和 order by 的列只涉及到一個表中的列，這樣 mysql 纔可以用索引去優化。

join 優化

當表 a 和表 b 都用列 c 列來關聯時候，如果優化器關聯的順序是 a b，那麼只需要再 b 表 c 列添加索引即可；

具體原因可以參考優化器優化 sql 後得執行邏輯，反推就可以得到以上結果。

謂詞下推

謂詞下推將查詢語句中的過濾表達式計算儘可能下推到距離數據源最近的地方，以儘早完成數據的過濾，進而顯著地減少數據傳輸或計算的開銷。

謂詞下推案例

-- 謂詞下推到存儲層
-- demo1
select * from t where a < 1;
-- demo2
select * from t where a < substring('123', 1, 1);

-- 謂詞下推到 join 下方
-- demo3
select * from t join s on t.a = s.a where t.a < 1;
-- 在該查詢中，將謂詞 t.a < 1 下推到 join 前進行過濾，可以減少 join 時的計算開銷
-- 這條 sql 執行的是內連接，且 on 條件是 t.a = s.a，可以由 t.a < 1 推導出謂詞 s.a < 1，並將其下推至 join 運算前對 s 表進行過濾，可以進一步減少 join 時的計算開銷
-- 得到
select * from t join s on t.a = s.a and t.a < 1 and s.a < 1

謂詞下推失效案例

-- demo 1
-- 不對函數進行支持則無法下推
select * from t where substring('123', a, 1) = '1';
-- demo 2
-- 外連接中內表上的謂詞不能下推
-- 該謂詞沒有被下推到 join 前進行計算，這是因爲外連接在不滿足 on 條件時會對內表填充 NULL，而在該查詢中 s.a is null 用來對 join 後的結果進行過濾，如果將其下推到 join 前在內表上進行過濾，則下推前後不等價，因此不可進行下推
select * from t left join s on t.a = s.a where s.a is null;

數倉項目思考

sql\spark sql \ 其他場景 優化

列表優化

列表中涉及的業務信息表，用戶信息表全部都是大表，列表性能很差，短期內想分表需要改的業務太多，急需提高整體合同列表的性能。

採用訂閱 binlog 方案，訂閱表部分字段 [滿足查詢條件的字段]，將結果輸入新表，極大減少數據量，業務重要接口使用新表查詢。

繼續優化方案: 業務數據數據存入 es。

count 內增加條件

數倉進行各種複雜指標查詢，往往需要分組後對各種指標進行條件匹配在進行 count，常用以下兩種方式：

-- 常用以下兩種
-- 1
count(distinct
case when
    condition
then
 result1
else
    result2
end
)

-- 2
count(condition or null)
日 / 周 / 月 任務合併

使用一個 spark sql 定時任務同時寫多個相同類型的 sql，減少任務量，也可以把同類型任務歸類。

主要包含三部分 sql 即可：

  • 日子任務

  • 周子任務

  • 月子任務

如果不是每週一，則 sql 跳過周任務，如果不是每月一號，則 sql 跳過月任務。

select if(date_format('${yyyy-mm-dd}' ,"u") between '1' and '5','平日','週末')

周任務跳過:

月任務跳過:

最終得到得任務可以同時跑日周月表，到達時間就會進行對應的表生成，任務數降低，相同模塊表聚合度增強，更易維護，這些表我統一命名爲 d_7d_1m。

累計表任務優化

運營往往會提出彙總表需求

類似如下表頭：

可以看到該表是個彙總數據，是很常見的數倉需求。主要得難點字段如圖爲 累計數目，難點在於 需要將當天數目和之前數目合併。

目前數倉常用的方法是，任務每次執行出當天的累計數目，也就是當天的數據。

該方法存在的問題：

如果需要回溯歷史數據，那麼需要補充執行歷史任務，一旦數目過多，對任務提交執行機和計算服務都是很大挑戰。

比如最近遇到的如下需求：

將某累計表數據回溯至 2020 年

從當前時間回溯到 2020 年，超過 500 個任務實例，當前的服務器配置下，每次執行的實例數超過某個經驗值就可能出現各種問題，提交任務阻塞，超時失敗，cpu100%，oom 等等，直接就會引起各種告警，lead 們都會額外關注，對研發簡直是一種消磨，重複的噩夢每天都驚心動魄。

最初的方案，每天的數據需要一個任務實例，只能跑當天數據

-- 彙總數據表-日數據
insert overwrite table test.test partition(timeline='${yyyy-mm-dd-1d}')
select ...,
    count(distinct
           case when left(reg.create_time , 10) = '${yyyy-mm-dd-1d}'
           then reg.id
           else null
           end
           as create_cnt,
    count(distinct reg.id) as create_cnt_al
from
       ...

得到的數據大概是這樣的：

補數據的話其實就是將這個任務實例裏面的 yyyy-mm-dd-1d 傳入不同的值得到每天的數據，一個任務補一年就要 365 個實例

使用窗口函數簡單優化可以得到如下 sql(缺點是不存在天數的數據則不展示，增量時間不連續，預期可能希望是 0)

實現方式不唯一，這裏僅展示一種，可以推敲更簡便的方案省去一些查詢

-- 思路如下
-- 1. 首先使用 timeline 分組, 使用窗口函數, 計算每個時間分組內的一個增量數目
-- 2. 獲取每個時間分組的數目的最大值, 也就是該時間段產生的一個數目
-- 3. 使用窗口函數, 不指定分組(則默認就是整個表作爲一個分組), 此時使用 sum 得到累加值
select
    timeline,
    day_cnt,
    sum(day_cnt) over (order by timeline) as day_cnt_add
from (
    select
        timeline,
        max(create_cnt) as day_cnt
    from (
        select
            timeline,
            count(id) over(partition by timeline order by timeline rows between unbounded preceding and current row) as create_cnt
        from
            default.test
    ) as group_test
    group by
        timeline
    order by
        timeline
) as group_test

結果

6X3Uw1

一次性得到了從最初時間到現在每天的彙總和累計值

ps: 可以看到上面的第一列缺少部分天數，那幾天沒有數據產生，其實也應該產出一條數據，這個再後面的第三個方案處理了，這裏不重複。

但是，如果用了下面的方式太過於複雜，邏輯混亂，考慮到我們這邊用的是窗口函數，我們可以用下面方案試試。

不使用窗口函數的方案（複雜一點不推薦，使用老版本 mysql 不支持窗口時候可以用）

下面爲流程和註冊的累計表數據，但是還有個存在的問題就是累計表不一定是連續的 如果某天沒有數據，則這一天累計數據爲空，解決辦法就是把下面多個累計表按照時間 full join，使用分組函數 max() sum() 等查詢出每天的累計數據，不在此贅述。

with tmp2 as (
    select
    a.first_time,
    count(a.org_id) as num
    from
    (
        select
        test.org_id as org_id,
        min(left(create_date, 10)) as first_time
        from
        default.test as test
        where
        org_id != ''
        group by
        test.org_id
    ) as a
    group by a.first_time
)
select
 a.first_time,
 sum(b.num) as all_num
from
 tmp2 a,
 tmp2 b
where
 b.first_time <= a.first_time
group by a.first_time
order by a.first_time

--
with tmp as (
        select
        A.give_day,
        count(A.org_id) as num
        from(
                        select
                        a.*,
                        left(created_on, 10) as give_day
                        from
                        test_org as a
                ) A
        where
                A.give_day >= '2000-01-01'
        group by A.give_day
)
select
 a.give_day,
        -- a.num,
        sum(case when b.give_day = a.give_day then b.num else 0 end),
 sum(b.num) as all_num
from
 tmp a,
 tmp b
where
 b.give_day <= a.give_day
group by a.give_day
order by a.give_day

一張表需要多庫數據如何臨時導出

數倉建設時候除了一些需要每日 / 周 / 月展示的需求可以用一些定時離線任務也搞以外，還會有一些臨時查詢的需求，快速出數據，其中可能包含一些沒有拉取到數倉得數據信息。

此時基本上會從數倉查詢出部分數據，剩餘數據部分會去 mysql join 的方式去連接。

比如 select ... from users where id in (...)

問題： 這個時候拉取到的 ... 字段可能和數倉導出的 id 列無法對齊

可以用如下方式拉取

select ... from users where id in (...) order by field(id, 'id1', 'id2', ...)

得到的兩部分數據直接拼接起來就 ok。

引用

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/ZdZqgeM9hqnpdQ4hGPpNTA

猜你喜歡