今天剛上班不久,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
結果如下: