# 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
),
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;

• 博文量
123
• 访问量
178269