sql大賽第四期

/*
*解題思路：題目需要求解按照公司、服務、當天人數來計算費用。
*為了減少記錄數，不按照每天一計算，按照一段時間計算的方式求解。
*比如：在1號到5號某個公司、服務的人數都是一樣的，那麼記錄一條記錄即可。公司、服務、時間段、*人數
*求解步驟：
*1、求出所有的時間點包括輸入的引數
*2、按照時間點來將一條記錄分割成多條記錄
*3、按照公司、服務、時間段分組、求出費用
*/
WITH
INSERT_TIME--輸入的引數：兩個時間點
AS
(
SELECT TO_DATE(:P_START_DATE,'YYYYMMDD') DATE1,TO_DATE(:P_END_DATE,'YYYYMMDD')+1 DATE2 FROM DUAL
),
ALL_TIME--所有的時間點，包括輸入的兩個時間點
AS
(
SELECT SU.COMPANY_ID,S.SERVICE_ID,S.START_DATE DT1
FROM SERVICE_USAGE S,SERVICE_USERS SU
WHERE S.USER_ID=SU.USER_ID
UNION
SELECT SU.COMPANY_ID,S.SERVICE_ID,S.END_DATE+1
FROM SERVICE_USAGE S,SERVICE_USERS SU
WHERE S.USER_ID=SU.USER_ID
UNION
SELECT C.COMPANY_ID,S.SERVICE_ID,DATE1
FROM INSERT_TIME,COMPANIES C,SERVICES S
UNION
SELECT C.COMPANY_ID,S.SERVICE_ID,DATE2
FROM INSERT_TIME,COMPANIES C,SERVICES S
),
ALL_GROUP--根據所有的時間點，將一條記錄分成多條，根據上邊求出的所有時間段(使用LEAD函式)
AS
(
SELECT SU.COMPANY_ID,S.USER_ID,S.SERVICE_ID,S.START_DATE,S.END_DATE+1 END_DATE,ALT.DT1,
       LEAD(DT1,1) OVER(PARTITION BY SU.COMPANY_ID, S.USER_ID, S.SERVICE_ID ORDER BY ALT.DT1 ) DT2
FROM SERVICE_USAGE S,SERVICE_USERS SU,ALL_TIME ALT,INSERT_TIME IT
WHERE S.USER_ID=SU.USER_ID
AND SU.COMPANY_ID=ALT.COMPANY_ID
AND S.SERVICE_ID=ALT.SERVICE_ID
AND S.START_DATE<=ALT.DT1
AND S.END_DATE+1>=ALT.DT1
AND ALT.DT1>=IT.DATE1
AND ALT.DT1<=IT.DATE2
),
GROUP_COM--按照公司、服務、時段分組，求出人數及時間間隔
AS
(
SELECT AA.COMPANY_ID,AA.SERVICE_ID,DT1,DT2,COUNT(1) PEOPLE_NUM,DT2-DT1 DATES
FROM ALL_GROUP AA
WHERE AA.DT2 IS NOT NULL
GROUP BY AA.COMPANY_ID,AA.SERVICE_ID,DT1,DT2
),
BELONG_GROUP--求出屬於哪個歸檔
AS
(
SELECT BB.COMPANY_ID,BB.SERVICE_ID,PEOPLE_NUM,DT1,DT2,DATES,
       CASE WHEN PEOPLE_NUM>=SR.USER_COUNT_MIN AND PEOPLE_NUM<=SR.USER_COUNT_MAX AND CATEGORY_ID=1 THEN 1      WHEN PEOPLE_NUM>=SR.USER_COUNT_MIN AND PEOPLE_NUM<=SR.USER_COUNT_MAX AND CATEGORY_ID=2 THEN 2      WHEN PEOPLE_NUM>=SR.USER_COUNT_MIN AND PEOPLE_NUM<=SR.USER_COUNT_MAX AND CATEGORY_ID=3 THEN 3      WHEN PEOPLE_NUM>=SR.USER_COUNT_MIN AND PEOPLE_NUM<=SR.USER_COUNT_MAX AND CATEGORY_ID=4 THEN 4      END LEV
FROM GROUP_COM BB,SERVICE_RATES SR
WHERE BB.COMPANY_ID=SR.COMPANY_ID
AND BB.SERVICE_ID=SR.SERVICE_ID
AND ((PEOPLE_NUM>=SR.USER_COUNT_MIN AND PEOPLE_NUM<=SR.USER_COUNT_MAX AND CATEGORY_ID=1)
OR (PEOPLE_NUM>=SR.USER_COUNT_MIN AND PEOPLE_NUM<=SR.USER_COUNT_MAX AND CATEGORY_ID=2)
OR (PEOPLE_NUM>=SR.USER_COUNT_MIN AND PEOPLE_NUM<=SR.USER_COUNT_MAX AND CATEGORY_ID=3)
OR (PEOPLE_NUM>=SR.USER_COUNT_MIN AND PEOPLE_NUM<=SR.USER_COUNT_MAX AND CATEGORY_ID=4)
)
),
all_money--求出費用
AS
(
SELECT CC.COMPANY_ID,CC.SERVICE_ID,LEV,SUM(DATES*PEOPLE_NUM*SR.RATE) SINGLE_FEE
FROM BELONG_GROUP CC,SERVICE_RATES SR
WHERE CC.COMPANY_ID=SR.COMPANY_ID
AND CC.SERVICE_ID=SR.SERVICE_ID
AND LEV=SR.CATEGORY_ID
GROUP BY CC.COMPANY_ID,CC.SERVICE_ID,LEV
)--行專列
SELECT DD.COMPANY_ID,C.COMPANY_NAME,DD.SERVICE_ID,S.SERVICE_NAME,
       MAX(DECODE(LEV,1,SINGLE_FEE)),
       MAX(DECODE(LEV,2,SINGLE_FEE)),
       MAX(DECODE(LEV,3,SINGLE_FEE)),
       MAX(DECODE(LEV,4,SINGLE_FEE)),
       SUM(SINGLE_FEE)
FROM all_money DD,COMPANIES C,SERVICES S
WHERE DD.COMPANY_ID=C.COMPANY_ID
AND DD.SERVICE_ID=S.SERVICE_ID
GROUP BY DD.COMPANY_ID,C.COMPANY_NAME,DD.SERVICE_ID,S.SERVICE_NAME
ORDER BY DD.COMPANY_ID,C.COMPANY_NAME,DD.SERVICE_ID,S.SERVICE_NAME;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25323853/viewspace-692487/，如需轉載，請註明出處，否則將追究法律責任。