快捷搜索:

Oracle实现分组统计记录

本日刚上班不久,QQ滴滴的响个不绝,看了下信息是一个网友要我协助下一个SQL语句,大年夜体意思是:统计heart_active字段为不合环境的记录数,然后按光阴来分组。

我想了下,心里想这好办,于是顿时建了一个表,语句如下:

CREATE TABLE rfid_fixed_heart (input_date date,

heart_activeVARCHAR2(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

结果如下:

您可能还会对下面的文章感兴趣: