我需要查找上个月为大于0的金额开具发票而在本月未开具发票的用户数

Question

我需要查找上个月为大于0的金额开具发票而本月未开具发票的用户数量。此计算将在单个查询中进行12个月。输出应如下所示。

月计数

2019年7月1日50

2019年8月1日34

2019年9月1日23

2019年10月1日98

2019年11月1日10

01/12/2019 5

2020年1月1日32

2020年2月1日65

2020年3月1日23

2020年4月1日12

2020年5月1日64

2020年6月1日54

2020年7月1日78

我只能在一个月内得到价值。我想在一个查询中获得所有月份的信息。

这是我当前的查询：

SELECT COUNT(DISTINCT TWO_MONTHS_AGO.USER_ID), TWO_MONTHS_AGO.MONTH AS INVOICE_MONTH
FROM (
SELECT USER_ID, LAST_DAY(invoice_ct_dt)) AS MONTH
FROM table a AS ID
WHERE invoice_amt > 0
AND LAST_DAY(invoice_ct_dt)) = ADD_MONTHS(LAST_DAY(CURRENT_DATE - 1), - 2)
GROUP BY user_id
) AS TWO_MONTHS_AGO
LEFT JOIN (
SELECT user_id,LAST_DAY(invoice_ct_dt)) AS MONTH
FROM table a AS ID
AND LAST_DAY(invoice_ct_dt)) = ADD_MONTHS(LAST_DAY(CURRENT_DATE - 1), - 1)
GROUP BY USER_ID
) AS ONE_MONTH_AGO ON TWO_MONTHS_AGO.USER_ID = ONE_MONTH_AGO.USER_ID
WHERE ONE_MONTH_AGO.USER_ID IS NULL
GROUP BY INVOICE_MONTH;

提前谢谢。Lona

Answer 1

可能有很多不同的方法，但我的方法如下：

1. 按用户和月份总结过去13个月的数据(您需要12个月加上第一个月的前一个月
2. 比较"；这个"；月份(具有数据(到"；下一个"；月，并选择没有"月"的记录；下一个"；月份数据
3. 按月份和不同的用户ID总结此数据集

例如，假设一个表创建如下：

create table INVOICE_DATA (
USERID varchar(4),
INVOICE_DT date,
INVOICE_AMT NUMBER(10,2)
);

以下查询应该会给你你想要的-你可能需要根据你的计算中是否包括本月，或者只包括上个月底来调整它，等等：

--Summarise data by user and month
WITH MONTH_SUMMARY AS 
(
SELECT USERID
,TO_CHAR(INVOICE_DT,'YYYY-MM') "INVOICE_MONTH"
,TO_CHAR(ADD_MONTHS(INVOICE_DT,1),'YYYY-MM') "NEXT_MONTH"
,SUM(INVOICE_AMT) "MONTHLY_TOTAL"
FROM INVOICE_DATA
WHERE INVOICE_DT >= TRUNC(ADD_MONTHS(current_date(),-13),'MONTH') -- Last 13 months of data
GROUP BY 1,2,3
),
--Get data for users with invoices in this month but not the next month
USER_DATA AS 
(
SELECT USERID, INVOICE_MONTH, MONTHLY_TOTAL
FROM MONTH_SUMMARY MS_THIS
WHERE NOT EXISTS 
(
SELECT USERID
FROM MONTH_SUMMARY MS_NEXT
WHERE 
MS_THIS.USERID = MS_NEXT.USERID AND
MS_THIS.NEXT_MONTH = MS_NEXT.INVOICE_MONTH
)
AND MS_THIS.INVOICE_MONTH < TO_CHAR(current_date(),'YYYY-MM') -- Don't include this month as obviously no next month to compare to 
)
SELECT INVOICE_MONTH, COUNT(DISTINCT USERID) "USER_COUNT"
FROM USER_DATA
GROUP BY INVOICE_MONTH
ORDER BY INVOICE_MONTH
;