PostgreSQL中替代SUM(rn=1)实现按3月起财年统计客户首现数并展示所有订单日期

测试基础数据 #

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    order_date DATE,
    customerID VARCHAR(255)
);

INSERT INTO customers
(order_date, customerID
)
VALUES 
('2020-01-15', 'Customer_01'),
('2020-02-03', 'Customer_01'),
('2020-02-15', 'Customer_01'),
('2020-03-18', 'Customer_01'),
('2020-03-20', 'Customer_01'),
('2020-04-22', 'Customer_01'),
('2021-01-19', 'Customer_01'),

('2020-01-25', 'Customer_02'),
('2020-02-26', 'Customer_02'),
('2020-11-23', 'Customer_02'),
('2021-01-17', 'Customer_02'),
('2021-02-20', 'Customer_02');

业务统计规则 #

• 财年定义：每年3月到次年2月为一个完整财年（例如2020年3月-2021年2月为单个财年周期）
• 输出要求：列出所有订单日期，按日期聚合统计对应值
  • 若客户在所属财年内的首笔订单出现在该日期，统计值记为1
  • 同一财年内客户后续的重复订单，对应统计值记为0
• 预期输出样例：

order_date   | quantity
-------------|----------
2020-01-15   | 1
2020-01-25   | 1
2020-02-03   | 0
2020-02-15   | 0
2020-02-26   | 0
2020-03-18   | 1
2020-03-20   | 0
2020-04-22   | 0
2020-11-23   | 1
2021-01-17   | 0
2021-01-19   | 0
2021-02-20   | 0

原查询的报错问题 #

从MariaDB迁移的查询逻辑在PostgreSQL中执行时，在SUM(rn = 1)环节触发报错：function sum(boolean) does not exist

报错原因：MariaDB 会自动将布尔判断结果隐式转换为1/0整数参与聚合计算，但PostgreSQL 不支持这种隐式类型转换，无法直接接收布尔值作为SUM函数的入参。

修正后的等价实现 #

PostgreSQL 中实现同等逻辑有两种常用写法，效果完全一致：

1. 显式将布尔判断结果转为整数类型再参与求和（简写形式）

SELECT
  order_date,
  SUM((rn = 1)::int) AS quantity
FROM (
  SELECT 
    order_date, 
    row_number() over(
      PARTITION BY DATE_PART('year', (order_date - INTERVAL '2 month')::date), 
      customerID 
      ORDER BY order_date
    ) rn
  FROM customers
) t
GROUP BY order_date
ORDER BY order_date;

2. 使用CASE语句显式转换判断结果（可读性更强，兼容所有SQL方言）

SELECT
  order_date,
  SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) AS quantity
FROM (
  SELECT 
    order_date, 
    row_number() over(
      PARTITION BY DATE_PART('year', (order_date - INTERVAL '2 month')::date), 
      customerID 
      ORDER BY order_date
    ) rn
  FROM customers
) t
GROUP BY order_date
ORDER BY order_date;

两种写法执行结果和预期完全匹配，与原有MariaDB的实现逻辑一致。

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