oracle 排名以获得每组的2个最高值

vc9ivgsu 于 2023-08-04 发布在 Oracle

关注(0)|答案(1)|浏览(99)

我试图在Oracle SQL中创建一个Rank函数，但我根本没有成功。我想在我已经创建的查询结果上创建一个排名，并且效果很好。这是我的疑问：

select 
   MACHINE, 
   sum(DURATION/60)  AS Min,   
   OBSERVATION,
   to_char(min(START_DATE), 'MM/DD/YYYY_HH24') as date_hour,
   OBSERVATION || ' (' || rtrim(to_char(sum(DURATION/60), 'FM90.99'), '.') || '  Min) '   AS lost_prod 
 from MyTable

where TRUNC(START_DATE) = TRUNC(CURRENT_DATE) and OBSERVATION IS NOT NULL 
group by  MACHINE, OBSERVATION, to_char(START_DATE, 'MM/DD/YYYY_HH24')

字符串
这个查询给我带来了一天中同一小时内每个MACHINE和OBSERVATION的持续时间之和。我的查询带来了一天中每个小时的观察（与其持续时间的不同总和连接为LOST_PROD），但我希望此查询仅显示machine、observation和date_hour的每个组合中持续时间最长的两个观察。我该怎么做？
我上面的查询给了我以下结果：

MACHINE  |   MIN  |  OBSERVATION  |   DATE_HOUR    |      LOST_PROD
AAA            2.7      OFF RANGE     07/16/2023_10     OFF RANGE (2.7 Min)
AAA            3.1      LOW LEVEL     07/16/2023_10     LOW LEVEL (3.1 Min)
AAA            1.5      COLD FLEX     07/16/2023_10     COLD FLEX (1.5 Min)
AAA            1.1      LOW LEVEL     07/16/2023_11     COLD FLEX (1.1 Min)
AAA            4.7      OFF RANGE     07/16/2023_11     COLD FLEX (4.7 Min)
AAA            0.2      COLD FLEX     07/16/2023_11     COLD FLEX (0.2 Min)
TTT            1.9      LOW LEVEL     07/16/2023_11     COLD FLEX (1.9 Min)
TTT            1.4      RED ALERT     07/16/2023_11     COLD FLEX (1.4 Min)
TTT            1.1      COLD FLEX     07/16/2023_11     COLD FLEX (1.1 Min)

型
我想做的这个Rank查询的预期结果如下所示：

MACHINE  |     MIN   |  OBSERVATION |   DATE_HOUR    |     LOST_PROD
AAA            2.7      OFF RANGE     07/16/2023_10     OFF RANGE (2.7 Min)
AAA            3.1      LOW LEVEL     07/16/2023_10     LOW LEVEL (3.1 Min)
AAA            1.1      LOW LEVEL     07/16/2023_11     COLD FLEX (1.1 Min)
AAA            4.7      OFF RANGE     07/16/2023_11     COLD FLEX (4.7 Min)
TTT            1.9      LOW LEVEL     07/16/2023_11     COLD FLEX (1.9 Min)
TTT            1.4      RED ALERT     07/16/2023_11     COLD FLEX (1.4 Min)

型

oracle

来源：https://stackoverflow.com/questions/76623527/rank-to-get-the-2-highest-values-of-each-group

1条答案

按热度按时间

r7xajy2e1#

使用ROW_NUMBER解析函数：

SELECT machine, 
       min,   
       observation,
       TO_CHAR(date_hour, 'MM/DD/YYYY_HH24') as date_hour,
       lost_prod
FROM   (
  SELECT machine, 
         SUM(duration/60) AS Min,   
         observation,
         TRUNC(start_date, 'HH24') as date_hour,
         observation || ' ('
           || RTRIM(TO_CHAR(SUM(duration/60), 'FM90.99'), '.') || '  Min)'
           AS lost_prod,
         ROW_NUMBER() OVER (
           PARTITION BY machine, TRUNC(start_date, 'HH24')
           ORDER BY SUM(duration/60) DESC
         ) AS rn
  FROM   MyTable
  WHERE  start_date >= TRUNC(CURRENT_DATE)
  AND    start_date <  TRUNC(CURRENT_DATE) + 1
  AND    observation IS NOT NULL 
  GROUP BY
         machine,
         observation,
         TRUNC(start_date, 'HH24')
)
WHERE  rn <= 2;

字符串
其中，对于样本数据：

CREATE TABLE mytable (MACHINE, duration, OBSERVATION, start_date) AS
SELECT 'AAA', 2.7 * 60, 'OFF RANGE', TRUNC(CURRENT_DATE) + INTERVAL '10' HOUR FROM DUAL UNION ALL
SELECT 'AAA', 3.1 * 60, 'LOW LEVEL', TRUNC(CURRENT_DATE) + INTERVAL '10' HOUR FROM DUAL UNION ALL
SELECT 'AAA', 1.5 * 60, 'COLD FLEX', TRUNC(CURRENT_DATE) + INTERVAL '10' HOUR FROM DUAL UNION ALL
SELECT 'AAA', 1.1 * 60, 'LOW LEVEL', TRUNC(CURRENT_DATE) + INTERVAL '11' HOUR FROM DUAL UNION ALL
SELECT 'AAA', 4.7 * 60, 'OFF RANGE', TRUNC(CURRENT_DATE) + INTERVAL '11' HOUR FROM DUAL UNION ALL
SELECT 'AAA', 0.2 * 60, 'COLD FLEX', TRUNC(CURRENT_DATE) + INTERVAL '11' HOUR FROM DUAL UNION ALL
SELECT 'TTT', 1.9 * 60, 'LOW LEVEL', TRUNC(CURRENT_DATE) + INTERVAL '11' HOUR FROM DUAL UNION ALL
SELECT 'TTT', 1.4 * 60, 'RED ALERT', TRUNC(CURRENT_DATE) + INTERVAL '11' HOUR FROM DUAL UNION ALL
SELECT 'TTT', 1.1 * 60, 'COLD FLEX', TRUNC(CURRENT_DATE) + INTERVAL '11' HOUR FROM DUAL;

型
输出：
| MIN|观察|日期小时|LOST_PROD| LOST_PROD |
| --|--|--|--| ------------ |
| 3.1|低水平|2019 - 05 - 10|低液位（3.1分钟）| LOW LEVEL (3.1 Min) |
| 2.7|超出范围|2019 - 05 - 10|关闭范围（2.7分钟）| OFF RANGE (2.7 Min) |
| 4.7|超出范围|2019 - 05 - 11|关闭范围（4.7分钟）| OFF RANGE (4.7 Min) |
| 1.1|低水平|2019 - 05 - 11|低液位（1.1分钟）| LOW LEVEL (1.1 Min) |
| 1.9|低水平|2019 - 05 - 11|低液位（1.9分钟）| LOW LEVEL (1.9 Min) |
| 1.4|红色警报|2019 - 05 - 11|红色警报（1.4分钟）| RED ALERT (1.4 Min) |
fiddle

赞(0）回复(0）举报 2023-08-04

我来回答

oracle 排名以获得每组的2个最高值

1条答案

相关问题

热门标签

最新问答