不使用子查询的高级分组

Question

这是我所拥有的数据的样本。

-ID-        -Rank-      -Type-      -Status-    -Amount-
1142474     2       Under Offer Approved    23
1148492     1       Present     Current     56
1148492     2       Under Offer Approved    3
2273605     1       Present     Current     24

当ID相同时，我只需要排名最高的记录。因此查询的最终结果如下。

-ID-        -Rank-      -Type-      -Status-    -Amount-
1142474     2       Under Offer Approved    23
1148492     1       Present     Current     56
2273605     1       Present     Current     24

现在获取原始数据集是一项昂贵的操作，因此我不想按ID进行分组，然后计算排名最小值，再将其与数据集重新连接。因此，查询需要另一种方式来完成工作。

祝好 安东尼

- vdh_ant

Answer 1

这将有效：

with temp as (
select *, row_number() over (partition by id order by rank) as rownum
from table_name
)
select * from temp where rownum = 1

将按id给出每个记录，其中rank表示最小的数字

- Rashmi Pandit

Answer 2

SELECT * FROM TheTable
WHERE 1 = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Rank DESC)

- Alex Martelli

---

这是我的第一反应。也许楼主会发布一些时间结果。 - mechanical_meat

只是为了记录，在我的情况下，这会导致错误 - “窗口函数只能出现在SELECT或ORDER BY子句中。” 所以我不得不将over部分放入子查询中，将where部分放入外查询中。 - vdh_ant

Answer 3

select t1.id
       , t1.rank
       , t1.type
       , t1.status
       , t1.amount

from   my_table t1 

       left outer join my_table as t2 
       on t1.id = t2.id 
    and 
       t2.rank < t1.rank 

where  t2.id is null

- mechanical_meat

---

@Anthony：与t2的连接也被称为自连接。它是同一张表的另一个副本。它能够工作的原因是我们在谓词（连接条件和WHERE子句）中指定了我们要排除除每个id的最高排名项以外的所有内容。 - mechanical_meat

@adam：问题在于从my_table中获取数据非常昂贵（即需要2到6秒），因此我想避免再次加入该表... - vdh_ant

我已经做了（尽管我真的不想这样），我把结果放到一个临时表中，然后将该临时表自连接... - vdh_ant

好的，那可能是正确的方法。看看另一个SO用户对将复杂查询分解为步骤的看法：https://dev59.com/CnRA5IYBdhLWcg3w_DLF#754570 - mechanical_meat

你尝试过Alex的解决方案了吗？https://dev59.com/ZEfSa4cB1Zd3GeqPBOxK#968399 - mechanical_meat

是的，但性能差异非常小。但既然它简化了查询，我可能会使用它。 - vdh_ant

Answer 4

通常可用的选项包括：

• 将所示数据存储在临时表中，然后查询临时表。
• 使用WITH子句定义复杂查询，然后让DBMS处理查询。

WITH子句有效地允许您为子查询命名；优化器将尽可能避免重新评估它。 TEMP表解决方案可能是最简单的。这将对ID和MIN（rank）进行GROUP BY，并加入返回结果。

- Jonathan Leffler

Answer 5

为什么获取数据集如此昂贵，我在这里看不到任何非常复杂的东西。您是否拥有所需的索引，查询是否使用它们？统计信息是否过时？

- HLGEM

---

为了方便起见，我简化了场景。基本上，该表是一个Table_valued函数，它将来自另外两个Table_valued函数的结果联合起来，每个函数使用大约6个临时表来构建结果。这是由于数据库中存在的规范化级别以及需要派生多少数据来构建数据图像。实际上，这些数据应该在Materialize View或类似的东西中捕获。但是，在此发布周期中，我无法进行任何更改。谢谢。 - vdh_ant