基于多层关联表按BrokerId查询多维度交易总量

需求概述 #

需要编写SQL查询，关联以下数据表：

• Broker (Id, ...)
• Desk (Id, BrokerId, ...)
• Trader (Id, DeskId, ...)
• Orders (Id, TraderId, ...)
• Trade (Id, OrderId, TradePrice, TradeVolume, TradeTime, ...)

返回字段包括：

• Broker Id：经纪商ID
• 24hr traded vol：过去24小时的总交易量
• current month traded vol：当前自然月的总交易量
• year to date traded vol：当年年初至今的总交易量

查询支持参数@BrokerId：若参数为NULL则返回所有经纪商数据，否则仅返回指定经纪商的统计结果。

通用SQL实现（兼容多数数据库，日期函数可按需调整） #

SELECT
    b.Id AS `Broker Id`,
    SUM(CASE WHEN t.TradeTime >= NOW() - INTERVAL 24 HOUR THEN t.TradeVolume ELSE 0 END) AS `24hr traded vol`,
    SUM(CASE WHEN DATE_FORMAT(t.TradeTime, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') THEN t.TradeVolume ELSE 0 END) AS `current month traded vol`,
    SUM(CASE WHEN YEAR(t.TradeTime) = YEAR(NOW()) THEN t.TradeVolume ELSE 0 END) AS `year to date traded vol`
FROM
    Broker b
LEFT JOIN Desk d ON b.Id = d.BrokerId
LEFT JOIN Trader tr ON d.Id = tr.DeskId
LEFT JOIN Orders o ON tr.Id = o.TraderId
LEFT JOIN Trade t ON o.Id = t.OrderId
WHERE
    (@BrokerId IS NULL OR b.Id = @BrokerId)
GROUP BY
    b.Id
ORDER BY
    b.Id;

数据库适配说明 #

不同数据库的日期函数略有差异，以下是常见调整：

• SQL Server：替换NOW()为GETDATE()，DATE_FORMAT改为FORMAT或CONVERT，时间间隔用DATEADD(HOUR, -24, GETDATE())
• PostgreSQL：替换NOW() - INTERVAL 24 HOUR为CURRENT_TIMESTAMP - INTERVAL '24 hours'，DATE_FORMAT改为TO_CHAR(t.TradeTime, 'YYYY-MM')
• Oracle：替换NOW()为SYSDATE，时间间隔用SYSDATE - 1，月份匹配用TO_CHAR(t.TradeTime, 'YYYYMM') = TO_CHAR(SYSDATE, 'YYYYMM')

示例结果 #

当@BrokerId = 1时：

当@BrokerId = 3时：

当@BrokerId = NULL时（全量结果）：

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