SQL-запрос для вычисления части продолжительности посещения от таблицы журнала
Идете ли Вы для serialVersionUID или не (я предлагаю, чтобы Вы сделали), тогда необходимо действительно рассмотреть создание исчерпывающего набора тестов для последовательной совместимости.
также стоит разработать последовательный формат с осторожностью. Это - эффективно общедоступный API.
5 ответов
Еще несколько примеров данных и, надеюсь, логичное предположение о том, сколько времени каждый пользователь потратил на каждый курс.
INSERT INTO PageLogSample (userid, courseid, sessionid, requestdate)
-- [0, 10] = 10 minutes
SELECT 1, 1, 1, '00:00:00'
UNION ALL SELECT 1, 1, 1, '00:10:00'
-- [0, 3] = 3 minutes
-- there is no way to know how long the user was on that last page
UNION ALL SELECT 2, 1, 2, '00:00:00'
UNION ALL SELECT 2, 2, 2, '00:03:00'
UNION ALL SELECT 2, 2, 2, '00:05:00'
UNION ALL SELECT 2, 1, 2, '00:12:00'
-- [0, 3] + [12, 15] = 6 minutes
-- the [5, 12] part was spent on a page of course 2
UNION ALL SELECT 3, 1, 3, '00:00:00'
UNION ALL SELECT 3, 2, 3, '00:03:00'
UNION ALL SELECT 3, 2, 3, '00:05:00'
UNION ALL SELECT 3, 1, 3, '00:12:00'
UNION ALL SELECT 3, 2, 3, '00:15:00'
-- [1, 3] + [13, 15] = 4 minutes
UNION ALL SELECT 4, 2, 4, '00:00:00'
UNION ALL SELECT 4, 1, 4, '00:01:00'
UNION ALL SELECT 4, 2, 4, '00:03:00'
UNION ALL SELECT 4, 2, 4, '00:05:00'
UNION ALL SELECT 4, 1, 4, '00:13:00'
UNION ALL SELECT 4, 2, 4, '00:15:00'
-- [0, 5] + [10, 15] = 10 minutes
UNION ALL SELECT 5, 1, 5, '00:00:00'
UNION ALL SELECT 5, 1, 5, '00:05:00'
UNION ALL SELECT 5, 1, 6, '00:10:00'
UNION ALL SELECT 5, 1, 6, '00:15:00'
-- [0, 10] = 10 minutes (ignoring everything inbetween)
UNION ALL SELECT 6, 1, 7, '00:00:00'
UNION ALL SELECT 6, 1, 7, '00:03:00'
UNION ALL SELECT 6, 1, 7, '00:05:00'
UNION ALL SELECT 6, 1, 7, '00:07:00'
UNION ALL SELECT 6, 1, 7, '00:10:00'
-- [0, 5] + [7, 11] = 9 minutes
UNION ALL SELECT 7, 1, 8, '00:00:00'
UNION ALL SELECT 7, 1, 8, '00:03:00'
UNION ALL SELECT 7, 2, 8, '00:05:00'
UNION ALL SELECT 7, 2, 8, '00:06:00'
UNION ALL SELECT 7, 1, 8, '00:07:00'
UNION ALL SELECT 7, 1, 8, '00:11:00'
-- [0, 1] + [2, 4] + [5, 7] + [8, 13] = 10
UNION ALL SELECT 8, 1, 9, '00:00:00'
UNION ALL SELECT 8, 2, 9, '00:01:00'
UNION ALL SELECT 8, 1, 9, '00:02:00'
UNION ALL SELECT 8, 1, 9, '00:03:00'
UNION ALL SELECT 8, 2, 9, '00:04:00'
UNION ALL SELECT 8, 1, 9, '00:05:00'
UNION ALL SELECT 8, 1, 9, '00:06:00'
UNION ALL SELECT 8, 2, 9, '00:07:00'
UNION ALL SELECT 8, 1, 9, '00:08:00'
UNION ALL SELECT 8, 1, 9, '00:13:00'
-- there is nothing we can say about either of there requests
-- 0 minutes
UNION ALL SELECT 9, 1, 10, '00:10:00'
UNION ALL SELECT 9, 1, 11, '00:20:00'
;
Теперь мы получаем наши данные выглядят так:
WITH numberedcte (rn, userid, courseid, sessionid, requestdate)
AS (
SELECT ROW_NUMBER() OVER (PARTITION BY sessionid, userid ORDER BY id)
, userid
, courseid
, sessionid
, requestdate
FROM PageLogSample
)
, valuecte (value, userid, courseid, sessionid)
AS (
SELECT CASE
--alone in session
WHEN previousrequest.courseid IS NULL
AND nextrequest.courseid IS NULL
THEN 0
--alone
WHEN ( previousrequest.courseid IS NULL
OR previousrequest.courseid <> numberedcte.courseid
)
AND nextrequest.courseid <> numberedcte.courseid
THEN DATEDIFF(MINUTE, numberedcte.requestdate, nextrequest.requestdate)
--between
WHEN previousrequest.courseid = nextrequest.courseid
THEN 0
--begin
WHEN previousrequest.courseid IS NULL
OR nextrequest.courseid = numberedcte.courseid
THEN -1 * DATEPART(MINUTE, numberedcte.requestdate)
--ignored (end with no next request)
WHEN nextrequest.courseid IS NULL
AND previousrequest.courseid <> numberedcte.courseid
THEN 0
--end
WHEN nextrequest.courseid IS NULL
OR previousrequest.courseid = numberedcte.courseid
THEN DATEPART(MINUTE, ISNULL(nextrequest.requestdate, numberedcte.requestdate))
--impossible?
ELSE 0
END
, numberedcte.userid
, numberedcte.courseid
, numberedcte.sessionid
FROM numberedcte
LEFT JOIN numberedcte previousrequest
ON previousrequest.userid = numberedcte.userid
AND previousrequest.sessionid = numberedcte.sessionid
AND previousrequest.rn = numberedcte.rn - 1
LEFT JOIN numberedcte nextrequest
ON nextrequest.userid = numberedcte.userid
AND nextrequest.sessionid = numberedcte.sessionid
AND nextrequest.rn = numberedcte.rn + 1
WHERE numberedcte.courseid = @courseid
)
SELECT userid
, courseid
, COUNT(DISTINCT sessionid) AS sessioncount
, SUM(value) AS duration
FROM valuecte
GROUP BY userid
, courseid
ORDER BY userid
;
Это результат, который я получаю. Я очень доволен этим. Обратите внимание, что количество сеансов остается правильным для пользователя 9.
userid courseid sessioncount duration
1 1 1 10
2 1 1 3
3 1 1 6
4 1 1 4
5 1 2 10
6 1 1 10
7 1 1 9
8 1 1 10
9 1 2 0
Извините, но я думаю, у вас проблема с данными. Глядя на образец данных, предоставленный пользователю 2, находится под курсом 1 в течение 12 минут и курсом 2 в течение 2 минут.
Вы уверены, что ввели правильные данные?
Это так близко, насколько я могу. Он не работает для идентификатора пользователя 4.
Как я сказал в своем комментарии, requestdate иногда является началом, а иногда и концом курса, и я не вижу простого общего правила для определения того, какую роль он воспроизводится в заданной строке.
DECLARE @courseid INT;
SET @courseid = 1;
WITH orderCTE
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY sessionid
ORDER BY id
) AS rn
FROM PageLogSample
--order by rn
)
,startendCTE
AS
(
SELECT CASE WHEN start1.rn = 1
THEN start1.courseid
ELSE end1.courseid
END courseid
,start1.sessionid
,start1.userid
,DATEDIFF(mi,start1.requestdate,end1.requestdate) duration
FROM orderCTE AS start1
JOIN orderCTE AS end1
ON end1.rn = start1.rn + 1
AND end1.sessionid = start1.sessionid
)
SELECT courseid
,COUNT(1) sessionCount
,userid
,SUM(duration) totalDuration
FROM startendCTE
WHERE courseid = @courseid
GROUP BY courseid
,userid;
Это довольно запутанно, но похоже, что это работает для CourseID 1. Я не пробовал это с другими курсами, так что вы можете проверить это! : D
Основная предпосылка состоит в том, что я получаю продолжительность времени между первым и последним сеансом целевого CourseID, а затем вычитаю продолжительность всех сеансов, которые не были от указанного CourseID, но где время запроса сеанса попало в минимальное и максимальное время запроса целевого CourseID. Я надеюсь, что это имеет смысл.
Запрос определенно можно очистить, возможно, с помощью CTE или чего-то еще. Интересный вопрос, кстати! :)
DECLARE @courseid INT;
SET @courseid = 1;
SELECT
TargetCourse.UserID,
COUNT(Distinct(TargetCourse.SessionID)) as SessionCount,
SUM(TargetCourse.Duration - Coalesce(OtherCourses.Duration,0)) as Duration
FROM
(
SELECT
TargetCourse.UserID, TargetCourse.SessionID,
MIN(TargetCourse.RequestDate) FirstRequest, MAX(TargetCourse.RequestDate) LastRequest,
DATEDIFF(MINUTE, MIN(TargetCourse.RequestDate), MAX(TargetCourse.RequestDate)) AS duration
FROM
PageLogSample TargetCourse
WHERE
TargetCourse.CourseID = @courseid
GROUP BY
TargetCourse.UserID, TargetCourse.SessionID
) as TargetCourse
LEFT OUTER JOIN
(
SELECT
OtherCourses.UserID, OtherCourses.SessionID,
MIN(OtherCourses.RequestDate) AS FirstRequest, MAX(OtherCourses.RequestDate) AS LastRequest,
DATEDIFF(MINUTE, MIN(OtherCourses.RequestDate), MAX(OtherCourses.RequestDate)) AS duration
FROM
PageLogSample OtherCourses
WHERE
OtherCourses.CourseID <> @courseid AND
OtherCourses.RequestDate between
(Select MIN(RequestDate) From PageLogSample T Where T.UserID = OtherCourses.UserID and T.CourseID = @courseid) AND
(Select MAX(RequestDate) From PageLogSample T Where T.UserID = OtherCourses.UserID and T.CourseID = @courseid)
GROUP BY
OtherCourses.UserID, OtherCourses.SessionID
) as OtherCourses ON
OtherCourses.UserID = TargetCourse.UserID AND
OtherCourses.FirstRequest BETWEEN TargetCourse.FirstRequest and TargetCourse.LastRequest
Group By TargetCourse.UserID
«Данные верны, но из них просто трудно получить соответствующий смысл»
Я вынужден ответить, что это противоречие терминов. Данные, о которых вы не знаете, что они означают, не являются данными.
Что касается вашего исходного вопроса:
Что вам нужно, так это СУБД, которая предлагает достойную поддержку для типов INTERVAL. В этой лиге нет системы SQL. За исключением нескольких обучающих систем, моя собственная СУБД (в данном контексте я не буду вдаваться в подробности, поэтому без ссылки) - единственная известная мне СУБД, которая предлагает такую поддержку, которая действительно необходима для решения таких проблем.
Если вы ' Если интересно, погуглите "типы интервалов", "упакованная нормальная форма", "временные данные", и вы в конце концов столкнетесь с этим.