时间戳

首页 > 解决方案 > 在 MySql (8.0.13) 中查找 max 和 second max 之间的差异，按列分组，而不使用 order by 和 limit 子句

mysql - 在 MySql (8.0.13) 中查找 max 和 second max 之间的差异，按列分组，而不使用 order by 和 limit 子句

问题描述

我有一个包含 ID、ad_id、amount_time 列的表，其中显示了用户的 ID、他们看到的广告以及他们看到的时间。以下是数据示例：

table name: ads

ID | ad_id | amount_time
 1      2        600            
 1      3        300
 3      1        400
 1      3        100
 1      1        700

我们希望结果显示 amount_time 的 max 和 2nd max 之间的差异，按 ID 和 ad_id 分组

所以结果是

ID |ad_id | diff_amount_time
 1    3      200

我可以通过执行获得总表的 max 和 second_max：

select
(SELECT MAX(amount_time) FROM ads) maxtime,
(SELECT MAX(amount_time) FROM ads
WHERE amount_time NOT IN (SELECT MAX(amount_time) FROM ads )) as 
second_max_time

但是，我无法合并 group by 子句以获得我的结果。我知道有一种方法可以合并order by并limit 2获得最大值和第二个最大值，但这在计算上很昂贵，并且想知道是否有另一种解决方案而无需订购 amount_time 列。

标签： mysqlgroup-bymax

解决方案

在 MySQL 8.0.2+ 中，最简单且可能最高效的方法是使用Window Functions。

我们将使用Row_Number()函数，它将确定ID和组合内的行号值ad_id。行号将基于降序amount_time值。因此，最高amount_time值的行号为 1，第二高的行号为 2。

现在，我们将此结果集用作派生表，并在和上聚合 ( GROUP BY) 。我们可以使用条件表达式来确定每个组中最高值和第二高值之间的差异。IDad_idCASE .. WHEN

SELECT
  dt.ID, 
  dt.ad_id, 
  (MAX(CASE WHEN dt.row_no = 1 THEN dt.amount_time END) - 
  MAX(CASE WHEN dt.row_no = 2 THEN dt.amount_time END)) AS diff_amount_time
FROM 
(
 SELECT 
   ID, 
   ad_id, 
   amount_time, 
   ROW_NUMBER() OVER (PARTITION BY CONCAT(ID, '-', ad_id)
                      ORDER BY amount_desc) AS row_no
 FROM ads 
) AS dt 
GROUP BY dt.ID, dt.ad_id 
-- to remove cases where there is no second highest
-- when there is no second highest amount, then the difference will be null
-- because 5 - null = null
HAVING diff_amount_time IS NOT NULL

推荐阅读