如何统计近两个月Car会话数？解决SQL子查询多行返回错误

问题背景 #

有一张Transactions表，包含字段id、type、guest_id、timestamp，需要统计近两个月的Car会话总数，规则是：同一guest在同一小时内的所有type为'Car'的交易算作1个会话。原尝试的SQL触发了上述错误：

SELECT  
    (
        SELECT COUNT(*)::int
        FROM transactions t
        WHERE t.type = 'Car'
        AND t.timestamp  <= gs + '1 hour'::interval
          AND t.timestamp > gs
        group by t.guest_id
    ) AS count
    FROM generate_series('2022-11-17 00:00:00'::timestamp, '2023-01-17 23:59:59'::timestamp, '1 hour'::interval) AS gs        
    ORDER BY gs.date ASC

错误原因 #

子查询中使用了GROUP BY t.guest_id，这意味着每个时间片gs（每小时）会返回多行结果（每个有Car交易的guest对应一行），但SELECT子句中的子查询作为字段只能返回单个值，因此数据库抛出错误。

解决方案 #

方案1：按小时统计每个小时的Car会话数 #

如果需要按小时维度统计每个小时内的会话数量（每个guest每小时算1个会话），可以使用JOIN结合COUNT(DISTINCT)实现：

SELECT 
    gs.hour_start,
    COUNT(DISTINCT t.guest_id) AS session_count
FROM generate_series('2022-11-17 00:00:00'::timestamp, '2023-01-17 23:59:59'::timestamp, '1 hour'::interval) AS gs(hour_start)
LEFT JOIN transactions t 
    ON t.type = 'Car'
    AND t.timestamp > gs.hour_start
    AND t.timestamp <= gs.hour_start + '1 hour'::interval
GROUP BY gs.hour_start
ORDER BY gs.hour_start ASC;

方案2：统计近两个月的Car会话总数 #

如果只需要统计近两个月的总会话数，无需按小时拆分，可以直接通过DISTINCT结合时间截断来实现：

SELECT COUNT(DISTINCT (guest_id, DATE_TRUNC('hour', timestamp))) AS total_car_sessions
FROM transactions
WHERE type = 'Car'
AND timestamp BETWEEN '2022-11-17 00:00:00'::timestamp AND '2023-01-17 23:59:59'::timestamp;

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