如何在SQL中基于微秒级UNIX时间实现多粒度数据聚合？

核心逻辑 #

UNIX时间戳是从1970-01-01 UTC开始的累计时间计数，微秒级戳就是该数值乘以1000000。自定义粒度聚合的核心是把每个时间戳映射到其所属的区间起始点，再按这个起始点分组计算。

实现方案（按Azure服务分） #

1. Azure Data Explorer (KQL) #

假设你的数据表为YourTable，时间戳列是字符串格式的timestamp_str，按以下步骤操作：

定义参数

先把起始/结束时间戳转成数值型，同时指定聚合粒度的微秒数：

// 定义时间范围与聚合粒度
let Tstart = tolong("1684738800000000");
let Tstop = tolong("1684825200000000");
let granularity_us = 3 * 60 * 60 * 1000000; // 3小时对应的微秒数，可按需修改

聚合查询

YourTable
// 过滤目标时间范围内的数据
| where tolong(timestamp_str) between (Tstart .. Tstop)
// 转换字符串时间戳为数值型微秒数
| extend timestamp_us = tolong(timestamp_str)
// 计算当前时间戳所属的区间起始点（对齐Tstart）
| extend interval_start_us = floor((timestamp_us - Tstart) / granularity_us) * granularity_us + Tstart
// 可选：转成datetime格式便于阅读
| extend interval_start_datetime = datetime_add('microsecond', interval_start_us, datetime(1970-01-01))
// 按区间分组聚合（示例为求和，可替换为count/avg等）
| summarize sum(value_column) by interval_start_us, interval_start_datetime
// 按时间顺序排序
| order by interval_start_us

2. Azure SQL Database #

若使用Azure SQL，逻辑一致，语法调整为SQL：

定义参数

DECLARE @Tstart BIGINT = 1684738800000000;
DECLARE @Tstop BIGINT = 1684825200000000;
DECLARE @granularity_us BIGINT = 3 * 3600 * 1000000; -- 3小时微秒数，按需修改

聚合查询

SELECT
    -- 计算区间起始点（微秒级）
    FLOOR((CAST(timestamp_str AS BIGINT) - @Tstart) / @granularity_us) * @granularity_us + @Tstart AS interval_start_us,
    -- 转成datetime格式便于阅读
    DATEADD(microsecond, FLOOR((CAST(timestamp_str AS BIGINT) - @Tstart) / @granularity_us) * @granularity_us + @Tstart, '1970-01-01') AS interval_start_datetime,
    -- 聚合操作（示例为求和，可替换为COUNT/AVG等）
    SUM(value_column) AS total_value
FROM YourTable
WHERE CAST(timestamp_str AS BIGINT) BETWEEN @Tstart AND @Tstop
GROUP BY
    FLOOR((CAST(timestamp_str AS BIGINT) - @Tstart) / @granularity_us) * @granularity_us + @Tstart,
    DATEADD(microsecond, FLOOR((CAST(timestamp_str AS BIGINT) - @Tstart) / @granularity_us) * @granularity_us + @Tstart, '1970-01-01')
ORDER BY interval_start_us;

粒度参数快速替换 #

不同聚合粒度对应的微秒数计算：

• 2秒：2 * 1000000
• 7小时：7 * 60 * 60 * 1000000
• 1天：24 * 60 * 60 * 1000000

注意事项 #

• 必须将字符串格式的时间戳转换为数值型（long/BIGINT）才能进行算术运算。
• 上述方案是基于你指定的Tstart对齐区间，确保分组从你的起始时间开始；若无需对齐Tstart，直接用floor(timestamp_us / granularity_us) * granularity_us计算区间起始点即可。

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