SQL如何查询上榜时间最长的记录？FIRST_VALUE与日期差计算详解

分析榜单数据时，经常需要找出某个对象在榜上连续停留最久的一次记录。比如运营想了解哪个商品曾连续霸榜时间最长，或者哪些内容在排行榜上坚持最久。直接拿最大时间减最小时间并不准确，因为对象可能多次上榜、中间有下榜间隔。正确做法是先把流水拆成连续区间，再用窗口函数计算每个区间的开始时间与结束时间差值。FIRST_VALUE在这个过程里扮演关键角色，它能把每个分区的首行时间带到所有行，方便后续做日期差计算。

从流水表到连续上榜区间：口径与分组方法

假设我们有一张榜单事件流水表rank_event，每次上榜或下榜都会写入一行，字段包含数据主键、对象名称、事件类型和事件时间。要计算连续上榜时长，首先要明确统计口径：一次连续上榜是指从一条上榜事件开始，到紧接着的下一条下榜事件结束；如果还没有下榜事件，就视为仍在榜上。这个口径下，不能直接用聚合函数把最早时间和最晚时间相减，因为中间可能有断档。

把流水拆成连续区间，常见做法是用窗口函数生成一个分组编号。对于每个对象按时间排序后，每遇到一次上榜事件就让编号增加1，这样从这次上榜到下一次上榜之前的所有行都会归入同一个区间组。窗口函数SUM配合CASE WHEN可以实现这个效果。具体执行时，先按item_name分区，再按event_time和id排序，累加上榜标记即可。

下面先创建示例表并插入几行数据，后面所有查询都以它为基础。

CREATE TABLE rank_event (
  id INT PRIMARY KEY,
  item_name VARCHAR(50),
  event_type VARCHAR(10),
  event_time DATE
);

INSERT INTO rank_event (id, item_name, event_type, event_time) VALUES
(1, '商品A', '上榜', '2023-01-01'),
(2, '商品A', '下榜', '2023-01-05'),
(3, '商品A', '上榜', '2023-01-10'),
(4, '商品A', '下榜', '2023-01-20'),
(5, '商品B', '上榜', '2023-01-02'),
(6, '商品B', '下榜', '2023-01-03'),
(7, '商品B', '上榜', '2023-01-06'),
(8, '商品B', '下榜', '2023-02-01');

用以下查询给每一行打上区间编号，观察分组结果会更直观。

SELECT
  id,
  item_name,
  event_type,
  event_time,
  SUM(CASE WHEN event_type = '上榜' THEN 1 ELSE 0 END)
    OVER (PARTITION BY item_name ORDER BY event_time, id) AS grp
FROM rank_event
ORDER BY item_name, event_time, id;

运行后可以看到，商品A的前两条记录被分到第1组，后两条分到第2组；商品B同理。这个grp字段就是后续窗口函数分区的依据。

用FIRST_VALUE把首行时间带到每一行

有了区间编号后，下一步是提取每个区间的开始时间。FIRST_VALUE函数可以返回窗口分区内按指定排序的第一行字段值。它的语法是FIRST_VALUE(column) OVER (PARTITION BY ... ORDER BY ...)，与聚合函数不同，它不会折叠行数，而是把首行值复制到分区内的每一行。这一点非常关键，因为后续还需要在行级做条件判断和差值计算。

为什么不用MIN窗口函数？虽然MIN(event_time) OVER ...也能拿到最早时间，但FIRST_VALUE更贴近“第一条记录”的语义。尤其当排序字段不是时间，或者首行需要根据多个条件筛选时，FIRST_VALUE配合CASE表达式可以精确指定取哪一行的哪个值。例如我们只关心上榜事件的时间，可以写成FIRST_VALUE(CASE WHEN event_type = '上榜' THEN event_time END) OVER (...)，这样即使分区首行不是上榜事件，也能安全地跳过空值行。

下面的查询在已生成grp的基础上，用FIRST_VALUE计算每个分区内的开始时间，同时用条件聚合窗口函数提取下榜时间。

WITH grouped AS (
  SELECT
    id,
    item_name,
    event_type,
    event_time,
    SUM(CASE WHEN event_type = '上榜' THEN 1 ELSE 0 END)
      OVER (PARTITION BY item_name ORDER BY event_time, id) AS grp
  FROM rank_event
)
SELECT
  item_name,
  grp,
  FIRST_VALUE(CASE WHEN event_type = '上榜' THEN event_time END)
    OVER (PARTITION BY item_name, grp ORDER BY event_time, id) AS start_time,
  MAX(CASE WHEN event_type = '下榜' THEN event_time END)
    OVER (PARTITION BY item_name, grp) AS end_time,
  event_type,
  event_time
FROM grouped
ORDER BY item_name, grp, event_time;

从结果中可以看到，同一个grp内的每一行都有相同的start_time，而end_time只出现在有下榜事件的行上，没有下榜则为NULL。这样我们就得到了计算持续时长所需的两个边界值。

日期差计算的细节与多数据库差异

拿到开始时间和结束时间后，就需要计算日期差。最常用的函数是DATEDIFF，但不同数据库的用法并不完全一致。MySQL中DATEDIFF(date1, date2)返回两个日期相差的天数，写法为DATEDIFF(end_time, start_time)；SQL Server中则是DATEDIFF(day, start_time, end_time)，需要指定日期部分；PostgreSQL可以直接用日期相减，返回整数天数。

还有一个容易被忽略的业务口径问题：是否包含起始日。比如1月1日上榜，1月5日下榜，实际在榜天数是1、2、3、4、5共5天，而DATEDIFF('2023-01-05', '2023-01-01')在MySQL里返回4，因此通常需要加1。如果业务只算完整24小时，则不加。这个细节最好在需求阶段确认清楚。

对于还没有下榜的记录，end_time会是NULL。此时应使用COALESCE把当前日期作为结束时间，从而得到截至今天的持续天数。修改后的聚合查询如下。

WITH grouped AS (
  SELECT
    id,
    item_name,
    event_type,
    event_time,
    SUM(CASE WHEN event_type = '上榜' THEN 1 ELSE 0 END)
      OVER (PARTITION BY item_name ORDER BY event_time, id) AS grp
  FROM rank_event
),
interval_info AS (
  SELECT
    item_name,
    grp,
    FIRST_VALUE(CASE WHEN event_type = '上榜' THEN event_time END)
      OVER (PARTITION BY item_name, grp ORDER BY event_time, id) AS start_time,
    MAX(CASE WHEN event_type = '下榜' THEN event_time END)
      OVER (PARTITION BY item_name, grp) AS end_time
  FROM grouped
)
SELECT
  item_name,
  grp,
  MIN(start_time) AS start_time,
  COALESCE(MAX(end_time), CURRENT_DATE) AS end_time,
  DATEDIFF(COALESCE(MAX(end_time), CURRENT_DATE), MIN(start_time)) + 1 AS duration_days
FROM interval_info
GROUP BY item_name, grp
ORDER BY duration_days DESC;

这个查询会返回每个对象的每一次连续上榜区间及持续天数，按天数倒序排列后，第一行就是上榜时间最长的记录。如果只想返回每个对象的最长区间，可以在外层再加一层窗口函数ROW_NUMBER按duration_days降序排名后筛选序号为1的行。

对比其他方案与优化建议

除了FIRST_VALUE，还可以用LAG函数找到每行的前一行时间，或者用自连接把同一分区的首尾行关联起来。但LAG更适合相邻行比较，在连续区间计算中要额外处理区间边界；自连接则需要扫描多次表，数据量大时性能明显变差。窗口函数方案一次扫描即可完成分组和边界提取，SQL逻辑也更接近业务描述。

实际使用时，建议在item_name和event_time上建立复合索引，以提高分区排序的效率。如果事件表非常大，可以把这种计算放到离线分析任务中，或者用物化视图缓存区间结果。对于需要实时计算的小表，还可以把区间结果封装成视图，供报表直接查询。

另外要注意，如果同一对象在极短时间内频繁上下榜，分组编号的逻辑需要结合业务规则判断。例如两次上榜之间如果间隔小于1分钟，可能属于数据重复而非新的连续区间。这时可以在生成分组前先做去重或加条件判断。总的来说，FIRST_VALUE与日期差计算的组合足够应对大多数榜单时长统计需求，掌握了这个模式，再遇到类似连续区间问题也能举一反三。

SQL窗口函数FIRST_VALUE日期差计算修改时间：2026-09-24 11:49:54