如何在Cosmos中按两小时窗口统计记录数并实现类UNION ALL合并

核心需求梳理 #

你需要实现两项查询能力：

• 按2小时滚动时间窗口统计各窗口内的记录总数，预期输出格式如下：

Time window                count
10:00 AM to 12:00 PM        100
12:00 PM TO 2 AM             200
2:00 AM to 4 AM             300
4:00 AM  to 6 AM              400

• 实现类似SQL UNION ALL 的结果合并效果，已知Cosmos DB原生不支持UNION ALL语法，需要替代实现。

注意：你提供的参考SQL存在别名混用问题，部分过滤条件把表别名a错写成了c，后续方案会修正这个错误。
本次查询使用的样例数据如下：

ingest_time          count
2022-05-09T22:00:00  10
2022-05-09T22:10:00  20
2022-05-09T22:30:00  40
2022-05-09T23:00:00  10
2022-05-09T23:20:00  45
2022-05-09T23:40:00  50
2022-05-09T24:00:00  100

注：样例中2022-05-09T24:00:00为非法ISO时间格式，实际等价于2022-05-10T00:00:00。

2小时滚动窗口统计实现 #

Cosmos DB Core API内置DateTimeBin时间分桶函数，可以直接按固定步长切分时间窗口，配合字符串拼接即可生成要求的时间窗口展示字段，查询语句如下：

SELECT
    -- 拼接2小时窗口起止时间的展示文本
    CONCAT(
        LEFT(DateTimeBin(a.ingest_time, 'hour', 2, '0001-01-01T00:00:00'), 16),
        ' to ',
        LEFT(DateTimeAdd('hour', 2, DateTimeBin(a.ingest_time, 'hour', 2, '0001-01-01T00:00:00')), 16)
    ) AS [Time window],
    COUNT(1) AS count
FROM a
WHERE a.file_model = "Log_Type"
-- 替换为实际需要统计的时间范围
AND a.ingest_time >= "2022-05-09T20:00:00"
AND a.ingest_time < "2022-05-10T06:00:00"
GROUP BY DateTimeBin(a.ingest_time, 'hour', 2, '0001-01-01T00:00:00')
ORDER BY DateTimeBin(a.ingest_time, 'hour', 2, '0001-01-01T00:00:00')

说明：DateTimeBin会自动将时间对齐到指定步长（此处为2小时）的窗口起点，无需手动计算时间边界；如果需要输出12小时制带AM/PM的时间格式，搭配DateTimePart函数做格式转换即可。

针对给出的样例数据，上述查询的统计结果为：

无UNION ALL语法下的多结果集合并实现 #

Cosmos DB不支持UNION ALL，有两种可落地的替代方案：

方案1：条件聚合（推荐，性能最优） #

通过CASE WHEN做条件判断，一次查询返回多个统计指标，完全等价于UNION ALL的统计逻辑，且只需要扫描一次数据，性能远高于多次查询，修正原SQL别名错误后的语句如下：

SELECT
    -- 第一个统计项：符合Log_Type条件的总文件数
    COUNT(1) AS File_count,
    -- 第二个统计项：符合Log_Type + mapr_time非空 + 文件名含SDK的文件数
    SUM(
        CASE 
            WHEN a.mapr_time != "" AND CONTAINS(a.fileName, 'SDK', true) THEN 1
            ELSE 0
        END
    ) AS SDI_Files_Processed_Count
FROM a
WHERE a.file_model = "Log_Type"
AND a.ingest_time >= "2022-05-09T20:00:00"
AND a.ingest_time <= "2022-05-09T22:00:00"

方案2：数组拼接（返回和UNION ALL完全一致的行结构） #

如果需要和UNION ALL完全一致的行式输出（两个指标分两行返回，而非一行两列），可以通过构造数组+ARRAY_CONCAT+JOIN的方式实现合并，语句如下：

SELECT VALUE result FROM (
    SELECT
        ARRAY(
            SELECT {"metric_name":"File_count", "metric_value":COUNT(1)}
            FROM a WHERE a.file_model = "Log_Type"
            AND a.ingest_time >= "2022-05-09T20:00:00" AND a.ingest_time <= "2022-05-09T22:00:00"
        ) AS arr1,
        ARRAY(
            SELECT {"metric_name":"SDI_Files_Processed_Count", "metric_value":COUNT(1)}
            FROM a WHERE a.file_model = "Log_Type"
            AND a.ingest_time >= "2022-05-09T20:00:00" AND a.ingest_time <= "2022-05-09T22:00:00"
            AND a.mapr_time != "" AND CONTAINS(a.fileName,'SDK',true)
        ) AS arr2
) t JOIN result IN ARRAY_CONCAT(arr1, arr2)

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