汇总和分析功能
问题描述:
我怎样才能NSR和DSR的每个月的差别,然后为 以下SQL结果的%增加/减少:汇总和分析功能
type period typecount typetotal
----------------------------------------------
DSR 2014-10 88 117
NSR 2014-10 29 117
DSR 2014-09 139 363
NSR 2014-09 224 363
DSR 2014-08 226 439
NSR 2014-08 213 439
DSR 2014-07 181 409
NSR 2014-07 228 409
DSR 2014-06 103 321
NSR 2014-06 218 321
DSR 2014-05 334 552
NSR 2014-05 218 552
DSR 2014-04 188 398
NSR 2014-04 210 398
DSR 2014-03 199 447
NSR 2014-03 248 447
DSR 2014-02 166 505
NSR 2014-02 339 505
DSR 2014-01 294 559
NSR 2014-01 265 559
答
我会建议使用条件聚合得到的一切在一排,然后做在该结构中的工作:
select period,
max(case when type = 'DSR' then typecount end) as typecount_dsr,
max(case when type = 'NSR' then typecount end) as typecount_nsr,
max(typetotal) as typetotal
from table t
group by period;
然后,你可以很容易地做你的计算,是这样的:
with p as (
select period,
max(case when type = 'DSR' then typecount end) as typecount_dsr,
max(case when type = 'NSR' then typecount end) as typecount_nsr,
max(typetotal) as typetotal
from table t
group by period
)
select p.*, (typecount_dsr - typecount_nsr) as diff,
(typecount_dsr - typecount_nsr)/lag(typecount_dsr - typecount_nsr) over (order by period) as diffinc
from p;
答
谢谢@ Gordon,@ Mihai这是我尝试过的,现在正在工作。
SELECT DISTINCT SUBSTR(cmse_application_number,1,3) App_Type,
TO_CHAR(cmse_created_date,'YYYY-MM') Period,
COUNT(SUBSTR(cmse_application_number,1,3)) APP_TYPE_COUNT,
SUM(COUNT(SUBSTR(cmse_application_number,1,3))) OVER(PARTITION BY TO_CHAR(cmse_created_date,'YYYY-MM') ORDER BY TO_CHAR(cmse_created_date,'YYYY-MM')) App_MONTH_TOTAL,
COUNT(SUBSTR(cmse_application_number,1,3))-lag(COUNT(SUBSTR(cmse_application_number,1,3))) over(PARTITION BY TO_CHAR(cmse_created_date,'YYYY-MM') ORDER BY TO_CHAR(cmse_created_date,'YYYY-MM')) AS NSRDiff ,
COUNT(SUBSTR(cmse_application_number,1,3))-LEAD(COUNT(SUBSTR(cmse_application_number,1,3))) over(PARTITION BY TO_CHAR(cmse_created_date,'YYYY-MM') ORDER BY TO_CHAR(cmse_created_date,'YYYY-MM')) AS DSRDiff
FROM
(SELECT DISTINCT cmse_service_number,
CMSE_APPLICATION_NUMBER,
CMSE_CREATED_DATE,
cmse_ss_uid
FROM cmse_service_enquiry a ,
cmlt_line_type l,
subd_detail s,
bst_type t -- links to subd to eliminate failed applications
WHERE A.CMSE_CMLT_UID =L.CMLT_UID
AND A.CMSE_BST_UID =T.BST_UID
AND A.CMSE_SERVICE_NUMBER = S.SUBD_SERVICE_NAME
AND SUBSTR(cmse_application_number,1,3) IN ('NSR','DSR')
-- AND TO_CHAR(cmse_created_date,'YYYY-MM')='2014-10' -- filter to check specific period
AND cmse_ss_uid=58 -- makes sure workflow completes
)
GROUP BY SUBSTR(cmse_application_number,1,3) ,
TO_CHAR(cmse_created_date,'YYYY-MM')
ORDER BY 2 DESC ;
那么你有什么尝试,如果有什么? – Mihai 2014-10-20 14:13:54