SQL 开发中的十个高级用法

导读

SQL 是一种每位数据开发者必备的开发语言，不同的用户使用 SQL 语言的程度不同，最开始接触到的 SQL 就是 SELECT ,INSERT, UPDATE, DELETE 以及 WHERE 子句对数据进行筛选，如果需要关联，可能会使用 JOIN 关联查询多张表。随着数据量的增多以及需求复杂性的要求，对数据开发者的要求可以不仅仅以上简单的使用方式。今天我们一起来了解一些日常开发中常用的几种 SQL 高级概念，带你在 SQL Server 数据开发中起飞。

1，公共表达式 CTE

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

1. 单个语句的执行范围内定义的临时结果集
2. 只在查询期间有效
3. 可以自引用，也可以在查询中多次引用
4. 实现代码的重复利用，提升代码可读性
5. 以优雅的方式实现递归等复杂的查询

[ 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

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 用来比较两个查询或者表之间的行，但是他们之间存在细微的差别。

1. EXCEPT 会去重复，NOT IN 不会，除非在 SELECT 语句中显式指定了去重；
2. EXCEPT 比较的是所有列，如果查询的左侧是右侧具有不同数量的列，则查询会导致错误。此外 UNION, INTERSECT运算符组合的查询也必须具有相等数量的表达式。NOT IN 要求将一个表中的单个列或者子查询中的单个列进行比较，否则会导致错误。在进行多列比较时 NOT EXISTS 也是不错的选择；
3. 如果右边的表中包含 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

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 函数，三个参数设置为不同值，详细执行结果如下：

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 函数，三个参数设置为不同值，详细执行结果如下：

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 使用方法，可以解决项目中常见的大多数问题。如果你对其中的用法或者还有其他的问题，可以加我的个人微信一起探讨学习。

往期文章

数据仓库系列

数据仓库之维度表

数据仓库之日期维度表

Hive 系列

Hive 必知必会（一）介绍

Hive 必知必会（二）基本操作

Hive 必知必会（三）基本操作（续）

SQL Server 优化

一文读懂 SQL Server 执行计划