如何统计每日登录用户数？基于logged_log表的SQL查询问题

问题描述 #

我有一张logged_log表，包含username、login_time和logout_time字段，需要统计每日登录用户数。

表结构 #

示例数据 #

预期结果 #

我的尝试及问题 #

我尝试在子查询中用CASE语句替换为NULL的logout_time，再创建日期列表临时表date_period，但关联查询后仅能得到第一天的数据和全时段的总用户数，我的SQL代码如下：

SELECT
   daily_logged_log.date, count( daily_logged_log.username )
FROM (
   SELECT
      date_period.date, logged_log.username
   FROM (
      SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as date
      FROM (SELECT 0 as a 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) as a
      cross join (SELECT 0 as a 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) as b
      cross join (SELECT 0 as a 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) as c
      cross join (SELECT 0 as a 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) as d
   ) as date_period
   LEFT JOIN (
      SELECT 
         username, 
         DATE( login_time ) as login_at,
         CASE
            WHEN logout_time IS NULL
            THEN DATE( NOW() )
            ELSE DATE( logout_time )
            END
         as logout_at
      FROM logged_log
      WHERE DATE( login_time ) <= '2023-01-09'
      AND CASE
            WHEN logout_time IS NULL
            THEN DATE( NOW() )
            ELSE DATE( logout_time )
            END >= '2023-01-07'
   ) as logged_log
   ON date_period.date BETWEEN logged_log.login_at AND logged_log.logout_at
   WHERE date_period.date BETWEEN '2023-01-07' AND '2023-01-09'
   GROUP BY date_period.date, logged_log.username
) as daily_logged_log

内容的提问来源于stack exchange，提问作者黃梓榆