如何找出未完成财年付款的客户及对应财年？Oracle SQL

我有customers和payments两张表，payments表记录客户的付款日期。要求每位客户每个财年（当年7月1日至次年6月30日）至少完成一次付款，金额为0的付款尝试也视为已付款。需要找出2019财年（2018年7月1日至2019年6月30日）至今未付款的客户及其对应的财年。

客户分为三种情况：

• 每个财年均完成付款
• 在1个或多个财年付款，但遗漏部分财年
• 从未付款

表结构 #

CREATE TABLE customers (
 name varchar2(32) not null 
);

CREATE TABLE payments (
  cus_name varchar2(32) not null, 
  date_paid date not null,
  amount_paid number not null
);

测试数据 #

INSERT INTO customers (name) VALUES ('Bob');
INSERT INTO customers (name) VALUES ('Sarah');
INSERT INTO customers (name) VALUES ('James');
INSERT INTO customers (name) VALUES ('Andrew');

INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Bob', TO_DATE('2018-02-12', 'yyyy-mm-dd'), 84);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Bob', TO_DATE('2019-05-23', 'yyyy-mm-dd'), 54);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Bob', TO_DATE('2019-05-27', 'yyyy-mm-dd'), 9);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Bob', TO_DATE('2020-06-14', 'yyyy-mm-dd'), 87);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Bob', TO_DATE('2021-02-12', 'yyyy-mm-dd'), 84);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Bob', TO_DATE('2022-04-21', 'yyyy-mm-dd'), 43);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Bob', TO_DATE('2022-08-03', 'yyyy-mm-dd'), 34);

INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Sarah', TO_DATE('2020-08-17', 'yyyy-mm-dd'), 34);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Sarah', TO_DATE('2021-09-11', 'yyyy-mm-dd'), 0);

INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('James', TO_DATE('2019-12-01', 'yyyy-mm-dd'), 65);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('James', TO_DATE('2020-07-01', 'yyyy-mm-dd'), 43);

期望结果 #

我的尝试 #

SELECT
c.name as Customer_Name,
--Year_They_Didnt_Pay
FROM customers c
OUTER JOIN payments p
ON c.name = p.cus_name

解决方案 #

思路 #

1. 生成2019财年至今的所有财年列表；
2. 生成所有客户与所有财年的组合，得到每个客户需要覆盖的财年范围；
3. 提取每个客户实际已付款的财年（同一财年多次付款只保留一条记录）；
4. 通过左连接找出客户-财年组合中无对应付款记录的条目，即为未付款的情况。

实现代码 #

WITH fiscal_years AS (
    -- 生成2019到2023财年的列表，若需动态适配当前财年可替换为下方注释的动态生成逻辑
    SELECT 2019 AS fiscal_year FROM DUAL
    UNION ALL SELECT 2020 FROM DUAL
    UNION ALL SELECT 2021 FROM DUAL
    UNION ALL SELECT 2022 FROM DUAL
    UNION ALL SELECT 2023 FROM DUAL
    /* 动态生成财年的逻辑：自动计算从2019到当前日期对应的财年
    SELECT 
        2019 + LEVEL -1 AS fiscal_year
    FROM DUAL
    CONNECT BY 2019 + LEVEL -1 <= 
        CASE WHEN EXTRACT(MONTH FROM SYSDATE) >=7 THEN EXTRACT(YEAR FROM SYSDATE)+1 ELSE EXTRACT(YEAR FROM SYSDATE) END
    */
),
customer_fiscal_years AS (
    -- 所有客户与所有财年的笛卡尔积
    SELECT c.name AS customer_name, fy.fiscal_year
    FROM customers c
    CROSS JOIN fiscal_years fy
),
customer_paid_years AS (
    -- 每个客户已付款的财年（去重）
    SELECT DISTINCT
        p.cus_name AS customer_name,
        -- 根据付款日期判断所属财年：7月1日及之后的付款属于下一个财年
        CASE 
            WHEN EXTRACT(MONTH FROM p.date_paid) >=7 THEN EXTRACT(YEAR FROM p.date_paid) +1
            ELSE EXTRACT(YEAR FROM p.date_paid)
        END AS fiscal_year
    FROM payments p
)
-- 筛选未付款的客户-财年组合
SELECT 
    cfy.customer_name AS Customer_Name,
    cfy.fiscal_year AS Year_They_Didnt_Pay
FROM customer_fiscal_years cfy
LEFT JOIN customer_paid_years cpy 
    ON cfy.customer_name = cpy.customer_name 
    AND cfy.fiscal_year = cpy.fiscal_year
WHERE cpy.customer_name IS NULL
ORDER BY cfy.customer_name, cfy.fiscal_year;

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