SQL如何按指定列聚合多行数据统计各用户访问次数

你需要在保留原表所有明细记录的基础上，给每一行追加对应interactionuserkey的总出现次数（即用户总访问次数），用窗口函数实现最简洁，不需要额外做表关联。

核心SQL写法（适用于支持窗口函数的引擎：MySQL8.0+、Hive、Spark SQL、PostgreSQL、SQL Server等） #

SELECT
  interactionuserkey,
  visitdatecode,
  COUNT(*) OVER (PARTITION BY interactionuserkey) AS `number of visits`
FROM fact.userinteraction;

语法说明 #

• PARTITION BY interactionuserkey会把数据按用户ID切分成独立分区，COUNT(*)会统计每个分区内的总记录数，也就是对应用户的总访问次数，结果会自动填充到该用户的每一条明细记录上
• 因为别名number of visits包含空格，需要用反引号包裹；如果是PostgreSQL、SQL Server引擎，换成双引号包裹别名即可

低版本兼容写法（不支持窗口函数的场景） #

如果你的数据库版本不支持窗口函数，可以先按用户分组统计访问次数，再关联回原表得到结果：

SELECT
  a.interactionuserkey,
  a.visitdatecode,
  b.visit_count AS `number of visits`
FROM fact.userinteraction a
LEFT JOIN (
  SELECT
    interactionuserkey,
    COUNT(*) AS visit_count
  FROM fact.userinteraction
  GROUP BY interactionuserkey
) b ON a.interactionuserkey = b.interactionuserkey;

样例执行结果 #

针对你提供的测试数据，两种写法返回的结果完全一致：

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