Блог

Итак, есть номер счета, и у нас есть ежедневная информация об их платежах. Предположим, у нас есть информация за 1 год, предшествующий сегодняшнему дню, который приходится на 08 / март / 2019, я хотел бы подсчитать, сколько раз он / она переплачивал за последнюю 1 неделю. Я использовал функцию mysql window, но по какой-то причине она, похоже, не работает

@GMB Пример данных будет выглядеть следующим образом: предположим, что для этой учетной записи у нас есть информация за март 2018 года. Мне просто нужно количество раз, когда paid_status = переплачено с последней даты, которая у меня есть в моем файле, которая относится к сегодняшнему дню — 08 / март / 2019 и предыдущие 7 дней, 14 дней, 1 месяц или любой продолжительности по моему выбору. Ваш запрос будет жестко запрограммирован только на 7 дней.

 ACCOUNT_ID paid_status amt dte
-----------------------
1234      overpaid   100 01/March/2018
.
.
.
1234      overpaid   120 01/March/2019
1234      not paid   0   02/March/2019
1234      overpaid   110 03/March/2019
1234      overpaid   120 04/March/2019
1234      overpaid   130 05/March/2019
1234      overpaid   120 06/March/2019
1234      overpaid   120 07/March/2019
1234      overpaid   121 08/March/2019
  

Запрос:

 ,COUNT(CASE WHEN paid_status = 'OVERPAID' THEN 1 END)
 over (PARTITION BY ACCOUNT_ID
       ORDER BY DTE ROWS BETWEEN 7 PRECEDING AND UNBOUNDED FOLLOWING
 ) AS num_times_overpaid_week1
  

Вывод должен быть таким (не включая сегодняшнюю информацию):

 account_id num_times_overpaid_week1
1234               6
  

Пока я получаю несколько строк для одного и того же account_id, и это не совсем корректно вычисляет поле

Из ваших выборочных данных кажется, что вы ищете простой агрегированный запрос (нет необходимости в оконных функциях):

 SELECT account_id, SUM(paid_status = 'OVERPAID') AS num_times_overpaid_week1
FROM mytable
WHERE dte >= CURRENT_DATE - INTERVAL 7 DAY
GROUP BY account_id
  

Выражение SUM(paid_status = 'OVERPAID') использует приятную функцию MySQL, где условия возвращаются, 1 когда выполняются, а 0 когда нет.

ПРИМЕЧАНИЕ: если по какой-либо причине вы хотите использовать оконные функции (возможно, для выполнения других вычислений), тогда вам нужно будет использовать ROW_NUMBER() для ранжирования записей по дате и отфильтровывать только самую последнюю запись для каждой учетной записи во внешнем запросе. Я думаю, что определение окна может быть в значительной степени упрощено:

 SELECT *
FROM (
    SELECT 
        account_id, 
        SUM(paid_status = 'OVERPAID') OVER(PARTITION BY account_id) AS num_times_overpaid_week1,
        -- possibly other columns
        ROW_NUMBER() OVER(PARTITION BY account_id ORDER BY dte DESC) rn
    FROM mytable
    WHERE dte >= CURRENT_DATE - INTERVAL 7 DAY
) x WHERE rn = 1