如何以优雅方式查找分组最大值对应的最早日期？

数据表结构与数据 #

需求说明 #

针对每个Group分组，找出该组中Value1或Value2取最大值时对应的Insert Date，再从这些日期中选取最早的日期。
示例结果：Group a的最值出现在2023-02-01，Group b的最值出现在2023-01-02，最终结果为2023-01-02。

当前实现的SQL语句 #

SELECT MIN([Insert Date])
FROM (
  SELECT [Insert Date], [RowNo] = ROW_NUMBER() OVER(PARTITION BY [Group] ORDER BY [Value1] DESC, [Insert Date] DESC) FROM table WITH(NOLOCK)
  UNION
  SELECT [Insert Date], [RowNo] = ROW_NUMBER() OVER(PARTITION BY [Group] ORDER BY [Value2] DESC, [Insert Date] DESC) FROM table WITH(NOLOCK)
) AS src
WHERE [RowNo] = 1

优化后的优雅解决方案 #

方案一：单扫描表+窗口函数标记最值 #

只需扫描一次表，通过窗口函数直接计算每组的Value1/Value2最大值，再筛选符合条件的行：

SELECT MIN([Insert Date])
FROM (
    SELECT 
        [Insert Date],
        [Group],
        Value1,
        Value2,
        MAX(Value1) OVER(PARTITION BY [Group]) AS GroupMaxVal1,
        MAX(Value2) OVER(PARTITION BY [Group]) AS GroupMaxVal2
    FROM [table] WITH(NOLOCK)
) src
WHERE Value1 = GroupMaxVal1 OR Value2 = GroupMaxVal2;

方案二：先聚合每组最值日期再取全局最早 #

先筛选出每组内对应最值的所有日期，取每组最早的那个，再从这些日期里选全局最早：

SELECT MIN(GroupEarliestDate)
FROM (
    SELECT 
        [Group],
        MIN([Insert Date]) AS GroupEarliestDate
    FROM (
        SELECT 
            [Group],
            [Insert Date],
            CASE 
                WHEN Value1 = MAX(Value1) OVER(PARTITION BY [Group]) 
                     OR Value2 = MAX(Value2) OVER(PARTITION BY [Group]) 
                THEN 1 
                ELSE 0 
            END AS IsMaxRow
        FROM [table] WITH(NOLOCK)
    ) t
    WHERE IsMaxRow = 1
    GROUP BY [Group]
) t;

方案三：简化最值判断逻辑 #

如果不需要区分是Value1还是Value2的最大值，可直接用GREATEST函数合并判断，逻辑更简洁（注：部分数据库如SQL Server需自定义逻辑替代GREATEST）：

-- 以SQL Server为例，用IIF实现类似GREATEST的效果
SELECT MIN(GroupEarliestDate)
FROM (
    SELECT 
        [Group],
        MIN([Insert Date]) AS GroupEarliestDate
    FROM [table] WITH(NOLOCK) t
    WHERE IIF(t.Value1 >= t.Value2, t.Value1, t.Value2) = 
          (SELECT MAX(IIF(Value1 >= Value2, Value1, Value2)) FROM [table] WITH(NOLOCK) WHERE [Group] = t.[Group])
    GROUP BY [Group]
) t;

以上方案均避免了原SQL中UNION导致的两次表扫描，逻辑更清晰，执行效率更优。

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