编写SQL查询计算每日声量份额及Top3竞争对手分析

核心查询代码 #

WITH RECURSIVE date_range AS (
    -- 生成指定日期范围内的所有日期，完整覆盖:start到:end区间
    SELECT :start AS date
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < :end
),
daily_total_traffic AS (
    -- 统计每日所有网站的总流量，作为声量份额计算的分母
    SELECT 
        date,
        SUM(est_traffic) AS total_traffic
    FROM serp_analytics
    WHERE date BETWEEN :start AND :end
    GROUP BY date
),
top3_competitors AS (
    -- 基于:end日期的声量份额，筛选出排除youtube.com的Top3竞争对手
    SELECT website
    FROM (
        SELECT 
            website,
            (SUM(est_traffic) / (SELECT SUM(est_traffic) FROM serp_analytics WHERE date = :end)) * 100 AS volume_share,
            ROW_NUMBER() OVER (ORDER BY volume_share DESC) AS rank
        FROM serp_analytics
        WHERE date = :end AND website != 'youtube.com'
        GROUP BY website
    ) ranked_websites
    WHERE rank <= 3
),
target_websites AS (
    -- 合并需分析的目标网站：youtube.com + Top3竞争对手
    SELECT 'youtube.com' AS website
    UNION ALL
    SELECT website FROM top3_competitors
)
-- 生成最终结果：每日各目标网站的声量份额，无数据时显示0
SELECT 
    tw.website,
    dr.date,
    COALESCE(
        (SUM(sa.est_traffic) / dt.total_traffic) * 100,
        0
    ) AS volume_share
FROM date_range dr
CROSS JOIN target_websites tw
LEFT JOIN serp_analytics sa 
    ON dr.date = sa.date AND tw.website = sa.website
LEFT JOIN daily_total_traffic dt 
    ON dr.date = dt.date
GROUP BY tw.website, dr.date, dt.total_traffic
ORDER BY dr.date, tw.website;

关键逻辑说明 #

• 日期全覆盖：通过递归CTE生成:start到:end的所有日期，确保无数据的日期也能输出结果。
• 份额计算逻辑：用每日总流量作为分母，单网站当日流量总和作为分子，计算占比后乘以100得到声量份额，无数据时用COALESCE置为0。
• Top3竞争对手筛选：仅基于:end日期的声量份额排序，提取除youtube.com外的前3名网站，确保后续全日期分析包含这些竞争对手。
• 关联匹配：通过CROSS JOIN将所有日期与目标网站组合，再用LEFT JOIN关联流量数据，保证每个日期-网站组合都有对应记录。

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