指定日期區間中不包含分數資料補0分資料列

2010-12-23

4877
0
Oracle
2010-12-30

實在不知道該怎麼下標題

還是看實例吧

我有一些這樣的資料，要列出09801~09812年份的資料並sum出分數SumScoreTab

實在不知道該怎麼下標題

還是看實例吧

我有一些這樣的資料，要列出09801~09812年份的資料並sum出分數SumScoreTab

其中沒有資料的月份要補0分

OK，來源去group 時已經沒有資料了，所以只好另外準備一個table

裡面有12筆記錄(YYYMMTab)


            yymm
  FROM   (SELECT   ADD_MONTHS (
                      TO_DATE ('1911' + :beginYY || :beginMM || '01',
                               'yyyyMMdd'),
                      ROWNUM - 1
                   )
                      yymmdd
            FROM   all_objects
           WHERE   ROWNUM <= :monthCount)

去left join SumScoreTab才有辦法變成

目前看起來都很簡單

但是實際上我可能有6~70個部門sale1、sale2、sale3、sale4

所以我應該要產生的YYYMMTab應該要有12 * 部門數

原本很直覺得覺得就是跑部門數的迴圈去union all就好了

但是又想起跑pl/sql但是想起pl/sql是最慢的方式，所以還是先試看看直接下sql可不可以達成

所以我就手工用sql的方法去產生囉

先想辦法做出部門數*12個月的筆數(TotalTab)


                 FROM   all_objects
                WHERE   ROWNUM <= 12*:deptCount

為什麼要多一個modnum，因為我要先用他來join原本的YYYMMTab

最後長這樣


    FROM      (SELECT   ROWNUM AS rnum, MOD (ROWNUM, 12) AS modnum
                 FROM   all_objects
                WHERE   ROWNUM <= 12 * :deptCount) TotalTab
           INNER JOIN
              (SELECT   (ROWNUM - 1) AS modnum,
                           '0'
                        || (TO_CHAR (yymmdd, 'YYYY') - 1911)
                        || TO_CHAR (yymmdd, 'MM')
                           yymm
                 FROM   (SELECT   ADD_MONTHS (
                                     TO_DATE (
                                        '1911' + :beginYY || :beginMM || '01',
                                        'yyyyMMdd'
                                     ),
                                     ROWNUM - 1
                                  )
                                     yymmdd
                           FROM   all_objects
                          WHERE   ROWNUM <= :monthCount)) YYMMTab
           ON TotalTab.modnum = YYMMTab.modnum
ORDER BY   yymm

於是就產生了

已經產生所有需要的年月了，就把他先跟部門串起來吧


    FROM      (SELECT   yymm, TotalTab.modnum, CEIL (rnum / 12) rnum
                 FROM      (SELECT   ROWNUM AS rnum, MOD (ROWNUM, 12) AS modnum
                              FROM   all_objects
                             WHERE   ROWNUM <= 12 * :deptCount) TotalTab
                        INNER JOIN
                           (SELECT   (ROWNUM - 1) AS modnum,
                                        '0'
                                     || (TO_CHAR (yymmdd, 'YYYY') - 1911)
                                     || TO_CHAR (yymmdd, 'MM')
                                        yymm
                              FROM   (SELECT   ADD_MONTHS (
                                                  TO_DATE (
                                                       '1911'
                                                     + :beginYY
                                                     || :beginMM
                                                     || '01',
                                                     'yyyyMMdd'
                                                  ),
                                                  ROWNUM - 1
                                               )
                                                  yymmdd
                                        FROM   all_objects
                                       WHERE   ROWNUM <= :monthCount)) YYMMTab
                        ON TotalTab.modnum = YYMMTab.modnum) AllYYMMTab
           LEFT JOIN
              AllDeptTab
           ON AllYYMMTab.rnum = AllDeptTab.rnum
ORDER BY   yymm, dept

利用ceil來做分組是為了要跟部門的table做join

這樣出來的結果長這樣

接下來就拿去跟分數的Table串吧


    FROM      (SELECT   YYMM,
                        deptTab.rnum,
                        deptName,
                        deptno
                 FROM      (SELECT   yymm,
                                     TotalTab.modnum,
                                     CEIL (rnum / 12) rnum
                              FROM      (SELECT   ROWNUM AS rnum,
                                                  MOD (ROWNUM, 12) AS modnum
                                           FROM   all_objects
                                          WHERE   ROWNUM <= 12 * :deptCount)
                                        TotalTab
                                     INNER JOIN
                                        (SELECT   (ROWNUM - 1) AS modnum,
                                                  '0'
                                                  || (TO_CHAR (yymmdd, 'YYYY')
                                                      - 1911)
                                                  || TO_CHAR (yymmdd, 'MM')
                                                     yymm
                                           FROM   (SELECT   ADD_MONTHS (
                                                               TO_DATE (
                                                                    '1911'
                                                                  + :beginYY
                                                                  || :beginMM
                                                                  || '01',
                                                                  'yyyyMMdd'
                                                               ),
                                                               ROWNUM - 1
                                                            )
                                                               yymmdd
                                                     FROM   all_objects
                                                    WHERE   ROWNUM <=
                                                               :monthCount))
                                        YYMMTab
                                     ON TotalTab.modnum = YYMMTab.modnum)
                           AllYYMMTab
                        LEFT JOIN
                           (SELECT   *
                              FROM   (SELECT   ROWNUM AS rnum, deptname, deptno
                                        FROM   deptTab)) deptTab
                        ON AllYYMMTab.rnum = deptTab.rnum) AllYYMMDept
           LEFT JOIN
              (  SELECT   deptNo, SUM (Score) sumscore, yymm
                   FROM   scoreTab
               GROUP BY   deptNo, yymm
               ORDER BY   deptNo, yymm) SumScoreTab
           ON AllYYMMDept.YYMM = SumScoreTab.YYMM
              AND AllYYMMDept.deptno = SumScoreTab.deptNo
ORDER BY   deptname, AllYYMMDept.YYMM

結果

整個過程為了要join其實還用了蠻多的rownum去做例如ceil(rownum / 12)之類的分組動作

有時想要抓出比較複雜的資料時可以先分析一下

再去一步一步找出要的資料，解題的過程其實是很有樂趣的^^

create sample


create Table ScoreTab(
deptNo number,
Score number,
YYMM varchar(5)
)

--insert into data,多跑幾次，亂數產生資料
INSERT INTO ScoreTab (
                         deptNo,
                         score,
                         yymm
           )
  VALUES   (
               (MOD (CEIL (DBMS_RANDOM.VALUE * 100), 2) + 1),
               CEIL (DBMS_RANDOM.VALUE * 1000),
               '098' || lpad(  (MOD (CEIL (DBMS_RANDOM.VALUE * 100), 12) + 1),2,'0')
           );
           
           
--create table
create table DeptTab(
    deptNo number,
    deptName varchar(5)
)

--inserti into date
insert into DeptTab(deptNo, deptName) values(1,'Sale1')
insert into DeptTab(deptNo, deptName) values(2,'Sale2')

補充 :

通常因為sql越寫越長，為了日後好收拾

最好是一開始就先把tab取個好辨識的名稱

最好是連註解也寫一下，雖然有時註解真的很難寫的清楚XD

另外小弟使用的工具有程式碼摺疊的功能

在開發時也是有非常大的幫助

而這個工具也有format sql code的功能

排版好比較容易看

如果您使用的工具沒有辦法format sql code的話也可以嘗試

線上版的sql formatter

回首頁