sql - Oracle SQL 排名

Question

我有如下表格:

[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 进行排名。

在上述情况下，美国的总分钟数为 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 作为排名的一部分。

Answer 1

根据评论编辑:

需要两步，先计算分钟数之和，然后排序:

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;