T-SQL - превратить таблицу с текущей страницей и предыдущими страницами в последовательный порядок за сеанс
Я пытаюсь создать таблицу, чтобы показать активность за сеанс на веб-сайте. Должно выглядеть как-то так
Предпочтительный стол:
+------------+---------+--------------+-----------+
| SessionID | PageSeq| Page | Duration |
+------------+---------+--------------+-----------+
| 1 | 1 | Home | 5 |
| 1 | 2 | Sales | 10 |
| 1 | 3 | Contact | 9 |
| 2 | 1 | Sales | 5 |
| 3 | 1 | Home | 30 |
| 3 | 2 | Sales | 5 |
+------------+---------+--------------+-----------+
К сожалению, мой текущий набор данных не содержит информации о session_id, но может быть вычтен на основе времени и пути.
Текущая таблица:
+------------------+---------+------------+---------------+----------+
| DATE_HOUR_MINUTE | Page | Prev_page | Total_session | Duration |
+------------------+---------+------------+---------------+----------+
| 201801012020 | Home | (entrance) | 24 | 5 |
| 201801012020 | Sales | Home | 24 | 10 |
| 201801012020 | Contact | Sales | 24 | 9 |
| 201801012020 | Sales | (entrance) | 5 | 5 |
| 201801012020 | Home | (entrance) | 35 | 30 |
| 201801012020 | Sales | Home | 35 | 5 |
+------------------+---------+------------+---------------+----------+
Каков наилучший способ преобразовать текущую таблицу в предпочтительный формат таблицы?
Я пробовал искать вложенные таблицы, зацикленные таблицы, пока не нашел ничего, связанного с этой проблемой.
Невозможно надежно идентифицировать ваши сеансы; в данных, которые вы показываете, хорошо, Duration в сумме равно Total_Session, но что, если у вас есть два набора с одинаковыми Total_Session, но немного разными Durations, например, Home = 4, затем Contact = 10, затем Salse = 10 (всего 24, то же самое, что у вас сейчас).
@thewildhealer Я использую Sql-сервер
@MarcinJ правильное наблюдение. Однако в «реальном» наборе данных у меня более 3000 разных страниц. Вероятность того, что кто-то начнет сеанс точно в то же время, с той же продолжительностью и по тому же пути, близка к 0 (несовершенство, с которым я могу справиться).
Это сложный запрос, и я не знаю, можно ли его выполнить в SQL, поскольку для получения результата требуется динамическое количество соединений. Если бы это был Postgres, я бы порекомендовал поиграться с функциями, но у меня нет знаний о SQL Server. Обновлено: Ну, Марчин только что доказал, что я ошибаюсь.
Ответы 1
Поэтому, если вы можете рисковать сеансами, начинающимися в одно и то же время с одинаковой продолжительностью, это должно быть достаточно легко сделать с помощью рекурсивного запроса.
;WITH sessionTree AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as sessionId
, 1 AS PageSeq
, *
FROM Session
WHERE PrevPage = '(entrance)'
UNION ALL
SELECT prev.sessionId
, prev.PageSeq + 1
, next.*
FROM sessionTree prev
JOIN Session next
ON next.TotalDuration = prev.TotalDuration
AND next.PrevPage = prev.Page
AND next.date_hour_minute >= prev.date_hour_minute
)
SELECT * FROM sessionTree
ORDER BY sessionId, PageSeq
sessionId генерируется для каждой записи с (entrance) как prevPage, с PageSeq = 1. Затем в рекурсивной части посещения с отметкой времени позже предыдущей страницы и с той же продолжительностью объединяются по условию prev.page = next.PrevPage.
Какую систему баз данных вы используете?