计算每个会计年度的不重复数，并在查询结果中显示所有日期 [英] Count distinct per fiscal year and display all dates in query result

问题描述

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

---

在上述结果中，我想列出所有订购日期和计数，每个客户 不同会计年度.
财政年度在日历年之后的两个月开始，因此从三月到二月.
(例如，从 2020-03 直到 2021-02 ).

---

In the above result I want to list all order dates and count the number of customers distinct per fiscal year.
The fiscal year starts two months after the calender year and therefore goes from March to February.
(e.g. from 2020-03 til 2021-02).

例如， Customer_01 在会计年度 2020-03 直到 2021-的第一次出现在 2020-03-18 上02 .
因此，此 order_date 被分配了 1 .
如果客户在会计年度内再次出现，则下一个 order_date 将为其分配 0 .

For example Customer_01 appears the first time on 2020-03-18 within the fiscal year 2020-03 til 2021-02.
Therefore, this order_date gets assigned 1 to it.
If the customer appears again within the fiscal year the next order_date will get assigned 0 to it.

到目前为止，我已经使用了以下查询:

So far I have used this query:

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;

它非常适合日历年.
但是，我不知道如何应用从会计年度开始的两个月的轮班时间.
你有什么主意吗?

It works perfectly for the calender year.
However, I have no clue how I can apply the two months shift from the fiscal year to it.
Do you have any idea?

推荐答案

我更新了您的查询，如下所示:

I have updated your query as below:

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;

这篇关于计算每个会计年度的不重复数，并在查询结果中显示所有日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！