如何计算文章点击率（Click Through Rate）：首页跳转至文章行为的聚合统计方法咨询

看起来你已经搞定了单用户的访问记录查询，接下来咱们把逻辑扩展到全量用户，核心就是精准识别从首页跳转至其他文章的有效行为，再做聚合统计就行。下面分两种常见业务场景给你解决方案：

场景1：宽泛统计——包含首页的会话中所有后续文章访问 #

这种场景下，只要用户在同一次访问会话（userID+visitnum 组合代表一次完整访问）里先浏览了首页，之后不管隔了多少页面，访问的其他文章都算作从首页跳转来的点击。

WITH home_sessions AS (
    -- 第一步：筛选出所有访问过首页的用户会话
    SELECT userID, visitnum
    FROM table
    WHERE articleID = 'home_page'
),
article_clicks_from_home AS (
    -- 第二步：找出这些会话中所有非首页的文章访问记录
    SELECT t.articleID
    FROM table t
    JOIN home_sessions hs 
        ON t.userID = hs.userID 
        AND t.visitnum = hs.visitnum
    WHERE t.articleID != 'home_page'
),
home_total_sessions AS (
    -- 第三步：统计首页的总访问会话数（每个会话算一次首页访问）
    SELECT COUNT(DISTINCT CONCAT(userID, '-', visitnum)) AS total_home_sessions
    FROM table
    WHERE articleID = 'home_page'
)
-- 第四步：计算每个文章的点击率
SELECT 
    ac.articleID,
    COUNT(ac.articleID) AS total_clicks_from_home,
    hts.total_home_sessions,
    ROUND(COUNT(ac.articleID) / hts.total_home_sessions, 4) AS click_through_rate
FROM article_clicks_from_home ac
CROSS JOIN home_total_sessions hts
GROUP BY ac.articleID, hts.total_home_sessions
ORDER BY click_through_rate DESC;

场景2：精准统计——首页访问后紧接着的文章访问 #

如果业务上只把首页之后的下一个页面算作直接跳转，那可以调整关联条件，匹配visitpagenum刚好比首页大1的记录：

WITH home_page_records AS (
    -- 获取所有首页访问的详细记录
    SELECT userID, visitnum, visitpagenum
    FROM table
    WHERE articleID = 'home_page'
),
direct_clicks_after_home AS (
    -- 匹配首页之后紧接着的文章访问
    SELECT t.articleID
    FROM table t
    JOIN home_page_records hp 
        ON t.userID = hp.userID 
        AND t.visitnum = hp.visitnum
        AND t.visitpagenum = hp.visitpagenum + 1
),
home_total_views AS (
    SELECT COUNT(*) AS total_home_views
    FROM table
    WHERE articleID = 'home_page'
)
SELECT 
    dca.articleID,
    COUNT(dca.articleID) AS direct_click_count,
    htv.total_home_views,
    ROUND(COUNT(dca.articleID) / htv.total_home_views, 4) AS click_through_rate
FROM direct_clicks_after_home dca
CROSS JOIN home_total_views htv
GROUP BY dca.articleID, htv.total_home_views
ORDER BY click_through_rate DESC;

关键逻辑说明 #

• 用CTE（公共表表达式）拆分步骤，让SQL更易读也方便后续调整；
• 会话识别：userID+visitnum是判断跳转行为的核心维度，它代表用户的一次完整访问周期；
• 点击率定义：这里的点击率是「从首页跳转至该文章的次数」除以「首页的总访问次数/会话数」，你可以根据业务需求灵活调整分子分母的统计规则（比如是否需要对用户去重）。

内容的提问来源于stack exchange，提问作者CowboyCoder