flowable的流程任务统计sql(续）

    继续之前的文章，我们这里还需要进行按月统计，同时为了适应jeecg的图形显示，所以做了一些调整如下：

  1、按月统计任务数量

select DATE_FORMAT(c.days, '%Y%m') months, count(num) num from (SELECT DATE_FORMAT(START_TIME_, '%Y-%m-%d') AS days,COUNT(1) AS num FROM act_hi_procinst GROUP BY DATE_FORMAT(START_TIME_, '%Y-%m-%d') ORDER BY START_TIME_) c GROUP BY months

   根据实际jeecg图形显示需要就用下面这个sql

select DATE_FORMAT(c.days, '%Y%m') x, count(num) y from (SELECT DATE_FORMAT(START_TIME_, '%Y-%m-%d') AS days,COUNT(1) AS num FROM act_hi_procinst GROUP BY DATE_FORMAT(START_TIME_, '%Y-%m-%d') ORDER BY START_TIME_) c GROUP BY x

 

2、按月统计流程数量

select DATE_FORMAT(c.days, '%Y%m') months, count(num) num from (select DATE_FORMAT(b.DEPLOY_TIME_, '%Y-%m-%d') AS days , count(a.KEY_) as num from (select DEPLOYMENT_ID_,KEY_,MAX(VERSION_) from act_re_procdef GROUP BY KEY_) a LEFT JOIN act_re_deployment b on a.DEPLOYMENT_ID_ = b.ID_ GROUP BY DATE_FORMAT(b.DEPLOY_TIME_, '%Y-%m-%d') ORDER BY b.DEPLOY_TIME_) c GROUP BY months

 根据实际jeecg图形显示需要就用下面这个sql

select DATE_FORMAT(c.days, '%Y%m') x, count(num) y from (select DATE_FORMAT(b.DEPLOY_TIME_, '%Y-%m-%d') AS days , count(a.KEY_) as num from (select DEPLOYMENT_ID_,KEY_,MAX(VERSION_) from act_re_procdef GROUP BY KEY_) a LEFT JOIN act_re_deployment b on a.DEPLOYMENT_ID_ = b.ID_ GROUP BY DATE_FORMAT(b.DEPLOY_TIME_, '%Y-%m-%d') ORDER BY b.DEPLOY_TIME_) c GROUP BY x

 

附：一些统计方法

1）按天统计：

select DATE_FORMAT(start_time,'%Y%m%d') days,count(product_no) count from test group by days;
2）按周统计：

select DATE_FORMAT(start_time,'%Y%u') weeks,count(product_no) count from test group by weeks;
3）按月统计:

select DATE_FORMAT(start_time,'%Y%m') months,count(product_no) count from test group bymonths;