您可以使用MAX(...) KEEP (DENSE_RANK LAST ORDER BY invoice_date)来获取最新发票的值，并使用条件聚合来计算有发票的月份数，然后从总月份数中减去这些值以查找缺少的发票：

SELECT c.Customer_id,
       c.Customer_Account_number,
       c.Customer_Status,
       r.country,
       i.last_invoice_date,
       i.latest_payment_method,
       i.latest_invoice_amount,
       EXTRACT(MONTH FROM SYSDATE) - COALESCE(i.missing_invoices_this_year, 0)
         AS missing_invoices_this_year,
       12 - COALESCE(i.missing_invoices_last_year, 0)
         AS missing_invoices_last_year
FROM   customer c
       INNER JOIN supplier_remit r
       ON (c.supplier_id = r.supplier_id)
       LEFT OUTER JOIN (
         SELECT customer_id,
                MAX(invoice_date) AS last_invoice_date,
                MAX(payment_method) KEEP (DENSE_RANK LAST ORDER BY invoice_date)
                  AS latest_payment_method,
                MAX(invoice_amount) KEEP (DENSE_RANK LAST ORDER BY invoice_date)
                  AS latest_invoice_amount,
                COUNT(
                  DISTINCT
                  CASE
                  WHEN invoice_date <  SYSDATE
                  AND  invoice_date >= TRUNC(SYSDATE, 'YY') 
                  THEN TRUNC(invoice_date, 'MM')
                  END
                ) AS missing_invoices_this_year,
                COUNT(
                  DISTINCT
                  CASE
                  WHEN invoice_date <  TRUNC(SYSDATE, 'YY') 
                  AND  invoice_date >= ADD_MONTHS(TRUNC(SYSDATE, 'YY'), -12)
                  THEN TRUNC(invoice_date, 'MM')
                  END
                ) AS missing_invoices_last_year
         FROM   invoice
         GROUP BY customer_id
       ) i
       ON (c.customer_id = i.customer_id)
WHERE  c.customer_status = 'Active';

其中，对于示例数据：

CREATE TABLE customer (Customer_id, Customer_Account_number, Customer_Status, Supplier_id, Supplier_Remit_id) AS
SELECT 1, 1501, 'Active',   11, 111 FROM DUAL UNION ALL
SELECT 2, 1502, 'Inactive', 12, 112 FROM DUAL UNION ALL
SELECT 3, 1503, 'Active',   13, 113 FROM DUAL UNION ALL
SELECT 4, 1504, 'Active',   14, 114 FROM DUAL UNION ALL
SELECT 5, 1505, 'Inactive', 15, 115 FROM DUAL;

CREATE TABLE invoice (Invoice_Date, Invoice_Amount, Invoice_Number, Payment_Method, Customer_id) AS
SELECT DATE '2023-01-01',  100,  1000001, 'Cash',        1 FROM DUAL UNION ALL
SELECT DATE '2022-12-01',  150,  1000002, 'Credit Card', 1 FROM DUAL UNION ALL
SELECT DATE '2022-11-09',  200,  1000003, 'Credit Card', 1 FROM DUAL UNION ALL
SELECT DATE '2022-12-09',  300,  1000004, 'Cash',        2 FROM DUAL UNION ALL
SELECT DATE '2022-04-15', 1000,  1000005, 'Cash',        2 FROM DUAL UNION ALL
SELECT DATE '2022-04-15', 1000,  1000006, 'Credit Card', 3 FROM DUAL UNION ALL
SELECT DATE '2022-10-31',  250,  1000007, 'Cash',        4 FROM DUAL UNION ALL
SELECT DATE '2022-10-25',  250,  1000008, 'Cash',        4 FROM DUAL UNION ALL
SELECT DATE '2022-09-20',  130,  1000009, 'Credit Card', 5 FROM DUAL UNION ALL
SELECT DATE '2022-05-20',  120, 10000010, 'Credit Card', 5 FROM DUAL;

CREATE TABLE supplier (Supplier_Name, Supplier_id) AS
SELECT 'ABC',  11 FROM DUAL UNION ALL
SELECT 'ACCC', 12 FROM DUAL UNION ALL
SELECT 'ADEF', 13 FROM DUAL UNION ALL
SELECT 'AJKL', 14 FROM DUAL UNION ALL
SELECT 'AFLR', 15 FROM DUAL;

 
CREATE TABLE supplier_remit (City, Country, Supplier_Remit_id, Supplier_id) AS
SELECT 'Boston',  'US', 111, 11 FROM DUAL UNION ALL
SELECT 'Oak',     'US', 112, 12 FROM DUAL UNION ALL
SELECT 'Albany',  'US', 113, 13 FROM DUAL UNION ALL
SELECT 'Madison', 'US', 114, 14 FROM DUAL UNION ALL
SELECT 'Los Ang', 'US', 115, 15 FROM DUAL;

输出：
| 客户ID|客户账号|客户状态|国家|最后发票日期|最新付款方式|最新发票金额|本年度缺少发票|缺少发票去年|
| - ------| - ------| - ------| - ------| - ------| - ------| - ------| - ------| - ------|
| 1个|小行星15|活跃|美国|2023年1月1日00时00分|现金|一百|无|十个|
| 三个|小行星1503|活跃|美国|2022年4月15日00时00分|信用卡|一千|1个|十一|
| 四个|小行星1504|活跃|美国|2022年10月31日上午00时00分|现金|二百五十|1个|十一|
fiddle

为了找到缺少的内容，首先必须定义应该存在的内容，因此需要创建每个月的日历。然后可以使用发票表的外部连接来查找该客户在该月没有任何记录的地方。有很多方法可以编写SQL来实现这一点。下面是一种方法：

WITH months AS(SELECT /*+ MATERIALIZE */ *
                 FROM (SELECT 'Current' year,
                              ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),ROWNUM-1) month_start
                         FROM [any table with at least 12 rows]
                        WHERE ROWNUM <= 12)
                 WHERE month_start < SYSDATE
               UNION ALL
               SELECT 'Previous' year,
                      ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE,-12),'YYYY'),ROWNUM-1)
                 FROM [any table with at least 12 rows]
                WHERE ROWNUM <= 12)
SELECT customer.*,
       inv.invoice_amount most_recent_invoice_amount,
       inv.payment_method most_recent_payment_method,
       (SELECT COUNT(*)
          FROM months,
               invoice
         WHERE months.year = 'Current'
           AND months.month_start = TRUNC(invoice_date(+),'MM')
           AND invoice.customer_id(+) = customer.customer_id
           AND invoice.customer_id IS NULL) missed_current_year_months,
       (SELECT COUNT(*)
          FROM months,
               invoice
         WHERE months.year = 'Previous'
           AND months.month_start = TRUNC(invoice_date(+),'MM')
           AND invoice.customer_id(+) = customer.customer_id
           AND invoice.customer_id IS NULL) missed_previous_year_months 
  FROM customer
       OUTER APPLY (SELECT invoice_amount,
                           payment_method
                      FROM (SELECT invoice_amount,
                                   payment_method,
                                   ROW_NUMBER() OVER (ORDER BY invoice_date DESC) seq
                              FROM invoice
                             WHERE invoice.customer_id = customer.customer_id)
                     WHERE seq = 1) inv