一尘不染

根据其他列的顺序从组中选择一个值

mysql

问题

假设我有这张桌子tab（小提琴可用）。

| g | a | b |     v |
---------------------
| 1 | 3 | 5 |   foo |
| 1 | 4 | 7 |   bar |
| 1 | 2 | 9 |   baz |
| 2 | 1 | 1 |   dog |
| 2 | 5 | 2 |   cat |
| 2 | 5 | 3 | horse |
| 2 | 3 | 8 |   pig |

我按行对行进行分组g，对于每个组，我都希望从column中获得一个值v。但是，我不需要任何
值，但是我想从具有maximal的行中获取值a，并从所有这些中获取具有maximal的值b。换句话说，我的结果应该是

| 1 |   bar |
| 2 | horse |

当前解决方案

我知道一个查询来实现这一点：

SELECT grps.g,
(SELECT v FROM tab
 WHERE g = grps.g
 ORDER BY a DESC, b DESC
 LIMIT 1) AS r
FROM (SELECT DISTINCT g FROM tab) grps

题

但是我认为这个查询 很难看 。主要是因为它使用了一个 依赖的子查询
，感觉就像是真正的性能杀手。因此，我想知道是否有更简单的解决方案来解决这个问题。

预期答案

我期望这个问题的最可能答案是某种MySQL（或MariaDB）附加组件或补丁，它确实为此提供了功能。但是我也欢迎其他有用的灵感。任何没有依赖子查询的方法都可以作为答案。

如果您的解决方案仅适用于单个排序列，即无法区分cat和horse，请随时提出答案，我希望它对大多数用例仍然有用。例如，100*a+b一种可能的方式是按两列对上述数据进行排序，同时仍仅使用一个表达式。

我想到了一些漂亮的解决方案，可能会在一段时间后添加它们，但是我首先要看看是否先倒入了一些不错的新解决方案。

基准结果

由于仅通过查看就很难比较各种答案，因此我对它们进行了一些基准测试。这是使用MySQL
5.1在我自己的桌面上运行的。这些数字不会与任何其他系统进行比较，而只能与其他系统进行比较。如果性能对应用程序至关重要，则您可能应该使用真实数据进行自己的测试。当出现新答案时，我可以将其添加到脚本中，然后重新运行所有测试。

100,000个项目，有1,000个组可供选择，InnoDb：
1. MvG为0.166s（来自问题）
2. RichardTheKiwi为0.520秒
3. xdazz 2.199s
4. 19.24s为民主党（顺序的子查询）
5. 攻击48.72秒
100,000个项目，50,000个组可供选择，InnoDb：
1. xdazz为0.356秒
2. 理查德· 奇异鸟0.640秒
3. MvG为0.764秒（来自问题）
4. 连击51.50秒
5. __对于 Dems 而言太长 （顺序子查询）
100,000个项目，InnoDb有100组可供选择：
1. MvG为0.163秒（来自问题）
2. 理查德·奇异鸟（RichardTheKiwi）0.523秒
3. 2.072s为民主党（顺序的子查询）
4. xdazz为17.78秒
5. 攻击49.85秒

因此，到目前为止，即使是依赖子查询，我自己的解决方案似乎也还不错。令人惊讶的是，同样使用依赖子查询的acatt解决方案，其效果也要差得多，因此我也考虑过。MySQL优化器可能无法解决的问题。RichardTheKiwi提出的解决方案似乎也具有良好的整体性能。其他两个解决方案在很大程度上取决于数据的结构。对于许多小组，xdazz的方法要优于其他所有小组，而Dems的解决方案在少数几个小组中表现最佳（尽管仍然不是很好）。

阅读 191

2020-05-17

共1个答案

一尘不染

SELECT g, a, b, v
  FROM (
            SELECT *, 
                   @rn := IF(g = @g, @rn + 1, 1) rn, 
                   @g := g
              FROM (select @g := null, @rn := 0) x, 
                   tab
          ORDER BY g, a desc, b desc, v
       ) X
 WHERE rn = 1;

单通。在我看来，所有其他解决方案都为O（n ^ 2）。

2020-05-17