如何在sconsole表中查询同site_id下每月新增的Google Search Console关键词？

嘿，我来帮你解决这个问题。首先得明确你要的「每月新增关键词」是两种情况里的哪一种：是该关键词在整个数据历史里首次出现在当月，还是上月同site_id下没有、本月刚出现的关键词？我两种情况都给你写方案，你可以按需选择。

先假设你的表sconsole里有site_id、query、date（存储每条数据的日期）这几个核心字段——如果你的日期是拆成year和month字段的，稍微调整下日期处理的部分就行。

情况1：找首次出现的新增关键词（全历史首次出现） #

这个思路是先找出每个site_id+query组合的首次出现月份，再筛选出首次月份等于当月的记录，就是当月的新增：

WITH keyword_first_seen AS (
    SELECT 
        site_id,
        query,
        DATE_TRUNC('month', MIN(date)) AS first_seen_month -- 提取首次出现的月份
    FROM sconsole
    GROUP BY site_id, query
)
SELECT 
    first_seen_month AS month,
    site_id,
    query
FROM keyword_first_seen
ORDER BY month, site_id, query;

情况2：找相对于上月的新增关键词（上月无、本月有） #

如果你的需求是每个月和紧挨着的上月对比，找出上月没出现过的关键词，用这个方案：

WITH monthly_distinct_keywords AS (
    -- 先去重：每个月每个site下的唯一关键词（GSC数据可能每天重复，必须去重）
    SELECT 
        site_id,
        query,
        DATE_TRUNC('month', date) AS record_month
    FROM sconsole
    GROUP BY site_id, query, DATE_TRUNC('month', date)
),
prev_month_mapping AS (
    -- 把上月的关键词映射到本月，方便后续对比
    SELECT 
        site_id,
        query,
        record_month + INTERVAL '1 month' AS current_month
    FROM monthly_distinct_keywords
)
SELECT 
    mdk.record_month AS month,
    mdk.site_id,
    mdk.query
FROM monthly_distinct_keywords mdk
LEFT JOIN prev_month_mapping pmm
    ON mdk.site_id = pmm.site_id
    AND mdk.query = pmm.query
    AND mdk.record_month = pmm.current_month
WHERE pmm.query IS NULL; -- 本月有、上月无的就是新增

为什么你之前的查询返回零结果？ #

大概率是这几个原因：

• 数据本身无新增：比如所有关键词每个月都存在，自然查不到新增；
• 日期处理不一致：比如你提取年月的方式混乱（一会用字符串拼接YEAR(date)||'-'||MONTH(date)，一会用DATE_TRUNC），导致月份匹配不上；
• 未去重：GSC数据可能每天都有同关键词的记录，直接用原始表join会因为重复记录导致错误匹配；
• 月份断档：如果数据里缺了某几个月的记录（比如只有1月和3月），那3月对比2月时，2月无数据会返回所有3月关键词——如果这不是你想要的，用下面的方案处理不连续月份：

WITH monthly_distinct_keywords AS (
    SELECT 
        site_id,
        query,
        DATE_TRUNC('month', date) AS record_month,
        -- 找出每个site+query的上一个出现月份
        LAG(DATE_TRUNC('month', date)) OVER (PARTITION BY site_id, query ORDER BY DATE_TRUNC('month', date)) AS last_seen_month
    FROM sconsole
    GROUP BY site_id, query, DATE_TRUNC('month', date)
)
SELECT 
    record_month AS month,
    site_id,
    query
FROM monthly_distinct_keywords
WHERE 
    last_seen_month IS NULL -- 首次出现的关键词
    OR last_seen_month <> record_month - INTERVAL '1 month'; -- 上一次出现不是上月，即相对于上月新增

简便实现的小技巧 #

• 用CTE把逻辑拆成小块，可读性强还容易调试；
• 必须先对每月的关键词去重，避免重复数据干扰；
• 所有日期处理用统一函数（比如DATE_TRUNC），别混用字符串拼接，防止格式错误；
• 上面的SQL都是一次性批量处理所有月份，不需要手动逐月迭代，跑一次就能拿到所有月份的新增结果。

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