mysql - 每个会计年度计算不同并在查询结果中显示所有日期

Question

DB-Fiddle :

CREATE TABLE customers (
    id int auto_increment primary key,
    order_date DATE,
    customerID VARCHAR(255)
);

INSERT INTO customers
(order_date, customerID
)
VALUES 
("2020-01-15", "Customer_01"),
("2020-02-15", "Customer_01"),
("2020-03-18", "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");

预期结果:

order_date   |      quantity
             |    (fiscal year)
-----------------------------------------------------------------
2020-01-15   |           1         --> Customer_01 appears the first time between 2019-03 and 2020-02)
2020-01-25   |           1         --> Customer_02 appears the first time between 2019-03 and 2020-02)
2020-02-15   |           0
2020-02-26   |           0
2020-03-18   |           1         --> Customer_01 appears the first time between 2020-03 and 2021-02
2020-04-22   |           0
2020-11-23   |           1         --> Customer_02 appears the first time between 2020-03 and 2021-02
2021-01-17   |           0
2021-01-19   |           0
2021-02-20   |           0

---

在上面的结果中，我想列出所有订单日期并计算每个客户distinct的数量财政年度。
财政年度从日历年之后的两个月开始，因此从三月到二月。
(例如，从 2020-03 到 2021-02)。

例如 Customer_01 在 2020-03 到 2021- 财政年度内首次出现在 2020-03-18 02。
因此，此 order_date 被分配给 1。
如果客户在财政年度内再次出现，下一个 order_date 将被分配 0 给它。

---

到目前为止，我已经使用了这个查询:

SELECT
order_date,
SUM(rn = 1) AS quantity
FROM 
  (SELECT 
  order_date, 
  row_number() over(PARTITION BY YEAR(order_date), customerID ORDER BY order_date) rn
  FROM customers
  ) t
GROUP BY 1;

它非常适合日历年。
但是，我不知道如何将财政年度的两个月转移应用到它。
你有什么想法吗？

Answer 1

我已将您的查询更新如下:

SELECT
order_date,
SUM(rn = 1) AS quantity
FROM 

  (SELECT 
  order_date, 
  row_number() over(PARTITION BY YEAR(order_date - INTERVAL 2 MONTH), customerID ORDER BY order_date) rn
  FROM customers
  ) t
  
GROUP BY 1;