如何在SQL中按日期和金额排名并获取对应最小开始日期

现有同一ID的数据集如下：

ID  START_DATE  END_DATE    AMOUNT  RANK    MIN START DATE
1   11/04/2012  24/10/2012  6   2   11/04/2012
1   25/10/2012  09/12/2012  9   1   25/10/2012
1   10/12/2012  10/04/2013  9   1   25/10/2012
1   11/04/2013  17/06/2014  9   1   25/10/2012
1   15/09/2019  10/10/2019  6   2   11/04/2012

需求是为每条记录获取对应分组的最小开始日期，但同一金额（6）出现在两个不连续时间段，导致当前排名逻辑无法得到正确结果。期望得到的目标数据集如下：

ID  START_DATE  END_DATE    AMOUNT  RANK    MIN START DATE
1   11/04/2012  24/10/2012  6   2   11/04/2012
1   25/10/2012  09/12/2012  9   1   25/10/2012
1   10/12/2012  10/04/2013  9   1   25/10/2012
1   11/04/2013  17/06/2014  9   1   25/10/2012
1   15/09/2019  10/10/2019  6   3   15/09/2019

实现思路 #

核心是识别同一金额的连续时间段分组，而非单纯按金额分组，具体步骤如下：

• 生成连续分组标识：
  先按ID、START_DATE排序，用窗口函数对比当前行与上一行的AMOUNT值。若两者不同，则生成一个新的分组标识（累加计数），这样同一金额的连续时间段会被归为同一组，不连续的同金额段则分到不同组。示例逻辑（以MySQL为例）：

  SELECT 
      *,
      SUM(CASE WHEN AMOUNT = LAG(AMOUNT) OVER (PARTITION BY ID ORDER BY START_DATE) THEN 0 ELSE 1 END) 
      OVER (PARTITION BY ID ORDER BY START_DATE) AS GROUP_ID
  FROM your_table;
  

• 计算分组最小开始日期：
  基于生成的GROUP_ID，按ID和GROUP_ID分组，计算每个组的MIN(START_DATE)，再将结果关联回原表，得到每条记录对应的分组最小开始日期。
• 重新计算排名：
  按ID维度，以分组的MIN(START_DATE)升序排列，用DENSE_RANK()函数生成新的排名值，确保不连续的同金额分组获得不同排名。完整示例逻辑：

  SELECT 
      t.ID,
      t.START_DATE,
      t.END_DATE,
      t.AMOUNT,
      DENSE_RANK() OVER (PARTITION BY t.ID ORDER BY grp.MIN_START_DATE) AS RANK,
      grp.MIN_START_DATE
  FROM your_table t
  JOIN (
      SELECT 
          ID,
          GROUP_ID,
          MIN(START_DATE) AS MIN_START_DATE
      FROM (
          SELECT 
              *,
              SUM(CASE WHEN AMOUNT = LAG(AMOUNT) OVER (PARTITION BY ID ORDER BY START_DATE) THEN 0 ELSE 1 END) 
              OVER (PARTITION BY ID ORDER BY START_DATE) AS GROUP_ID
          FROM your_table
      ) sub
      GROUP BY ID, GROUP_ID
  ) grp ON t.ID = grp.ID AND t.GROUP_ID = grp.GROUP_ID
  ORDER BY t.START_DATE;
  

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