ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sql大赛第四期

sql大赛第四期

原创 Linux操作系统 作者:Nalternative 时间:2011-04-14 13:27:33 0 删除 编辑
/*
*解题思路:题目需要求解按照公司、服务、当天人数来计算费用。
*为了减少记录数,不按照每天一计算,按照一段时间计算的方式求解。
*比如:在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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: sql大赛总结
请登录后发表评论 登录
全部评论

注册时间:2011-02-09

  • 博文量
    123
  • 访问量
    178269