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);
}