#sql #lag #partition
#sql #задержка #раздел
Вопрос:
У меня есть такая таблица —
| row_no | Фильм. | movie_start_time | movie_end_time |
|---|---|---|---|
| 1 | A | 2021-02-01 01:00:00 | 2021-02-01 02:00:00 |
| 2 | B | 2021-02-01 01:00:00 | 2021-02-01 02:00:00 |
| 3 | A | 2021-02-01 01:30:00 | 2021-02-01 02:30:00 |
| 4 | A | 2021-02-01 01:30:00 | 2021-02-01 02:30:00 |
| 5 | A | 2021-02-01 02:15:00 | 2021-02-01 03:15:00 |
| 6 | B | 2021-02-01 02:15:00 | 2021-02-01 03:15:00 |
| 7 | A | 2021-02-01 04:15:00 | 2021-02-01 05:15:00 |
Я хочу добавить в таблицу один дополнительный столбец, который имеет разницу между предыдущим временем воспроизведения и текущим временем воспроизведения для одного и того же фильма. У меня также есть условие, что предыдущее время воспроизведения и текущее время воспроизведения не должны перекрываться. Таким образом, в приведенном выше сценарии результаты должны выглядеть следующим образом:
| row_no | Фильм. | movie_start_time | movie_end_time. | last_play |
|---|---|---|---|---|
| 1 | A | 2021-02-01 01:00:00 | 2021-02-01 02:00:00 | — |
| 2 | B | 2021-02-01 01:00:00 | 2021-02-01 02:00:00 | — |
| 3 | A | 2021-02-01 01:30:00 | 2021-02-01 02:30:00 | — |
| 4 | A | 2021-02-01 01:30:00 | 2021-02-01 02:30:00 | — |
| 5 | A | 2021-02-01 02:15:00 | 2021-02-01 03:15:00 | 15 минут |
| 6 | B | 2021-02-01 02:15:00 | 2021-02-01 03:15:00 | 15 минут |
| 7 | A | 2021-02-01 04:15:00 | 2021-02-01 05:15:00 | 60 минут |
Я попытался написать ниже запрос для получения предыдущего movie_end_time, чтобы я мог вычислить разницу позже :
select movie, movie_start_time, movie_end_time, lag(movie_end_time) over (partition by movie order by movie_start_time) prev_end_time from table where prev_end_time <= movie_start_time
Но это не работает, поскольку мы не можем добавить предложение where с помощью оконных функций. Есть ли какой-либо другой способ решить эту проблему?
Комментарии:
1.
select * from (<that query of yours>) t where prev_end_time <= movie_start_time?2. ну, вы уверены, что пятая строка правильная? потому что он перекрывается с другим фильмом A в 4-й строке.
3. @GSerg Это не сработает, поскольку задержка просто даст мне немедленную предыдущую строку, мне нужна немедленная предыдущая строка, которая удовлетворяет условию prev_end_time <= movie_start_time
4. @ARAT Я думаю, что выбранный мной пример был немного запутанным — 15 минут в 5-й строке — это отличие от неперекрывающейся предыдущей записи, которая является 1-й строкой
Ответ №1:
Итак, я использовал PostgreSQL для создания таблицы:
CREATE TABLE IF NOT EXISTS table1 (
row_no INT,
Movie CHAR,
movie_start_time timestamp,
movie_end_time timestamp
);
и вставьте записи в эту таблицу:
INSERT INTO table1 (row_no, Movie, movie_start_time, movie_end_time) VALUES(1, 'A', '2021-02-01 01:00:00', '2021-02-01 02:00:00');
INSERT INTO table1 (row_no, Movie, movie_start_time, movie_end_time) VALUES(2, 'B', '2021-02-01 01:00:00', '2021-02-01 02:00:00');
INSERT INTO table1 (row_no, Movie, movie_start_time, movie_end_time) VALUES(3, 'A', '2021-02-01 01:30:00', '2021-02-01 02:30:00');
INSERT INTO table1 (row_no, Movie, movie_start_time, movie_end_time) VALUES(4, 'A', '2021-02-01 01:30:00', '2021-02-01 02:30:00');
INSERT INTO table1 (row_no, Movie, movie_start_time, movie_end_time) VALUES(5, 'A', '2021-02-01 02:15:00', '2021-02-01 03:15:00');
INSERT INTO table1 (row_no, Movie, movie_start_time, movie_end_time) VALUES(6, 'B', '2021-02-01 02:15:00', '2021-02-01 03:15:00');
INSERT INTO table1 (row_no, Movie, movie_start_time, movie_end_time) VALUES(7, 'A', '2021-02-01 04:15:00', '2021-02-01 05:15:00');
Тогда то, что вы хотите, это:
select row_no, movie, movie_start_time, movie_end_time, EXTRACT(EPOCH FROM (movie_start_time - prev_end_time)::INTERVAL)/60 AS last_play FROM
(select row_no,movie, movie_start_time, movie_end_time, lag(movie_end_time) over (partition by movie order by movie_start_time) AS "prev_end_time"
from table1) t
where prev_end_time <= movie_start_time
ORDER BY movie_start_time
Комментарии:
1. В ваших результатах 5-я строка отсутствует в ожидаемом результате. Вы просто проверяете непосредственную предыдущую запись для этого фильма. Но мне нужна немедленная предыдущая запись для фильма, где prev_end_time <= movie_start_time . В случае 5-й строки ближайшей предыдущей записью, удовлетворяющей условию, будет 1-я строка
Ответ №2:
Я смог решить проблему с помощью приведенного ниже запроса :
with a as
(select row_no,movie, movie_start_time, movie_end_time,
array_agg(movie_end_time) over (partition by movie order by movie_start_time
rows between unbounded preceding and 1 preceding) AS prev_end_time from `table1`),
b as (select row_no,movie, movie_start_time, movie_end_time,
case
when prev_end_time is null then null
else (select max(i_prev_end_time) from unnest(prev_end_time)i_prev_end_time
where i_prev_end_time <= movie_start_time)
end previous_end_time from a)
select row_no,movie, movie_start_time, movie_end_time,
unix_seconds(movie_start_time) - unix_seconds(previous_end_time) last_run from b