Oracle實(shí)現(xiàn)分組統(tǒng)計(jì)記錄
今天剛上班不久，QQ滴滴的響個(gè)不停，看了下信息是一個(gè)網(wǎng)友要我?guī)兔ο乱粋€(gè)SQL語(yǔ)句，大體意思是：統(tǒng)計(jì)heart_active字段為不同情況的記錄數(shù)，然后按時(shí)間來(lái)分組。
我想了下，心里想這好辦，于是馬上建了一個(gè)表，語(yǔ)句如下：
- CREATE TABLE rfid_fixed_heart (input_date date,heart_active VARCHAR2(2));
接下來(lái)往rfid_fixed_heart表中插入了數(shù)據(jù)，heart_active字段為0和1, input_date中插入YYYY-MM-DD格式的數(shù)據(jù)。
后來(lái)就寫(xiě)了下面兩個(gè)SQL給她，語(yǔ)句一和語(yǔ)句二有點(diǎn)區(qū)別，語(yǔ)句一快一統(tǒng)計(jì)出heart_active字段為不同情況的記錄數(shù)，而語(yǔ)句二則只統(tǒng)計(jì)heart_active字段為0和1情況的記錄數(shù)，兩個(gè)語(yǔ)句的輸出格式也有不同。具體如下：
語(yǔ)句一：
- 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;
語(yǔ)句二：
- 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;
很快就反饋過(guò)結(jié)果來(lái)了，沒(méi)有達(dá)到預(yù)期的效果，但從她的結(jié)果可以看出是由于input_date插入的是YYYY-MM-DD 24HH:MI:SS格式的數(shù)據(jù)導(dǎo)致無(wú)法按日期來(lái)分組。
既然插入的是YYYY-MM-DD 24HH:MI:SS格式得數(shù)據(jù)，要按日期來(lái)排序就需要對(duì)input_date使用trunc函數(shù)來(lái)截取日期值。
最終把原來(lái)的兩個(gè)SQL改成如下語(yǔ)句：
語(yǔ)句三：
- 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;
語(yǔ)句四：
- 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');
把語(yǔ)句給那網(wǎng)友后，運(yùn)行滿足要求，OK。對(duì)于SQL語(yǔ)句的編寫(xiě)需要認(rèn)真考慮數(shù)據(jù)特殊性和表結(jié)構(gòu)，那樣才能夠?qū)崿F(xiàn)SQL語(yǔ)句對(duì)不同環(huán)境的適用。
附未使用decode函數(shù)的實(shí)現(xiàn)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
結(jié)果如下：
原文鏈接：http://www.cnblogs.com/Automation_software/archive/2011/03/02/1968737.html
【編輯推薦】
