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

:

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

在上面的结果中我想列出所有order dates and count的数量customers distinct per 财政年度.
The fiscal year starts 两个月日历年之后，因此从March to February.
（例如来自2020-03 til 2021-02).

例如Customer_01第一次出现在2020-03-18财政年度内2020-03 til 2021-02.
因此，这order_date被分配1 to it.
如果客户在下一个会计年度内再次出现order_date将被分配0 to it.

到目前为止我已经使用过这个查询：

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;

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

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

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;