项目实战：统计图（近一年的任务人员出席率，费用）

项目实战：统计图（近一年的任务人员出席率，费用）

概述：

不知道各位在做需求时，是否会碰到这样的场景：

统计某个机构下的所有人员近一年（一个时间段）的出勤率，出勤费用。

我在做项目的时候，经常会碰到这种需求，之前在网上找到过某种实现方式，但是太过繁琐，这一次又碰到了，所以就记录一下，以后再碰到类似的可以进行复用。

这一次我用到的方式是先用Java计算出要统计的这个时间段的开头和结尾的月份，比如近一年，那以这个月开始就是，2021-1 ~ 2021-12 。这样就先得到了这个时间段。

Java代码实例：

    
    public String getLatest12Month(Date date){
        Calendar from = Calendar.getInstance();
        from.setTime(date);
        String str1 = from.get(Calendar.YEAR)+"-"+fillZero(from.get(Calendar.MONTH)+1);
        //11个月前
        from.add(Calendar.MONTH, -11);
        String str2 = from.get(Calendar.YEAR)+"-"+fillZero(from.get(Calendar.MONTH)+1);
        return str2+"~"+str1;
    }
    
    public static String fillZero(int i){
        String month = "";
        int limit = 10;
        if (i < limit) {
            month = "0" + i;
        } else {
            month = String.valueOf(i);
        }
        return month;
    }

因为我们要统计近一年，也就是12个月份，这样的话，有可能某一个月没有任务，也就没有人员的出勤率和费用。这样的话，如果SQL不经过处理，直接去查询，可能就没有某个月的数据，这样返回给前端的数据就会缺失。所以，我们要通过上一步计算出的时间段，来执行下面的这段SQL得到每个月的值。

SQL：其中to_timestamp函数中的日期，就是上一步计算出的时间段开始和结束时间

SELECt A
	.TIME 
FROM
	(
SELECt
	to_char( b, 'YYYY-MM' ) AS TIME 
FROM
	generate_series ( to_timestamp( '2021-01', 'YYYY-MM' )
  , to_timestamp( '2021-12', 'YYYY-MM' ), '1 month' ) AS b 
GROUP BY
TIME 
ORDER BY
TIME ASC 
	) AS A

得到的值如下图：

 然后，遍历这个时间段，计算某人的在这一时间段的各月出勤率：

SELECt CAST
	(
	CAST (( SELECT COUNT ( * ) FROM t_people WHERe people_id = ? 
    AND status = '2' 
    AND to_char( reply_time, 'YYYY-MM' ) = :thisMonth ) 
    AS float4 ) /
    CASE
	( SELECt COUNT ( * ) FROM t_people 
    WHERe to_char( reply_time, 'YYYY-MM' ) 
    BETWEEN :startDate AND :endDate ) 
	WHEN 0 THEN NULL 
    ELSE ( SELECt COUNT ( * ) FROM t_people 
    WHERe to_char( reply_time, 'YYYY-MM' ) 
    BETWEEN :startDate AND :endDate ) 
	END AS DECIMAL ( 10, 2 )) AS attendance

 这条SQL中用到了CAST转换类型函数和to_char转换字符串函数还有CASE WHEN THEN ELSE 语句。

总之，这条SQL会被执行12次，计算出每个月的近一年该人员出勤率。

具体效果：

x轴的月份数据，就是此接口返回的，实现月份动态变化。

总结：

其实这种方法的核心思想就是首先得到要计算的时间段，然后用SQL生成每个月的月份，之后遍历执行，计算出这个时间段内要统计的数据。此方法适用于统计某一时间段内的各种数据。

扩展：PGSQL中的generate_series函数

PostgreSQL 中有一个很有用处的内置函数generate_series，可以按不同的规则产生一系列的填充数据。

函数参数类型返回类型描述generate_series(start,stop)int 或 bigintsetof int 或 setof bigint（与参数类型相同）生成一个数值序列，从start 到 stop，步进为一generate_series(start,stop,step)int 或 bigintsetof int 或 setof bigint(与参数类型相同)生成一个数值序列，从start 到 stop，步进为stepgenerate_series(start, stop, step_interval)timestamp or timestamp with time zonetimestamp 或 timestamp with time zone生成一个数值序列，从start 到 stop，步进为step 例子：int类型

如果step 是正数，而start 大于stop，那么返回零行。相反，如果step 是负数，start 小于stop，则返回零行。如果是NULL 输入，也产生零行。step 为零则是一个错误。

 例子：时间类型

以1个月为step，进行递增

也可以小时，天数为step，看具体的业务需求

 例子：IP类型

首先，表中数据格式如下

 很实用的一个函数，在项目中多次用到，尤其是统计图需要返回给前端时间段数据时，更是为妙。

扩展转载自：PostgreSQL的generate_series函数应用 - David_Tang - 博客园