这个需求很常见，其实只要把时间字段的时分秒部分去掉，基于纯日期来做累计就搞定了！我分两种常用场景给你说清楚：

场景1：想要每天一行，显示到当天的累计总数 #

这种适合做报表统计，先按日期分组算出每天的记录数，再对每天的数量做累计求和。不同数据库的日期处理函数略有不同，我给你几个主流数据库的写法：

MySQL/MariaDB

SELECT
  date_entry,
  SUM(daily_count) OVER (ORDER BY date_entry) AS total_num
FROM (
  SELECT
    DATE(entry_time) AS date_entry,
    COUNT(*) AS daily_count
  FROM my_table
  GROUP BY DATE(entry_time)
) AS daily_stats;

PostgreSQL

SELECT
  date_entry,
  SUM(daily_count) OVER (ORDER BY date_entry) AS total_num
FROM (
  SELECT
    TRUNC(entry_time, 'day') AS date_entry,
    COUNT(*) AS daily_count
  FROM my_table
  GROUP BY TRUNC(entry_time, 'day')
) AS daily_stats;

SQL Server

SELECT
  date_entry,
  SUM(daily_count) OVER (ORDER BY date_entry) AS total_num
FROM (
  SELECT
    CAST(entry_time AS DATE) AS date_entry,
    COUNT(*) AS daily_count
  FROM my_table
  GROUP BY CAST(entry_time AS DATE)
) AS daily_stats;

场景2：保留原始表的每一行，每行显示到当前日期的累计总数 #

如果需要保留每条记录的原始时间，同时显示到该记录所在日期为止的累计总数，可以直接在窗口函数里用截断后的日期排序：

MySQL/MariaDB

SELECT
  entry_time,
  COUNT(*) OVER (ORDER BY DATE(entry_time)) AS total_num
FROM my_table;

PostgreSQL

SELECT
  entry_time,
  COUNT(*) OVER (ORDER BY TRUNC(entry_time, 'day')) AS total_num
FROM my_table;

SQL Server

SELECT
  entry_time,
  COUNT(*) OVER (ORDER BY CAST(entry_time AS DATE)) AS total_num
FROM my_table;

为什么原来的语句是按时间累计？ #

你之前的语句COUNT(*) OVER (ORDER BY entry_time)是按精确到时分秒的时间排序，所以每一行的累计数是到当前时间点的总记录数。改成按截断后的纯日期排序后，窗口函数会把同一日期的所有行归为同一“阶段”，累计数就会变成到当前日期的总记录数啦。

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