Microsoft SQL Server 2022 新特性之 T-SQL 语言增强

时间:2022-11-22 09:54:52

Microsoft SQL Server 2022 已经正式发布,可以下载使用。本文给大家介绍一下该版本中的部分 T-SQL 新功能。

窗口函数增强

新版本中的窗口函数支持命名窗口(WINDOW)子句,可以利用该子句定义窗口变量,然后在 OVER 子句中使用该窗口变量。

举例说明(示例脚本):

SELECT d.dept_name AS "部门名称", e.emp_name AS "姓名", e.salary AS "月薪",
       ROW_NUMBER() OVER w AS "row_number",
       RANK() OVER w AS "rank",
       DENSE_RANK() OVER w AS "dense_rank",
       PERCENT_RANK() OVER w AS "percent_rank"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
WINDOW w AS (PARTITION BY e.dept_id ORDER BY e.salary DESC);

部门名称 |姓名 |月薪      |row_number|rank|dense_rank|percent_rank       |
-----+---+--------+----------+----+----------+-------------------+
行政管理部|刘备 |30000.00|         1|   1|         1|                0.0|
行政管理部|关羽 |26000.00|         2|   2|         2|                0.5|
行政管理部|张飞 |24000.00|         3|   3|         3|                1.0|
人力资源部|诸葛亮|24000.00|         1|   1|         1|                0.0|
人力资源部|黄忠 | 8000.00|         2|   2|         2|                0.5|
人力资源部|魏延 | 7500.00|         3|   3|         3|                1.0|
...

示例使用 WINDOW 子句定义了一个窗口变量 w,然后在 4 个窗口函数的 OVER 子句中使用了该变量。这种使用窗口变量的写法可以简化窗口选项的输入。

新增的 WINDOW 子句还支持其他用法,具体可以查看官方文档

IS DISTINCT FROM

IS [NOT] DISTINCT FROM 的作用是判断两个表达式是否相同,它们支持 NULL 值的比较。例如:

CREATE TABLE test(id INT);
INSERT INTO test VALUES(100);

SELECT * 
FROM test 
WHERE 1 IS NOT DISTINCT FROM 1;
id |
---+
100|

SELECT * 
FROM test 
WHERE 1 IS NOT DISTINCT FROM 2;
id|
--+

SELECT * 
FROM test 
WHERE NULL IS NOT DISTINCT FROM NULL;
id |
---+
100|

SELECT * 
FROM test 
WHERE NULL = NULL;
id|
--+

注意,IS NOT DISTINCT FROM 的作用类似于 =,IS DISTINCT FROM 的作用类似于 !=,具体说明参考官方文档

时间序列函数

新增 DATE_BUCKET() 函数,用于返回从指定时间点开始,以固定日期时间间隔为存储桶的每个起始日期时间。例如:

DECLARE @date DATETIME2 = '2022-11-20 21:22:11';
DECLARE @orign DATETIME2 = '2022-11-01 00:00:00';

SELECT DATE_BUCKET(WEEK, 1, @date, @orign);
                       |
-----------------------+
2022-11-15 00:00:00.000|

SELECT DATE_BUCKET(WEEK, 2, @date, @orign);
                       |
-----------------------+
2022-11-15 00:00:00.000|

SELECT DATE_BUCKET(WEEK, 3, @date, @orign);
                       |
-----------------------+
2022-11-01 00:00:00.000|

从 2022-11-01 00:00:00 开始,以 1 或者 2 周为存储桶计算,包含 2022-11-20 21:22:11 的存储桶的起始日期为 2022-11-15 00:00:00。如果以 3 周为存储桶计算,包含 2022-11-20 21:22:11 的存储桶的起始日期为 2022-11-01 00:00:00。

新增 GENERATE_SERIES() 函数,可以用于生成数字序列,例如:

SELECT value FROM GENERATE_SERIES(1, 5);
value
-----------
1
2
3
4
5

SELECT value FROM GENERATE_SERIES(10, 1, -2);
value
-----------
10
8
6
4
2

另外,以下窗口函数支持 IGNORE NULLS 和 RESPECT NULLS 选项,分别表示计算时忽略或者考虑 NULL 值:

JSON 函数增强

新版本中的 ISJSON() 函数增加了一个可选参数:

ISJSON ( expression [, json_type_constraint] )

参数 json_type_constraint 用于指定要测试的 JSON 类型,包括 VALUE、ARRAY、OBJECT 或 SCALAR。例如:

SELECT ISJSON('true', VALUE);

SELECT ISJSON('test string', VALUE);

新增 JSON_PATH_EXISTS() 函数,可以用于检查 JSON 字符串中是否存在指定的 SQL/JSON 路径。例如:

DECLARE @jsonInfo NVARCHAR(MAX)

SET @jsonInfo=N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';

SELECT JSON_PATH_EXISTS(@jsonInfo,'$.info.address'); -- 1

新增 JSON_OBJECT() 函数,用于构造 JSON 对象。例如:

SELECT JSON_OBJECT('name':'value', 'type':1);

{"name":"value","type":1}

新增 JSON_ARRAY() 函数,用于构造 JSON 数组。例如:

SELECT JSON_ARRAY('a', JSON_OBJECT('name':'value', 'type':1));

["a",{"name":"value","type":1}]

聚合函数增强

新增 APPROX_PERCENTILE_CONT() 以及 APPROX_PERCENTILE_DISC() 函数,可以返回一组数据中的近似百分位数。

对于大型数据集,它们可以用作 PERCENTILE_CONT 以及 PERCENTILE_DISC 的替代方法,与响应时间较慢的准确百分位值相比,可以接受可忽略的错误和较快的响应。

T-SQL 函数增强

新增 GREATEST() 以及 LEAST(),分别用于返回一个或多个参数中的最大值以及最小值。例如:

SELECT GREATEST ('6.62', 3.1415, N'7')AS GreatestVal,
       LEAST ('6.62', 3.1415, N'7')AS LeastVal;

GreatestVal|LeastVal|
-----------+--------+
     7.0000|  3.1415|

STRING_SPLIT() 函数支持一个额外的可选参数,可以为拆分后的每个字符串返回一个序号。例如:

SELECT * FROM STRING_SPLIT('SQL Server 2022', ' ', 1);

value |ordinal|
------+-------+
SQL   |      1|
Server|      2|
2022  |      3|

新增 DATETRUNC() 函数,可以对日期时间数据进行截断。例如:

DECLARE @d datetime2 = '2021-12-08 11:30:15.1234567';
SELECT 'Year', DATETRUNC(year, @d);
SELECT 'Quarter', DATETRUNC(quarter, @d);
SELECT 'Month', DATETRUNC(month, @d);
SELECT 'Week', DATETRUNC(week, @d); -- Using the default DATEFIRST setting value of 7 (U.S. English)
SELECT 'Iso_week', DATETRUNC(iso_week, @d);
SELECT 'DayOfYear', DATETRUNC(dayofyear, @d);
SELECT 'Day', DATETRUNC(day, @d);
SELECT 'Hour', DATETRUNC(hour, @d);
SELECT 'Minute', DATETRUNC(minute, @d);
SELECT 'Second', DATETRUNC(second, @d);
SELECT 'Millisecond', DATETRUNC(millisecond, @d);
SELECT 'Microsecond', DATETRUNC(microsecond, @d);

Year        2021-01-01 00:00:00.0000000
Quarter     2021-10-01 00:00:00.0000000
Month       2021-12-01 00:00:00.0000000
Week        2021-12-05 00:00:00.0000000
Iso_week    2021-12-06 00:00:00.0000000
DayOfYear   2021-12-08 00:00:00.0000000
Day         2021-12-08 00:00:00.0000000
Hour        2021-12-08 11:00:00.0000000
Minute      2021-12-08 11:30:00.0000000
Second      2021-12-08 11:30:15.0000000
Millisecond 2021-12-08 11:30:15.1230000
Microsecond 2021-12-08 11:30:15.1234560

LTRIM() 以及 RTRIM() 函数支持一个额外的可选参选,可以指定要删除的字符。例如:

SELECT LTRIM('123abc.' , '123.'), RTRIM('.123abc.' , 'abc.');

    |    |
----+----+
abc.|.123|

TRIM() 函数可以选择从字符串的开头和/或末尾删除空格字符或其他指定字符。例如:

SELECT TRIM(LEADING '-' FROM  '---SQL Server 2022---') AS lead,
       TRIM(TRAILING '-' FROM  '---SQL Server 2022---') AS trail,
       TRIM(BOTH '-' FROM  '---SQL Server 2022---') AS both;

lead              |trail             |both           |
------------------+------------------+---------------+
SQL Server 2022---|---SQL Server 2022|SQL Server 2022|

位操作函数

新增,LEFT_SHIFT() 函数以及 << 运算符,用于实现按位左移运算。例如:

SELECT LEFT_SHIFT(1, 5), 1<<5;

  |  |
--+--+
32|32|

新增 RIGHT_SHIFT() 函数以及 >> 运算符,用于实现按位右移运算。例如:

SELECT RIGHT_SHIFT(32, 2), 32>>2;

 | |
-+-+
8|8|

新增 BIT_COUNT() 函数,用于返回整数或者二进制数中设置为 1 的位数。例如:

SELECT BIT_COUNT(10) as Count1, BIT_COUNT(0x0a) as Count2;

Count1|Count2|
------+------+
     2|     2|

整数 10 和十六进制 0x0a 的二进制表达式都为 1010,只有 2 位设置为 1。

新增 GET_BIT() 函数,用于返回整数或者二进制数中指定偏移量对应的位,偏移量从 0 开始。例如:

SELECT GET_BIT(10, 1) as Get_1st_Bit, GET_BIT(10, 2) as Get_2nd_Bit;

Get_1st_Bit|Get_2nd_Bit|
-----------+-----------+
          1|          0|

整数 10 的二进制表达式为 1010,偏移量为 1 的位为 1,偏移量为 2 的位为 0。

新增 SET_BIT() 函数,用于设置并返回整数或者二进制数中指定偏移量对应的位,偏移量从 0 开始。例如:

SELECT SET_BIT(10, 0) as Result1,  SET_BIT(10, 1, 0) as Result2;

Result1|Result2|
-------+-------+
     11|      8|

整数 10 的二进制表达式为 1010,将偏移量为 0 的位设置为 1,返回结果为二进制的 1011,也就是十进制的 11。将偏移量为 1 的位设置为 0,返回结果为二进制的 1000,也就是十进制的 8。