GCP BigQuery 按Qualifiers与TradeCatogary组合的5分钟多指标聚合查询实现

核心逻辑 #

• 用条件聚合实现单查询多维度指标输出：在聚合函数内部增加维度匹配规则，无需多表关联，仅扫描一次原表即可得到所有拆分指标
• 空值默认填0：交易数、交易量、交易金额指标直接通过IF(匹配规则, 统计字段, 0)实现，VWAP额外增加除0防护避免分母为空时报错

完整SQL代码 #

以下是基于你现有逻辑扩展的示例，你可以根据实际枚举的Qualifiers、TradeCatogary维度值自行扩展或删减对应字段，调整到你需要的21列即可：

SELECT
    TIMESTAMP_SECONDS(CAST((ROUND(UNIX_SECONDS(Date_Time) / 300) * 300) AS int64)) AS timestamp,
    -- 原有全量聚合指标
    count(*) AS tradeCount,
    SUM(Volume) AS totalVolume,
    SUM(Price * Volume) AS dollarTraded,
    IFNULL(SUM(Price * Volume) / NULLIF(SUM(Volume), 0), 0) AS VWAP,
    -- 按Qualifiers=U拆分的指标
    COUNT(IF(Qualifiers = 'U', 1, NULL)) AS count_U,
    SUM(IF(Qualifiers = 'U', Volume, 0)) AS volume_U,
    SUM(IF(Qualifiers = 'U', Price * Volume, 0)) AS dollar_U,
    IFNULL(SUM(IF(Qualifiers = 'U', Price * Volume, 0)) / NULLIF(SUM(IF(Qualifiers = 'U', Volume, 0)), 0), 0) AS VWAP_U,
    -- 按Qualifiers=K拆分的指标
    COUNT(IF(Qualifiers = 'K', 1, NULL)) AS count_K,
    SUM(IF(Qualifiers = 'K', Volume, 0)) AS volume_K,
    SUM(IF(Qualifiers = 'K', Price * Volume, 0)) AS dollar_K,
    IFNULL(SUM(IF(Qualifiers = 'K', Price * Volume, 0)) / NULLIF(SUM(IF(Qualifiers = 'K', Volume, 0)), 0), 0) AS VWAP_K,
    -- 按Qualifiers=U + TradeCatogary=OML拆分的指标
    COUNT(IF(Qualifiers = 'U' AND TradeCatogary = 'OML', 1, NULL)) AS count_U_OML,
    SUM(IF(Qualifiers = 'U' AND TradeCatogary = 'OML', Volume, 0)) AS volume_U_OML,
    SUM(IF(Qualifiers = 'U' AND TradeCatogary = 'OML', Price * Volume, 0)) AS dollar_U_OML,
    IFNULL(SUM(IF(Qualifiers = 'U' AND TradeCatogary = 'OML', Price * Volume, 0)) / NULLIF(SUM(IF(Qualifiers = 'U' AND TradeCatogary = 'OML', Volume, 0)), 0), 0) AS VWAP_U_OML,
    -- 按Qualifiers=U + TradeCatogary=KCL拆分的指标
    COUNT(IF(Qualifiers = 'U' AND TradeCatogary = 'KCL', 1, NULL)) AS count_U_KCL,
    SUM(IF(Qualifiers = 'U' AND TradeCatogary = 'KCL', Volume, 0)) AS volume_U_KCL,
    SUM(IF(Qualifiers = 'U' AND TradeCatogary = 'KCL', Price * Volume, 0)) AS dollar_U_KCL
FROM
    `dbd-sdlc-prod.HKG_NORMALISED.HKG_NORMALISED`
WHERE
    RIC = '1606.HK'
    AND Type="Trade"
    AND Date_Time BETWEEN TIMESTAMP('2016-07-11 00:00:00.000000') AND TIMESTAMP('2016-07-11 23:59:59.999999')
    AND Volume >0
    AND Price >0
GROUP BY 
    ROUND(UNIX_SECONDS(Date_Time) / 300), timestamp
ORDER BY timestamp

补充说明 #

所有字段的扩展逻辑完全一致，只需要调整IF内的维度匹配条件即可，不需要修改GROUP BY和WHERE逻辑，无匹配数据的时间段对应指标会自动填充0。

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