Проблема в том, что мне нужно рассчитать среднее количество страниц/посещений после достижения страница (включая pax hit).
База данных:
CREATE TABLE search (
SESSION_ID INTEGER,
HIT_NUMBER INTEGER,
PAGE VARCHAR(24),
MEDIUM_T VARCHAR(24)
);
INSERT INTO search
(SESSION_ID, HIT_NUMBER, PAGE, MEDIUM_T)
VALUES
('123', '1', 'home', 'direct'),
('123', '2', 'flights_home', 'direct'),
('123', '3', 'results', 'direct'),
('456', '1', 'pax', 'metasearch'),
('789', '1', 'home', 'partners'),
('789', '2', 'flights_home', 'partners'),
('789', '3', 'results', 'partners'),
('789', '4', 'home', 'partners'),
('146', '1', 'results', 'SEM'),
('146', '2', 'pax', 'SEM'),
('146', '3', 'payment', 'SEM'),
('146', '4', 'confirmation', 'SEM');
И мой подход:
SELECT s1.SESSION_ID, COUNT(*) as sCOUNT
FROM search s1
WHERE PAGE = 'pax'
GROUP BY s1.SESSION_ID
UNION ALL
SELECT 'Total AVG', AVG(a.sCOUNT)
FROM (
SELECT COUNT(*) as sCOUNT
FROM search s2
GROUP BY s2.SESSION_ID
) a
Очевидно, что строка 3r неверна, в моем коде отсутствует часть, в которой после отображения «pax» начинается отсчет, и у меня нет для этого никакой подсказки.
Заранее благодарю :)
Поиск всех pax
страниц и последующих можно сделать с помощью exists
. Остальное прямо вперед:
SELECT AVG(hits)
FROM (
SELECT session_id, COUNT(*) AS hits
FROM search AS s1
WHERE page = 'pax' OR EXISTS (
SELECT *
FROM search AS s2
WHERE s2.session_id = s1.session_id
AND s2.hit_number < s1.hit_number
AND s2.page = 'pax'
)
GROUP BY session_id
) AS x
При использовании MySQL 8 оконные функции обеспечивают более простое решение:
WITH cte1 AS (
SELECT session_id, MAX(CASE WHEN page = 'pax' THEN 1 END) OVER (
PARTITION BY session_id
ORDER BY hit_number
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS countme
FROM search
), cte2 as (
SELECT COUNT(*) AS hits
FROM cte1
WHERE countme IS NOT NULL
GROUP BY session_id
)
SELECT AVG(hits)
FROM cte2
Спасибо, это решение довольно прямолинейно и мне легко понять! :)
В моем подходе используется WITH CTE (common-table-expression) для предварительного объявления базовой основы запроса, а затем запроса и усреднения на основе этого.
Во-первых, одна предпосылка, которая не была явно отражена в ваших примерах данных. Что происходит ЕСЛИ пользователь переходит туда-сюда между несколькими страницами и нажимает на страницу PAX более одного раза. Теперь у вас есть несколько посещений страницы pax. Я бы предположил, что вы хотите, чтобы ПЕРВЫЙ экземпляр был на такой странице pax, и это включает все обращения к странице. Это решение должно помочь объяснить это.
Давайте посмотрим на самое внутреннее предложение from с окончательным псевдонимом «pxHits».
Я группирую по идентификатору сеанса и захватываю ПЕРВЫЙ ЭКЗЕМПЛЯР обращения к странице pax (или null, если такая страница pax не встречается), но ТАКЖЕ захватываю САМОЕ ВЫСОКОЕ число обращений за сеанс. Предложение HAVING гарантирует, что оно возвращает только те сеансы, для которых была возвращена страница HAD PAX, при этом все остальные сеансы исключаются из результатов.
Это приведет к тому, что две записи будут переданы внешнему выбору, который включает вычисление 1 + lastHitNumber - firstPaxHit
. Причина для 1 + в том, что вы хотя бы один раз НАЖИМАЛИ на страницу. Но в сценарии вашего сеанса 456, где первое и последнее обращение БЫЛО первой страницей, вам нужно, чтобы, поскольку lastHitNumber - firstPaxHit был нулевым. Это было бы верно, если бы у человека было 25 посещений страниц, и он попал на страницу pax на странице 26. Ваш результат все равно будет 1 через 1 + 26 - 26 = 1 общая страница, включая страницу pax, а не 25 до этого.
Ваша другая квалификационная сессия будет 146. Первое попадание было 2, но они перешли к максимальному количеству посещений страницы, равному 4. Таким образом, 1 + 4 - 2 = всего 3 страницы.
Итак, теперь к финалу. Поскольку вы можете видеть, КАК все готовится, теперь мы можем получить средние значения. Вы не можете смешивать / автоматически преобразовывать разные типы данных (session_id против фиксированного сообщения вашего «Total Avg». Они должны быть одного типа. Поэтому мой запрос преобразует session_id в символ для соответствия. Я получаю СРЕДНЕЕ запрос сначала как простой выбор из псевдонима WITH CTE, а ЗАТЕМ получение фактического идентификатора session_id и счетчиков.
with PaxSummary as
(
select
pxHits.*,
1 + lastHitNumber - firstPaxHit HitsIncludingPax
from
( select
session_id,
min( case when page = 'pax'
then hit_number
else null end ) firstPaxHit,
max( hit_number ) lastHitNumber
from
search
group by
session_id
having
min( case when page = 'pax'
then hit_number
else null end ) > 0 ) pxHits
)
select
'Avg Pax Pages' FinalMsg,
avg( ps2.HitsIncludingPax ) HitsIncludingPax
from
PaxSummary ps2
union all
select
cast( ps1.session_id as varchar) FinalMsg,
ps1.HitsIncludingPax
from
PaxSummary ps1
В качестве альтернативы шаблону EXISTS (correlated subquery)
мы можем написать запрос, который возвращает нам hit_number первого попадания «pax» для каждого session_id и использовать его как встроенное представление.
Что-то в этом роде:
-- count hits on or after the first 'pax' of each session_id that has a 'pax' hit
SELECT s.session_id
, COUNT(*) AS cnt_hits_after_pax
FROM ( -- get the first 'pax' hit for each session_id
-- exclude session_id that do not have a 'pax' hit
SELECT px.session_id AS pax_session_id
, MIN(px.hit_number) AS pax_hit_number
FROM search px
WHERE px.page = 'pax'
) p
-- all the hits for session_id on or after the first 'pax' hit
JOIN search s
ON s.session_id = p.session_id
AND s.hit_number >= p.hit_number
GROUP BY s.session_id
чтобы получить среднее значение из этого запроса, мы можем заключить его в скобки и превратить во встроенное представление.
SELECT AVG(c.cnt_hits_after_pax) AS avg_cnt_hits_after_pax
FROM (
-- query above goes here
) c
Пожалуйста, включите желаемые результаты для данных данных.