Как составить запрос выборки баллов?

Question

[enchikiben]

У меня есть табличка баллов пользователей: id. user_id, point, point_lifetime
point - это баллы они как положительные так и отрицательные
point_lifetime - время жизни баллов, в timestamp

Мне необходимо найти запись, а точнее point_lifetime, при условии что сумма point (баллов) меньше или равна N.

|      id       |      user_id      |      point     |      point_lifetime  |
|      1        |      1            |      10        |      1701533439      |
|      2        |      1            |      10        |      1701533439      |
|      3        |      2            |      10        |      1001433439      |
|      4        |      1            |      -10       |      1901533439      |
|      5        |      1            |      100       |      1801733439      |

Мне нужно найти запись на который у пользователя где у пользователя 1 накопится допустим 30 баллов, это запись id=5. Сортировать нужно по point_lifetime, чтобы суммировались записи у которых время жизни скоро кончится.

Answer 1

[alexalexes]

Вас просят написать что-то с клаузой Having.
Если читать вопрос влоб, то вас просят сгруппировать выборку по атрибуту point_lifetime, и проанализировать сумму баллов в having:

select point_lifetime, sum(point)
from t
group by point_lifetime
having sum(point) <= N -- N нужно рассматривать как входной параметр запроса

Тут бы логично по user_id что-то интересное искать, но в задачке point_lifetime. В общем, нужно видеть с какими исходными данными работаете, чтобы понять, что вы группируете.
Дополнено:

-- тестовые данные
with t as (select  1 id, 1 user_id, 10 point, from_unixtime(1701533439) point_lifetime
union
select  2 id, 1 user_id, 10 point, from_unixtime(1701533439) point_lifetime
union
select  3 id, 2 user_id, 10 point, from_unixtime(1001433439) point_lifetime
union
select  4 id, 1 user_id, -10 point, from_unixtime(1901533439) point_lifetime
union
select  5 id, 1 user_id, 100 point, from_unixtime(1801733439) point_lifetime)

-- экспериментальный запрос
select a.*
from (select t.id,
       t.user_id,
       t.point,
       t.point_lifetime, 
       sum(t.point) over (partition by t.user_id) sum_point,
       row_number() over (partition by t.user_id order by case when t.point > 0 then 0 else 1 end, -- продавливаем положительные баллы вверх
                                                                t.point_lifetime desc) rn -- локальный счетчик записей по пользователю, чтобы понять, какую запись хотите выдернуть из подзапроса, нужно сделать так, чтобы интересуемые записи имели rn = 1
from t
  where point_lifetime > str_to_date('02.12.2023 10:00:00', '%d.%m.%Y %H:%i:%s') -- считаем, что отметка "сейчас" - это 02.12.2023 10:00:00
) a
where a.sum_point >= 30 -- берем все то, что больше минимальной суммы
  and a.rn = 1 -- берем только интересуемую запись по каждому пользователю
 order by id

Comments

[enchikiben]

Я так пробовал оно не работает, как надо

[enchikiben]

Дополнил вопрос примером.

[Akina]

Не, думаю, что от него хотят CTE и SUM OVER

[alexalexes]

Непонятно, что такое point_lifetime относительно текущего времени?
Это отметка, когда выставлены баллы? (говорящим было бы обозначение add_datetime)
Или когда заканчивается срок действия баллов? (говорящим было бы обозначение expire_datetime)

[alexalexes]

Дополнил ответ, каркасом запроса с оконными функциями. Не хватает конкретики, чтобы сделать точно.

[enchikiben]

alexalexes, второе, когда заканчивается срок баллов

[alexalexes]

Поправил запрос, но он будет возвращать id=4, так как у списания самая большая дата по этому пользователю.
Нет ли дополнительных ограничений?
Может среди зачисленных баллов дату нужно выводить, а сумму считать по зачисленным и списанным баллам (по непросроченным записям)?

[alexalexes]

В общем, добавил в row_number условие сортировки:
case when t.point > 0 then 0 else 1 end
Тогда дата у положительных баллов будет в приоритете при формировании счетчика rn.
Следовательно, в итоговой выборке будут последние данные по зачислению баллов по каждому пользователю, но не будет упоминаться списание. Однако, действующие списания будут учитываться в сумме баллов.

[alexalexes]

Еще раз перечитал ваш вопрос.

накопится допустим 30 баллов

это значит:
a.sum_point >= 30
Тогда еще вопрос. Если дальше пользователь будет копить баллы, допустим:

select  6 id, 1 user_id, 20 point, from_unixtime(1802733439) point_lifetime

Тогда, какая запись должна выйти?
Там где достигли минимального порога в 30 баллов (id=5) или та, которая отражает текущий баланс баллов, который больше либо равен 30 (id=6)?

Answer 2

[Akina]

Мне нужно найти запись на который у пользователя где у пользователя 1 накопится допустим 30 баллов, это запись id=5.

WITH cte AS (
    SELECT *, SUM(point) OVER (ORDER BY point_lifetime) cum_sum
    FROM tablename
    WHERE user_id = 1 --   у пользователя 1 
    )
SELECT *
FROM cte
WHERE cum_sum >= 30   --   накопится 30 баллов
ORDER BY point_lifetime LIMIT 1;

PS. Поскольку значения в point_lifetime неуникальны, то однозначного решения может и не быть. Во всяком случае до тех пор, пока не будет точно определён порядок записей при равенстве значений этого поля.