GORM是否支持在单条查询中实现多统计计数？

我定义了如下结构体和原生SQL查询代码：

type Result struct {
    StartedLast15Minutes  int `gorm:"column:started_last_15_minutes"`
    StartedLast60Minutes  int `gorm:"column:started_last_60_minutes"`
    StartedLast24Hours    int `gorm:"column:started_last_24_hours"`
    StartedLast48Hours    int `gorm:"column:started_last_48_hours"`
    FinishedLast15Minutes int `gorm:"column:finished_last_15_minutes"`
    FinishedLast60Minutes int `gorm:"column:finished_last_60_minutes"`
    FinishedLast24Hours   int `gorm:"column:finished_last_24_hours"`
    FinishedLast48Hours   int `gorm:"column:finished_last_48_hours"`
}

statsResult := Result{}
err = g.db.
    Raw(`
SELECT 
    COUNT(*) filter (where oe.timestamp  >= current_timestamp - interval '15 minutes' and type = 1 ) as started_last_15_minutes,
    COUNT(*) filter (where oe.timestamp  >= current_timestamp - interval '1 hour' and type = 1 ) as started_last_60_minutes,
    COUNT(*) filter (where oe.timestamp  >= current_timestamp - interval '24 hours' and type = 1 ) as started_last_24_hours,
    COUNT(*) filter (where oe.timestamp  >= current_timestamp - interval '48 hours' and type = 1 ) as started_last_48_hours,
    COUNT(*) filter (where oe.timestamp  >= current_timestamp - interval '15 minutes' and type = 37 ) as finished_last_15_minutes,
    COUNT(*) filter (where oe.timestamp  >= current_timestamp - interval '1 hour' and type = 37 ) as finished_last_60_minutes,
    COUNT(*) filter (where oe.timestamp  >= current_timestamp - interval '24 hours' and type = 37 ) as finished_last_24_hours,
    COUNT(*) filter (where oe.timestamp  >= current_timestamp - interval '48 hours' and type = 37 ) as finished_last_48_hours
FROM onboarding_events as oe
`).
    Scan(&statsResult).
    Error

但尝试转换为GORM链式语法时，只找到多查询的实现方式：

g.db.
    Model(model.OnboardingEvent{}).
    Where("timestamp  >= current_timestamp - interval '24 hours' and type = 37").
    Count(&singleCound).
    Where("timestamp  >= current_timestamp - interval '48 hours' and type = 37").
    Count(&singleCount2)

请问GORM是否支持在单条查询中实现多统计计数？

回答 #

GORM支持单条查询实现多统计计数，有两种常用实现方式：

1. 使用Select配合聚合表达式构建链式查询 #

通过GORM的Select方法直接编写聚合统计逻辑，最终将结果扫描到你的Result结构体中：

var statsResult Result
err := g.db.Model(&model.OnboardingEvent{}).
    Select([]string{
        "COUNT(*) filter (where timestamp >= current_timestamp - interval '15 minutes' and type = 1) as started_last_15_minutes",
        "COUNT(*) filter (where timestamp >= current_timestamp - interval '1 hour' and type = 1) as started_last_60_minutes",
        "COUNT(*) filter (where timestamp >= current_timestamp - interval '24 hours' and type = 1) as started_last_24_hours",
        "COUNT(*) filter (where timestamp >= current_timestamp - interval '48 hours' and type = 1) as started_last_48_hours",
        "COUNT(*) filter (where timestamp >= current_timestamp - interval '15 minutes' and type = 37) as finished_last_15_minutes",
        "COUNT(*) filter (where timestamp >= current_timestamp - interval '1 hour' and type = 37) as finished_last_60_minutes",
        "COUNT(*) filter (where timestamp >= current_timestamp - interval '24 hours' and type = 37) as finished_last_24_hours",
        "COUNT(*) filter (where timestamp >= current_timestamp - interval '48 hours' and type = 37) as finished_last_48_hours",
    }).
    Scan(&statsResult).Error

2. 保留原生Raw查询 #

你最初使用的Raw方法本身就是GORM官方支持的特性，完全可以继续使用。这种方式在处理复杂聚合查询时，比链式语法更直接，无需强行转换为GORM链式调用，逻辑可读性更强。

两种方式都能实现单查询多统计的需求，可根据场景选择：如果需要复用GORM的模型关联、钩子等特性，优先选择链式查询方案；如果查询逻辑复杂，原生SQL写法更清晰，就保留Raw方案。

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