Oracle實現分組統計記錄

   今天剛上班不久，QQ滴滴的響個不停，看了下資訊是一個網友要我幫忙下一個SQL語句，大體意思是：統計heart_active欄位為不同情況的記錄數，然後按時間來分組。

   我想了下，心裡想這好辦，於是馬上建了一個表，語句如下：

CREATE TABLE rfid_fixed_heart (input_date date,

heart_active   VARCHAR2(2));

    接下來往rfid_fixed_heart表中插入了資料，heart_active欄位為0和1, input_date中插入YYYY-MM-DD格式的資料。

後來就寫了下面兩個SQL給她，語句一和語句二有點區別，語句一快一統計出heart_active欄位為不同情況的記錄數，而語句二則只統計heart_active欄位為0和1情況的記錄數，兩個語句的輸出格式也有不同。具體如下：

語句一：

SELECT a.input_date, a.heart_active, SUM(decode(a.heart_active, 1, 1, 0, 1))

      FROM rfid_fixed_heart a

 GROUP BY a.heart_active, a.input_date

 ORDER BY a.input_date DESC;

語句二：

SELECT a.input_date, SUM(decode(a.heart_active, '0', '1')) AS heart_active_0,

                   SUM(decode(a.heart_active, '1', '1')) AS heart_active_1

      FROM rfid_fixed_heart a

 GROUP BY a.input_date;

很快就反饋過結果來了，沒有達到預期的效果，但從她的結果可以看出是由於input_date插入的是YYYY-MM-DD 24HH:MI:SS格式的資料導致無法按日期來分組。

既然插入的是YYYY-MM-DD 24HH:MI:SS格式得資料，要按日期來排序就需要對input_date使用trunc函數來截取日期值。

最終把原來的兩個SQL改成如下語句：

語句三：

SELECT trunc(a.input_date, 'dd'), a.heart_active, SUM(decode(a.heart_active, 1, 1, 0, 1))

      FROM rfid_fixed_heart a

 GROUP BY a.heart_active, trunc(a.input_date, 'dd')

 ORDER BY trunc(a.input_date, 'dd') DESC;

  

    語句四：

SELECT trunc(a.input_date, 'dd'), SUM(decode(a.heart_active, '0', '1')) AS heart_active_0,

                   SUM(decode(a.heart_active, '1', '1')) AS heart_active_1

      FROM rfid_fixed_heart a

 GROUP BY trunc(a.input_date, 'dd');

把語句給那網友後，運行滿足要求，OK。對於SQL語句的編寫需要認真考慮資料特殊性和表結構，那樣才能夠實現SQL語句對不同環境的適用。

   附未使用decode函數的實現SQL：

SELECT op_date, heart_active, SUM(heart_active_0) AS heart_active_0,

                   SUM(heart_active_1) AS heart_active_1

      FROM (SELECT to_char(rfid_fixed_heart.input_date, 'yyyy-mm-dd') AS op_date,

                                                heart_active AS heart_active,

                                                CASE heart_active

                                                             WHEN '0' THEN

                                                                  COUNT(heart_active)

                                                             ELSE

                                                                  0

                                                 END AS heart_active_0,

                                                CASE heart_active

                                                             WHEN '1' THEN

                                                                  COUNT(heart_active)

                                                             ELSE

                                                                  0

                                                 END AS heart_active_1

                               FROM rfid_fixed_heart

                              GROUP BY input_date, heart_active) a

 GROUP BY op_date, heart_active

 ORDER BY op_date DESC

      結果如下：