根据不同日期类型分组查询 group by（日、周、月、季度、年）

已于 2024-04-30 14:23:26 修改
阅读量3.5k 收藏 8
点赞数 12
分类专栏： PostgreSQL 文章标签： oracle 数据库 postgresql sql
于 2023-12-14 12:01:05 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_53940224/article/details/134992098
版权

1.数据库类型 

数据库为PostgreSQL 版本为 PostgreSQL 11.18

2.SQL代码

分为SQL片段 和 完整SQL

2.1 SQL片段

2.1.1.声明 起始时间 结束时间
 
WITH date_range AS (
  SELECT
    '2021-12-09'::date AS start_date,
    '2023-12-17'::date AS end_date
)
2.1.2. 声明时间间隔
--2.声明时间间隔
, all_intervals AS (
  SELECT
	  make_interval(0, 0, 0, 1) AS day_interval,
	  make_interval(0, 0, 1, 0) AS week_interval,
    make_interval(0, 1, 0, 0) AS month_interval,
		make_interval(0, 3, 0, 0) AS quarter_interval,
    make_interval(1, 0, 0, 0) AS year_interval
)
2.1.3.生成日期序列
--3.生成日期序列 
		--3.1 间隔为日的序列
, ri_all_dates AS (
  SELECT
    generate_series(
      DATE_TRUNC('day', (SELECT start_date FROM date_range))::date,
      DATE_TRUNC('day', (SELECT end_date FROM date_range))::date ,
      (SELECT day_interval FROM all_intervals)::interval
    )::date AS date_start
)
		--3.1 间隔为周的序列
, zhou_all_dates AS (
  SELECT
    generate_series(
      DATE_TRUNC('week', (SELECT start_date FROM date_range))::date,
      DATE_TRUNC('week', (SELECT end_date FROM date_range))::date+ INTERVAL '1 week' - INTERVAL '1 day',
      (SELECT week_interval FROM all_intervals)::interval
    )::date AS date_start
)
		--3.1 间隔为月的序列
, yue_all_dates AS (
  SELECT
    generate_series(
      DATE_TRUNC('month', (SELECT start_date FROM date_range))::date,
      DATE_TRUNC('month', (SELECT end_date FROM date_range))::date + INTERVAL '1 month' - INTERVAL '1 day',
      (SELECT month_interval FROM all_intervals)::interval
    )::date AS date_start
)
		--3.1 间隔为季度的序列
,jidu_all_dates AS (
  SELECT
    generate_series(
      DATE_TRUNC('quarter', (SELECT start_date FROM date_range))::date,
      DATE_TRUNC('quarter', (SELECT end_date FROM date_range))::date + make_interval(0, 3, 0, 0) - INTERVAL '1 day',
      (SELECT quarter_interval FROM all_intervals)::interval
    )::date AS date_start
)
		--3.1 间隔为年的序列
,nian_all_dates AS (
  SELECT
    generate_series(
      DATE_TRUNC('year', (SELECT start_date FROM date_range))::date,
      DATE_TRUNC('year', (SELECT end_date FROM date_range)::date + INTERVAL '1 year')::date - INTERVAL '1 day',
      (SELECT year_interval FROM all_intervals)::interval
    )::date AS date_start
)
2.1.4.格式化输出
--4.格式化输出
, ri AS (
  SELECT
  to_char(date_start, 'YYYY') || '年第' || to_char(date_start, 'FMMM') || '月' ||  to_char(date_start, 'FMDD') || '日' AS time_description
  FROM
    ri_all_dates
)
,  zhou AS (
  SELECT
    to_char(date_start, 'IYYY') || '年第' || to_char(EXTRACT(WEEK FROM date_start)::INTEGER, 'FM999') || '周' AS time_description

  FROM
    zhou_all_dates
),yue AS (
  SELECT
    to_char(date_start, 'YYYY') || '年第' || to_char(date_start, 'FMMM') || '月' AS time_description
  FROM
    yue_all_dates
)
,jidu AS (
  SELECT
    to_char(date_start, 'YYYY') || '年第' || to_char(date_start, 'Q') || '季度' AS time_description
  FROM
    jidu_all_dates
)
, nian AS (
  SELECT
    to_char(date_start, 'YYYY') || '年' AS time_description
  FROM
    nian_all_dates
)
2.1.5.测试查询

可以选择 ri, zhou, yue, jidu, nian 中的一个或多个进行查询

--SELECT * FROM ri;
--SELECT * FROM zhou;
--SELECT * FROM yue;
--SELECT * FROM jidu;
--SELECT * FROM nian;

2.2 完整SQL


--1.声明 起始时间 结束时间
WITH date_range AS (
  SELECT
    '2021-12-09'::date AS start_date,
    '2023-12-17'::date AS end_date
)
--2.声明时间间隔
, all_intervals AS (
  SELECT
	  make_interval(0, 0, 0, 1) AS day_interval,
	  make_interval(0, 0, 1, 0) AS week_interval,
    make_interval(0, 1, 0, 0) AS month_interval,
		make_interval(0, 3, 0, 0) AS quarter_interval,
    make_interval(1, 0, 0, 0) AS year_interval
)
--3.生成日期序列 
		--3.1 间隔为日的序列
, ri_all_dates AS (
  SELECT
    generate_series(
      DATE_TRUNC('day', (SELECT start_date FROM date_range))::date,
      DATE_TRUNC('day', (SELECT end_date FROM date_range))::date ,
      (SELECT day_interval FROM all_intervals)::interval
    )::date AS date_start
)
		--3.1 间隔为周的序列
, zhou_all_dates AS (
  SELECT
    generate_series(
      DATE_TRUNC('week', (SELECT start_date FROM date_range))::date,
      DATE_TRUNC('week', (SELECT end_date FROM date_range))::date+ INTERVAL '1 week' - INTERVAL '1 day',
      (SELECT week_interval FROM all_intervals)::interval
    )::date AS date_start
)
		--3.1 间隔为月的序列
, yue_all_dates AS (
  SELECT
    generate_series(
      DATE_TRUNC('month', (SELECT start_date FROM date_range))::date,
      DATE_TRUNC('month', (SELECT end_date FROM date_range))::date + INTERVAL '1 month' - INTERVAL '1 day',
      (SELECT month_interval FROM all_intervals)::interval
    )::date AS date_start
)
		--3.1 间隔为季度的序列
,jidu_all_dates AS (
  SELECT
    generate_series(
      DATE_TRUNC('quarter', (SELECT start_date FROM date_range))::date,
      DATE_TRUNC('quarter', (SELECT end_date FROM date_range))::date + make_interval(0, 3, 0, 0) - INTERVAL '1 day',
      (SELECT quarter_interval FROM all_intervals)::interval
    )::date AS date_start
)
		--3.1 间隔为年的序列
,nian_all_dates AS (
  SELECT
    generate_series(
      DATE_TRUNC('year', (SELECT start_date FROM date_range))::date,
      DATE_TRUNC('year', (SELECT end_date FROM date_range)::date + INTERVAL '1 year')::date - INTERVAL '1 day',
      (SELECT year_interval FROM all_intervals)::interval
    )::date AS date_start
)
--4.格式化输出
, ri AS (
  SELECT
  to_char(date_start, 'YYYY') || '年第' || to_char(date_start, 'FMMM') || '月' ||  to_char(date_start, 'FMDD') || '日' AS time_description
  FROM
    ri_all_dates
)
,  zhou AS (
  SELECT
    to_char(date_start, 'IYYY') || '年第' || to_char(EXTRACT(WEEK FROM date_start)::INTEGER, 'FM999') || '周' AS time_description

  FROM
    zhou_all_dates
),yue AS (
  SELECT
    to_char(date_start, 'YYYY') || '年第' || to_char(date_start, 'FMMM') || '月' AS time_description
  FROM
    yue_all_dates
)
,jidu AS (
  SELECT
    to_char(date_start, 'YYYY') || '年第' || to_char(date_start, 'Q') || '季度' AS time_description
  FROM
    jidu_all_dates
)
, nian AS (
  SELECT
    to_char(date_start, 'YYYY') || '年' AS time_description
  FROM
    nian_all_dates
)

--5.测试查询
-- 可以选择 ri, zhou, yue, jidu, nian 中的一个或多个进行查询
--SELECT * FROM ri;
--SELECT * FROM zhou;
--SELECT * FROM yue;
--SELECT * FROM jidu;
--SELECT * FROM nian;

3.效果图

Java小白白同学
关注
专栏目录
group by按照日期分组
dianzhongqu2330的博客
05-19 3821
一、group by分组函数大家很熟悉，就是按照某一列进行分组排序。但是很多时候分组排序的时候，我们需要按照日或者月或者年来分组当前的数据。但是数据表中时间的字段是精确到分钟的，这种要怎么处理呢？ 1. 其实很简单，只要找到当前表中的日期列，并且其转换成需要排序的年月格式便可，并且取出对应的字符长度。 2. 如下，我需要将金额数据按照月度汇总，那么我需要做的就是把当前日期先转换成年月...
分组统计 - 不同时间颗粒度下，按照秒、分、时、日、周、月、季度、年 GROUP BY 分组统计 - （MySQL）
黄饱饱
01-24 5900
数据处理时，经常需要：统计不同时间粒度下的数据分布情况。 例如，网站每天（or每小时）的访问量，周杰伦每年（or每季度 or每月）的收入等。 首先有一个表叫：table_test，其中 time 列为最细的时间颗粒度，类型是datetime，income 列是收入（下图）。  代码： ##################################################...
mysql按日期分组统计的查询
ba2394242的博客
03-05 395
SELECT DATE_FORMAT( TRANS_TIME, "%Y-%m-%d" ),SUM(SUM) FROM ibsp_pay_bill WHERE TRANS_TIME>"2018-02-05 00:00:00" AND TRANS_TIME<"2018-03-05 23:59:59" AND COMPARE_SIGN="支付正常" GROUP BY DA...
【SQL】掌握SQL查询技巧：数据分组与排序
最新发布
人生苦短，睡觉要紧，睡醒再说
10-08 1757
GROUP BY子句用于将来自SELECT查询的数据行分组，通常与聚合函数（如COUNTSUMAVGMAX和MIN）一起使用，以便对每个组执行计算。这种分组使我们能够对数据进行汇总分析，提取有用的信息。ORDER BY子句用于对查询结果进行排序。默认情况下，排序是升序的，但可以使用DESC关键字指定降序排序。这有助于用户根据特定的需求查看数据。通过掌握GROUP BY和ORDER BY子句，你可以有效地分析和整理 SQL 查询的结果。无论是进行数据汇总还是结果排序，这两者都是数据分析中不可或缺的工具。
MySQL 根据日期 group by
或非与博客
12-15 1万+
mysql 有一张这样的表，需要根据customer_ip_date这个字段的日期进行group by 排序,得出每天的数量是多少 MySQL 根据日期 group by有两种写法: 1.字符串截取：substring，从第一个字段截取到第十个字符串,然后group by SELECT substring(customers_ip_date, 1, 10),count(distinct customers_ip) as days_count FROM `userLoginTable...
MySql 使用GROUP BY 按照日期分组统计
u010716097的专栏
02-17 2829
&lt;pre name="code" class="sql" style="line-height: 18px;"&gt;&lt;span style="color: rgb(0, 0, 255); background-color: rgb(204, 204, 204);"&gt;SELECT &lt;/span&gt;&lt;span style="bac
mysql groupBy 按日期分组
i小明同学的博客
12-10 6846
需求中经常会有按日期,月份,年份分组统计的操作 那么groupBy直接是实现不了的,需要转化一下,话不多说,看代码 SELECT FROM_UNIXTIME(create_time, '%Y-%m-%d') as day, create_time, count( DISTINCT uid ) AS amount FROM `black_production_user` WHERE create_time >= 1588262400 AND create_time <= 16
mysql group by日期_GROUP BY和HAVING 以及mysql中常用的日期函数
weixin_28900989的博客
01-25 909
一.mysql中的GROUP BY和HAVINGGROUP BY常见的是和聚合函数(SUM,MIN,MAX,COUNT)搭配使用.比如:SELECT category,SUM(money) AS `total` FROM user_money GROUP BY category;按类别分组统计user_money表每个类别总计有多少money现在如果增加个条件,需要统计每个类别中支出的money总...
oracle实现按天，周，月，季度，年查询排序方法
09-09
对于按月查询，我们可以使用`'YYYY-MM'`作为格式模型： ```sql SELECT to_char(t.start_time, 'YYYY-MM') AS month, COUNT(*) FROM test t WHERE to_char(t.start_time, 'YYYY') = '2019' GROUP BY to_char(t....
Pandas 分组之后按照不同时间维度算总和【年，季度，月，周算总和】
qq_36622490的博客
03-15 3990
需求是公司需要的大概数据在数据库中的格式如下: 模拟数据库表 客户名字cus_name 利润 时间 cus1 2342 2021-1-2 cus2 2352 2021-1-3 cus1 34 2021-1-4 cus3 3344 2021-2-1 cus3 451 2021-2-4 cus2 2352 2021-1-6 cus1 251 2021-1-29 cus3 24
java中,根据年月日周季度,不同,查询不同分组的数据
02-07
在 Java 中，可以使用 java.time 包中的类来获取年、月、日、周、季度信息。 例如，可以使用 LocalDate 类来表示日期，然后使用 ...以此类推，可以根据需要查询按日、周、季度分组的数据。 希望这个答案能够帮到你。
group by 按某一时间段分组统计并查询(推荐)
01-21
工作中要求对日志表中的时间戳字段进行分组。分组的条件是，相隔 1分钟间隔的被分到一组。 数据库表是这样的 SELECT COUNT(log_id) AS u_count FROM logdb.`log` GROUP BY TIMESTAMP-TIMESTAMP%(1*60) 其中timestamp 是数据库时间字段。 括号中的1 是间隔时间，1分钟。 如果是1小时，就写60，如果是30s 就写0.5 结果 那被分组的id 如何解决呢？分完组我们还需要 哪些记录被分到哪些组了。 我们可以这么做。 SELECT COUNT(log_id) AS u_count ,GROUP_CONCAT
MySQL查询GROUP BY日/月/年
p15097962069的博客
01-03 7873
有可能我做一个简单的查询来计算我在一个确定的时间段内有多少记录，如年，月或日，有一个TIMESTAMP字段，如： SELECT COUNT(id) FROM stats WHERE record
MYSQL之查询按日期分组统计
嘉禾嘉宁papa
03-22 9584
mysql根据日期按日、周、月、季、年分组查询
oracle 日期分组查询
weixin_40646228的博客
02-05 1870
自定义分组规则前景实现例子在此基础上查询时间所属时间段 前景 在做数据报表时，需要对某一时间段分组，以1小时为时间间隔统计各项数据，如9点-10点，10点-11点…，但是现在有种情况，时间有可能不是整点开始，会有半点的情况，9点30，需要按照9点30-10点30,10点30-11点30分组统计 实现 以时间范围起始时间作为参照时间，计算与其差值(分)/所需时间间隔(1小时) 得出值作为分组依据。 例子 select xx, floor( floor( (re
mysql按日期分组（group by）查询统计的时候，没有数据补0的解决办法。
热门推荐
星雨晴空
03-14 5万+
写博客真实个费时费力的差事，好佩服那些博客阅读几十上百万的人。今天研究了半天按照日期分组统计，没有数据就为空了，我要让他显示0呀。想了办法，都没有找到一个好的，解决方案，然后用了一个很low的方法实现了，还是把它记录下来。方法是用于mysql，与开发语言无关。 1、案例中的数据结构和数据如下 2、在没有解决的时候，是这样的SELECT date(downtime) AS dday, count(*
group by 日期_高中地理——每日讲1题（晨昏线、地方时、日期、日出日落）
weixin_39729837的博客
11-07 357
知识点晨昏线、地方时、日出日落由于地球自转，并且地球既不发光也不透明，因此产生了昼夜交替。白昼与黑夜的分界线，叫做“晨昏线”。地球的自转方向为自西向东，顺着自转的方向，由黑夜进入白天的分界线，叫做晨线；由白天进入黑夜的分界线，叫做昏线。晨昏线的具体应用如下：①判断时间。晨线与赤道的交点为早上6:00，昏线与赤道的交点为晚上18:00；夜半球的中央经线为0:00，昼半球的中央经线为正午12:00；②...
MySQL 按日期 group by 实现
H_Rhui的博客
10-08 2万+
背景： 最近在统计数据，今天有个要求是要统计每天的订单数量。表中 created_date（创建日期）字段是 datetime 类型。 心路历程： 1.group by 字符串 把 created_date 变成 字符串，然后截取前十位，使用 group by， 统计 count(0) 尝试了以下 sql 之后，没有成功。 SELECT substring(create...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值