oracle 根据两列选择前2个最高总和

wb1gzix0 于 2023-08-04 发布在 Oracle

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

我有一个结构如下的表：

|     START_DATE        |         END_DATE     | MINUTES | MACHINE |  TYPE  |
23/06/2023 10:05:35      23/06/2023 10:09:35       4         Z       GREEN
23/06/2023 10:10:40      23/06/2023 10:25:40       15        Z       GREEN
23/06/2023 10:25:47      23/06/2023 10:27:47       2         Z       GREEN
23/06/2023 10:27:35      23/06/2023 10:37:35       10        Z       RED
23/06/2023 10:37:35      23/06/2023 10:39:35       2         Z       RED
23/06/2023 10:39:35      23/06/2023 10:45:35       6         X       BLUE
23/06/2023 11:00:00      23/06/2023 11:05:00       5         Y       GREEN
23/06/2023 11:05:00      23/06/2023 11:13:00       8         Y       BLUE

字符串
我想创建一个查询，它只显示相同日期和小时（从START_DATE列）中每个MACHINE和TYPE的前2个最大分钟总和，并且还显示一个列，在该列中它将TYPE与分钟总和连接起来。这将是预期的结果：

|     DATE_HOUR       |   MINUTES | MACHINE |  TYPE_MINUTES  |
    23/06/2023_10           21         Z       GREEN (21 minutes)
    23/06/2023 10           12         Z       RED (12 minutes)
    23/06/2023 11           5          Y       GREEN (5 minutes)
    23/06/2023 11           8          Y       BLUE (8 minutes)

型

oracle

来源：https://stackoverflow.com/questions/76615011/select-top-2-highest-sum-based-on-two-columns

2条答案

按热度按时间

mitkmikd1#

您可以：

select *
from (
  select x.*, row_number() over(partition by machine order by m desc) as rn
  from (
    select machine, type, sum(minutes) as m,
      to_char(min(start_date), 'MM/DD/YYYY_HH24') as date_hour
    from t
    group by machine, type
  ) x
) s
where rn <= 2

字符串

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

6tdlim6h2#

对于每个machine和type以及日期/小时组合，您可以SUMminutes，然后使用RANK分析函数来查找每个分区的最大两个排名行：

SELECT machine,
       start_date,
       type || ' (' || minutes || ')' AS type_minutes
FROM   (
  SELECT machine,
         type,
         TRUNC(start_date, 'HH24') AS start_date,
         SUM(minutes) AS minutes,
         RANK() OVER (PARTITION BY machine, type ORDER BY SUM(minutes) DESC) AS rnk
  FROM   table_name
  GROUP BY
         machine,
         type,
         TRUNC(start_date, 'HH24')
)
WHERE  rnk <= 2;

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

CREATE TABLE table_name (START_DATE, END_DATE, MINUTES, MACHINE, TYPE ) AS
SELECT DATE '2023-06-23' + INTERVAL '10:05:35' HOUR TO SECOND, DATE '2023-06-23' + INTERVAL '10:09:35' HOUR TO SECOND,  4, 'Z', 'GREEN' FROM DUAL UNION ALL
SELECT DATE '2023-06-23' + INTERVAL '10:10:40' HOUR TO SECOND, DATE '2023-06-23' + INTERVAL '10:25:40' HOUR TO SECOND, 15, 'Z', 'GREEN' FROM DUAL UNION ALL
SELECT DATE '2023-06-23' + INTERVAL '10:25:47' HOUR TO SECOND, DATE '2023-06-23' + INTERVAL '10:27:47' HOUR TO SECOND,  2, 'Z', 'GREEN' FROM DUAL UNION ALL
SELECT DATE '2023-06-23' + INTERVAL '10:27:35' HOUR TO SECOND, DATE '2023-06-23' + INTERVAL '10:37:35' HOUR TO SECOND, 10, 'Z', 'RED' FROM DUAL UNION ALL
SELECT DATE '2023-06-23' + INTERVAL '10:37:35' HOUR TO SECOND, DATE '2023-06-23' + INTERVAL '10:39:35' HOUR TO SECOND,  2, 'Z', 'RED' FROM DUAL UNION ALL
SELECT DATE '2023-06-23' + INTERVAL '10:39:35' HOUR TO SECOND, DATE '2023-06-23' + INTERVAL '10:45:35' HOUR TO SECOND,  6, 'X', 'BLUE' FROM DUAL UNION ALL
SELECT DATE '2023-06-23' + INTERVAL '11:00:00' HOUR TO SECOND, DATE '2023-06-23' + INTERVAL '11:05:00' HOUR TO SECOND,  5, 'Y', 'GREEN' FROM DUAL UNION ALL
SELECT DATE '2023-06-23' + INTERVAL '11:05:00' HOUR TO SECOND, DATE '2023-06-23' + INTERVAL '11:13:00' HOUR TO SECOND,  8, 'Y', 'BLUE' FROM DUAL;

型
输出：
| 开始日期|类型_分钟| TYPE_MINUTES |
| --|--| ------------ |
| 2023-06-23 10：00：00|蓝色（6）| BLUE (6) |
| 2023-06-23 11：00：00|蓝色（8）| BLUE (8) |
| 2023-06-23 11：00：00|绿色（5）| GREEN (5) |
| 2023-06-23 10：00：00|绿色（21）| GREEN (21) |
| 2023-06-23 10：00：00|红色（12）| RED (12) |
fiddle

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

我来回答

oracle 根据两列选择前2个最高总和

2条答案

相关问题

热门标签

最新问答