Join с подзапросом

Рейтинг: 0Ответов: 1Опубликовано: 22.02.2023

Помогите составить запрос, который выводит список транзакций, превысившие определенный месячный лимит. Существует банковская система, где клиент может совершать транзакции и устанавливать лимит на свой аккаунт. Также клиент может сам устанавливать себе лимит, пример: 01.01.2023 клиент установил лимит в 1000руб. 02.01.2023 совершил транзакцию на 900руб, остаток лимита составляет 100руб, 03.01.2023 совершает транзакцию на 500руб, остаток лимита равен -400, соответственно лимит превышен(limit_exceed = true). 10.01.2023 клиент устанавливает лимит в 2000руб, остаток лимита = 1600, лимит не превышен. Попробовал решить задачу с помощью join'a с подзапросом и агрегирующей функцией, но не могу грамотно составить. Прошу помощи. Пример своей запроса и ERD прикладываю.

select
    *
from
    solva.transactions t
left join solva.limits l (
    select
        max(l.setting_date)
    from
        solva.limits l2
    where
        l2.setting_date <= t.date_time
    group by
        t.id,
        l.id,
        t.date_time,
        t.account_from,
        t.date_time) on
    l.user_account = t.account_from
where
    t.limit_exceeded = true
    and t.account_from = l.user_account
    and t.date_time between :startdate and :enddate;

ERD

sql postgresql join
Источник: Stack Overflow на русском

Ответы

▲ 0Принят

Можно сделать привязку действующего лимита к дате транзакции так:

select *
from solva.transactions tsolva.transactions t 
left join(
  select *
    ,lead(setting_date,1,current_date)
        over(partition by user_account order by setting_date) 
    - interval 'days 1' to_date
   from limits 
 ) l on l.user_account = t.account_from
    and t.date_time between l.setting_date and l.to_date
where
    t.limit_exceeded = true
--теперь не нужно    and t.account_from = l.user_account
    and t.date_time between :startdate and :enddate;

Здесь в качестве срока действия последнего лимита указано current_date. Можно поставить что-то иное, подходящее по смыслу задачи. Сравнение времени с датами надо дополнительно проверить.
Что-же касается превышения лимита, по пояснению к вопросу не понятно, с какой даты считаются транзакции. Возможно, с даты первой установки лимита. Но гадать не буду. Можете посчитать накопленные транзакции оконной функцией, как советовал @Akina.