毫秒级时间分组异常：Azure Synapse查询Parquet数据聚合精度不符

问题背景 #

使用Azure Synapse无服务器SQL池查询Azure Data Lake Gen2中的Parquet数据，通过Microsoft SQL Server对接Grafana做可视化。现有查询意图按200毫秒分辨率分组聚合，但实际仅能按1秒粒度聚合；尝试改用MILLISECOND时间单位时触发溢出错误。

原查询语句 #

SELECT
    DATEADD(SECOND, DATEDIFF(SECOND, '2020', t) / (200/1000.0) * (200/1000.0), '2020') AS time,
    AVG(accelerationx) as AVG_accelerationx
FROM
    OPENROWSET(
         BULK 'https://cssdatalakestoragegen2.dfs.core.windows.net/cssdatalakestoragegen2filesystem/3BA199E2/CAN2_gnssimu/*/*/*/*',
         FORMAT = 'PARQUET'
   ) AS r
WHERE
    t BETWEEN '2020-10-28T14:35:31Z' AND '2020-10-28T14:38:10Z'
GROUP BY
    DATEDIFF(SECOND, '2020', t) /  (200/1000.0)
ORDER BY time
OFFSET 0 ROWS;

问题现象 #

• 预期按200毫秒分组，但实际结果为1秒粒度聚合（原始Parquet数据分辨率为100毫秒）
• 将DATEDIFF的时间单位替换为MILLISECOND时，触发溢出错误：

convert frame from rows error: mssql: The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

问题原因 #

1. 秒级分组的根源：DATEDIFF(SECOND, '2020', t)返回整数秒数，丢失了毫秒级信息。后续除法/乘法运算本质是对整秒数操作，最终DATEADD生成的time列仍为秒级精度，导致分组粒度只能到1秒。
2. 溢出错误的根源：DATEDIFF(MILLISECOND, '2020', t)返回的毫秒数远超过INT类型最大值（约21亿），触发整数溢出。

解决方案 #

方案一：使用DATEDIFF_BIG避免溢出（通用场景） #

通过DATEDIFF_BIG返回BIGINT类型的毫秒数，直接按200毫秒间隔分组：

SELECT
    DATEADD(MILLISECOND, (DATEDIFF_BIG(MILLISECOND, '2020-01-01', t) / 200) * 200, '2020-01-01') AS time,
    AVG(accelerationx) AS AVG_accelerationx
FROM
    OPENROWSET(
         BULK 'https://cssdatalakestoragegen2.dfs.core.windows.net/cssdatalakestoragegen2filesystem/3BA199E2/CAN2_gnssimu/*/*/*/*',
         FORMAT = 'PARQUET'
   ) AS r
WHERE
    t BETWEEN '2020-10-28T14:35:31Z' AND '2020-10-28T14:38:10Z'
GROUP BY
    DATEDIFF_BIG(MILLISECOND, '2020-01-01', t) / 200
ORDER BY time
OFFSET 0 ROWS;

• 核心：DATEDIFF_BIG支持返回大整数，避免毫秒数溢出；通过/200取整实现200毫秒分组。

方案二：基于当天毫秒数计算（单日查询场景） #

如果查询时间范围限定在同一天，可仅计算当天内的毫秒数，避免大数运算：

SELECT
    DATEADD(MILLISECOND, 
        DATEDIFF(MILLISECOND, CAST(t AS DATE), t) / 200 * 200, 
        CAST(t AS DATE)
    ) AS time,
    AVG(accelerationx) AS AVG_accelerationx
FROM
    OPENROWSET(
         BULK 'https://cssdatalakestoragegen2.dfs.core.windows.net/cssdatalakestoragegen2filesystem/3BA199E2/CAN2_gnssimu/*/*/*/*',
         FORMAT = 'PARQUET'
   ) AS r
WHERE
    t BETWEEN '2020-10-28T14:35:31Z' AND '2020-10-28T14:38:10Z'
GROUP BY
    DATEADD(MILLISECOND, 
        DATEDIFF(MILLISECOND, CAST(t AS DATE), t) / 200 * 200, 
        CAST(t AS DATE)
    )
ORDER BY time
OFFSET 0 ROWS;

• 核心：通过CAST(t AS DATE)获取当天零点，计算当前时间与零点的毫秒差（该数值远小于INT最大值，不会溢出），再按200毫秒分组。

内容的提问来源于stack exchange，提问作者mfcss