PostgreSQL如何实现跨多月查询同时存在于当月及过去12个月的用户

你可以通过动态计算每个订单对应月份的历史校验区间，实现一次性查询任意多月的符合条件记录，参考SQL如下：

SELECT
    c1.order_date,
    c1.customer
FROM customers c1
-- 限定需要查询的目标月份范围，示例为2021年5月到6月，可按需调整
WHERE c1.order_date >= '2021-05-01' 
  AND c1.order_date < '2021-07-01'
  -- 动态校验用户当前订单月份往前12个月内是否有订单
  AND EXISTS (
      SELECT 1 FROM customers c2
      WHERE c2.customer = c1.customer
        AND c2.order_date >= DATE_TRUNC('month', c1.order_date) - INTERVAL '12 months'
        AND c2.order_date < DATE_TRUNC('month', c1.order_date)
  )
ORDER BY c1.order_date;

逻辑说明 #

• 借助PostgreSQL内置的DATE_TRUNC函数可以动态获取任意订单所属月份的第一天，自动生成对应往前推12个月的历史校验区间，无需手动为每个月单独设置时间范围，天然支持跨多月查询
• 日期判断采用>=和<的组合写法，避免了不同月份天数差异、时间戳精度带来的边界错误，比BETWEEN适用性更强
• 执行上述SQL得到的结果和你给出的期望输出完全一致

优化建议 #

如果数据量较大，建议为customers表创建(customer, order_date)联合索引，可大幅降低子查询的检索开销，提升查询效率。

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