sql - Oracle SQL 等级

我有这张表，如下所示:

[Table A][1]

B_NUMBER_COUNTRY    OUTGOING_CARRIER    MINUTES
CAN                 A                  1,045.71
CAN                 B                      7.98
CAN                 C                    973.52
FRA                 A                    566.19
FRA                 B                    521.52
FRA                 C                     27.03
FRA                 D                    549.14
FRA                 E                      0.21
USA                 A                     32.57
USA                 B                    303.17
USA                 C                  9,837.53
USA                 D                      3.91
USA                 E                      0.07
USA                 F                  2,469.00
USA                 G                     67.68
USA                 H                      0.37
USA                 I                    933.72

我需要根据分钟总和对 b_number_country 进行排名。

在上述情况下，USA 的总分钟数为 13K，CAN 为 2K，FRA 为 1.6K。所以排名应该是 USA - 1, CAN - 2 和 FRA - 3. 通过添加排名列，它应该显示如下:

[Table A (rank)][2]

B_NUMBER_COUNTRY    OUTGOING_CARRIER    MINUTES  RANK
CAN                 A                  1,045.71  2
CAN                 B                      7.98  2
CAN                 C                    973.52  2
FRA                 A                    566.19  3
FRA                 B                    521.52  3
FRA                 C                     27.03  3
FRA                 D                    549.14  3
FRA                 E                      0.21  3
USA                 A                     32.57  1
USA                 B                    303.17  1
USA                 C                  9,837.53  1
USA                 D                      3.91  1
USA                 E                      0.07  1
USA                 F                  2,469.00  1
USA                 G                     67.68  1
USA                 H                      0.37  1
USA                 I                    933.72  1

我无法获得正确的查询来执行此操作。每次尝试都将 b_number_country 和 outgoing_carrier 作为排名的一部分。

最佳答案

根据评论编辑:

你需要两个步骤，先计算分钟的总和，然后对它们进行排名:

SELECT ...,
   DENSE_RANK()
   OVER (ORDER BY sumMinutes DESC) -- must be DENSE_RANK
FROM
 (
   SELECT b_number_country, interval_of_day, outgoing_carrier,
      SUM (call_duration)/60 AS Minutes,
      SUM (call_count) AS attempt,
      SUM (answered_count) AS answered,
      SUM (seizure_count) AS seizure,
      SUM(start_call_count) AS Count_X,
      SUM(ner_count) AS NER_COUNT,
      SUM(SUM (call_duration)/60)
      OVER (PARTITION BY B_NUMBER_COUNTRY) AS sumMinutes
   FROM bm_archived_cdr
   WHERE call_direction = 'O'
     AND call_date = DATE '2016-04-21'
   GROUP BY b_number_country, interval_of_day, outgoing_carrier
 ) dt;

关于sql - Oracle SQL 等级，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36803238/