PostgreSQL如何实现按年、月、日、周、时、分、秒的分组统计

按年查询 select to_char(date::DATE, YYYY ) as year,sum(shares) as shares, sum(visit

按年查询

select to_char(date::DATE, 'YYYY') as year,sum(shares) as shares, sum(visits) as visits 
from database_table
where date >= '2019-01-01' and  date <= '2020-01-01'  group by year order by year

按月查询

select to_char(date::DATE, 'YYYY-MM') as month,sum(shares) as shares, sum(visits) as visits 
from database_table
where date >= '2019-01-01' and  date <= '2020-01-01'  group by month order by month

按周查询

select to_char(date::DATE-(extract(dow from date::TIMESTAMP)-1||'day')::interval, 'YYYY-mm-dd') week,
sum(shares) as shares, sum(visits) as visits 
from database_table 
where date >= '2019-01-01' and  date <= '2020-01-01' group by week order by week

按天查询

select to_char(date::DATE, 'YYYY-MM-DD') as day,sum(shares) as shares, sum(visits) as visits 
from database_table
where date >= '2019-01-01' and  date <= '2020-01-01'  group by day order by day

按小时查询

select to_char(date::DATE, 'YYYY-MM-DD  HH24') as hour,sum(shares) as shares, sum(visits) as visits 
from database_table
where date >= '2019-01-01' and  date <= '2020-01-01'  group by hour order by hour

按分钟查询

select to_char(date::DATE, 'YYYY-MM-DD  HH24:MI ') as minute,sum(shares) as shares, sum(visits) as visits 
from database_table
where date >= '2019-01-01' and  date <= '2020-01-01'  group by minute order by minute

按秒查询

select to_char(date::DATE, 'YYYY-MM-DD  HH24:MI:SS ') as second,sum(shares) as shares, sum(visits) as visits 
from database_table
where date >= '2019-01-01' and  date <= '2020-01-01'  group by second order by second

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持好代码网。

您可能有感兴趣的文章
PostgreSQL数据库管理系统快速入门

PostgreSQL安装、配置及简单如何使用方法

PostgreSQL入门简介

PostgreSQL与MySQL优劣势比较浅谈

Windows 系统 PostgreSQL 手工安装配置方法