使用pivot显示列式统计结果实例

产品人员经常需要按照日期统计交易额和交易笔数，并希望结果中每个省份（或者人员、部门等粒度）一条记录，每天为一条记录。结果类似：

 

主要的方法有两种：

1.使用case when语句判断日期

形如：

sum(case when t.trade_time>=to_date('2015-10-01','yyyy-mm-dd') and t.trade_time<to_date('2015-10-02','yyyy-mm-dd') then t.order_price end)  '10月1日交易额',

sum(case when t.trade_time>=to_date('2015-10-02','yyyy-mm-dd') and t.trade_timee<to_date('2015-10-03','yyyy-mm-dd') then t.order_price end)  '10月2日交易额'

……

 

2.先按照日期进行统计，在进行行列转换，得到所需格式

 

此问题适合使用pivot透视函数解决，语句形如：

 

select *

from

(

select

u.province_name 省份,

to_char(t.trade_time,'mm')||'月'||to_char(t.trade_time,'dd')||'日' trade_date,

sum(t.order_price) order_price,

sum(t.order_numbers) order_numbers

from user u,trade_info t

where u.userid=t.userid

and t.trade_time>=to_date('20151001','yyyymmdd')

and t.trade_time<to_date('20151101','yyyymmdd')

group by u.province_name,to_char(t.trade_time,'mm')||'月'||to_char(t.trade_time,'dd')||'日'

)

pivot

(

sum(order_price) 交易额,

sum(order_counts) 交易笔数

for trade_date in ('10月01日','10月02日','10月03日','10月04日','10月05日','10月06日','10月07日','10月08日','10月09日','10月10日','10月11日','10月12日','10月13日','10月14日','10月15日','10月16日','10月17日','10月18日','10月19日','10月20日','10月21日','10月22日','10月23日','10月24日','10月25日','10月26日','10月27日','10月28日','10月29日','10月30日')

)

 

但由于每个月的天数不同，而pivot函数语法中需要具体指定。可以采用下列语句生成日期字符串，替换到上述语句的标红部分。

 

select '('''||mod_day2||''')'--将日期包含在单引号中，并使用逗号分隔

from

(

select listagg(mon_day,''',''') within group(order by mon_day) mod_day2

from

  (

    select

    to_char(trunc(sysdate,'mm')+level-1,'mm')||'月'||to_char(trunc(sysdate,'mm')+level-1,'dd')||'日' mon_day

    from dual

    connect by level<add_months(trunc(sysdate,'mm'),1)-trunc(sysdate,'mm')--当月的天数

  )

);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-1814227/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-1814227/