基于Symfony 6和Doctrine按时间区间统计活跃用户数

直接搜这些关键词就能找到对应的实现方案：

• 时间区间重叠统计
• 固定时间间隔分组统计
• Doctrine 原生SQL 时间分组
• MySQL/PostgreSQL 时间桶函数
• 活跃用户 时间切片统计

你的需求本质是统计每个15分钟时段内，会话周期（created_at到updated_at）与该时段有重叠的唯一用户数，以下是具体实现方案：

1. 核心逻辑说明 #

用户会话只要满足 会话开始时间 ≤ 时段结束时间 且 会话结束时间 ≥ 时段开始时间，就属于该时段的活跃用户。同时需要先生成连续的15分钟时间序列，避免遗漏无数据的时段。

2. 原生SQL查询（MySQL为例） #

假设你的会话表名为 user_session，字段包括 id（uniqid）、created_at（datetime）、updated_at（datetime）：

WITH time_intervals AS (
    -- 生成从起始日期到结束日期的所有15分钟间隔
    SELECT 
        DATE_ADD(:start_date, INTERVAL (15 * n) MINUTE) AS interval_start,
        DATE_ADD(:start_date, INTERVAL (15 * (n + 1)) MINUTE) AS interval_end
    FROM 
        -- 生成足够多的数字（这里覆盖了24小时内的所有间隔，可按需扩展）
        (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL SELECT 39 UNION ALL SELECT 40 UNION ALL SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43 UNION ALL SELECT 44 UNION ALL SELECT 45 UNION ALL SELECT 46 UNION ALL SELECT 47 UNION ALL SELECT 48 UNION ALL SELECT 49 UNION ALL SELECT 50 UNION ALL SELECT 51 UNION ALL SELECT 52 UNION ALL SELECT 53 UNION ALL SELECT 54 UNION ALL SELECT 55 UNION ALL SELECT 56 UNION ALL SELECT 57 UNION ALL SELECT 58 UNION ALL SELECT 59) AS numbers
    WHERE 
        DATE_ADD(:start_date, INTERVAL (15 * n) MINUTE) <= :end_date
)
-- 关联会话表统计活跃用户数
SELECT 
    ti.interval_start AS period,
    COUNT(DISTINCT us.id) AS active_users
FROM 
    time_intervals ti
LEFT JOIN 
    user_session us ON us.created_at <= ti.interval_end AND us.updated_at >= ti.interval_start
WHERE 
    us.created_at BETWEEN :start_date AND :end_date -- 过滤目标日期范围的会话
GROUP BY 
    ti.interval_start
ORDER BY 
    ti.interval_start;

3. Symfony/Doctrine 中执行查询 #

在你的Repository或Controller中调用原生SQL：

use Doctrine\ORM\EntityManagerInterface;

class UserSessionRepository
{
    public function __construct(private EntityManagerInterface $entityManager) {}

    public function getActiveUsersBy15MinuteInterval(\DateTimeInterface $startDate, \DateTimeInterface $endDate): array
    {
        $connection = $this->entityManager->getConnection();
        
        $sql = <<<SQL
WITH time_intervals AS (
    SELECT 
        DATE_ADD(:start_date, INTERVAL (15 * n) MINUTE) AS interval_start,
        DATE_ADD(:start_date, INTERVAL (15 * (n + 1)) MINUTE) AS interval_end
    FROM 
        (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL SELECT 39 UNION ALL SELECT 40 UNION ALL SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43 UNION ALL SELECT 44 UNION ALL SELECT 45 UNION ALL SELECT 46 UNION ALL SELECT 47 UNION ALL SELECT 48 UNION ALL SELECT 49 UNION ALL SELECT 50 UNION ALL SELECT 51 UNION ALL SELECT 52 UNION ALL SELECT 53 UNION ALL SELECT 54 UNION ALL SELECT 55 UNION ALL SELECT 56 UNION ALL SELECT 57 UNION ALL SELECT 58 UNION ALL SELECT 59) AS numbers
    WHERE 
        DATE_ADD(:start_date, INTERVAL (15 * n) MINUTE) <= :end_date
)
SELECT 
    ti.interval_start AS period,
    COUNT(DISTINCT us.id) AS active_users
FROM 
    time_intervals ti
LEFT JOIN 
    user_session us ON us.created_at <= ti.interval_end AND us.updated_at >= ti.interval_start
WHERE 
    us.created_at BETWEEN :start_date AND :end_date
GROUP BY 
    ti.interval_start
ORDER BY 
    ti.interval_start;
SQL;

        $stmt = $connection->prepare($sql);
        $stmt->execute([
            'start_date' => $startDate->format('Y-m-d H:i:s'),
            'end_date' => $endDate->format('Y-m-d H:i:s'),
        ]);
        
        return $stmt->fetchAllAssociative();
    }
}

4. PostgreSQL 适配说明 #

如果用PostgreSQL，生成时间序列更简单，用generate_series替换CTE部分：

WITH time_intervals AS (
    SELECT 
        generate_series(:start_date, :end_date, '15 minutes'::interval) AS interval_start,
        generate_series(:start_date + '15 minutes'::interval, :end_date + '15 minutes'::interval, '15 minutes'::interval) AS interval_end
)
-- 后续关联统计逻辑和MySQL一致

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