MYSQL Не удается подсчитать появление определенного значения в диапазоне дат
Итак, есть номер счета и у нас есть ежедневная информация об их платежах. Предположим, у нас есть информация за 1 год до сегодняшнего дня, то есть 8 марта 2019 года. Я хотел бы подсчитать, сколько раз он/она переплатил за последнюю неделю. Я использовал оконную функцию mysql, но по какой-то причине она не работает.
@GMB Пример данных будет выглядеть так: предположим, для этой учетной записи у нас есть информация за последний март 2018 года. Мне просто нужно, сколько раз заплаченный_статус = переплаченный с последней даты, которая есть в моем файле, которая на сегодняшний день - 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, и это не совсем правильно вычисляет поле
Обратите внимание, что ROWS BETWEEN 7 PRECEDING AND UNBOUNDED FOLLOWING определяет окно, которое содержит последние 7 записей, текущая запись и все последующие записи. Может быть, вам просто нужны последние 7 записей и текущая? Тогда просто: ROWS 7 PRECEDING.
@GMB Я добавил образцы данных
Не могли бы вы показать весь запрос?
Итак, в наборе результатов вам просто нужна одна запись для каждой учетной записи с количеством переплат за последние 7 дней?
Да, хотелось бы по одной записи для каждого аккаунта с количеством переплат за последние 7 дней
СОЗДАТЬ временную ТАБЛИЦУ dlq_hist КАК ВЫБРАТЬ отдельный (LOAN_ACCOUNT_ID), COUNT (СЛУЧАЙ, КОГДА DPD_BUCKET = '0.OVERPAID' THEN 1 END) более (PARTITION BY LOAN_ACCOUNT_ID ORDER BY RPTDTE ДИАПАЗОН МЕЖДУ ИНТЕРВАЛОМ 1 НЕДЕЛЯ ПРЕДЫДУЩАЯ И ТЕКУЩАЯ СТРОКА) КАК num_weeks_overpaid_ROM
Я получаю 7/8/9 строк на учетную запись, что не совсем то, что я хочу
Ответы 1
Из ваших примеров данных кажется, что вы ищете простой агрегированный запрос (нет необходимости в оконных функциях):
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, когда нет.
NB: если по какой-то причине вы хотите использовать оконные функции (возможно, для выполнения других вычислений), вам нужно будет использовать 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
Представьте, что таких статусов 10 и 20 и, следовательно, нужна оконная функция? Нельзя ли это сделать с помощью оконной функции
@Rohan: учитывая ваши образцы данных, условная агрегация, как показано в моем ответе, является самым простым способом. Можете ли вы на самом деле протестировать запрос на ваших данных, чтобы увидеть, дает ли он ожидаемый результат?
в то время как ваш подход работает отлично, причина, по которой я хочу использовать функцию Windows, заключается в том, что я могу вычислить сумму за время жизни, максимум, среднее значение для нескольких столбцов (в этом случае я добавил amt в качестве поля). Использование функций Windows помогает. Просто за эту переплаченную вещь я могу написать два запроса sql, а затем присоединиться, но я хотел сделать это за один раз. Благодарю вас за помощь, например, если бы я хотел узнать время жизни, за которое владелец этой учетной записи переплатил - ,COUNT(CASE WHEN DPD_BUCKET = '0.OVERPAID' THEN 1 END) более (PARTITION BY LOAN_ACCOUNT_ID) AS num_times_overpaid, если я получил это за один раз , включая прошлую неделю(недавность) будет хел
Добавил еще несколько данных и кода выше о том, чего я пытаюсь достичь
Большое спасибо, я использовал функцию row_number в другом запросе до этого, дело в том, что я хочу автоматизировать это для любого временного интервала по моему выбору, будь то 7 дней/14 дней/1 месяц/3 месяца, извиняюсь за то, что не сделал это ясно в первую очередь
@Rohan: вы не можете продолжать изменять свои требования таким образом ... Я помог вам улучшить ваш вопрос и предоставил 2 запроса, которые, по моему мнению, имеют отношение к вашему варианту использования. Если вы считаете, что это правильно помогло вам, пожалуйста, поддержите / примите мой ответ. Если вам нужна дополнительная помощь, рассмотрите возможность задать другой вопрос (с соответствующими образцами данных, ожидаемым результатом и полным запросом вначале... конечно!). Ваше здоровье.
Давайте продолжить обсуждение в чате.
FWIW, я думаю, вы, а не автор темы, выкопали эту дыру.
@Strawberry: не могли бы вы Объясни подробней?
Почему бы просто не попросить большей ясности с самого начала?
@Strawberry: Во-первых, я это сделал ... См. Комментарии в исходном вопросе. Ожидаемый результат был добавлен, затем я ответил. Дело в том, что изменились требования после Я первый ответил. Дал второй ответ. Когда требования снова изменились, я в принципе сдался... В чате попытался объяснить, как улучшить качество следующего вопроса... Видимо с не повезло.