SQL中无需新建表或自联结，如何统计指定周期内名称出现频次？

当然有更简洁高效的方法！你完全不需要新建临时表或者做多次自联结，用**窗口函数（Window Functions）**就能一步到位计算出每个名称在不同时间周期内的记录次数，而且性能更优。

核心思路 #

窗口函数允许你在不拆分表的前提下，对分组内的特定范围数据进行聚合计算。这里我们可以按name分组，然后针对每条记录的date，计算该日期往前推一周、一个月、一年的区间内，同名称的记录总数。

针对PostgreSQL的实现 #

PostgreSQL直接支持在窗口函数中使用时间区间语法，写法非常直观：

SELECT
  name,
  date,
  -- 过去一周（含当前日期）的记录数
  COUNT(*) OVER (
    PARTITION BY name
    ORDER BY date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS past_week_count,
  -- 过去一个月（含当前日期）的记录数
  COUNT(*) OVER (
    PARTITION BY name
    ORDER BY date
    RANGE BETWEEN INTERVAL '1 month' PRECEDING AND CURRENT ROW
  ) AS past_month_count,
  -- 过去一年（含当前日期）的记录数
  COUNT(*) OVER (
    PARTITION BY name
    ORDER BY date
    RANGE BETWEEN INTERVAL '1 year' PRECEDING AND CURRENT ROW
  ) AS past_year_count
FROM original_table
ORDER BY name, date;

• PARTITION BY name：按名称分组，确保只统计同一名称的记录
• ORDER BY date：按日期排序，保证时间范围的计算是有序的
• RANGE BETWEEN ... AND CURRENT ROW：定义从当前日期往前推的时间区间，COUNT(*)会统计这个区间内的所有记录数

针对MySQL 8.0+的实现 #

MySQL的窗口函数不直接支持时间区间的RANGE语法，但可以通过将日期转换为时间戳（秒数）来实现：

SELECT
  name,
  date,
  -- 过去一周（604800秒=7天）的记录数
  COUNT(*) OVER (
    PARTITION BY name
    ORDER BY UNIX_TIMESTAMP(date)
    RANGE BETWEEN 604800 PRECEDING AND CURRENT ROW
  ) AS past_week_count,
  -- 过去一个月（≈2592000秒=30天）的记录数
  COUNT(*) OVER (
    PARTITION BY name
    ORDER BY UNIX_TIMESTAMP(date)
    RANGE BETWEEN 2592000 PRECEDING AND CURRENT ROW
  ) AS past_month_count,
  -- 过去一年（31536000秒=365天）的记录数
  COUNT(*) OVER (
    PARTITION BY name
    ORDER BY UNIX_TIMESTAMP(date)
    RANGE BETWEEN 31536000 PRECEDING AND CURRENT ROW
  ) AS past_year_count
FROM original_table
ORDER BY name, date;

注意：这里的一个月用了30天的秒数，如果需要精确到自然月（比如当月1号到当前日期），可以调整为用DATE_SUB(date, INTERVAL 1 MONTH)结合条件判断，但上面的写法是通用的“过去30天”统计。

这种方法的优势 #

• 无需额外表/联结：一次查询直接输出原表所有字段+三个统计字段，完全不需要创建临时表或者自联结
• 性能更优：窗口函数的计算逻辑由数据库引擎优化，比多次自联结的IO开销小很多，数据量大时优势明显
• 结果精准对应：每条原表记录都会带上对应的统计值，逻辑清晰易维护

注意事项 #

• 确保date字段是DATE或DATETIME类型，避免字符串格式导致的计算错误
• 如果你的“过去一周”是指不含当前日期的前7天，可以把区间调整为RANGE BETWEEN INTERVAL '7 days' PRECEDING AND INTERVAL '1 day' PRECEDING，根据实际需求灵活修改

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