ITPUB SQL大赛第四期

WITH DAY AS
(
	SELECT TO_DATE(:P_START_DATE, 'YYYYMMDD') + ROWNUM - 1 DAY 
	FROM DUAL
	CONNECT BY ROWNUM <= TO_DATE(:P_END_DATE, 'YYYYMMDD') - TO_DATE(:P_START_DATE, 'YYYYMMDD') + 1
), 
COMPANY_INFO AS 
(
	SELECT A.USER_ID, SERVICE_ID, B.COMPANY_ID, 
		GREATEST(START_DATE, TO_DATE(:P_START_DATE, 'YYYYMMDD')) START_DATE, 
		LEAST(END_DATE, TO_DATE(:P_END_DATE, 'YYYYMMDD')) END_DATE
	FROM SERVICE_USAGE A, SERVICE_USERS B 
	WHERE A.USER_ID = B.USER_ID
	AND START_DATE <= TO_DATE(:P_END_DATE, 'YYYYMMDD')
	AND END_DATE >= TO_DATE(:P_START_DATE, 'YYYYMMDD')
	AND TO_DATE(:P_END_DATE, 'YYYYMMDD') - TO_DATE(:P_START_DATE, 'YYYYMMDD') >= 60
), 
COMPANY_DAY1 AS 
(
	SELECT SERVICE_ID, COMPANY_ID, DAY, COUNT(DISTINCT USER_ID) CN
	FROM DAY D, COMPANY_INFO C
	WHERE D.DAY BETWEEN C.START_DATE AND C.END_DATE
	GROUP BY SERVICE_ID, COMPANY_ID, DAY
), 
COMPANY_DAY2 AS 
(
	SELECT SERVICE_ID, COMPANY_ID, DAY, COUNT(DISTINCT A.USER_ID) CN
	FROM 
	(
		SELECT USER_ID, SERVICE_ID, START_DATE, END_DATE, DAY
		FROM SERVICE_USAGE 
		WHERE START_DATE <= TO_DATE(:P_END_DATE, 'YYYYMMDD')
		AND END_DATE >= TO_DATE(:P_START_DATE, 'YYYYMMDD')
		AND TO_DATE(:P_END_DATE, 'YYYYMMDD') - TO_DATE(:P_START_DATE, 'YYYYMMDD') < 60
		MODEL UNIQUE SINGLE REFERENCE
		PARTITION BY (USER_ID, SERVICE_ID, 
			GREATEST(START_DATE, TO_DATE(:P_START_DATE, 'YYYYMMDD')) START_DATE, 
			LEAST(END_DATE, TO_DATE(:P_END_DATE, 'YYYYMMDD')) END_DATE)
		DIMENSION BY (CAST(NULL AS DATE) DAY)
		MEASURES (0 V)
		RULES
		(
		V[
		FOR DAY 
			FROM TO_DATE(:P_START_DATE, 'YYYYMMDD')
			TO TO_DATE(:P_END_DATE, 'YYYYMMDD')
			INCREMENT 1
		] = 1
		) 
	) A, SERVICE_USERS B 
	WHERE A.USER_ID = B.USER_ID
	AND DAY BETWEEN START_DATE AND END_DATE 
	GROUP BY SERVICE_ID, COMPANY_ID, DAY
), 
COMPANY_DAY AS
(
	SELECT SERVICE_ID, COMPANY_ID, DAY, CN
	FROM COMPANY_DAY1
	UNION ALL
	SELECT SERVICE_ID, COMPANY_ID, DAY, CN
	FROM COMPANY_DAY2
)
SELECT R.COMPANY_ID, COMPANY_NAME, R.SERVICE_ID, SERVICE_NAME, FEE1, FEE2, 
	FEE3, FEE4, NVL(FEE1, 0) + NVL(FEE2, 0) + NVL(FEE3, 0) + NVL(FEE4, 0) TOTAL_FEE 
FROM
(
	SELECT CD.SERVICE_ID, CD.COMPANY_ID, CATEGORY_ID, RATE*CN
	FROM COMPANY_DAY CD, SERVICE_RATES SR
	WHERE CD.SERVICE_ID = SR.SERVICE_ID
	AND CD.COMPANY_ID = SR.COMPANY_ID
	AND CN BETWEEN USER_COUNT_MIN AND USER_COUNT_MAX
) 
PIVOT 
(
	SUM(RATE) FOR CATEGORY_ID IN (1 FEE1, 2 FEE2, 3 FEE3, 4 FEE4)
) R, SERVICES S, COMPANIES C
WHERE R.SERVICE_ID = S.SERVICE_ID
AND R.COMPANY_ID = C.COMPANY_ID
ORDER BY 1, 3;

WITH DAY AS ( SELECT TO_DATE(:P_START_DATE, 'YYYYMMDD') + ROWNUM - 1 DAY FROM DUAL CONNECT BY ROWNUM <= TO_DATE(:P_END_DATE, 'YYYYMMDD') - TO_DATE(:P_START_DATE, 'YYYYMMDD') + 1 ), COMPANY_INFO AS ( SELECT A.USER_ID, SERVICE_ID, B.COMPANY_ID, GREATEST(START_DATE, TO_DATE(:P_START_DATE, 'YYYYMMDD')) START_DATE, LEAST(END_DATE, TO_DATE(:P_END_DATE, 'YYYYMMDD')) END_DATE FROM SERVICE_USAGE A, SERVICE_USERS B WHERE A.USER_ID = B.USER_ID AND START_DATE <= TO_DATE(:P_END_DATE, 'YYYYMMDD') AND END_DATE >= TO_DATE(:P_START_DATE, 'YYYYMMDD') AND TO_DATE(:P_END_DATE, 'YYYYMMDD') - TO_DATE(:P_START_DATE, 'YYYYMMDD') >= 60 ), COMPANY_DAY1 AS ( SELECT SERVICE_ID, COMPANY_ID, DAY, COUNT(DISTINCT USER_ID) CN FROM DAY D, COMPANY_INFO C WHERE D.DAY BETWEEN C.START_DATE AND C.END_DATE GROUP BY SERVICE_ID, COMPANY_ID, DAY ), COMPANY_DAY2 AS ( SELECT SERVICE_ID, COMPANY_ID, DAY, COUNT(DISTINCT A.USER_ID) CN FROM ( SELECT USER_ID, SERVICE_ID, START_DATE, END_DATE, DAY FROM SERVICE_USAGE WHERE START_DATE <= TO_DATE(:P_END_DATE, 'YYYYMMDD') AND END_DATE >= TO_DATE(:P_START_DATE, 'YYYYMMDD') AND TO_DATE(:P_END_DATE, 'YYYYMMDD') - TO_DATE(:P_START_DATE, 'YYYYMMDD') < 60 MODEL UNIQUE SINGLE REFERENCE PARTITION BY (USER_ID, SERVICE_ID, GREATEST(START_DATE, TO_DATE(:P_START_DATE, 'YYYYMMDD')) START_DATE, LEAST(END_DATE, TO_DATE(:P_END_DATE, 'YYYYMMDD')) END_DATE) DIMENSION BY (CAST(NULL AS DATE) DAY) MEASURES (0 V) RULES ( V[ FOR DAY FROM TO_DATE(:P_START_DATE, 'YYYYMMDD') TO TO_DATE(:P_END_DATE, 'YYYYMMDD') INCREMENT 1 ] = 1 ) ) A, SERVICE_USERS B WHERE A.USER_ID = B.USER_ID AND DAY BETWEEN START_DATE AND END_DATE GROUP BY SERVICE_ID, COMPANY_ID, DAY ), COMPANY_DAY AS ( SELECT SERVICE_ID, COMPANY_ID, DAY, CN FROM COMPANY_DAY1 UNION ALL SELECT SERVICE_ID, COMPANY_ID, DAY, CN FROM COMPANY_DAY2 ) SELECT R.COMPANY_ID, COMPANY_NAME, R.SERVICE_ID, SERVICE_NAME, FEE1, FEE2, FEE3, FEE4, NVL(FEE1, 0) + NVL(FEE2, 0) + NVL(FEE3, 0) + NVL(FEE4, 0) TOTAL_FEE FROM ( SELECT CD.SERVICE_ID, CD.COMPANY_ID, CATEGORY_ID, RATE*CN FROM COMPANY_DAY CD, SERVICE_RATES SR WHERE CD.SERVICE_ID = SR.SERVICE_ID AND CD.COMPANY_ID = SR.COMPANY_ID AND CN BETWEEN USER_COUNT_MIN AND USER_COUNT_MAX ) PIVOT ( SUM(RATE) FOR CATEGORY_ID IN (1 FEE1, 2 FEE2, 3 FEE3, 4 FEE4) ) R, SERVICES S, COMPANIES C WHERE R.SERVICE_ID = S.SERVICE_ID AND R.COMPANY_ID = C.COMPANY_ID ORDER BY 1, 3;