ocean

Help Desk 說：「使用者回報登入錯誤，你幫我修一下。」
RD 回：「我先看 log，嗯…沒看到錯誤」
Help Desk：「我不是叫你看 log，我是要你先試著登錄看看啊！」
RD：「那我得先知道帳號」
Help Desk：「我剛不是說了嗎？就是那個登入錯誤的使用者啊！」

SQL 的執行順序，理性、結構化，你說「先 SELECT 再篩資料」，它說「不行，我得先把資料來源 JOIN 起來再說」

了解 SQL 的邏輯執行順序， 當你懂它的思考邏輯，你就不會再覺得它「不聽話」， 反而能跟它合作無間，讓查詢更快、更準、更漂亮



💼 邏輯執行順序

SQL 查詢的邏輯執行順序，並不是你「寫」的順序，而是資料庫「想」的順序

步驟 關鍵字 意義
1 FROM / JOIN 先決定資料來源（要用哪些表）
2 ON 決定 JOIN 的配對條件
3 WHERE 過濾出你想要的資料
4 GROUP BY 把資料分組（分類）
5 HAVING 過濾掉不符合條件的群組
6 SELECT 挑出你想顯示的欄位
7 UNION 等集合運算 合併多個查詢結果
8 ORDER BY 排序結果
9 OFFSET / FETCH 取出指定筆數（分頁）

首先，FROM 和 JOIN 是 SQL 語句執行的第一步。它們的邏輯結果是一個笛卡爾積，決定了接下來要操作的資料集，但要注意的是，邏輯執行順序並不代表物理執行順序，實際上資料庫在獲取表中的資料之前會使用 ON 和 WHERE 過濾條件進行最佳化查詢!

其次，應用 ON 條件對上一步的結果進行過濾並生成新的資料集；然後，執行 WHERE 子句對上一步的資料集再次進行過濾

接著，基於 GROUP BY 子句指定的表示式進行分組；同時，對於每個分組計算聚合函式 agg_func 的結果。經過 GROUP BY 處理之後，資料集的結構就發生了變化，只保留了分組欄位和聚合函式的結果；如果存在 GROUP BY 子句，可以利用 HAVING 針對分組後的結果進一步進行過濾，通常是針對聚合函式的結果進行過濾；

接下來，SELECT 可以指定要拿的欄位；如果指定了 DISTINCT 關鍵字，需要對結果集進行去重複操作。另外還會為指定了 AS 的欄位生成別名；

如果還有集合運算子（UNION、INTERSECT、EXCEPT）和其他的 SELECT 語句，執行該查詢並且合併兩個結果集。對於集合操作中的多個 SELECT 語句，資料庫通常可以支援併發執行；

然後，應用 ORDER BY 子句對結果進行排序。如果存在 GROUP BY 子句或者 DISTINCT 關鍵字，只能使用分組欄位和聚合函式進行排序；否則，可以使用 FROM 和 JOIN 表中的任何欄位排序；最後，OFFSET 和 FETCH（LIMIT、TOP）限定了最終返回的行數。例如 WHERE 子句在 HAVING 子句之前執行，因此我們應該儘量使用 WHERE 進行資料過濾，避免無謂的操作；除非業務需要針對聚合函式的結果進行過濾



💼 在 WHERE 條件中使用別名的錯誤

1
2
3
SELECT emp_name AS empname
FROM employee
WHERE empname ='張飛'; -- 只能使用 emp_name

這段 SQL 會出錯，因為 WHERE 子句執行時，SELECT 子句還沒被執行。換句話說，此時 empname 這個別名「還不存在」。



💼 GROUP BY 之後欄位的變化

1
2
3
SELECT dept_id, emp_name, AVG(salary)
FROM employee
GROUP BY dept_id;

這段 SQL 也會出錯，因為在 GROUP BY 之後，結果集只保留了分組欄位（dept_id）和聚合函式結果（AVG(salary)），非分組欄位（這裡是 emp_name）就「不再存在」於結果集裡。
如果想同時看到員工與部門平均薪資，可以用「視窗函式（Window Function）」

1
2
3
4
5
SELECT 
    emp_name,
    dept_id,
    AVG(salary) OVER(PARTITION BY dept_id) AS dept_avg_salary
FROM employee;


💼 篩選條件放在 on 和 where 後的區別

1
2
3
4
SELECT e.emp_name, d.dept_name
FROM employee e
LEFT JOIN department d ON (e.dept_id = d.dept_id)
WHERE e.emp_name ='張飛';
emp_name dept_name
張飛 行政管理部

第一個查詢（WHERE）：WHERE 是在 JOIN 完成之後過濾結果。因此，只留下 emp_name = ‘張飛’ 的那筆資料

1
2
3
SELECT e.emp_name, d.dept_name
FROM employee e
LEFT JOIN department d ON (e.dept_id = d.dept_id AND e.emp_name ='張飛');
emp_name dept_name
劉備 [NULL]
關羽 [NULL]
張飛 行政管理部
諸葛亮 [NULL]
…

第二個查詢（ON）：ON 是在 JOIN 過程中 決定「哪些資料可以匹配」。因為這是 LEFT JOIN，左表（employee）的所有資料都會被保留，但只有符合 e.emp_name = ‘張飛’ 的那一筆會配到右表（department）的資料，其他的員工因為不符合 ON 條件，右表的資料欄位就會是 NULL。



💼 一個電商平台想分析每個用戶的首單情況

1
2
3
4
5
6
7
8

SELECT 
    user_id, 
    MIN(order_date) as first_order_date,
    order_amount
FROM orders
GROUP BY user_id

雖然 MIN (order_date) 可以找出首單日期，但 order_amount 不在聚合函數內，且不在 GROUP BY 中，這會導致拿到的 order_amount 是不確定的，因此 SQL 會噴錯
SQL 的執行順序是先 GROUP BY，然後執行聚合函數，最後選擇非聚合列。要修正這個問題，可以使用子查詢 & Window Function：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT 
    user_id, 
    first_order_date,
    order_amount
FROM (
    SELECT 
        user_id, 
        order_date,
        order_amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) as rn
    FROM orders
) sub
WHERE rn = 1



💼 公司要給每個部門薪資排名前三的員工發放獎金

1
2
3
4
5
6
7
8
9

SELECT 
    department,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM employees
WHERE salary_rank <= 3

WHERE 比 SELECT 早執行，因此在 WHERE 中不能引用在 SELECT 中定義的別名（這裡是 salary_rank）。
正確的方法是使用子查詢或 CTE

1
2
3
4
5
6
7
8
9
10
11
12

WITH RankedEmployees AS (
    SELECT 
        department,
        employee_name,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
    FROM employees
)
SELECT * FROM RankedEmployees
WHERE salary_rank <= 3



💼 遊戲公司想找出連續登錄三天或以上的用戶

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

WITH UserLoginDates AS (
    -- 轉換日期 & 移除當天重複登入資料
    SELECT DISTINCT
        User_Id,
        CONVERT(date, Login_Date) AS LoginDate
    FROM #User_Login
),
LoginStreaks AS (
    -- 計算登入串流
    SELECT
        User_Id,
        LoginDate,
        DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY User_Id ORDER BY LoginDate), LoginDate) AS StreakGroup
    FROM UserLoginDates
)
-- 找出連續登入至少三天的用戶
SELECT User_Id
FROM LoginStreaks
GROUP BY User_Id, StreakGroup
HAVING COUNT(*) >= 3;

在第一個 CTE 中使用 DISTINCT 是為了確保每個用戶每天只計算一次登錄。
通過日期減去列號來創建連續日期的組，連續日期會得到相同的 StreakGroup 值。



💼 有一個銷售數據表，需要動態生成每個產品在不同月份的銷售額報表。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SELECT 
    Product_Id,
    SUM(CASE WHEN MONTH(Sale_Date) = 1 THEN Amount ELSE 0 END) as Jan_sales,
    SUM(CASE WHEN MONTH(Sale_Date) = 2 THEN Amount ELSE 0 END) as Feb_sales,
    SUM(CASE WHEN MONTH(Sale_Date) = 3 THEN Amount ELSE 0 END) as Mar_sales,
    SUM(CASE WHEN MONTH(Sale_Date) = 4 THEN Amount ELSE 0 END) as Apr_sales,
    SUM(CASE WHEN MONTH(Sale_Date) = 5 THEN Amount ELSE 0 END) as May_sales,
    SUM(CASE WHEN MONTH(Sale_Date) = 6 THEN Amount ELSE 0 END) as Jun_sales,
    SUM(CASE WHEN MONTH(Sale_Date) = 7 THEN Amount ELSE 0 END) as Jul_sales,
    SUM(CASE WHEN MONTH(Sale_Date) = 8 THEN Amount ELSE 0 END) as Aug_sales,
    SUM(CASE WHEN MONTH(Sale_Date) = 9 THEN Amount ELSE 0 END) as Sep_sales,
    SUM(CASE WHEN MONTH(Sale_Date) = 10 THEN Amount ELSE 0 END) as Oct_sales,
    SUM(CASE WHEN MONTH(Sale_Date) = 11 THEN Amount ELSE 0 END) as Nov_sales,
    SUM(CASE WHEN MONTH(Sale_Date) = 12 THEN Amount ELSE 0 END) as Dec_sales
FROM #Sales
GROUP BY Product_Id
HAVING SUM(Amount) > 10000;

GROUPBY >> 聚合函数 >> HAVING



💼 金融應用需要計算每個用戶的交易累計總和，並標記首次超過 1000 的交易

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

WITH TransactionSums AS (
    SELECT 
        user_id,
        transaction_date,
        amount,
        SUM(amount) OVER (PARTITION BY user_id ORDER BY transaction_date) as cumulative_sum,
        CASE 
            WHEN SUM(amount) OVER (PARTITION BY user_id ORDER BY transaction_date) > 1000 THEN 1 
            ELSE 0 
        END as exceed_1000
    FROM transactions
),
FirstExceed AS (
    SELECT *,
        LAG(cumulative_sum, 1, 0) OVER (PARTITION BY user_id ORDER BY transaction_date) as previous_cumulative_sum
    FROM TransactionSums
)
SELECT *
FROM FirstExceed
WHERE exceed_1000 = 1
  AND previous_cumulative_sum <= 1000;

LAG(cumulative_sum, 1, 0)：往前取一筆的 cumulative_sum；第三個參數 0 代表如果沒有上一筆（第一筆資料），就給 0。這樣每一筆交易就同時知道當前累積金額（cumulative_sum）、前一筆累積金額（previous_cumulative_sum）

_ exceed_1000 = 1 → 這筆交易的累計金額已經超過 1000；

  • previous_cumulative_sum <= 1000 → 但上一筆交易還沒超過。


💼 零售商想要顯示每個類別銷售額前 3 的產品，以及每個類別的總銷售額

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

WITH ByTotalPayTypeRank AS (
	SELECT 
		TradesOrderThirdPartyPayment_TypeDef,
		TradesOrderThirdPartyPayment_TotalPayment,
		SUM(TradesOrderThirdPartyPayment_TotalPayment) OVER (PARTITION BY TradesOrderThirdPartyPayment_TypeDef) AS PayTypeSum,
		ROW_NUMBER() OVER (PARTITION BY TradesOrderThirdPartyPayment_TypeDef ORDER BY TradesOrderThirdPartyPayment_TotalPayment DESC) AS SaleRankByPayType
	FROM TradesOrderThirdPartyPayment(NOLOCK)
)

SELECT *
FROM ByTotalPayTypeRank
WHERE SaleRankByPayType <= 3


SELECT TradesOrderThirdPartyPayment_TypeDef, SUM(TradesOrderThirdPartyPayment_TotalPayment) AS PayTypeSum
FROM TradesOrderThirdPartyPayment(NOLOCK)
GROUP BY TradesOrderThirdPartyPayment_TypeDef



💼 股票分析師想計算每支股票的 7 天移動平均價格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

WITH MovingAverages AS (
    SELECT 
        stock_id, 
        trade_date, 
        price,
        AVG(price) OVER (
            PARTITION BY stock_id 
            ORDER BY trade_date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS moving_avg
    FROM stock_prices
)
SELECT 
    stock_id, 
    trade_date, 
    price, 
    moving_avg
FROM MovingAverages
WHERE moving_avg > price;

🔹 HAVING 不能直接用 CASE 結果欄位名稱，要包成子查詢

sql 執行順序如下

1
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

HAVING 在 SELECT 之前執行，而 CASE WHEN … AS CustomerLevel 是在 SELECT 階段才產生的「暫時欄位名稱」，所以 HAVING 看不到 SELECT 的別名!

會出錯

1
2
3
4
5
6
7
8
9
SELECT CustomerId, SUM(Total) AS TotalSales,
  CASE 
    WHEN SUM(Total) < 40 THEN 'Normal'
    WHEN SUM(Total) >= 40 THEN 'VIP'
    ELSE 'Undefined'
  END AS CustomerLevel
FROM invoices
GROUP BY CustomerId
HAVING CustomerLevel = 'VIP'

包成子查詢

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
  CustomerLevel,
  COUNT(DISTINCT CustomerId) AS CustomerCnt
FROM
(
  SELECT
    CustomerId,
    CASE 
      WHEN SUM(Total) < 40 THEN 'Normal'
      WHEN SUM(Total) >= 40 THEN 'VIP'
      ELSE 'Undefined'
    END AS CustomerLevel
  FROM invoices
  GROUP BY CustomerId
) AS SubQuery
GROUP BY CustomerLevel