MySQL Count после отображения определенного значения

Проблема в том, что мне нужно рассчитать среднее количество страниц/посещений после достижения страница (включая 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» начинается отсчет, и у меня нет для этого никакой подсказки.

Заранее благодарю :)

Пожалуйста, включите желаемые результаты для данных данных.

Stu 15.03.2022 20:10
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
0
1
48
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Ответ принят как подходящий

Поиск всех 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

Спасибо, это решение довольно прямолинейно и мне легко понять! :)

Gerard Prieto 16.03.2022 09:53

В моем подходе используется 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

Другие вопросы по теме