SQL 開發中的十個高級用法

導讀

SQL 是一種每位數據開發者必備的開發語言，不同的用戶使用 SQL 語言的程度不同，最開始接觸到的 SQL 就是 SELECT ,INSERT, UPDATE, DELETE 以及 WHERE 子句對數據進行篩選，如果需要關聯，可能會使用 JOIN 關聯查詢多張表。隨着數據量的增多以及需求複雜性的要求，對數據開發者的要求可以不僅僅以上簡單的使用方式。今天我們一起來了解一些日常開發中常用的幾種 SQL 高級概念，帶你在 SQL Server 數據開發中起飛。

本文使用腳本 SQL 爲 SQL Server 2017 版本驗證過，本文章腳本與 TSQL 性能調優祕笈隨書腳本可以關注公衆號後發送 sql10 獲取，謝謝關注。

1，公共表達式 CTE

CTE (Common Table Expression), 公共表達式，在 SQL Server 2005 中引入的一個特性。

單個語句的執行範圍內定義的臨時結果集
只在查詢期間有效
可以自引用，也可以在查詢中多次引用
實現代碼的重複利用，提升代碼可讀性
以優雅的方式實現遞歸等複雜的查詢

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )

對比下面的兩種查詢語句，第一個語句中使用了子查詢進行查詢，近乎難以理解。

第一種：一般寫法

select Orders.orderid, Orders.orderdate, Orders.requireddate,
Orders.shippeddate,Orders.shipcity,Orders.shipaddress
from Sales.Orders
left join Sales.OrderDetails
on OrderDetails.orderid = Orders.orderid
where custid in(
select custid
from sales.Customers
where country in ('USA','Italy')
)
and OrderDetails.qty * OrderDetails.unitprice > 100
and datediff(day,requireddate,shippeddate) > 1;

第二種：公共表達式寫法

with cust as (
select custid
from sales.Customers
where country in ('USA','Italy')
),
qty as(
select orderid
from Sales.OrderDetails
where OrderDetails.qty * OrderDetails.unitprice > 100
)
select Orders.orderid, Orders.orderdate, Orders.requireddate,
Orders.shippeddate,Orders.shipcity,Orders.shipaddress
from Sales.Orders, qty, cust
where Orders.orderid = qty.orderid 
and Orders.custid = cust.custid
and datediff(day,requireddate,shippeddate) > 1;

對比以上兩種寫法，第一種寫法主要使用子查詢，第二種寫法是使用 CTE 公共表達式的寫法，代碼可讀性更高；其中 CTE 將代碼分解爲較小的塊，更利於後期的運維工作；而且 CTE 允許爲每個 CTE 分配不同的名稱。代碼可讀性也是項目交付的指標之一，除了代碼可讀性之外，CTE 可以用於實現遞歸查詢。

2，遞歸查詢

遞歸 CTE 是引用自己的 CTE，就像編程中的遞歸函數一樣。遞歸 CTE 經常用於查詢組織結構圖，文件系統，網頁之間的鏈接圖等的分層數據。

CTE 遞歸查詢構建需要三個部分：初始條件（也稱爲錨構件），遞歸調用表達式（引用 CTE 的遞歸查詢），終止條件（停止遞歸構建的終止條件）。CTE 遞歸查詢的僞代碼如下：

WITH cte_name ( column_name [,...n] )
AS
(
--Anchor member is defined 初始條件
CTE_query_definition 
UNION ALL
--Recursive member is defined referencing cte_name 
--遞歸調用表達式
CTE_query_definition 
)
-- Statement using the CTE
-- 遞歸查詢沒有顯式的遞歸終止條件，只有當遞歸子查詢返回空結果集（沒有數據行返回）或是超出了遞歸次數的最大限制時，才停止遞歸。
SELECT *
FROM cte_name

如下案例是使用遞歸查詢行政區劃的例子，詳細代碼可通過關注發送 “高級 SQL” 獲取樣例代碼。

with cte(Id,ParentID,Name,Level) as
(
select ID,ParentID,Name,0 as Level
from dbo.hierarchy 
where id=1

union all
select h.ID,h.ParentID,h.Name,c.Level+1 as Level
from dbo.hierarchy h
inner join cte c on h.ParentID=c.id 
--where c.id!=h.ID
)
select *
from cte
order by ParentID

以下是使用遞歸 CTE 從父集向子集查詢得到上海市（包含）下的所有行政區劃的信息示例：

行政區劃查詢

3，虛擬數字輔助表

數字輔助表是一個整數序列，可以用於生成日期和時間值序列，分裂值列表。通常建議在數據庫中保存這樣一張表，並填充儘可能多的數字，在需要的時候使用它。開發人員並不是所有的環境可以創建和向表中填充值以得到需要的邏輯，此時虛擬數字輔助表就派上了用場。

虛擬數字輔助表同樣是通過創建內聯表值型函數使用 CTE 公共表達式以及交叉連接創造的一張整數數字輔助表。下面就是數字輔助表的構建語句，在 5 級可以得到 4,294,967,296 行，滿足了大多數的場景。

CREATE FUNCTION [dbo].[GetNums](@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
    L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
            FROM L5)
  SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
  FROM Nums
  ORDER BY rownum;

GO

使用該函數可以產生實際需要的數字，比如我要獲取 100 到 110 之間的數字，可以通過如下方法獲得：

select * from dbo.getnums(100,110)

執行結果如下：

4，日期和時間值序列

在數據倉庫的構建過程中，我們常常遇到生成一個日期和時間序列的需求，序列的範圍是從開始日期到結束日期，且具有一定的時間間隔，比如 1 天，12 小時等。比如上一篇中介紹的數據倉庫維度表中的日期維度表，可以藉助上面實現的 GetNums 函數，接受輸入 start 和 end 作爲日期時間，使用 DateDiff 函數計算兩個時間之間的時間間隔。調用 GetNums 函數，輸入 low 爲 0， high 爲時間間隔，產生最終的時間序列如下。

declare @start as date= '20220810',
        @end as date = '20220820'
select dateadd(day, n, @start) as dt from dbo.getnums(0, datediff(day,@start,@end)) as nums

執行結果如下，我們獲取了從 2022-08-10 開始的 10 天的日期數據

間隔爲天的時間序列

加入間隔的單位是小時，調整查詢語句如下：

declare @start as datetime2= '2022-08-10 00:00:00.0000000',
        @end as datetime2 = '2022-08-20 12:00:00.0000000'
select dateadd(hour, n*12, @start) as dt from dbo.getnums(0, datediff(hour,@start,@end)/12) as nums

間隔爲 12 小時的時間序列

5，自聯結

一個 SQL 表自行連接自己，你可能會感覺沒有什麼用處，但是實際在某些場景下又是非常常見。許多現實場景中，比如員工信息，產品類別等等層級信息，需要通過自聯結查詢符合某些特殊場景的數據。

比如一個對陣表，參賽隊伍表中存儲了所有的參賽隊伍信息，明天所有的參賽隊伍就要開始比賽，我們需要爲所有的參賽隊伍隨機生成一份對陣表，這個 SQL 如何寫呢？使用表的自聯結就可以解決，如下爲兩種查詢語句：

--注意關聯條件，第一種查詢簡單高效
select a.name, a.city, b.name, b.city
from team as a, team as b
where a.name < b.name
order by 1;

--第二種使用了窗口函數降序排列獲取奇數數據
select *
from(
select a.name as aname, a.city as acity, b.name as bname, b.city as bcity,
row_number() over(order by a.name + b.name) rn 
from team as a, team as b
where a.name <> b.name
) result
where result.rn%2 = 1;

6，EXCEPT vs NOT IN

EXCEPT 和 NOT IN 用來比較兩個查詢或者表之間的行，但是他們之間存在細微的差別。

EXCEPT 會去重複，NOT IN 不會，除非在 SELECT 語句中顯式指定了去重；
EXCEPT 比較的是所有列，如果查詢的左側是右側具有不同數量的列，則查詢會導致錯誤。此外 UNION, INTERSECT 運算符組合的查詢也必須具有相等數量的表達式。NOT IN 要求將一個表中的單個列或者子查詢中的單個列進行比較，否則會導致錯誤。在進行多列比較時 NOT EXISTS 也是不錯的選擇；
如果右邊的表中包含 NULL 值，NOT IN 會返回一個空結果集，除非在右邊的表中進行了空值的處理，此時 EXCEPT 更好；

7，使用 CASE WHEN 實現行轉列

行轉列有兩種實現方法，SQL Server 2005 版本退出了 PIVOT 函數之外，我們也可以使用 CASE WHEN 語句來實現行轉列。例如，如果您有一個月列存儲了當月收入情況，但是您希望爲每個月創建一個單個列，則可以使用 CASE WHEN 實現數據重新格式化，以便每月都有一個收入列。

如下即爲我們使用 CASE WHEN 可以實現的行轉列功能。

Initial table:  
+------+---------+-------+  
| id   | revenue | month |  
+------+---------+-------+  
| 1    | 8000    | Jan   |  
| 2    | 9000    | Jan   |  
| 3    | 10000   | Feb   |  
| 1    | 7000    | Feb   |  
| 1    | 6000    | Mar   |  
+------+---------+-------+  
  
Result table:  
+------+-------------+-------------+-------------+-----+-----------+  
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |  
+------+-------------+-------------+-------------+-----+-----------+  
| 1    | 8000        | 7000        | 6000        | ... | null        |  
| 2    | 9000        | null        | null        | ... | null        |  
| 3    | null        | 10000       | null        | ... | null        |  
+------+-------------+-------------+-------------+-----+-----------+

8，Rank vs Dense Rank vs Ntile vs Row Number

SQL 標準支持 4 種用於排名計算的窗口函數，分別是 RANK, DENSE_RANK, NTILE, ROW_NUMBER。我們使用下面的例子來了解這 4 個窗口函數的區別。

select id,testid,
ROW_NUMBER() over( order by testid) as ROW_NUMBER_NO,
RANK() over(order by testid) as RANK_NO,
DENSE_RANK() over(order by testid) as DENSE_RANK_NO,
Ntile(4) over ( order by testid) as NTILE_NO
from test
order by testid

bwRbMH

窗口函數區別

9，Delta 值計算

Delta 值是一個希臘字母，最早出現在數學領域中使用，在漸漸發展過程中，也延伸到了投資領域。金融領域常遇到 Delta 數據指標的計算，代表衡量數據指標的變化幅度，可以是環比，也可能是同比。變化幅度的計算中，Lead() 和 LAG() 也就發揮作用了。SQL Server 2012 版本開始，引入了 LEAD 和 LAG 函數。

LAG/LEAD (scalar_expression [,offset] [,default])  
    OVER ( [ partition_by_clause ] order_by_clause )

lag 和 lead 有三個參數，第一個參數是列名，第二個參數是偏移的 offset，第三個參數是超出記錄窗口時的默認值。下面我們用例子來：

WITH T AS
 (
 SELECT 1 ID,10 NUM
 UNION ALL
 SELECT 1,20 
 UNION ALL
 SELECT 1,30 
 UNION ALL
 SELECT 2,40
 UNION ALL
 SELECT 2,50
 UNION ALL
 SELECT 2,60
 )
 SELECT ID,NUM,
 LAG(NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneDelta,
 LAG(NUM,1) OVER (PARTITION BY ID ORDER BY NUM) AS TwoDelta,
 LAG(NUM,2,0) OVER (PARTITION BY ID ORDER BY NUM) AS ThreeDelta
 FROM T;

按照上面的語句執行 LAG 函數，三個參數設置爲不同值，詳細執行結果如下：

LAG 用法

lead 函數與 lag 函數方向剛好相反，lead 是向前偏移指定的行數，默認都是 1 行。

WITH T AS
 (
 SELECT 1 ID,10 NUM
 UNION ALL
 SELECT 1,20 
 UNION ALL
 SELECT 1,30 
 UNION ALL
 SELECT 2,40
 UNION ALL
 SELECT 2,50
 UNION ALL
 SELECT 2,60
 )
 SELECT ID,NUM,
 LEAD(NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneDelta,
 LEAD(NUM,1) OVER (PARTITION BY ID ORDER BY NUM) AS TwoDelta,
 LEAD(NUM,2,0) OVER (PARTITION BY ID ORDER BY NUM) AS ThreeDelta
 FROM T;

按照上面的語句執行 LEAD 函數，三個參數設置爲不同值，詳細執行結果如下：

LEAD 用法

10，Running Total 計算

Running total 中文名稱爲累積統計，是一種常見的需求，比如計算銀行賬戶餘額，跟蹤倉庫中產品的庫存，跟蹤累計銷售額等等。SQL 中通常使用具有 SUM() 的窗口函數來計算運行總數。

DECLARE @T TABLE (ID char(1), Value int); 

INSERT INTO @T (ID, Value) VALUES 
('A', 1), 
('A', 1), 
('B', 1), 
('B', 1), 
('B', 1), 
('C', 1), 
('C', 1); 

SELECT 
    ID 
    ,Value 
    ,SUM(Value) OVER (PARTITION BY ID ORDER BY Value 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal1
 ,SUM(Value) OVER (ORDER BY Value 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal2
FROM @T 
ORDER BY ID, RunningTotal1, RunningTotal2;

執行累積統計計算結果如下：

累積統計計算

我們這裏大概列舉了 SQL Server 中經常使用到的一些中高級 SQL 使用方法，可以解決項目中常見的大多數問題。如果你對其中的用法或者還有其他的問題，可以加我的個人微信一起探討學習。

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

導讀