时间戳

首页 > 解决方案 > Oracle SQL：根据每个分区中的第一行进一步对 PARTITION BY 组进行排序

sql - Oracle SQL：根据每个分区中的第一行进一步对 PARTITION BY 组进行排序

问题描述

我目前有一个非常大的查询，并且正在尝试以特定方式对数据进行进一步排序。查询以下列方式返回数据，项目按 TimeToComplete 降序排序：

|   id     |    ItemKey     |TimeToComplete|
| -------- | -------------- |--------------|
| ABC      | KEY-211-01     |    580       |
| DEF      | KEY-311-01     |    456       |
| GHI      | KEY-111-01     |    150       |
| JKL      | KEY-411-01     |     87       |
| XYZ      | KEY-311-01     |     23       |
| KNE      | KEY-211-01     |     17       |

我要做的是对这些数据进行排序，以便将 ItemKeys 分组在一起，但除此之外仍保留基于组中第一项的 TimeToComplete 排序。像这样的例子：

|   id     |    ItemKey     |TimeToComplete|
| -------- | -------------- |--------------|
| ABC      | KEY-211-01     |    580       |
| KNE      | KEY-211-01     |     17       |
| DEF      | KEY-311-01     |    456       |
| XYZ      | KEY-311-01     |     23       |
| GHI      | KEY-111-01     |    150       |
| JKL      | KEY-411-01     |     87       |

我确实有一个部分工作的例子，但它有一些问题：

WITH GroupedRecords AS (
  SELECT 
          OriginalQuery.*,
          ROW_NUMBER() OVER (partition by ItemKey order by TimeToComplete DESC) as RN
  FROM (originally giant query here) OriginalQuery  
),
Sequence AS (
  SELECT 
         ItemKey,
         TimeToComplete,
         ROW_NUMBER() OVER (order by TimeToComplete DESC) as SequenceOrder
  FROM GroupedRecords
  WHERE RN = 1
)
select T.*, s.SequenceOrder
from GroupedRecords T
INNER JOIN Sequence S ON T.ItemKey = S.ItemKey
ORDER BY S.SequenceOrder ASC, T.TimeToComplete DESC

这样做的问题是：

  1. GroupedRecords 和 Sequence 之间的内部连接将我的一堆列名（但不是全部）更改为 Oracle 随机生成的名称（QCSJ_0000006）
  2. 连接也使查询方式太慢（OriginalQuery 已经不是很优化，但这使其执行时间增加了一倍）

问题：有没有更有效的方法可以在不使用序列/连接部分的情况下实现这种排序？

标签： sqloracleaggregate-functionspartition-by

解决方案

幸运的是，您只需要max()在order by子句中添加一个分析。你不需要做任何其他事情。

假设“当前查询”是您现有的查询，尚未以任何方式排序（无order by子句）。在最后添加以下内容：

... existing query ...
order  by max(timetocomplete) over (partition by itemkey) desc,
          itemkey,
          timetocomplete desc
;

请注意，您不需要将分析函数添加到select子句中。SQL标准说你做；Oracle 语法说你没有。Oracle 在幕后为我们处理额外的小步骤。

这将计算完成每个键的最长时间。它首先按该最大值排序。在平局的情况下（两个或多个不同的键具有相同的最大完成时间），它首先按键进一步排序，然后在每个键内按完成时间（降序）。

推荐阅读