Перемещение окна SQL по группе

avatar
slaw
8 августа 2021 в 19:52
113
2
0

У меня есть стол с несколькими игроками, и у каждого игрока есть выбор: играть в игру A или B (доступно только 2 игры), и их дата и время записываются:

Игрок Дата Время Игра
1 2021-01-01 1:00 А
1 2021-01-02 1:00 А
1 2021-01-03 1:00 А
1 2021-01-04 1:00 А
1 2021-01-05 1:00 А
1 2021-01-06 1:00 А
1 2021-01-07 1:00 А
1 2021-01-08 1:00 А
2 2021-01-01 5:00 А
2 2021-01-02 6:00 В
2 2021-01-03 1:00 В
2 2021-01-04 3:00 А
2 2021-01-05 2:00 А
2 2021-01-06 4:00 А
2 2021-01-07 9:00 А
2 2021-01-08 1:00 В
3 2021-01-01 5:00 А
3 2021-01-02 6:00 А
3 2021-01-03 1:00 В
3 2021-01-04 3:00 А
3 2021-01-05 2:00 В
3 2021-01-06 4:00 А
3 2021-01-07 5:00 В
3 2021-01-07 6:00 А
3 2021-01-07 7:00 В
3 2021-01-07 9:00 А
3 2021-01-08 1:00 В
4 2021-01-02 2:00 А
4 2021-01-03 2:00 НОЛЬ
4 2021-01-04 4:00 НОЛЬ
4 2021-01-05 1:00 НОЛЬ
4 2021-01-06 9:00 НОЛЬ
4 2021-01-07 8:00 В

Для каждого игрока я ищу эффективный и портативный SQL-запрос, чтобы определить количество уникальных/отличных игр, в которые они играли, используя окно в три (или четыре) дня. Обратите внимание, что Time (в течение дня), в которые игра ведется, не имеет значения. Итак, результаты для трехдневного окна будут выглядеть так:

Игрок Дата начала Дата окончания Сыграно уникальных игр
1 2021-01-01 2021-01-03 1
1 2021-01-02 2021-01-04 1
1 2021-01-03 2021-01-05 1
1 2021-01-04 2021-01-06 1
1 2021-01-05 2021-01-07 1
1 2021-01-06 2021-01-08 1
2 2021-01-01 2021-01-03 2
2 2021-01-02 2021-01-04 2
2 2021-01-03 2021-01-05 2
2 2021-01-04 2021-01-06 1
2 2021-01-05 2021-01-07 1
2 2021-01-06 2021-01-08 2
3 2021-01-01 2021-01-03 2
3 2021-01-02 2021-01-04 2
3 2021-01-03 2021-01-05 2
3 2021-01-04 2021-01-06 2
3 2021-01-05 2021-01-07 2
3 2021-01-06 2021-01-08 2
4 2021-01-02 2021-01-04 1
4 2021-01-03 2021-01-05 0
4 2021-01-04 2021-01-06 0
4 2021-01-05 2021-01-07 1

Я начал с чего-то вроде:

SELECT COUNT(DISTINCT GAME)
FROM PLAY_TABLE
GROUP BY PLAYER
ORDER BY DATE

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

Источник
Gordon Linoff
8 августа 2021 в 19:53
0

Отметьте свой вопрос с помощью базы данных, которую вы используете.

Ответы (2)

avatar
Isis Santos Costa
9 августа 2021 в 01:59
-1

сочетает ARRAY_AGG, FLATTEN, ARRAY_DISTINCT, FILTER и CARDINALITY:

select    player, Date as start_date, date_add(Date, 2) as end_date
        , cardinality(filter(array_distinct(flatten(array_agg(array_agg(Game)) 
          over (partition by player order by Date rows between current row and 2 following ))),
          x -> x is not null )) as unique_games_played
from PLAY_TABLE
group by player, Date
order by player, start_date

функции массива, описанные на https://prestodb.io/docs/current/functions/array.html

jarlh
9 августа 2021 в 07:32
0

Вы уверены, что OP использует Presto? (Этот ответ для конкретного продукта не соответствует стандарту ANSI SQL.)

avatar
Gordon Linoff
8 августа 2021 в 19:56
0

Если у вас есть пользователи и даты, вы можете использовать запрос, который генерирует нужные вам комбинации пользователей и дат. Затем вы можете left join вернуться к таблице, чтобы получить исходные строки, которые соответствуют пользователю и периоду даты, и агрегировать:

select p.player, d.date, d.date + interval '2 day',
       count(distinct t.game) as num_games
from (select distinct player from t) p cross join
     (<whatever dates you want>) d left join
     t
     on t.player = p.player and
        t.date = d.date and
        t.date <= d.date + interval '2 day'
group by p.player, d.date;

Арифметика даты и времени зависит от базы данных, поэтому точный синтаксис зависит от вашей базы данных.

jarlh
9 августа 2021 в 07:33
0

ANSI SQL требует interval '2' day.

slaw
9 августа 2021 в 20:01
0

Может быть, я что-то неправильно понимаю, но p должен содержать только результаты из select distinct player from t, что означает, что p.date не существует. И все же p.date используется в условии on в конце. Возможно, условие on должно быть ON ( t.player = p.player and t.date >= d.date and t.date <= d.date + interval '2' day)