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

🌐 经济型：买域名、轻量云服务器、用途：游戏网站等《腾讯云》特点：特价机便宜适合初学者用点我优惠购买

🚀 拓展型：买域名、轻量云服务器、用途：游戏网站等《阿里云》特点：中档服务器便宜域名备案事多点我优惠购买

🛡️ 稳定型：买域名、轻量云服务器、用途：游戏网站等《西部数码》特点：比上两家略贵但是稳定性超好事也少点我优惠购买

文章摘要

本文展示了如何利用SQL语言从数据库表中提取不同时间粒度的数据，并按具体时间范围进行分组及统计汇总。具体来说，文章通过7个不同的SQL语句，分别按年、月、周、日、小时、分钟、秒的时间粒度，从数据库表中提取数据，并计算"sum(shares)"和"sum(visits)"的值。这些方法可以帮助用户在指定的时间范围内，快速获取详细的统计数据。文章最后提到，希望对读者的学习有帮助，并呼吁大家多多支持脚本之家。

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 实现查询某时间区间的所有日期案例postgresql 计算时间差的秒数、天数实例