最近整理BLOG文章时发现，这篇文章当时没有贴出来。由于当时发现最后一题出现了错误，且手头的事情比较多，本打算搞清楚之后再把结果贴出来，没想到后来忘记了。虽然已经过去了一年了，不过信守自己的承诺，还是把最终结果贴出来。

我的代码为：

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;

这是改正之后的代码，与之前错误的代码相比，在SELECT CD.SERVICE_ID, CD.COMPANY_ID, CATEGORY_ID, RATE这一行，将RATE改为了RATE*CN。题目里面其实对这个问题进行了说明，不过后续在处理SQL的过程中漏掉了，于是导致了SQL的错误。

下面对SQL的实现进行简单的说明：

整个SQL由两大部分组成，第一个部分通过构造日期的方式获取每天每个SERVICE_ID和COMPANY_ID有多少不同的用户使用。第一部分的功能都通过WITH子查询方式实现，第二部分主要是利用11G的PIVOT实现列到行的转换。

出于性能方面的考虑，第一部分的逻辑通过等价的两种方式实现：分别是利用CONNECT BY方式构造日期和利用MODEL语句来构造日期，这两种方式分别对应COMPANY_DAY1和COMPANY_DAY2两个WITH语句。

在我的测试环境中，当:P_START_DATE与:P_END_DATE的时间间隔小于2个月的时候，第二种MODEL方式，也就是COMPANY_DAY2的效率更高，而当:P_START_DATE与:P_END_DATE的时间间隔较长，比如大于1年，则第一种方式，也就是COMPANY_DAY1的效率要远高于第二种方式。由于第一种方式线性更好，因此在不确定数据分布的情况下，将二者的阈值设置为60天，在两个WITH中加上了互斥的常量条件，分别为日期间隔大于等于60和间隔小于60。通过UNION ALL将两个WITH查询合并，为第二部分构造了统一的接口COMPANY_DAY，使得SQL根据输入间隔的不同自动选择更优的代码执行。

子查询COMPANY_DAY1的思路很简单：DAY子查询用于构造输入变量:P_START_DATE和:P_END_DATE之间所有的日期信息。关联SERVICE_USAGE和SERVICE_USERS表，获取COMPANY_ID和SERVICE_ID的对应关系，同时获取对应的时间范围，开始时间由SERVICE_USAGE中START_DATE和输入变量:P_START_DATE二者中大的值确定，结束时间由SERVICE_USAGE中END_DATE和输入变量:P_END_DATE中小的确定。

通过DAY子查询和COMPANY_INFO关联并聚集，使用COUNT(DISTINCT USER_ID)是为了避免同一个用户一天内多次使用该服务而被重复计算。同样用户配置使用服务的日期区间的重叠问题也会被DISTINCT消除掉。子查询COMPANY_DAY2的思路其实和COMPANY_DAY1没有区别，只不过利用了MODEL来自动生成日期维度，由于无论是利用FROM TO方式还是利用子查询方式，都无法带入当前记录的START_DATE和END_DATE。因此只能使用输入的变量:P_START_DATE和:P_END_DATE构造日期维度，随后构造的维度DAY和记录的START_DATE和END_DATE过滤数据，随后的思路和COMPANY_DAY1相同。

COMPANY_DAY主要是提供一个统一接口，关联COMPANY_DAY和SERVICE_RATES表，根据每天的人数和SERVICE_RATES的人数范围进行匹配，汇总对应的RATE并利用PIVOT进行行转列操作，最后关联SERVICES和COMPANIES维度表获取名称，并进行汇总值的计算