PostgreSQL查询：按用户ID分组统计每日用户登录总数

你提供的原查询问题在于，count(distinct date_trunc('day', datetime))统计的是用户登录过的不同天数，而不是每个用户每日的登录总次数，这和你的需求不匹配。

要实现按用户分组、统计每日登录总数的需求，你需要同时按userid和登录日期分组，然后统计每组的登录事件数量，正确的查询语句如下：

SELECT
  userid,
  date_trunc('day', created_at) AS login_date,
  COUNT(*) AS daily_login_count
FROM
  events
WHERE
  action = 'login'
GROUP BY
  userid,
  date_trunc('day', created_at)
ORDER BY
  userid,
  login_date;

语句说明： #

• date_trunc('day', created_at)：将created_at时间戳截断到日期维度，得到用户登录的具体日期
• 分组条件同时包含userid和截断后的日期，确保每组对应「单个用户+单天」的登录事件集合
• COUNT(*)：统计该组内的记录总数，也就是该用户当天的登录次数
• ORDER BY：按用户ID和登录日期排序，让结果更直观易读

如果你需要将每个用户的每日登录数据以横向列的形式展示（比如把日期作为列名），可以使用PostgreSQL的crosstab函数实现透视表效果，示例如下（需要提前安装tablefunc扩展）：

-- 先启用扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 透视查询
SELECT *
FROM crosstab(
  'SELECT userid, date_trunc(''day'', created_at)::date, COUNT(*)
   FROM events
   WHERE action = ''login''
   GROUP BY userid, date_trunc(''day'', created_at)::date
   ORDER BY 1, 2',
  'SELECT DISTINCT date_trunc(''day'', created_at)::date FROM events WHERE action = ''login'' ORDER BY 1'
) AS ct(userid INT, "2024-01-01" INT, "2024-01-02" INT, "2024-01-03" INT);

注意：透视表的列名需要根据实际存在的日期手动调整，或者使用动态SQL生成更灵活的结果。

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