PostgreSQL中基于指定月份的动态日期区间查询实现方案

要实现以指定月份为结束的过去12个月数据查询，核心是根据输入的目标月份动态计算起止日期，替换原查询里的硬编码日期即可。以下是具体实现方式：

核心逻辑：动态计算日期区间 #

给定目标月份（格式如 '2022-06'），计算规则为：

• 起始日期：目标月份往前推11个月的第一天（比如指定2022-06，起始日为2021-07-01）
• 结束日期：目标月份的最后一天（比如指定2022-06，结束日为2022-06-30）

用PostgreSQL日期函数可以直接实现这个计算：

-- 以目标月份'2022-06'为例，先计算起止日期
WITH date_range AS (
    SELECT 
        date_trunc('month', '2022-06'::date) - INTERVAL '11 months' AS start_date,
        (date_trunc('month', '2022-06'::date) + INTERVAL '1 month') - INTERVAL '1 day' AS end_date
)

修改后的完整查询 #

把原查询的硬编码日期替换为动态计算的区间，直接替换即可复用原有逻辑：

WITH date_range AS (
    SELECT 
        date_trunc('month', '2022-06'::date) - INTERVAL '11 months' AS start_date,
        (date_trunc('month', '2022-06'::date) + INTERVAL '1 month') - INTERVAL '1 day' AS end_date
),
base as
(
SELECT created_at as period ,order_number, TRIM(email) as email ,is_first_order
FROM  orders 
WHERE created_at::DATE BETWEEN (SELECT start_date FROM date_range) AND (SELECT end_date FROM date_range)
),
base_agg as
(
select TO_CHAR(period,'YYYY-MM') as period
    ,COUNT(DISTINCT email)FILTER(WHERE is_first_order IS TRUE) as new_users
    ,COUNT(DISTINCT order_number)FILTER(WHERE is_first_order IS FALSE) as returning_orders
FROM base
       GROUP BY 1   
),
base_cumulative as
(
SELECT ROW_NUMBER() OVER(ORDER BY PERIOD DESC ) as rno
      ,period
      ,new_users
      ,returning_orders
      ,sum("new_users")over (order by "period" asc rows between unbounded preceding and current row) as "cumulative_total"
from base_agg
 )
 
SELECT 
    (SELECT period FROM base_cumulative WHERE rno=1) period
   ,(SELECT cumulative_total FROM base_cumulative WHERE rno=1) as cumulated_customers
   ,SUM(returning_orders) as returning_orders
   ,SUM(returning_orders)/NULLIF((SELECT cumulative_total FROM base_cumulative WHERE rno=1),0)  as  rate
FROM base_cumulative 

更灵活的参数化调用方式 #

如果需要在应用或工具中动态传入月份，可以用以下两种方式：

1. 使用psql变量（适合命令行调用） #

-- 先设置目标月份变量
\set target_month '2022-06'

WITH date_range AS (
    SELECT 
        date_trunc('month', :'target_month'::date) - INTERVAL '11 months' AS start_date,
        (date_trunc('month', :'target_month'::date) + INTERVAL '1 month') - INTERVAL '1 day' AS end_date
),
-- 后续查询逻辑和上面一致
base as (...)

2. 封装为自定义函数（适合重复调用） #

把查询封装成函数，每次传入目标月份即可获取结果：

CREATE OR REPLACE FUNCTION get_order_stats(target_month text)
RETURNS TABLE(period text, cumulated_customers bigint, returning_orders bigint, rate numeric) AS $$
BEGIN
    RETURN QUERY
    WITH date_range AS (
        SELECT 
            date_trunc('month', target_month::date) - INTERVAL '11 months' AS start_date,
            (date_trunc('month', target_month::date) + INTERVAL '1 month') - INTERVAL '1 day' AS end_date
    ),
    base as
    (
    SELECT created_at as period ,order_number, TRIM(email) as email ,is_first_order
    FROM  orders 
    WHERE created_at::DATE BETWEEN (SELECT start_date FROM date_range) AND (SELECT end_date FROM date_range)
    ),
    base_agg as
    (
    select TO_CHAR(period,'YYYY-MM') as period
        ,COUNT(DISTINCT email)FILTER(WHERE is_first_order IS TRUE) as new_users
        ,COUNT(DISTINCT order_number)FILTER(WHERE is_first_order IS FALSE) as returning_orders
    FROM base
           GROUP BY 1   
    ),
    base_cumulative as
    (
    SELECT ROW_NUMBER() OVER(ORDER BY PERIOD DESC ) as rno
          ,period
          ,new_users
          ,returning_orders
          ,sum("new_users")over (order by "period" asc rows between unbounded preceding and current row) as "cumulative_total"
    from base_agg
     )
     
    SELECT 
        (SELECT period FROM base_cumulative WHERE rno=1) period
       ,(SELECT cumulative_total FROM base_cumulative WHERE rno=1) as cumulated_customers
       ,SUM(returning_orders) as returning_orders
       ,SUM(returning_orders)/NULLIF((SELECT cumulative_total FROM base_cumulative WHERE rno=1),0)  as  rate
    FROM base_cumulative;
END;
$$ LANGUAGE plpgsql;

-- 调用示例
SELECT * FROM get_order_stats('2022-06');

关键函数说明 #

• date_trunc('month', date)：将日期截断到对应月份的第一天
• 加减INTERVAL：实现月份偏移和当月最后一天的计算（加1个月后减1天，精准获取月末日期）

内容的提问来源于stack exchange，提问作者Sandeep