如何关联WITH子句结果并添加全局最频繁水果列

你的问题出在两个核心点：

1. ranking CTE 用 LIMIT 1 是全局限制，仅返回一条数据，关联后主查询直接引用 ranking.fruit 会违反SQL分组规则（非聚合列未加入GROUP BY），导致无数据返回或报错。
2. 原逻辑无法适配多用户场景，且LIMIT 1的全局限制方式不够严谨，不能准确匹配每个用户的全局最频繁水果。

改用窗口函数ROW_NUMBER()为每个用户的水果按全局食用次数排序，精准筛选出每个用户的最频繁水果，再与主查询关联：

WITH user AS (
    -- 你的原表生成逻辑，保持不变
),
ranked_fruits AS (
    SELECT
        user_id,
        fruit,
        -- 按用户分组，水果食用次数倒序排序，取排名第一的记录
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC) AS rn
    FROM user
    GROUP BY user_id, fruit
)
SELECT
    u.user_id,
    u.year_month,
    STRING_AGG(DISTINCT u.fruit, ', ') AS fruits,
    -- 提取当前用户的全局最频繁水果
    MAX(rf.fruit) AS most_frequent
FROM user u
LEFT JOIN ranked_fruits rf 
    ON u.user_id = rf.user_id 
    AND rf.rn = 1 -- 仅关联排名第一的水果记录
WHERE u.user_id = 1
GROUP BY u.user_id, u.year_month
ORDER BY u.year_month;

• ranked_fruits CTE：通过PARTITION BY user_id确保每个用户独立计算最频繁水果，ROW_NUMBER()为每个用户的水果按食用计数倒序标记排名，rn=1就是该用户全局最常吃的水果。
• 主查询关联时，rf.rn=1的条件确保只匹配每个用户的最频繁水果记录，避免关联多条冗余数据。
• 使用MAX(rf.fruit)是为了适配SQL分组规则：同一个用户的rf.fruit是唯一值，聚合后结果不变，同时避免非聚合列未加入GROUP BY的报错。

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