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

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.效果图