Создайте частную ПОСЛЕДОВАТЕЛЬНОСТЬ для каждого РАЗДЕЛА
У меня есть таблица матчей в разных играх, и я хотел бы рассчитать, насколько плотным является пространство матчей в отношении каждой игры. Пример таблицы:
id | game | start_dt
---+-------+-----------------
1 | dota2 | 2020-01-01 15:00
---+-------+-----------------
2 | dota2 | 2020-01-01 15:05
---+-------+-----------------
3 | dota2 | 2020-01-01 18:00
---+-------+-----------------
4 | cs-go | 2020-01-01 13:05
---+-------+-----------------
5 | cs-go | 2020-01-01 13:15
---+-------+-----------------
6 | dota2 | 2020-01-01 12:00
---+-------+-----------------
7 | cs-go | 2020-01-01 14:45
В идеале получится:
id | game | start_dt | time_group_id
---+-------+-----------------+---------------
6 | dota2 | 2020-01-01 12:00| 1
---+-------+-----------------+---------------
1 | dota2 | 2020-01-01 15:00| 2
---+-------+-----------------+---------------
2 | dota2 | 2020-01-01 15:05| 2
---+-------+-----------------+---------------
3 | dota2 | 2020-01-01 18:00| 3
---+-------+-----------------+---------------
4 | cs-go | 2020-01-01 13:05| 4
---+-------+-----------------+---------------
5 | cs-go | 2020-01-01 13:15| 4
---+-------+-----------------+---------------
7 | cs-go | 2020-01-01 14:45| 5
Что в основном означает, что если разрыв между следующей игрой и предыдущей меньше или равен 10 минутам, они считаются в одной временной группе. В противном случае это разные временные группы, и это продолжается.
Затем эти time_group_id используются для отображения полезной информации о совпадениях и их частоте времени.
Мой код приведен ниже, и он идеально подходит для этой цели, однако он не дает равномерно распределенных идентификаторов, поэтому я должен использовать комбинацию game VARCHAR и group_id для поля, чтобы уникально представлять группу. Пожалуйста, запустите его в dbfiddle, чтобы понять, что я имею в виду.
CREATE TABLE fight(
id BIGSERIAL PRIMARY KEY,
date TIMESTAMP NOT NULL,
game VARCHAR NOT NULL
);
INSERT INTO fight(date, game)
VALUES
('2020-01-01 15:00'::TIMESTAMP, 'dota2'),
('2020-01-01 15:05'::TIMESTAMP, 'dota2'),
('2020-01-01 18:00'::TIMESTAMP, 'dota2'),
('2020-01-01 13:05'::TIMESTAMP, 'cs-go'),
('2020-01-01 13:15'::TIMESTAMP, 'cs-go'),
('2020-01-01 12:00'::TIMESTAMP, 'dota2'),
('2020-01-01 14:45'::TIMESTAMP, 'cs-go');
SELECT * FROM fight;
CREATE SEQUENCE seq START 1 CACHE 1;
SELECT
a.id,
a.game,
a.start_dt,
(CASE WHEN (a.start_dt - INTERVAL '10 min' <= a.prev_start_dt) THEN currval('seq')
ELSE nextval('seq')
END)::VARCHAR || '|' || a.game AS time_group_id
FROM
(
SELECT
fight.id,
fight.game,
fight.date AS start_dt,
LAG (fight.date, 1, fight.date) OVER (PARTITION BY fight.game ORDER BY fight.date) AS prev_start_dt
FROM fight CROSS JOIN (SELECT setval('seq', 1)) s
) a
ORDER BY a.game, a.start_dt;
Вопрос в том, есть ли идеальный способ сделать это, или я должен придерживаться того, что у меня есть?
Спасибо за вопрос. В основном это связано с необходимостью сопоставления большого объема информации, хранящейся в другой таблице, с каждой группой событий. Например, есть 100 записей для первого события и 200 записей для второго и так далее, и этот запрос соединит эти записи в одну группу, если эти события близки по времени.
Ответы 2
Для этого вам не нужна последовательность, просто кумулятивная сумма:
SELECT f.*,
COUNT(*) FILTER (WHERE prev_date < date - interval '10 min') OVER (ORDER BY date) as time_group_id
FROM (SELECT f.*,
LAG(f.date) OVER (PARTITION BY f.game ORDER BY f.date) AS prev_date
FROM fight f
) f;
Примечания: это может начинаться с 0, а не 1. Если это имеет значение, используйте 1 +.
Получается число, а не строка. Вы можете преобразовать в строку (используя ::text), если это то, что вам действительно нужно.
Вот db<>рабочий пример
Но смысл запроса состоит в том, чтобы разделить бои на группы так, чтобы текущий бой был не позднее, чем на 10 минут, чем предыдущий, И присвоить каждой из этих групп уникальный идентификатор, основанный как на первом условии, так и на их игре. Я не смог выполнить ваш запрос, и я действительно не могу понять, что вы пытались донести. Не могли бы Вы уточнить?
@винвин. . . Я исправил запрос и добавил скрипку db<>.
Большое спасибо, благодаря вашей идее с накопительной суммой я смог решить эту проблему так, как было задумано! Я опубликую ответ, я действительно хотел бы, чтобы вы увидели, чего я пытался достичь. Ты классный парень, спасибо, я всегда поставлю тебе палец вверх.
SELECT
b.id,
b.game,
b.start_dt,
sum(b.time_group_count) OVER (ORDER BY b.game, b.start_dt) as time_group_id
FROM
(SELECT
a.id,
a.game,
a.start_dt,
CASE WHEN a.prev_start_dt IS NULL THEN 1
WHEN (a.start_dt - INTERVAL '10 min' <= a.prev_start_dt) THEN 0
ELSE 1
END AS time_group_count
FROM
(
SELECT
fight.id,
fight.game,
fight.date AS start_dt,
LAG (fight.date, 1) OVER (PARTITION BY fight.game ORDER BY fight.date) AS prev_start_dt
FROM fight
) a
ORDER BY a.game, a.start_dt) b;
Этот запрос дал мне результаты, которые я действительно хотел. Очень благодарен за идею cumsum от @Gordon Linoff, спасибо!
Ваш код выглядит нормально. Но почему вы хотите хранить эту информацию? Вы можете легко вычислить его на лету, когда это необходимо.