如何根据关联urls表的相同url值对keywords表关键词分组

核心思路 #

你直接使用GROUP BY无法得到正确结果的核心原因是：你需要先过滤出在urls表中出现次数超过3次的URL，再关联这些URL对应的关键词，最后按关键词维度分组。如果直接对keyword_id分组后统计URL数量，统计的是单个关键词对应的全部URL总数，和需求要求的「筛选重复出现的URL再匹配关键词」逻辑不符。

最优实现方案 #

优先使用CTE（公共表表达式）实现，可读性和执行效率都比较理想：

WITH repeated_urls AS (
    -- 第一步：筛选出出现次数超过3次的URL
    SELECT url
    FROM urls
    GROUP BY url
    HAVING COUNT(*) > 3
)
-- 第二步：关联得到对应关键词并按关键词分组
SELECT 
    k.id, 
    k.phrase, 
    k.volume, 
    k.difficulty, 
    COUNT(DISTINCT u.url) AS matched_repeated_url_num
FROM keywords k
INNER JOIN urls u 
    ON k.id = u.keyword_id
INNER JOIN repeated_urls ru 
    ON u.url = ru.url
GROUP BY k.id, k.phrase, k.volume, k.difficulty;

如果你的数据库不支持CTE，可以替换为子查询写法，执行效果完全一致：

SELECT 
    k.id, 
    k.phrase, 
    k.volume, 
    k.difficulty, 
    COUNT(DISTINCT u.url) AS matched_repeated_url_num
FROM keywords k
INNER JOIN urls u 
    ON k.id = u.keyword_id
INNER JOIN (
    SELECT url
    FROM urls
    GROUP BY url
    HAVING COUNT(*) > 3
) ru 
    ON u.url = ru.url
GROUP BY k.id, k.phrase, k.volume, k.difficulty;

简化场景实现 #

如果你不需要统计每个关键词匹配的重复URL数量，只需要获取去重后的符合要求的关键词列表，可以去掉外层分组逻辑，直接加DISTINCT即可：

SELECT DISTINCT k.*
FROM keywords k
INNER JOIN urls u 
    ON k.id = u.keyword_id
INNER JOIN (
    SELECT url FROM urls GROUP BY url HAVING COUNT(*) > 3
) ru 
    ON u.url = ru.url;

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