Вернуть строки, в которых впервые достигнуто определенное число (postgres)

Наткнулись на блокпост.

Контекст: я использую PostgreSQL 9.5.8

У меня есть следующая таблица с начисленными клиентами баллами. В таблице есть несколько строк для каждого клиента, так как в ней фиксируется каждое изменение баллов (как в таблице событий). т. е. клиент 1 может купить 1 товар и получить 10 баллов, что является одной строкой, затем в другой день потратить часть этих баллов и остаться с 5 баллами, что является другой строкой, а затем купить еще один товар и получить еще 10, возвращая их обратно. до 15, который отображается как другая строка. Каждая из этих строк с количеством баллов имеет столбец created_at.

Пример таблицы:

Customer ID  created_at    no_points   row
123          17/09/2017    5           1
123          09/10/2017    8           2
124          10/10/2017    12          3
123          10/10/2017    15          4
125          12/10/2017    12          5
126          17/09/2017    6           6
123          11/10/2017    11          7
123          12/10/2017    9           8
127          17/09/2017    5           9
124          11/10/2017    5           10
125          13/10/2017    5           11
123          13/10/2017    12          12

Я хочу отслеживать, когда клиент впервые достигает определенного порога, т.е. >= 10 баллов. Неважно, насколько они превышают 10 баллов, единственным критерием является то, что я выбираю первый раз, когда клиент достигает этого порога. Я также хотел бы, чтобы этот запрос извлекал только строки, в которых клиент достиг порогового значения 10 впервые за последнюю неделю.

Следуя этим правилам, в приведенном выше примере я хотел бы, чтобы мой запрос выбирал строки 3, 4 и 5.

Я пробовал следующий запрос:

SELECT  x.id,
        min(x.created_at)

FROM (

SELECT 
        p.id as id,
        p.created_at as created_at,
        p.amount as amount 

FROM "points" p 

WHERE p.amount >= 10  )  x 

WHERE x.created_at >= (now()::date - 7)
AND    x.created_at <   now()::date   

GROUP BY x.id

Я не уверен, что получаю правильную вещь, однако из набора результатов, который я вижу, и набор результатов огромен, поэтому это не очевидно. Может ли кто-нибудь смысл проверить?

Заранее спасибо.


postgresql sql conditional-statements postgresql-9.5
person Community    schedule 13.10.2017    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Используйте накопительные функции:

select p.*
from (select p.*,
             sum(num_points) over (partition by p.customer_id order by p.created_at) as cume_num_points
      from points p
     ) p
where cume_num_points >= 10 and
      (cume_num_points - num_points) < 10;

РЕДАКТИРОВАТЬ:

Я, возможно, неправильно понял вопрос. Если вам просто нужен первый перерыв, один метод использует оконные функции:

select p.*
from (select p.*,
             lag(num_points) over (partition by p.customer_id order by p.created_at) as prev_num_points
      from points p
     ) p
where num_points >= 10 and
      prev_num_points < 10;

Или без подзапроса:

select distinct on (p.customer_id) p.*
from customers p
where num_points >= 10
order by p.customer_id, p.created_at;
person Gordon Linoff    schedule 13.10.2017