oracle 按照时间分组统计求和
来源:互联网 发布:淘宝电子兑换券 编辑:程序博客网 时间:2024/06/05 00:26
select t.year,
t.季度 时间类型,
sum(A)over (order by rownum rows BETWEEN unbounded preceding and current row)A,
sum(B)over (order by rownum rows BETWEEN unbounded preceding and current row)B,
sum(C)
over (order by rownum rows BETWEEN
unbounded preceding and current row)
C ,
sum(D)
over (order by rownum
rows BETWEEN unbounded preceding and current row)
D ,
sum(升级)
over (order
by rownum rows BETWEEN unbounded preceding and current row)
升级 ,
sum(降级)
over (order by rownum rows BETWEEN unbounded preceding and
current row)
降级,
sum(正常受理)
over (order by rownum rows BETWEEN unbounded
preceding and current row)
正常受理 ,
sum(正常办结)
over (order by rownum rows
BETWEEN unbounded preceding and current row)
正常办结,
sum(挽回经济损失)
over (order by rownum rows BETWEEN unbounded preceding and current
row)
挽回经济损失,
sum(工商)over (order by rownum rows BETWEEN unbounded preceding and current row)工商,
sum(质监)over (order by rownum rows BETWEEN unbounded preceding and current row)质监,
sum(食药监)over (order by rownum rows BETWEEN unbounded preceding and current row)食药监,
sum(物价)over (order by rownum rows BETWEEN unbounded preceding and current row)物价
from (
select
t.year ,
t.季度,
sum(decode(t.F_CREDIT_LEVEL, 'A', t.NUM , 0)) "A",
sum(decode(t.F_CREDIT_LEVEL, 'B', t.NUM , 0)) "B",
sum(decode(t.F_CREDIT_LEVEL, 'C', t.NUM , 0)) "C",
sum(decode(t.F_CREDIT_LEVEL, 'D', t.NUM , 0)) "D",
sum(decode(t.F_CREDIT_LEVEL, '升级', t.NUM , 0)) "升级",
sum(decode(t.F_CREDIT_LEVEL, '降级', t.NUM , 0)) "降级",
sum(decode(t.F_CREDIT_LEVEL, '已受理', t.NUM , 0)) "正常受理",
sum(decode(t.F_CREDIT_LEVEL, '已办结', t.NUM , 0))"正常办结",
sum(decode(t.F_CREDIT_LEVEL, '挽回经济损失', t.NUM , 0)) "挽回经济损失",
sum(decode(t.F_CREDIT_LEVEL, '工商', t.NUM , 0)) "工商",
sum(decode(t.F_CREDIT_LEVEL, '质监', t.NUM , 0)) "质监",
sum(decode(t.F_CREDIT_LEVEL, '食药监', t.NUM , 0)) "食药监",
sum(decode(t.F_CREDIT_LEVEL, '物价', t.NUM , 0)) "物价"
from
(
select
"TO_CHAR"(F_FOUND_DATE,'yyyy')
year,
to_char(F_FOUND_DATE,'MM') 季度,
F_CREDIT_LEVEL,count(*) num
FROM(SELECT F_FOUND_DATE,F_CREDIT_LEVEL
from KEY_ENTITY_INFO union all
SELECT to_date(F_CANCEL_DATE,'yyyy-mm-dd
hh24:mi:ss'),F_CREDIT_LEVEL
from KEY_ENTITY_CANCELLED where
F_CREDIT_LEVEL !='Z')
where F_FOUND_DATE>"TO_DATE"('2014-01-01',
'yyyy-MM-dd')
group by
"TO_CHAR"(F_FOUND_DATE,'yyyy')
,to_char(F_FOUND_DATE,'MM')
,F_CREDIT_LEVEL
union all
select "TO_CHAR"(F_UPDATE_DATE,'yyyy')
year,
to_char(F_UPDATE_DATE,'MM') 季度,
'升级',count(*) num
FROM KEY_CREDIT_HISTORY
where
F_UPDATE_DATE>"TO_DATE"('2014-01-01', 'yyyy-MM-dd')
and
F_LEVEL_BEFORE>F_LEVEL_AFTER
group by "TO_CHAR"(F_UPDATE_DATE,'yyyy')
,to_char(F_UPDATE_DATE,'MM')
union all
select "TO_CHAR"(F_UPDATE_DATE,'yyyy')
year,
to_char(F_UPDATE_DATE,'MM') 季度,
'降级',count(*) num
FROM KEY_CREDIT_HISTORY
where
F_UPDATE_DATE>"TO_DATE"('2014-01-01', 'yyyy-MM-dd')
and
F_LEVEL_BEFORE<F_LEVEL_AFTER
group by
"TO_CHAR"(F_UPDATE_DATE,'yyyy')
,to_char(F_UPDATE_DATE,'MM')
union all
SELECT
'2013',
to_char(F_FOUND_DATE,'MM') 季度,
F_CREDIT_LEVEL,COUNT (*) num
FROM
(SELECT
F_FOUND_DATE,
F_CREDIT_LEVEL
FROM
KEY_ENTITY_INFO
UNION ALL
SELECT
TO_DATE (
F_CANCEL_DATE,
'yyyy-mm-dd
hh24:mi:ss'
),
F_CREDIT_LEVEL
FROM
KEY_ENTITY_CANCELLED
WHERE
F_CREDIT_LEVEL != 'Z'
)
WHERE
F_FOUND_DATE
< "TO_DATE" ('2014-01-01',
'yyyy-MM-dd')
GROUP BY
F_CREDIT_LEVEL
,to_char(F_FOUND_DATE,'MM')
UNION ALL
SELECT
'2013',
to_char(F_UPDATE_DATE,'MM') 季度,
'升级',
COUNT (*) num
FROM
KEY_CREDIT_HISTORY
WHERE
F_UPDATE_DATE <
"TO_DATE" ('2014-01-01', 'yyyy-MM-dd')
AND F_LEVEL_BEFORE >
F_LEVEL_AFTER
group by to_char(F_UPDATE_DATE,'MM')
UNION ALL
SELECT
'2013',
to_char(F_UPDATE_DATE,'MM') 季度,
'降级',
COUNT (*) num
FROM
KEY_CREDIT_HISTORY
WHERE
F_UPDATE_DATE
<"TO_DATE" ('2014-01-01', 'yyyy-MM-dd')
AND F_LEVEL_BEFORE <
F_LEVEL_AFTER
group by to_char(F_UPDATE_DATE,'MM')
union all
select
"TO_CHAR"(F_REG_TIME,'yyyy')year,
to_char(F_REG_TIME,'MM') 季度,
'已受理' type,
count(*) num
FROM
BIZ_COMPLAINTS_HISTORY
group by
"TO_CHAR"(F_REG_TIME,'yyyy')
,to_char(F_REG_TIME,'MM')
union all
select
"TO_CHAR"(F_CLOSE_TIME,'yyyy')year,
to_char(F_CLOSE_TIME,'MM') 季度,
'已办结' type,
count(*) num
FROM
BIZ_COMPLAINTS_HISTORY
group by
"TO_CHAR"(F_CLOSE_TIME,'yyyy')
,to_char(F_CLOSE_TIME,'MM')
union all
select
"TO_CHAR"(F_REG_TIME,'yyyy')year,
to_char(F_REG_TIME,'MM'),
'挽回经济损失' type,
sum(F_RECOUPEDECONOMY_LOST)
FROM
BIZ_COMPLAINTS_HISTORY
group by
"TO_CHAR"(F_REG_TIME,'yyyy')
,to_char(F_REG_TIME,'MM')
union all
select
"TO_CHAR"(F_SJ_CJSJ,'yyyy')year,
to_char(F_SJ_CJSJ,'MM'),
F_AY_AJLY,
count(*)
FROM
(
SELECT
CASE
WHEN T .F_AY_AJLY = 1 THEN
'工商'
WHEN T .F_AY_AJLY = 2 THEN
'质监'
WHEN T .F_AY_AJLY = 3 THEN
'食药监'
WHEN T .F_AY_AJLY = 4 THEN
'物价'
ELSE
''
END F_AY_AJLY,
t2.F_SJ_CJSJ
FROM
(
SELECT
F_Id,
SUBSTR (
T .ca,
INSTR (T .ca, ',', 1, c.lv) + 1,
INSTR (T .ca, ',', 1, c.lv + 1) - (INSTR(T .ca, ',', 1, c.lv) + 1)
) AS F_AY_AJLY
FROM
(
SELECT
F_Id,
',' || F_AY_AJLY || ',' AS ca,
LENGTH (F_AY_AJLY || ',') - NVL (
LENGTH (REPLACE(F_AY_AJLY, ',')),
0
) AS cnt,
F_SJ_CJSJ
FROM
act_aj_ayxx
WHERE
1 = 1
) T,
(
SELECT
LEVEL lv
FROM
dual CONNECT BY LEVEL <= 5
) c
WHERE
c.lv <= T .cnt
) T
LEFT JOIN ACT_AJ_AYXX t2 ON T .f_id = t2.f_id
)
group by
"TO_CHAR"(F_SJ_CJSJ,'yyyy'),
to_char(F_SJ_CJSJ,'MM'),
F_AY_AJLY
)t
group by t.year
,t.季度
ORDER BY year
,t.季度
)
t
group by rownum,t.year,
t.季度,
t.A,t.B,t.C,T.D,t.升级,t.降级,T.正常受理,t.正常办结,t.挽回经济损失,t.工商,t.质监,t.食药监,t.物价
order by rownum
t.季度 时间类型,
sum(A)over (order by rownum rows BETWEEN unbounded preceding and current row)A,
sum(B)over (order by rownum rows BETWEEN unbounded preceding and current row)B,
sum(C)
over (order by rownum rows BETWEEN
unbounded preceding and current row)
C ,
sum(D)
over (order by rownum
rows BETWEEN unbounded preceding and current row)
D ,
sum(升级)
over (order
by rownum rows BETWEEN unbounded preceding and current row)
升级 ,
sum(降级)
over (order by rownum rows BETWEEN unbounded preceding and
current row)
降级,
sum(正常受理)
over (order by rownum rows BETWEEN unbounded
preceding and current row)
正常受理 ,
sum(正常办结)
over (order by rownum rows
BETWEEN unbounded preceding and current row)
正常办结,
sum(挽回经济损失)
over (order by rownum rows BETWEEN unbounded preceding and current
row)
挽回经济损失,
sum(工商)over (order by rownum rows BETWEEN unbounded preceding and current row)工商,
sum(质监)over (order by rownum rows BETWEEN unbounded preceding and current row)质监,
sum(食药监)over (order by rownum rows BETWEEN unbounded preceding and current row)食药监,
sum(物价)over (order by rownum rows BETWEEN unbounded preceding and current row)物价
from (
select
t.year ,
t.季度,
sum(decode(t.F_CREDIT_LEVEL, 'A', t.NUM , 0)) "A",
sum(decode(t.F_CREDIT_LEVEL, 'B', t.NUM , 0)) "B",
sum(decode(t.F_CREDIT_LEVEL, 'C', t.NUM , 0)) "C",
sum(decode(t.F_CREDIT_LEVEL, 'D', t.NUM , 0)) "D",
sum(decode(t.F_CREDIT_LEVEL, '升级', t.NUM , 0)) "升级",
sum(decode(t.F_CREDIT_LEVEL, '降级', t.NUM , 0)) "降级",
sum(decode(t.F_CREDIT_LEVEL, '已受理', t.NUM , 0)) "正常受理",
sum(decode(t.F_CREDIT_LEVEL, '已办结', t.NUM , 0))"正常办结",
sum(decode(t.F_CREDIT_LEVEL, '挽回经济损失', t.NUM , 0)) "挽回经济损失",
sum(decode(t.F_CREDIT_LEVEL, '工商', t.NUM , 0)) "工商",
sum(decode(t.F_CREDIT_LEVEL, '质监', t.NUM , 0)) "质监",
sum(decode(t.F_CREDIT_LEVEL, '食药监', t.NUM , 0)) "食药监",
sum(decode(t.F_CREDIT_LEVEL, '物价', t.NUM , 0)) "物价"
from
(
select
"TO_CHAR"(F_FOUND_DATE,'yyyy')
year,
to_char(F_FOUND_DATE,'MM') 季度,
F_CREDIT_LEVEL,count(*) num
FROM(SELECT F_FOUND_DATE,F_CREDIT_LEVEL
from KEY_ENTITY_INFO union all
SELECT to_date(F_CANCEL_DATE,'yyyy-mm-dd
hh24:mi:ss'),F_CREDIT_LEVEL
from KEY_ENTITY_CANCELLED where
F_CREDIT_LEVEL !='Z')
where F_FOUND_DATE>"TO_DATE"('2014-01-01',
'yyyy-MM-dd')
group by
"TO_CHAR"(F_FOUND_DATE,'yyyy')
,to_char(F_FOUND_DATE,'MM')
,F_CREDIT_LEVEL
union all
select "TO_CHAR"(F_UPDATE_DATE,'yyyy')
year,
to_char(F_UPDATE_DATE,'MM') 季度,
'升级',count(*) num
FROM KEY_CREDIT_HISTORY
where
F_UPDATE_DATE>"TO_DATE"('2014-01-01', 'yyyy-MM-dd')
and
F_LEVEL_BEFORE>F_LEVEL_AFTER
group by "TO_CHAR"(F_UPDATE_DATE,'yyyy')
,to_char(F_UPDATE_DATE,'MM')
union all
select "TO_CHAR"(F_UPDATE_DATE,'yyyy')
year,
to_char(F_UPDATE_DATE,'MM') 季度,
'降级',count(*) num
FROM KEY_CREDIT_HISTORY
where
F_UPDATE_DATE>"TO_DATE"('2014-01-01', 'yyyy-MM-dd')
and
F_LEVEL_BEFORE<F_LEVEL_AFTER
group by
"TO_CHAR"(F_UPDATE_DATE,'yyyy')
,to_char(F_UPDATE_DATE,'MM')
union all
SELECT
'2013',
to_char(F_FOUND_DATE,'MM') 季度,
F_CREDIT_LEVEL,COUNT (*) num
FROM
(SELECT
F_FOUND_DATE,
F_CREDIT_LEVEL
FROM
KEY_ENTITY_INFO
UNION ALL
SELECT
TO_DATE (
F_CANCEL_DATE,
'yyyy-mm-dd
hh24:mi:ss'
),
F_CREDIT_LEVEL
FROM
KEY_ENTITY_CANCELLED
WHERE
F_CREDIT_LEVEL != 'Z'
)
WHERE
F_FOUND_DATE
< "TO_DATE" ('2014-01-01',
'yyyy-MM-dd')
GROUP BY
F_CREDIT_LEVEL
,to_char(F_FOUND_DATE,'MM')
UNION ALL
SELECT
'2013',
to_char(F_UPDATE_DATE,'MM') 季度,
'升级',
COUNT (*) num
FROM
KEY_CREDIT_HISTORY
WHERE
F_UPDATE_DATE <
"TO_DATE" ('2014-01-01', 'yyyy-MM-dd')
AND F_LEVEL_BEFORE >
F_LEVEL_AFTER
group by to_char(F_UPDATE_DATE,'MM')
UNION ALL
SELECT
'2013',
to_char(F_UPDATE_DATE,'MM') 季度,
'降级',
COUNT (*) num
FROM
KEY_CREDIT_HISTORY
WHERE
F_UPDATE_DATE
<"TO_DATE" ('2014-01-01', 'yyyy-MM-dd')
AND F_LEVEL_BEFORE <
F_LEVEL_AFTER
group by to_char(F_UPDATE_DATE,'MM')
union all
select
"TO_CHAR"(F_REG_TIME,'yyyy')year,
to_char(F_REG_TIME,'MM') 季度,
'已受理' type,
count(*) num
FROM
BIZ_COMPLAINTS_HISTORY
group by
"TO_CHAR"(F_REG_TIME,'yyyy')
,to_char(F_REG_TIME,'MM')
union all
select
"TO_CHAR"(F_CLOSE_TIME,'yyyy')year,
to_char(F_CLOSE_TIME,'MM') 季度,
'已办结' type,
count(*) num
FROM
BIZ_COMPLAINTS_HISTORY
group by
"TO_CHAR"(F_CLOSE_TIME,'yyyy')
,to_char(F_CLOSE_TIME,'MM')
union all
select
"TO_CHAR"(F_REG_TIME,'yyyy')year,
to_char(F_REG_TIME,'MM'),
'挽回经济损失' type,
sum(F_RECOUPEDECONOMY_LOST)
FROM
BIZ_COMPLAINTS_HISTORY
group by
"TO_CHAR"(F_REG_TIME,'yyyy')
,to_char(F_REG_TIME,'MM')
union all
select
"TO_CHAR"(F_SJ_CJSJ,'yyyy')year,
to_char(F_SJ_CJSJ,'MM'),
F_AY_AJLY,
count(*)
FROM
(
SELECT
CASE
WHEN T .F_AY_AJLY = 1 THEN
'工商'
WHEN T .F_AY_AJLY = 2 THEN
'质监'
WHEN T .F_AY_AJLY = 3 THEN
'食药监'
WHEN T .F_AY_AJLY = 4 THEN
'物价'
ELSE
''
END F_AY_AJLY,
t2.F_SJ_CJSJ
FROM
(
SELECT
F_Id,
SUBSTR (
T .ca,
INSTR (T .ca, ',', 1, c.lv) + 1,
INSTR (T .ca, ',', 1, c.lv + 1) - (INSTR(T .ca, ',', 1, c.lv) + 1)
) AS F_AY_AJLY
FROM
(
SELECT
F_Id,
',' || F_AY_AJLY || ',' AS ca,
LENGTH (F_AY_AJLY || ',') - NVL (
LENGTH (REPLACE(F_AY_AJLY, ',')),
0
) AS cnt,
F_SJ_CJSJ
FROM
act_aj_ayxx
WHERE
1 = 1
) T,
(
SELECT
LEVEL lv
FROM
dual CONNECT BY LEVEL <= 5
) c
WHERE
c.lv <= T .cnt
) T
LEFT JOIN ACT_AJ_AYXX t2 ON T .f_id = t2.f_id
)
group by
"TO_CHAR"(F_SJ_CJSJ,'yyyy'),
to_char(F_SJ_CJSJ,'MM'),
F_AY_AJLY
)t
group by t.year
,t.季度
ORDER BY year
,t.季度
)
t
group by rownum,t.year,
t.季度,
t.A,t.B,t.C,T.D,t.升级,t.降级,T.正常受理,t.正常办结,t.挽回经济损失,t.工商,t.质监,t.食药监,t.物价
order by rownum
阅读全文
0 0
- oracle 按照时间分组统计求和
- mysql查询数据时,按照时间分组求和
- 【统计】Oracle按照时间统计总结
- 【统计】Oracle按照时间统计总结 .
- SQL Server中按照时间进行分组统计
- Oracle 按照指定的时间间隔进行分组
- Oracle 按照指定的时间间隔进行分组
- oracle 按照周 分组
- Oracle按照年月日分组
- oracle 分组后,求和
- Stream流分组,统计,求和
- SQL-按照最新时间分组
- oracle 按照日期区间求和
- oracle 按照日期区间求和
- MYSQL实现将两个结果集合并,并且按照时间字段分组,其他字段的值求和
- Oracle 分组统计,按照天、月份周和自然周、月、季度和年
- Oracle按不同时间分组统计的sql
- oracle分组统计
- hdu 5875 Function
- SSR的搭建教程和SSR一键脚本,安装BBR,锐速脚本
- window上mysql绿色版安装
- 如何部署JSP应用到阿里云服务器上(一)
- ViewPager使用方式
- oracle 按照时间分组统计求和
- C++ 直接选择排序的实现实例代码
- 如何部署JSP应用到阿里云服务器上(二)
- 1168: 账单(指针专题)
- UMeditor与SpringMVC的整合和上传图片使用
- js 获取行间样式
- 告诉各位为如何学习linux系统
- 初认小程序
- NoClassDefFoundError.