Oracle SQL-如何根据日期将计数累加到ORACLE中的多列中 [英] Oracle SQL - How to get counts based up on dates into multiple columns in ORACLE

问题描述

我在Oracle表中有这样的数据

I have data like this in a Oracle table

REGID SESSION_START_DATETIME USAGEID  
    1 7/11/2016                    1  
    1 6/10/2016                    1      
    1 6/09/2016                    1  
    1 5/04/2016                    1  
    1 5/04/2016                    1  
    1 5/04/2016                    1  

我需要类似的输出

REGID 0-30_days_usagecount 31-60_days_usagecount 61-90_days_usagecount  
    1                    1                     2                     3

usagecount基本上是count(usage_id)...如何编写此问题的查询?

usagecount is basically count(usage_id)... how to write a query for this problem?

请帮助

推荐答案

以下是使用PIVOT运算符进行此操作的一种方法.

Here is a way to do this using the PIVOT operator.

with 
     inputs (REGID, SESSION_START_DATETIME, USAGEID) as (  
       select 1 , to_date('7/11/2016', 'mm/dd/yyyy'), 1 from dual union all 
       select 1 , to_date('6/10/2016', 'mm/dd/yyyy'), 1 from dual union all       
       select 1 , to_date('6/09/2016', 'mm/dd/yyyy'), 1 from dual union all 
       select 1 , to_date('5/04/2016', 'mm/dd/yyyy'), 1 from dual union all 
       select 1 , to_date('5/04/2016', 'mm/dd/yyyy'), 1 from dual union all 
       select 1 , to_date('5/04/2016', 'mm/dd/yyyy'), 1 from dual
     )
select * from (
  select regid, session_start_datetime,
         case when trunc(sysdate) - session_start_datetime between  0 and 30
                   then  '0-30_days_usagecount'
              when trunc(sysdate) - session_start_datetime between 31 and 60
                   then '31-60_days_usagecount'
              when trunc(sysdate) - session_start_datetime between 61 and 90
                   then '61-90_days_usagecount'
              end
         as col
  from inputs
)
pivot (  count(session_start_datetime)
            for col in ( '0-30_days_usagecount', '31-60_days_usagecount',
                        '61-90_days_usagecount'
                       )
      )
;



     REGID '0-30_days_usagecount' '31-60_days_usagecount' '61-90_days_usagecount'
---------- ---------------------- ----------------------- -----------------------
         1                      1                       2                       3

1 row selected.

这篇关于Oracle SQL-如何根据日期将计数累加到ORACLE中的多列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！