ITPUB SQL大賽第四期

最近整理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維度表獲取名稱，並進行彙總值的計算