Как рассчитать оплату с даты регистрации пользователя плюс 30 дней В ORACLE SQL
Как рассчитать выплаты к определенному периоду.
В частности, мне интересно, если пользователь зарегистрировался на дату x, сколько у него будет платежей через 30 дней с этой даты.
Есть две таблицы:
create table user (user_id, contact, registration_date) as
select 1, 111 111, 1/18/2022 3:57:32 PM from dual union all
select 2, 222 222, 8/12/2021 12:00:12 AM from dual union all
select 3, 333 333, 12/11/2015 5:08:35 PM from dual union all
select 4, 444 444, 5/25/2020 10:59:10 AM from dual;
create table transaction (day, user_id, payment) as
select 1/20/2022, 1, 5 from dual union all
select 1/30/2022, 1, 8 from dual union all
select 2/20/2022, 1, 6 from dual union all
select 8/12/2021 , 2, 10 from dual union all
select 8/15/2021 , 2, 5 from dual union all
select 9/25/2021 , 2, 12 from dual union all
select 12/11/2015 , 3, 18 from dual union all
select 12/20/2015 , 3, 10 from dual union all
select 1/1/2016 , 3, 10 from dual union all
select 5/26/2020 , 4, 7 from dual union all
select 6/1/2020 , 4, 2 from dual;
Интересно что-то вроде этого, но этот запрос не работает,
select t.user_id,u.registration_date,sum(t.payment) from transaction t
left join user u on t.user_id = u.user_id
where t.day >= u.registration_date and t.day <= u.registration_date + interval '30' days
group by t.user_id
Моя ожидаемая таблица:
| Логин пользователя | Дата регистрации | оплата |
|---|---|---|
| 1 | 18.01.2022 15:57:32 | 13 |
| 2 | 12.08.2021 00:00:12 | 15 |
| 3 | 11.12.2015 17:08:35 | 38 |
| 4 | 25.05.2020 10:59:10 | 9 |
И, пожалуйста, никогда не говорите «но этот запрос не работает», не сообщив нам, почему он не работает. Выдает ошибку? Если да, то какие? Возвращает слишком много строк или слишком мало, или неправильные суммы, или...?
Ответы 3
Выглядит как
SQL> SELECT u.user_id, u.registration_date, SUM (t.payment) payment
2 FROM tuser u JOIN transaction t ON t.user_id = t.user_id
3 WHERE t.day BETWEEN u.registration_date
4 AND u.registration_date + INTERVAL '30' DAY
5 GROUP BY u.user_id, u.registration_date
6 ORDER BY u.user_id;
USER_ID REGISTRATI PAYMENT
---------- ---------- ----------
1 01/18/2022 13
2 08/12/2021 15
3 12/11/2015 38
4 05/25/2020 9
SQL>
Прежде всего, вы, вероятно, захотите выполнить внешнее соединение транзакций с пользователями, а не наоборот. Таким образом, вы также показываете пользователей, не совершивших транзакций в течение 30 дней после регистрации.
Затем Oracle требует, чтобы вы либо поместили u.registration_date в GROUP BY, либо псевдоагрегировали его (например, MIN(u.registration_date)). Это не соответствует стандарту SQL, но кажется, что Oracle еще не удалось должным образом определить функциональные зависимости, поэтому они просто не предлагают эту функцию.
Наконец, дата регистрации, несмотря на свое название, — это не дата, а дата и время. Чтобы сравнить его с датой транзакции, усеките его. Затем решите, хотите ли вы включить 30-е число после даты регистрации или нет (т. е. добавить 30 или 31 день и все равно использовать <).
select u.user_id, u.registration_date, sum(t.payment)
from users u
left join transactions t
on t.user_id = u.user_id
and t.day >= trunc(u.registration_date)
and t.day < trunc(u.registration_date) + interval '31' day
group by u.user_id, u.registration_date
order by u.user_id;
Демо: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=a6d8d13c994eff6a1c0f8afa6fcb176f
Ваш запрос не работает, потому что вы забыли добавить u.registration_date в предложение GROUP BY. Когда вы делаете GROUP BY, все поля, упомянутые в SELECT, должны быть либо добавлены в GROUP BY, либо использоваться в агрегатной функции, такой как SUM, MIN, MAX,...
Почему вы внешний присоединяете пользователя к транзакции? Есть ли транзакции без пользователя, и вы хотите, чтобы они были в результатах? Это маловероятно. И зачем вообще присоединяться к таблице пользователей? Что вы хотите получить от этой таблицы, чего вы не найдете в транзакциях? Вы хотели, может быть, наоборот; получить пользователей, у которых нет транзакций?