Блог

Таким образом, это своего рода сложный SQL-запрос, и я хочу сделать это оптимальным способом.

Я хочу получить список клиентов в базе данных, которые снимают все деньги, которые у них есть в банке, и прекращают пользоваться банком, а также время, когда прекращается использование банка, также важно для меня. Что я имею в виду, говоря «прекратить пользоваться банком», так это то, что они не вносили деньги после того, как сняли их. Таким образом, депозит по-прежнему равен 0.

Вот что я сделал до сих пор;

Таблица клиентов является;

    customer_id deposit  ts 
    101        44        2021-09-30 10:12:19 00
    101        0         2021-09-30 09:12:19 00
    101        65        2021-09-29 09:12:19 00  
    100        0         2021-10-31 14:12:19 00  
    100        0         2021-10-30 13:12:19 00  
    100        0         2021-09-30 10:12:19 00  
    100        21        2021-09-29 10:12:19 00
    104        54        2021-09-27 10:12:19 00
 

итак, здесь мы видим, что customer_id:101 в какой-то момент снимает все свои деньги(65 — > 0), но затем возвращает деньги обратно.(0 — > > 44), поэтому он все еще использует банк.
Но до сих пор customer_id:100, похоже, перестал использовать банк. в 2021-09-30 10:12:19 00 он выводит деньги, и когда мы смотрим на последнюю строку, принадлежащую клиенту:100, она по-прежнему равна 0.

Поэтому в результате я хочу получить только идентификатор пользователя:100;

    customer_id deposit      ts                     pre_deposit
      100        0         2021-09-30 10:12:19 00  21
 

Итак, то, что я сделал до сих пор, можно увидеть здесь;
http://sqlfiddle.com/#!15/4aef7/1/0

 select distinct on (customer_id) *
from 
(
 select customer_id, deposit, ts, 
        lead(deposit) over w as pre_deposit, 
        lead(ts) over w as pre_ts 
 from t
 window w as (partition by customer_id order by ts desc)
) t 
where pre_deposit != 0 AND deposit = 0 
order by customer_id, ts desc;
 

но это также возвращает идентификатор пользователя:101, потому что в какой-то момент депозит стал 0.
но единственная строка, которую я хочу получить, — это customer_id:100.

Так как же это можно сделать?

Ваш запрос близок, но ваши where критерии все еще позволяют на более позднюю дату указывать ненулевую сумму депозита, поскольку он сравнивает данные только из 2 начальных строк. Чтобы понять, что происходит, давайте просто взглянем на часть вашего запроса для клиента 101.

  select row_number() over()        -- added for discussion
      , customer_id, deposit, ts  
      , lead(deposit) over w as pre_deposit 
      , lead(ts) over w as pre_ts 
   from t
  where customer_id = 101
 window w as (partition by customer_id order by ts desc);
 

Этот запрос выдает следующее (и это временное значение, сгенерированное вашим полным запросом)::

  ---- ------------- --------- ------------------------------- ------------- ------------------------------- 
| rn | customer_id | deposit |              ts               | pre_deposit |            pre_ts             |
 ---- ------------- --------- ------------------------------- ------------- ------------------------------- 
|  1 |         101 |      44 | 2021-09-30 05:12:19.000 -0500 |           0 | 2021-09-30 04:12:19.000 -0500 |
|  2 |         101 |       0 | 2021-09-30 04:12:19.000 -0500 |          65 | 2021-09-29 04:12:19.000 -0500 |
|  3 |         101 |      65 | 2021-09-29 04:12:19.000 -0500 |             |                               |
 ---- ------------- --------- ------------------------------- ------------- ------------------------------- 
 

Теперь посмотрите на rn2. Он содержит значения из самого себя, а предварительные значения из rn3. Rn2-это строка, которая в конечном итоге выбирается. Обратите внимание, что он четко соответствует вашим where критериям. Проблема заключается в существовании строки 1. Поскольку rn1 есть, есть причина исключить. Это исключение отсутствует. Это исключение необходимо добавить в where пункт. Нужно убедиться, что такой строки не существует. (См. демонстрацию)

 with test as 
     ( select t.* 
            , lag(deposit) over w as pre_deposit 
            , lag(ts) over w as pre_ts 
         from t
       window w as (partition by customer_id order by ts)
     ) 
select distinct on (customer_id) *
  from test t1
 where t1.deposit = 0  
   and not exists ( select null 
                      from test t2
                     where t2.customer_id = t1.customer_id
                       and t2.deposit > 0 
                       and t2.ts > t1.ts
                   ) ; 
 

ПРИМЕЧАНИЕ: Я запустил демо-версию в Postgres 9.3, такую же, как у вас. Я предполагаю, что это версия, которую вы используете. Однако эта версия попала в конец списка в ноябре 2018 года. Я бы настоятельно рекомендовал вам обновить версию, если вы действительно используете 9.3.