Как задать окно в таблице до значение 0 в поле?

Question

[marselabdullin]

Есть таблица card_info:

Клиент Дата Баланс
1   01.03.2010 150
1   02.03.2010 100
1   03.03.2010 200
1   04.03.2010 0
1   05.03.2010 50
1   06.03.2010 0
2   16.03.2010 1000
2   17.03.2010 0

Для каждого клиента нужно вывести все периоды, где баланс отличен от нуля и средний баланс

Должна получиться такая табличка:

Клиент Дата1 дата2 Баланс
1   01.03.2010 03.03.2010 150
1   05.03.2010 05.03.2010 50
2   16.03.2010 16.03.2010 1000

Не понимаю как остановиться в группировке на 0, знаю про функцию lead для partition by которая показывает следующий элемент, но как она поможет распознать до куда группировать пока непонятно. Буду рад любой подсказке)

Answer 1

[galaxy]

Я, наверно, переусложнил, но пока так получилось (СУБД - Postgres):

select client_id, st, en, avg(bal)
  from (
    select *,
           max(day) filter (where l = 1) over (partition by client_id order by day) st,
           min(day) filter (where r = 1) over (partition by client_id order by day range between current row and unbounded following) en
      from (
         select *,
           case when (lag(bal) over w) > 0 then 0 else 1 end as l,
           case when (lead(bal) over w) > 0 then 0 else 1 end as r
          from balance
        window w as (partition by client_id order by day)
      ) t
      where bal > 0
) t1 group by client_id, st, en
  order by 1, 2

Самый внутренний запрос определяет пограничные строки (начало/конец периода ненулевого баланса). Промежуточный - вычисляет даты границ, внешний - группирует и считает среднее

https://www.db-fiddle.com/f/495cu1zp5BSmZDPwqXJ9Ev/0

Comments

[marselabdullin]

galaxy спасибо, а можешь пояснить по filter во втором запросе, это чисто постгрес тема, на оракле просто не запускается из за него

[galaxy]

marselabdullin, думаю, можно заменить на что-то типа:
max(case when l = 1 then day end) over ...

Answer 2

[Антон Шаманов]

SELECT Клиент, AVG(Баланс) AS CреднийБаланс WHERE Баланс>0 GROUP BY Клиент

Comments

[marselabdullin]

Антон Шаманов, тут еще и по периодам группировка не только по клиентам

[Антон Шаманов]

marselabdullin, иди читай min/max функции в sql

[Vitsliputsli]

Антон Шаманов, нельзя сделать требуемое только через агрегационные функции. Здесь нужны аналитические, как автор и писал. Ну или в mysql, может получится через извращения с переменными.

[Антон Шаманов]

Vitsliputsli, WITH либо фреймовые/оконные функции + min/max

[Vitsliputsli]

Антон Шаманов, with будет мало, а оконные функции это как правило аналитические.

[Антон Шаманов]

Vitsliputsli, https://cs11.pikabu.ru/post_img/2019/11/05/10/1572...