Блог

Мне нужно найти количество пользователей, которым были выставлены счета на сумму, превышающую 0 в предыдущем месяце, и не были выставлены счета в текущем месяце. Этот расчет должен быть выполнен за 12 месяцев в одном запросе. Вывод должен быть таким, как показано ниже.

Количество месяцев

01/07/2019 50

01/08/2019 34

01/09/2019 23

01/10/2019 98

01/11/2019 10

01/12/2019 5

01/01/2020 32

01/02/2020 65

01/03/2020 23

01/04/2020 12

01/05/2020 64

01/06/2020 54

01/07/2020 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

Вероятно, много разных подходов, но я бы сделал это следующим образом:

1. Суммируйте данные по пользователям и месяцам за последние 13 месяцев (вам нужно 12 месяцев плюс предыдущий месяц к этому первому месяцу
2. Сравните «этот» месяц (в котором есть данные) со «следующим» месяцем и выберите записи, в которых нет данных «следующего» месяца
3. Суммируйте этот набор данных по месяцам и отдельным идентификаторам пользователей

Например, предполагая, что таблица создана следующим образом:

 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
 ;