進階資料分析模式

Databricks SQL 入門

Kevin Barlow

Data Manager

動機

Lakehouse 圖示－分析

子查詢

透過 SQL 查詢「巢狀」來達成
- 將巢狀查詢視為新的「資料表」
- 實務上類似建立檢視
適用多種情境
- 取得另一個查詢的結果
- 簡化複雜查詢
- 在單一查詢中彙總

SELECT store, totalRev, product

FROM (
    SELECT count(*) as count,
          sum(revenue) as totalRev,
          min(price) as minPrice,
          max(units) as maxUnits,
          region,
          store,
          product
      FROM sales 
      GROUP BY region, store, product
) q

視窗函式

一類 SQL 函式與技巧
在特定範圍的列上進行計算
- 這些列彼此相關
範例：
- 依時間範圍計算指標
- 計算相鄰列的變化
- 跨多個維度計算

SELECT name,
       dept,
       RANK() OVER 
           (PARTITION BY dept 
         ORDER BY salary) AS rank
  FROM employees;

name	dept	salary	rank
Lisa	Sales	10000	1
Alex	Sales	30000	2
Fred	Engineering	21000	1
Tom	Engineering	23000	2

進階 Databricks SQL 函式

RANK()

在分割區內比較列，為每列計算名次

SELECT a,
       b,
       RANK() OVER(PARTITION BY a ORDER BY b DESC),
    FROM table_name;

a	b	rank
A1	3	1
A1	1	3
A1	2	2
A2	1	1

LAG() 與 LEAD()

回傳同一列的前一筆（LAG()）或後一筆（LEAD()）值

SELECT a,
       b,
       LAG(b) OVER (PARTITION BY a ORDER BY b)
    FROM table_name;

a	b	lag
A1	3	NULL
A1	1	3
A1	2	1
A2	1	NULL

一起來練習吧！

Databricks SQL 入門