SQLServer2022新特性 GENERATE_SERIES函数_sqlserver 2022 函数的高级用法

SQLServer2022新特性 GENERATE_SERIES函数，在给定间隔内生成一系列数字。 序列值之间的间隔和步骤由用户定义。

参考官方地址
https://learn.microsoft.com/en-us/sql/t-sql/functions/generate-series-transact-sql?view=sql-server-ver16

1、本文内容

• 语法
• 参数
• 返回类型
• 权限
• 示例
• 相关内容

适用于：

• SQL Server 2022 (16.x)
• Azure SQL 数据库
• Azure SQL 托管实例
• Microsoft Fabric 中的 SQL 分析终结点
• Microsoft Fabric 中的仓库

在给定间隔内生成一系列数字。 序列值之间的间隔和步骤由用户定义。

兼容性级别 160

GENERATE_SERIES 要求兼容性级别至少为 160。 兼容性级别低于 160 时，数据库引擎将找不到 GENERATE_SERIES 函数。

若要更改数据库的兼容性级别，请参阅查看或更改数据库的兼容性级别。

Transact-SQL 语法约定

2、语法

GENERATE_SERIES ( start , stop [ , step ] )
1

3、参数

• start

  间隔中的第一个值。 start 会指定为变量、文本或者类型为 tinyint、smallint、int、bigint、decimal 或 numeric 的标量表达式。

• stop

  间隔中的最后一个值。 stop 会指定为变量、文本或者类型为 tinyint、smallint、int、bigint、decimal 或 numeric 的标量表达式。 最后一个生成的步骤值超过 stop 值后，序列将停止。

  stop 的数据类型必须与 start 的数据类型匹配。

• step

  表明在系列中的步骤之间要增加或减少的值的数量。 step 是 tinyint、smallint、int、bigint、decimal 或 numeric 类型的表达式。 step 可以是负数或正值，但不能为零 (0)。

  该参数可选。 如果 start 小于 stop，则 step 的默认值为 1，否则，如果 start 大于 stop，则默认值为 -1。

  如果 start 小于 stop 并且为 step 指定了负值，或者如果 start 大于 stop 并且为 step指定了正值，会返回一个空结果集。

4、返回类型

返回一个包含一系列值的单列表，其中每个值与前一个值相差 step。 列的名称为 value。 输出与 start 和 stop 的类型相同。

5、权限

GENERATE_SERIES 不需要任何权限。 但是，用户需要数据库 EXECUTE 权限，以及查询用作输入的任何数据的权限。

6、示例

以下示例展示了调用 GENERATE_SERIES 的语法。

6.1、以 1 为增量生成 1 到 10 之间的一系列整数值（默认）

SELECT value FROM GENERATE_SERIES(1, 10);

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

6.2、以 5 为增量生成 1 到 50 之间的一系列整数值

SELECT value FROM GENERATE_SERIES(1, 50, 5);

value
-----------
1
6
11
16
21
26
31
36
41
46
1
2
3
4
5
6
7
8
9
10
11
12
13
14

6.3、以 0.2 为增量生成 0.0 到 1.0 之间的一系列十进制值

DECLARE @start decimal(2, 1) = 0.0;
DECLARE @stop decimal(2, 1) = 1.0;
DECLARE @step decimal(2, 1) = 0.2;

SELECT value FROM GENERATE_SERIES(@start, @stop, @step);

value
---------------------------------------
0.0
0.2
0.4
0.6
0.8
1.0

(6 行受影响)

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

6.4、生成一个日期序列

Using the GENERATE_SERIES() function to a series of dates

生成从 '2024-06-26' 开始的 7 个连续的日期值

The following example uses the GENERATE_SERIES() function to generate a series of 7 date values starting from 2024-06-26

SELECT CONVERT(DATE, DATEADD (DAY, VALUE, '2024-06-26')) AS DATE FROM GENERATE_SERIES (0, 6);

DATE            
----------------
      2024-06-26
      2024-06-27
      2024-06-28
      2024-06-29
      2024-06-30
      2024-07-01
      2024-07-02

(7 rows affected)
1
2
3
4
5
6
7
8
9
10
11
12
13

它是如何工作的。

• 首先，使用GENERATE_SERIES()函数生成一系列从0到6的整数。这些数字表示我们在开始日期(‘2024-06-26’)上添加的天数。
• 其次，对GENERATE_SERIES()函数生成的每个数字，使用DATEADD()函数将相应的天数添加到开始日期(‘2024-06-26’)上。
• 第三，使用convert()函数将DATETIME值转换为DATE。

How it works.

First, generate a series of integers from 0 to 6 using the GENERATE_SERIES() function. These numbers represent the number of days that we add to the starting date (‘2024-06-26’).

Second, for each number generated by the GENERATE_SERIES() function, add the corresponding number of days to the starting date (‘2024-06-26’) using the DATEADD() function.

Third, convert the DATETIME values to DATE using the CONVERT() function.

6.5、Using the GENERATE_SERIES() function to calculate compound interest over years

使用GENERATE_SERIES()函数计算历年的复利

First, create a new table called t_savings that stores the principal and annual interest rate
首先，创建一个名为t_savings 的新表，用于存储本金和年利率

CREATE TABLE t_savings 
(
    id INT IDENTITY PRIMARY KEY,
    principal DECIMAL(18,2) NOT NULL,
    interest_rate DEC(19,5) NOT NULL
);
1
2
3
4
5
6

Second, insert rows into the t_savings table

其次，向储蓄表中插入行

INSERT INTO t_savings (principal, interest_rate)
VALUES (1008, 0.05),(5000, 0.18),
(6700, 0.53),(5056, 0.87),(6212, 0.54);
1
2
3

Third, retrieve data from the t_savings table

第三，从储蓄表中检索数据

SELECT * FROM t_savings

id          principal                               interest_rate
----------- --------------------------------------- ---------------------------------------
1           1008.00                                 0.05000
2           5000.00                                 0.18000
3           6700.00                                 0.53000
4           5056.00                                 0.87000
5           6212.00                                 0.54000

(5 行受影响)
1
2
3
4
5
6
7
8
9
10
11

Finally, cross join the savings table with the series of numbers generated by the GENERATE_SERIES() function to calculate the ending balance each year

最后，将储蓄表与GENERATE_SERIES()函数生成的一系列数字交叉连接，以计算每年的期末余额

SELECT
  id,
  principal,
  interest_rate,
  VALUE,
  principal * POWER(1 + interest_rate, VALUE) balance
FROM t_savings
  CROSS JOIN GENERATE_SERIES(1, 3);

id          principal                               interest_rate                           VALUE       balance
----------- --------------------------------------- --------------------------------------- ----------- ---------------------------------------
1           1008.00                                 0.05000                                 1           1058.400000
1           1008.00                                 0.05000                                 2           1111.320000
1           1008.00                                 0.05000                                 3           1166.891040
2           5000.00                                 0.18000                                 1           5900.000000
2           5000.00                                 0.18000                                 2           6962.000000
2           5000.00                                 0.18000                                 3           8215.150000
3           6700.00                                 0.53000                                 1           10251.000000
3           6700.00                                 0.53000                                 2           15684.030000
3           6700.00                                 0.53000                                 3           23996.586000
4           5056.00                                 0.87000                                 1           9454.720000
4           5056.00                                 0.87000                                 2           17680.326400
4           5056.00                                 0.87000                                 3           33062.195200
5           6212.00                                 0.54000                                 1           9566.480000
5           6212.00                                 0.54000                                 2           14732.379200
5           6212.00                                 0.54000                                 3           22687.839120

(15 行受影响)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

参考文档地址如下

https://www.sqlservertutorial.net/sql-server-system-functions/sql-server-generate_series/

SQL Server GENERATE_SERIES() Function (sqlservertutorial.net)

6.5、批量生成一些测试数据

SELECT value AS key_value,NEWID() AS sys_guid,RAND(CHECKSUM(NEWID())) AS RandomFloat, ABS(CHECKSUM(NEWID())) % 101 AS RandomNumber FROM generate_series(1,10) ;

key_value   sys_guid                             RandomFloat            RandomNumber
----------- ------------------------------------ ---------------------- ------------
1           52801F8D-6239-4EB3-B088-ADAA21B6421B 0.193339705149037      37
2           AA1B40CD-69E6-4D31-9DC3-08040F0D7BAD 0.609335014906728      89
3           C87AE677-64A2-4DD2-8F15-BD5B88656458 0.405448226599017      77
4           31123D2D-1139-4DF5-95E7-907325D218EC 0.878666761066185      54
5           6124C97D-0B60-4DBD-A661-9B98A53E80F4 0.765808999456877      11
6           12AF0A56-33D4-4823-8DE6-4BD32385778F 0.754044405713803      96
7           B129CA05-C857-41CE-BA87-6337DF53AFB2 0.04266710136897       39
8           C01D6FD8-A31D-40B3-A3A9-60AB42D3D910 0.262966783899889      11
9           F61693CB-FCDD-41CD-A443-BD2332953219 0.179683512783314      16
10          6950C933-90B8-4B1B-84E6-111323C46E0C 0.036969944911055      46

(10 行受影响)

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

注意：

• RAND(CHECKSUM(NEWID())) 生成 0 到 1 之间的随机浮点数
• 使用 NEWID() 和 CHECKSUM() 生成 0 到某个整数（例如 100）之间的随机整数
• % 101 是为了确保结果在 0 到 100 之间（包括 0 和 100）。
• 由于 RAND() 函数在 SQL Server 中的工作方式，如果你在一个查询中多次调用它（没有使用 NEWID() 或其他方法作为种子），它可能会为查询中的每一行返回相同的值
• 随机数的生成在数据库系统中通常不是最佳实践，除非有特定的需求（例如测试或模拟）。在大多数情况下，最好在应用程序级别生成随机数，并将它们作为参数传递给数据库查询

7、相关内容

• SEQUENCES (Transact-SQL) - SQL Server | Microsoft Learn
• Relational operators (Transact-SQL) - SQL Server | Microsoft Learn