一段时间内统计分析数据查询sql实现

最近在做一个关于统计分析的功能&＃xff0c;需求是将最近一周的数据统计出来并使用折线图展示。

1. 循环计算一周之内的日期

最先想到就是这种方法&＃xff0c;但是这种方法查询数据库次数多&＃xff0c;效率低。舍弃。

2.使用group子句以及mysql日期函数

这种方法只需查询一次&＃xff0c;效率高&＃xff0c;看着比较优雅。具体参考下面代码&＃xff1a;

/*** 查询一周之内的增加的积分或者减少的积分* * &＃64;return*/public List listAccSumByweek(String type){String hql&＃61;"select sum (t.dealAmount),DATE_FORMAT(t.createTm,&＃39;%Y%m%d&＃39;)"&＃43; " from AccPointsDetails as t where TIMESTAMPDIFF(WEEK,t.createTm,?)&＃61;0"&＃43; " and t.dealType &＃61;? group by DATE_FORMAT(t.createTm,&＃39;%Y%m%d&＃39;) ";return findArray(hql, new Timestamp(System.currentTimeMillis()),type);}


3. 计算任意时间段内的统计数据

基于第二种方法&＃xff0c;稍作修改即可实现&＃xff0c;示例代码如下&＃xff1a;


/*** 查询一周之内的增加的积分或者减少的积分* type PointsRuleConstant.POINTS_ADD or PointsRuleConstant.POINTS_MINUS* &＃64;return*/
public List listAccSumByweek(long startTimestamp, long endTimestamp,String type){String hql&＃61;"select sum (t.dealAmount),DATE_FORMAT(t.createTm,&＃39;%Y-%m-%d&＃39;)"&＃43; " from AccPointsDetails as t where TIMESTAMPDIFF(DAY,t.createTm,?)<&＃61;0"&＃43;" and TIMESTAMPDIFF(DAY,t.createTm,?)>&＃61;0"&＃43; " and t.dealType &＃61;? group by DATE_FORMAT(t.createTm,&＃39;%Y-%m-%d&＃39;) ";return findArray(hql,new Timestamp(startTimestamp),new Timestamp(endTimestamp),type);
}public List findArray(final String hql, final Object... values) {return createQuery(hql, values).list();
}


下面是代码出现的mysql日期函数的解释,官方文档

TIMESTAMPADD(unit,interval,datetime_expr)

Adds the integer expression interval to the date or datetime expression datetime_expr. The unit for interval is given by the unit argument, which should be one of the following values: MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

The unit value may be specified using one of keywords as shown, or with a prefix of SQL_TSI_. For example, DAY and SQL_TSI_DAY both are legal.

mysql> SELECT TIMESTAMPADD(MINUTE,1,&＃39;2003-01-02&＃39;);-> &＃39;2003-01-02 00:01:00&＃39;
mysql> SELECT TIMESTAMPADD(WEEK,1,&＃39;2003-01-02&＃39;);-> &＃39;2003-01-09&＃39;

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part &＃39;00:00:00&＃39; where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function.

mysql> SELECT TIMESTAMPDIFF(MONTH,&＃39;2003-02-01&＃39;,&＃39;2003-05-01&＃39;);-> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,&＃39;2002-05-01&＃39;,&＃39;2001-01-01&＃39;);-> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,&＃39;2003-02-01&＃39;,&＃39;2003-05-01 12:05:55&＃39;);-> 128885