mysql,postgresql 按 年、季、月、周、天 统计

mysql

按日

SELECT COUNT(*),DATE(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime)='2016' GROUP BY DAY(CreateTime)

按周

SELECT COUNT(*),WEEK(CreateTime) FROM t_voipchannelrecord WHERE MONTH(CreateTime) = '8' GROUP BY WEEK(CreateTime) 

周一到周五每天的统计结果

SELECT COUNT(*),DAYNAME(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime) = '2016' GROUP BY DAYNAME(CreateTime) 

统计本周数据

SELECT COUNT(*) FROM t_voipchannelrecord WHERE MONTH(CreateTime) = MONTH(CURDATE()) AND WEEK(CreateTime) = WEEK(CURDATE())

按月统计

SELECT COUNT(*),MONTH(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime) = '2016' GROUP BY MONTH(CreateTime) 

按季统计

SELECT COUNT(*),QUARTER(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime) = '2016' GROUP BY QUARTER(CreateTime) 

按年统计

SELECT COUNT(*),YEAR(CreateTime) FROM t_voipchannelrecord  GROUP BY YEAR(CreateTime) 

 

================================================================================================

 

postgresql

1、按日统计

to_char( time, 'yyyy-MM-dd' ) AS time

GROUP BY  to_char(time, 'yyyy-MM-dd' )

2、按月统计

to_char(time, 'yyyy-MM' ) AS time

GROUP BY  to_char(time, 'yyyy-MM' )

3、按年统计

to_char( time,'yyyy' ) AS time

GROUP BY to_char( time,'yyyy' )

4、按小时统计

to_char(time, 'yyyy-MM-dd HH' ) AS time

GROUP BY  to_char( time, 'yyyy-MM-dd HH' )

5、按分钟统计

to_char( time, 'yyyy-MM-dd HH:mm' ) AS time

GROUP BY  to_char( time, 'yyyy-MM-dd HH:mm' )

6、按周统计

按周统计最简单法

对时间row_date字段做处理，变成对应日期周一时间，然后按这个周一的时间去统计。减1的操作表示为对应日期的星期一，减1,2,3,4,5,6,7分别是对应日期的周一，周二，周三，周四，周五、周六、周日。

to_char( time-(extract (dow from time) - 1 ||'day')::interval,'yyyy-MM-dd')  row_date

然后按上面的语句分组统计即可实现按周统计，下面对应分组函数

GROUP BY  to_char(time-(extract (dow from time) - 1 ||'day')::interval,'yyyy-MM-dd')

 

================================================================================================

 

例：postgresql 补齐0

说明：看sql前要了解

day_key 类型 int8  20190618
参数 startTime 类型 VARCHAR  20190618
参数 endTime 类型 VARCHAR  20190618

生成日期

SELECT 
        to_char ( b, 'YYYY-MM-DD' ) AS time  
FROM
        generate_series (
            to_timestamp ( '2019-06-10', 'YYYY-MM-DD' ), 
            to_timestamp ( '2019-06-18', 'YYYY-MM-DD' ), 
            '1 days'
        ) AS b

SELECT
    to_char ( b, 'YYYY-MM' ) AS time
FROM
    generate_series (
        to_timestamp ('2019-01-10', 'YYYY-MM-DD' ),
        to_timestamp ('2019-06-18', 'YYYY-MM-DD' ), 
        '31 days'
    ) AS b

 

1、按日统计

 SELECT
	a.startTime AS statTime,
	COALESCE(c.mileage,0) as mileage ,
	COALESCE(c.duration,0) as duration ,
	COALESCE(c.averagespeed,0) as averagespeed
	from
	(
		<!-- 获取日期 -->
		SELECT
		to_char ( b, 'YYYYMMDD' ) AS startTime
		FROM
		generate_series (
			to_timestamp (  to_char(to_date(#{startTime,jdbcType = VARCHAR}, 'YYYYMMDD'), 'YYYY-MM-DD' ), 'YYYY-MM-DD' ),
			to_timestamp (  to_char(to_date(#{endTime,jdbcType = VARCHAR}, 'YYYYMMDD'), 'YYYY-MM-DD' ), 'YYYY-MM-DD' ),
			'1 days'
		) AS b
		GROUP BY startTime ORDER BY startTime asc
	) as a
	FULL OUTER JOIN
	(
		<!-- 获取数据 -->
		select  to_char(day_key, '99999999') as startTime,
		sum(total_mileage) as mileage
		from ts_vehicle_power_cons_daily
		<where>
			row_state = 1
			<if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
				and day_key <![CDATA[ >= ]]> to_number(#{startTime,jdbcType = VARCHAR},'99999999')
				and day_key <![CDATA[ <= ]]> to_number(#{endTime,jdbcType = VARCHAR},'99999999')
			</if>
		</where>
		group by startTime limit 7 offset 0
	) as c
	on a.startTime = c.startTime
	order by a.startTime asc ;

解释：

mybatis 不支持直接写 >= 或 <= ,      要写成  <![CDATA[ >= ]]>

to_number(#{endTime,jdbcType = VARCHAR},'99999999')     varchar 转  number

to_date(#{endTime,jdbcType = VARCHAR}, 'YYYYMMDD')     varchar 转 date

to_char(to_date(#{endTime,jdbcType = VARCHAR}, 'YYYYMMDD'), 'YYYY-MM-DD' )  date 格式转换

 

2、按月统计

SELECT
	a.startTime || '01' AS statTime,
	COALESCE(c.mileage,0) as mileage ,
	COALESCE(c.duration,0) as duration ,
	COALESCE(c.averagespeed,0) as averagespeed
	from
	(
		<!-- 获取日期 -->
		SELECT
		to_char ( b, 'YYYYMM' ) AS startTime
		FROM
		generate_series (
			to_timestamp (  to_char(to_date(#{startTime,jdbcType = VARCHAR}, 'YYYYMMDD'), 'YYYY-MM-DD' ), 'YYYY-MM-DD' ),
			to_timestamp (  to_char(to_date(#{endTime,jdbcType = VARCHAR}, 'YYYYMMDD'), 'YYYY-MM-DD' ), 'YYYY-MM-DD' ),
			'31 days'
		) AS b
		GROUP BY startTime ORDER BY startTime asc
	) as a
	FULL OUTER JOIN
	(
		<!-- 获取数据 -->
		select
		to_char(to_date(to_char(day_key, '99999999' ), 'YYYYMMdd'), 'yyyyMM' ) as startTime,
		sum(total_mileage) as mileage
		from ts_vehicle_power_cons_daily
		<where>
			row_state = 1
			<if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
				and day_key <![CDATA[ >= ]]> to_number(#{startTime,jdbcType = VARCHAR},'99999999')
				and day_key <![CDATA[ <= ]]> to_number(#{endTime,jdbcType = VARCHAR},'99999999')
			</if>
		</where>
		group by to_char(to_date(to_char(day_key, '99999999' ), 'YYYYMMdd'), 'yyyyMM' ) limit 7 OFFSET 0
	) as c
	on a.startTime = c.startTime
	order by a.startTime asc ;

 

3、按周统计

生成日期 只能加天，不能直接统计每周 周一的时间，所以按周统计要在业务逻辑层实现

 select  to_char( row_create_time - (extract (dow from row_create_time) - 1 ||'day')::interval,'yyyy-MM-dd') as startTime,
	sum(total_mileage) as mileage,
	sum(total_duration) as duration,
	sum(avg_speed) as averagespeed
	from ts_vehicle_power_cons_daily
	<where>
		row_state = 1
		<if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
			and day_key <![CDATA[ >= ]]> to_number(#{startTime,jdbcType = VARCHAR},'99999999')
			and day_key <![CDATA[ <= ]]> to_number(#{endTime,jdbcType = VARCHAR},'99999999')
		</if>
	</where>
	group by startTime
	order by startTime DESC limit 7 OFFSET 0

/**
 * 获取某一时间段特定星期几的日期
 * @param dateFrom 开始时间 20190430
 * @param dateEnd 结束时间 20190618
 * @return 返回时间数组
 * strWeekNumber 星期日：1，星期一：2，星期二：3，星期三：4，星期四：5，星期五：6，星期六：7
 */
public static List<String> getDates(String dateFrom, String dateEnd) {
	long time = 1l;
	long perDayMilSec = 24 * 60 * 60 * 1000;
	List<String> dateList = new ArrayList<>();
	SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
	//需要查询的星期系数
	String strWeekNumber = "2";
	try {
		dateFrom = sdf.format(sdf.parse(dateFrom).getTime() - perDayMilSec);
		while (true) {
			time = sdf.parse(dateFrom).getTime();
			time = time + perDayMilSec;
			Date date = new Date(time);
			dateFrom = sdf.format(date);
			if (dateFrom.compareTo(dateEnd) <= 0) {
				//查询的某一时间的星期系数
				Integer weekDay = dayForWeek(date);
				//判断当期日期的星期系数是否是需要查询的
				if (strWeekNumber.indexOf(weekDay.toString()) != -1) {
					dateList.add(dateFrom);
				}
			} else {
				break;
			}
		}
	} catch (ParseException e1) {
		e1.printStackTrace();
	}
	return dateList;
}

public static Integer dayForWeek(Date date) {
	Calendar calendar = Calendar.getInstance();
	calendar.setTime(date);
	return calendar.get(Calendar.DAY_OF_WEEK);
}