如何扩展Data Explorer查询，按指定标签统计发帖、回答及评论用户数？

没问题，我帮你把查询扩展一下，让它不仅统计发布含perl6、rakudo或同时含parrot+perl标签帖子的用户，还能把在这些帖子下回答或评论的用户也纳入统计，甚至可以区分用户的行为类型哦。

最终查询代码 #

WITH TargetPosts AS (
    -- 先筛选出所有符合标签条件的目标帖子ID，作为后续统计的基础范围
    SELECT Id
    FROM Posts
    WHERE Tags LIKE '%perl6%' 
       OR Tags LIKE '%rakudo%' 
       OR (Tags LIKE '%parrot%' AND Tags LIKE '%perl%')
),
PostAuthors AS (
    -- 统计发布目标帖子的用户及发帖数量
    SELECT 
        u.Id,
        u.DisplayName,
        '发帖' AS ActionType,
        COUNT(p.Id) AS ActionCount
    FROM Posts p
    JOIN Users u ON p.OwnerUserId = u.Id
    WHERE p.Id IN (SELECT Id FROM TargetPosts)
    GROUP BY u.Id, u.DisplayName
),
Answerers AS (
    -- 统计回答目标帖子的用户及回答数量（Stack Overflow中回答属于Posts表，通过ParentId关联主帖）
    SELECT 
        u.Id,
        u.DisplayName,
        '回答' AS ActionType,
        COUNT(a.Id) AS ActionCount
    FROM Posts a
    JOIN Users u ON a.OwnerUserId = u.Id
    WHERE a.ParentId IN (SELECT Id FROM TargetPosts)
    GROUP BY u.Id, u.DisplayName
),
Commenters AS (
    -- 统计在目标帖子下评论的用户及评论数量
    SELECT 
        u.Id,
        u.DisplayName,
        '评论' AS ActionType,
        COUNT(c.Id) AS ActionCount
    FROM Comments c
    JOIN Users u ON c.UserId = u.Id
    WHERE c.PostId IN (SELECT Id FROM TargetPosts)
    GROUP BY u.Id, u.DisplayName
)
-- 合并三类用户数据，按行为次数降序排序
SELECT 
    DisplayName AS Username,
    ActionType,
    ActionCount
FROM PostAuthors
UNION ALL
SELECT 
    DisplayName AS Username,
    ActionType,
    ActionCount
FROM Answerers
UNION ALL
SELECT 
    DisplayName AS Username,
    ActionType,
    ActionCount
FROM Commenters
ORDER BY ActionCount DESC, ActionType, Username;

代码说明 #

• TargetPosts：把标签筛选逻辑单独抽出来，避免重复编写，后续所有统计都基于这些目标帖子；
• PostAuthors：对应你原来的查询逻辑，统计发布相关帖子的用户；
• Answerers：关联回答数据（回答是Posts表中ParentId不为空的记录），统计参与回答的用户；
• Commenters：关联Comments表，统计参与评论的用户；
• 最后用UNION ALL合并三类用户的行为数据，按行为次数从多到少排序，方便查看活跃用户。

如果需要合并同一用户的不同行为（比如某用户既发过帖又回答过），可以用下面的调整版，把同一用户的所有行为次数合并，并展示其参与的行为类型：

-- 合并同一用户的所有行为统计
SELECT 
    DisplayName AS Username,
    SUM(ActionCount) AS TotalActions,
    STRING_AGG(ActionType, ', ') AS ActionTypes
FROM (
    SELECT * FROM PostAuthors
    UNION ALL
    SELECT * FROM Answerers
    UNION ALL
    SELECT * FROM Commenters
) AS AllUsers
GROUP BY DisplayName
ORDER BY TotalActions DESC;

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