問題是，我需要計算到達 pax 頁面（包括 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”之后開始計數的部分，我對此一無所知。
提前謝謝你:)
uj5u.com熱心網友回復：
我的方法使用 WITH CTE（通用表運算式）來預先宣告底層查詢基礎是什么，然后從中查詢和平均。
第一個前提沒有明確包含在您的樣本資料中。如果用戶在多個頁面之間來回跳轉并多次點擊 PAX 頁面會發生什么情況。您現在有多個 pax 頁面點擊。我假設您希望 FIRST 實體出現在這樣的 pax 頁面中，并且包括所有頁面點擊量。這個解決方案應該有助于解釋它。
讓我們看一下最終別名為“pxHits”的最里面的 from 子句。
我按會話 ID 分組并獲取 pax 頁面命中的第一個實體（如果沒有遇到此類 pax 頁面，則為 null），但還獲取每個會話的最高命中數。HAVING 子句將確保它只回傳那些回傳了 PAX 頁面的會話，而將所有其他會話排除在結果之外。
這將導致兩個條目傳遞到包含1 lastHitNumber - firstPaxHit計算的外部選擇。1 的原因是因為您至少點擊了一次頁面。但是，在您的會話 456 的場景中，第一個和最后一個點擊是第一頁，您需要它，因為 lastHitNumber - firstPaxHit 將凈為零。如果一個人有 25 個頁面點擊并到達第 26 頁的 pax 頁面，這將是正確的。您的結果仍然是 1 via 1 26 - 26 = 1 總頁面，包括 pax 頁面，而不是之前的 25。
您的另一個排位賽會話將是 146。第一個 pax 命中是 2，但他們繼續達到最高的頁面命中 4。所以 1 4 - 2 = 3 頁總頁數。
所以現在進入決賽。既然你可以看到事情是如何準備的，我們現在可以得到平均值。您不能混合/自動轉換不同的資料型別（session_id 與“總平均”的固定訊息。它們必須是相同的型別。所以我的查詢是將 session_id 轉換為字符以匹配。我碰巧得到了 AVERAGE首先查詢 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
uj5u.com熱心網友回復：
查找所有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
uj5u.com熱心網友回復：
作為該EXISTS (correlated subquery)模式的替代方案，我們可以撰寫一個查詢，獲取每個 session_id 的第一個“pax”命中的 hit_number，并將其用作行內視圖。
這些方面的東西：
-- 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/445197.html
上一篇：將每組的總和除以總計
下一篇：如何按原樣加入sql子查詢？