Azure Synapse中Generate_Series/CTE无法使用，如何生成日期范围？

Azure Synapse SQL池（专用池/无服务器池）不支持GENERATE_SERIES函数和递归CTE，以下是两种可行的替代方案：

方法1：临时数字表生成分钟级日期序列 #

通过非递归CTE生成覆盖年度分钟数的数字序列，再基于起始日期计算目标日期范围：

-- 创建临时数字表，生成0到525599的数字（对应一年总分钟数：365*24*60=525600）
CREATE TABLE #Numbers (n INT);

WITH N1 AS (SELECT 1 AS n UNION ALL SELECT 1),
N2 AS (SELECT 1 FROM N1 a, N1 b),
N3 AS (SELECT 1 FROM N2 a, N2 b),
N4 AS (SELECT 1 FROM N3 a, N3 b),
N5 AS (SELECT 1 FROM N4 a, N4 b),
N6 AS (SELECT 1 FROM N5 a, N5 b)
INSERT INTO #Numbers
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
FROM N6
WHERE ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 <= 525599;

-- 生成年度分钟级日期序列
DECLARE @StartOfYear DATETIME = DATEADD(MINUTE, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0));

SELECT DATEADD(MINUTE, n, @StartOfYear) AS DATE_TIME
FROM #Numbers
WHERE DATEADD(MINUTE, n, @StartOfYear) < DATEFROMPARTS(YEAR(GETDATE())+1,1,1);

DROP TABLE #Numbers;

方法2：创建永久日期维度表（推荐长期使用） #

若需频繁生成日期序列，建议预先创建包含多粒度日期信息的永久维度表，后续查询直接复用，性能更优：

创建并填充日期维度表 #

CREATE TABLE DateDimension (
    DateTimeValue DATETIME PRIMARY KEY,
    Year INT,
    Month INT,
    Day INT,
    Hour INT,
    Minute INT
);

-- 填充5年的分钟级日期数据（可按需调整时间范围）
WITH N1 AS (SELECT 1 AS n UNION ALL SELECT 1),
N2 AS (SELECT 1 FROM N1 a, N1 b),
N3 AS (SELECT 1 FROM N2 a, N2 b),
N4 AS (SELECT 1 FROM N3 a, N3 b),
N5 AS (SELECT 1 FROM N4 a, N4 b),
N6 AS (SELECT 1 FROM N5 a, N5 b),
Numbers AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
    FROM N6
    WHERE ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 <= 525599 * 5
)
INSERT INTO DateDimension
SELECT 
    DATEADD(MINUTE, n, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0)) AS DateTimeValue,
    YEAR(DATEADD(MINUTE, n, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0))) AS Year,
    MONTH(DATEADD(MINUTE, n, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0))) AS Month,
    DAY(DATEADD(MINUTE, n, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0))) AS Day,
    DATEPART(HOUR, DATEADD(MINUTE, n, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0))) AS Hour,
    DATEPART(MINUTE, DATEADD(MINUTE, n, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0))) AS Minute
FROM Numbers;

查询年度分钟级日期序列 #

SELECT DateTimeValue AS DATE_TIME
FROM DateDimension
WHERE DateTimeValue >= DATEADD(MINUTE, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
  AND DateTimeValue < DATEFROMPARTS(YEAR(GETDATE())+1,1,1);

注意事项 #

• 非递归CTE在Azure Synapse中是支持的，上述方案均基于该特性生成数字序列，规避了递归CTE的限制。
• 临时表方案适合一次性需求，日期维度表适合重复调用场景，可按需选择。

内容的提问来源于stack exchange，提问作者Alex Darling Cpt Darling