MySQL,PostgreSQL 按 年、季、月、周、天 统计

发表于 更新于 分类于 本文字数： 1.6k 阅读时长 ≈ 1 分钟

MySQL,PostgreSQL 按 年、季、月、周、天 统计

MySQL

按日

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

按周

1
2
3
4
5
6
7
8
9
-- 
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())

按月统计

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

按季统计

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

按年统计

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

PostgreSQL

按日统计

1
2
3
to_char( time, 'yyyy-MM-dd' ) AS time

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

按月统计

1
2
3
to_char(time, 'yyyy-MM' ) AS time

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

按年统计

1
2
3
to_char( time,'yyyy' ) AS time

GROUP BY to_char( time,'yyyy' )

按小时统计

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

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

按分钟统计

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

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

按周统计

按周统计最简单法

对时间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’)