pgsql按天、按月，按年分组统计数据

啊哈哈哈哈啊有

11655人浏览 · 2023-01-06 16:54:58

啊哈哈哈哈啊有 · 2023-01-06 16:54:58 发布



--1 日期格式存成字符型
SELECT 
	 SUBSTRING( strtime, 0, 8 ) AS asTime,
	 round( AVG (字段), 2 ) AVG 
FROM
	表 
WHERE
	SUBSTRING ( strtime, 0, 5 ) = 2022 
GROUP BY
	SUBSTRING ( strtime, 0, 8 )
	ORDER BY SUBSTRING ( strtime, 0, 8 )




--2日期格式 date格式，createtime::DATE声明转换
select
	to_char(createtime::DATE, 'YYYY-MM-DD') AS asTime,
	round( AVG (字段), 2 ) AVG 
from
  表 

GROUP BY asTime ORDER BY asTime

--3日期格式 date格式，日期转为字符
select
  SUBSTRING( to_char(createtime::TIMESTAMP(0) WITHOUT time ZONE,'YYYY-MM-DD HH24:MI:SS'),0,8) AS asTime,
  round(avg(字段))  clmm
from
表 


GROUP BY asTime ORDER BY asTime

SUBSTRING( strtime, 0, 8 )

to_char(createtime::DATE, 'YYYY-MM-DD')

按月和按年只需更改此处即可