在Oracle SQL中使用RANK()沿着ROLLUP对分组行进行排名和汇总

oug3syen 于 2023-03-22 发布在 Oracle

关注(0)|答案(3)|浏览(251)

给定一个任务、人员和时间表，我希望能够看到在每个任务上花费最多时间的人，他们在该任务上花费了多少时间，以及下面的摘要行。
示例表：

task        person  time
----        ------  ----
Admin       Sue     0.5
Admin       Ted     0.25
Meetings    Ted     1.25
Meetings    Sue     0.75

预期结果：

task        top_person
----        ----------
Admin       Sue (0.5)
Meetings    Ted (1.25)
Overall     Ted (1.5)

因此，我们可以看到每个任务的排名最高的人，以及排名最高的人在所有任务中的总时间总和。
我目前掌握的情况：

SELECT
    DECODE(GROUPING(task), 0, task, 'Overall') AS task,
    MAX(person || ' (' || time || ')') KEEP (DENSE_RANK FIRST ORDER BY time DESC) AS top_person
FROM time_table
GROUP BY ROLLUP(task)

输出：

task        top_person
----        ----------
Admin       Sue (0.5)
Meetings    Ted (1.25)
Overall     Ted (1.25)

此查询按任务对表进行分组，按时间对组中的任务进行排名，保留第一行，然后显示保留的行中的人员及其时间。除了汇总行外，此查询效果良好，似乎此行对各个任务组执行相同的逻辑，但对所有任务除外。我需要的是按人员分组的所有任务的时间总和，按小时排序，但只适用于汇总行，而不是其他行。有什么想法，我怎么能做到这一点？

oracle

来源：https://stackoverflow.com/questions/75702400/using-rank-along-with-rollup-in-oracle-sql-to-rank-grouped-rows-and-summarize

3条答案

按热度按时间

e0bqpujr1#

虽然Model子句工作得非常好，但如果您想要更多SQL特定的解决方案，您可以尝试-

WITH max_data AS (SELECT task, person, TASK_TIME,
                         ROW_NUMBER() OVER(PARTITION BY task ORDER BY TASK_TIME DESC) RN1
                    FROM your_table
                 ),
overall_person_data AS (SELECT person, SUM(TASK_TIME) sum_time
                          FROM your_table
                         GROUP BY person
                       )
SELECT task, person || '(' || TASK_TIME || ')'
  FROM max_data
 WHERE RN1 = 1
 UNION ALL
SELECT 'Overall', MAX(person) KEEP(DENSE_RANK FIRST ORDER BY sum_time DESC) 
                  || '(' 
                  ||  MAX(sum_time) KEEP(DENSE_RANK FIRST ORDER BY sum_time DESC)
                  || ')'
  FROM overall_person_data;

Demo.

赞(0）回复(0）举报 2023-03-22

zzlelutf2#

也许你可以考虑使用MODEL子句来处理这类查询。它对于行/列间的操作非常方便。
样本数据：

WITH
    tbl (TASK, PERSON, TASK_TIME) AS
        (
            Select 'Admin',     'Sue',    0.5     FRom Dual Union All
            Select 'Admin',     'Ted',   0.25     FRom Dual Union All
            Select 'Meetings',  'Ted',   1.25     FRom Dual Union All
            Select 'Meetings',  'Sue',   0.75     FRom Dual 
        )

问题中询问的SQL结果：

Select *
From    ( Select      TASK_STR "TASK", PERSON_STR "TOP_PERSON"
          From    (   Select    CAST(TASK as Varchar2(20)) "TASK", 
                                CAST(TASK as Varchar2(20)) "TASK_STR",
                                CAST(PERSON as VarChar2(20)) "PERSON", 
                                TASK_TIME "TASK_TIME",
                                '(' || PERSON || ' ' || TASK_TIME || ')' "PERSON_STR" 
                      From tbl
                  )
              MODEL Dimension By (TASK, PERSON)
                    Measures(TASK_TIME, TASK_STR, PERSON_STR)
                    Rules (
                              TASK_STR[ANY, ANY] = Case When TASK_TIME[CV(), CV()] = Max(TASK_TIME)[CV(), ANY] Then CV(TASK) END, 
                              TASK_STR['Overall', 'Time'] = CV(TASK) || ' ' || CV(PERSON),
                              PERSON_STR['Overall', 'Time'] = Sum(TASK_TIME)[TASK != CV(), PERSON != CV()]
                          )
        )
Where TASK Is Not Null
UNION ALL Select 'Overall ' || PERSON "TASK", To_Char(Sum(TASK_TIME)) "TOP_PERSON" From tbl Group By PERSON  

R e s u l t :
TASK                 TOP_PERSON                                   
-------------------- ----------------------------------------------
Admin                (Sue .5)                                       
Meetings             (Ted 1.25)                                     
Overall Time         2.75                                           
Overall Sue          1.25                                           
Overall Ted          1.5

更改MODEL子句的RULES部分可以为您提供可能需要或希望从数据中获取的任何其他内容：

... ... ...
              MODEL Dimension By (TASK, PERSON)
                    Measures(TASK_TIME, TASK_STR, PERSON_STR)
                    Rules (
                              TASK_STR[ANY, ANY] = Case When TASK_TIME[CV(), CV()] = Max(TASK_TIME)[CV(), ANY] Then CV(TASK) END, 
                              --
                              TASK_STR['Overall', 'Data'] = 'Overall Data Below',
                              PERSON_STR['Overall', 'Data'] = LPAD('-', 15, '-'),
                              --
                              TASK_STR['Time', 'Total'] = CV(TASK) || ' ' || CV(PERSON),
                              PERSON_STR['Time', 'Total'] = Sum(TASK_TIME)[TASK != CV(), PERSON != CV()],
                              --
                              TASK_STR['Rows', 'Count'] = 'Rows Count',
                              PERSON_STR['Rows', 'Count'] = Count(PERSON_STR)[TASK != CV() And TASK NOT IN('Overall', 'Time', 'Rows'), PERSON != CV() And PERSON NOT IN('Data', 'Total', 'Count', 'Average')],
                              --
                              TASK_STR['Time', 'Average'] = 'Time Average',
                              PERSON_STR['Time', 'Average'] = Avg(TASK_TIME)[TASK != CV() And TASK != 'Overall', PERSON != CV() And PERSON NOT IN('Data', 'Total', 'Count', 'Average')]
                          )
        )
Where TASK Is Not Null
Order By TASK

R e s u l t :  
TASK                 TOP_PERSON                                   
-------------------- ----------------------------------------------
Admin                (Sue .5)                                       
Meetings             (Ted 1.25)                                     
Overall Data Below   ---------------                                
Rows Count           4                                              
Time Average         .6875                                          
Time Total           2.75

赞(0）回复(0）举报 2023-03-22

omtl5h9j3#

建议：编写{1}一个查询，查询所需结果集的“详细信息”部分;编写{2}另一个查询，查询结果集的“总体”部分，并使用UNION组合这两个部分。VIEW将有助于轻松使用查询。

表格和数据

create table tasks( task, person, time )
as
select 'Admin',     'Sue',    0.5     from dual union all
select 'Admin',     'Ted',   0.25     from dual union all
select 'Meetings',  'Ted',   1.25     from dual union all
select 'Meetings',  'Sue',   0.75     from dual 
;

查询1（详情）

select task, person, time,
  dense_rank() over ( partition by task order by time desc ) rank_
from tasks ;

-- result
TASK     PERSON  TIME   RANK_
Admin    Sue     .5     1
Admin    Ted     .25    2
Meetings Ted     1.25   1
Meetings Sue     .75    2

查询2（整体）

select person, sum_
, dense_rank() over ( order by sum_ desc ) rank_
from (
  select person
  , sum( time ) sum_
  from tasks
  group by person
) ;

-- result
PERSON  SUM_    RANK_
Ted     1.5     1
Sue     1.25    2

现在我们有了2个包含“排名”行的子集，我们只需要编写WHERE子句来过滤结果（仅返回rank_ equals 1的行）。将包含硬编码的“section”值的列添加到查询是有利的，这样我们就可以可靠地对查询的结果集进行排序。（注意查询1和查询2都是VIEW的一部分。以上部分只是说明了视图的内部工作原理。）

查看

create or replace view taskperson_view
as
select 1 as section, task, person, time
from ( 
-- --------------------------------------------------------- query 1
  select task, person, time,
    dense_rank() over ( partition by task order by time desc ) rank_
  from tasks
-- ---------------------------------------------------------
)
where rank_ = 1
  union
select 2, 'Overall', person, sum_
from ( 
-- --------------------------------------------------------- query 2
  select person, sum_
  , dense_rank() over ( order by sum_ desc ) rank_
  from (
    select person
    , sum( time ) sum_
    from tasks
    group by person
  )
-- ---------------------------------------------------------
)
where rank_ = 1
;

一旦视图就位，只需编写一个简单的查询，并应用最终结果的所有格式和顺序。

查询&收尾

select 
  task
, person || ' (' || to_char( time, 'FM90.99' ) || ')' as top_person
from taskperson_view
order by section 
;

-- result
TASK            TOP_PERSON
Admin           Sue (0.5)
Meetings        Ted (1.25)
Overall         Ted (1.5)

Link to DBfiddle .

赞(0）回复(0）举报 2023-03-22

我来回答

在Oracle SQL中使用RANK()沿着ROLLUP对分组行进行排名和汇总

3条答案

相关问题

热门标签

最新问答