sql - 如何在 Stack Exchange 数据资源管理器中获取所有用户的帖子标签（包括答案​​标签）？

Question

我在Stack Exchange Data Explorer (SEDE) 中使用查询。

这是我的查询：

SELECT A.Id
       , A.PostTypeId
       , A.Title
       , A.Body
       , A.ParentId
       , A.Tags
       , A.CreationDate
FROM   posts A
       LEFT JOIN users U
              ON A.OwnerUserId = U.id
WHERE  U.Id = ##UserId##
       AND A.PostTypeId = 1
UNION
SELECT A.Id
       , A.PostTypeId
       , A.Title
       , A.Body
       , A.ParentId
       , B.Tags
       , A.CreationDate
FROM   posts A
       LEFT JOIN users U
              ON A.OwnerUserId = U.id
       RIGHT JOIN posts B
               ON A.ParentId = B.Id
WHERE  U.Id = ##UserId##
       AND A.PostTypeId = 2

在上面的代码中，Stack Overflow 中的帖子有两种类型：问题和答案。问题（PostTypeId在数据库模式中为 1）有标签，但答案（PostTypeId在数据库模式中为 2）没有标签。答案属于通过ParentId.

但是上面我的查询效率太低了，我只能得到一些（使用用户ID）帖子的标签。

如何在 SEDE 超时内获取所有用户的帖子标签？

标签： sqlsql-serversql-server-2017dataexplorer

Answer 1

几件事：

1. 您无法获取所有用户的帖子标签。 目前，有超过 4100 万个帖子，SEDE 将结果限制为 50,000 行。
2. 因此，您需要：(A)以某种方式限制结果或 (B)浏览4100 万个帖子，一次 5 万个（不推荐），或 (C) 改用数据转储或Google 的 BigQuery($)。
3. 如果您不打算从Users表中提取除 ID 之外的任何内容，则不要包含该表。它咀嚼周期并且Posts.OwnerUserId是同一回事。
4. UNION尽可能避免陈述（在这种情况下）。
5. 如果使用UNION语句，UNION ALL请尽可能使用（在这种情况下）。这使引擎不必进行重复检查。

所以，这里是原始查询的执行计划：

这是一个精简的计划：

以及与之对应的查询：

SELECT      TOP 50000
            p.OwnerUserId                                       AS [User]
            , p.Id                                              AS [Post Id]
            , CASE WHEN p.PostTypeId = 1 THEN 'Q' ELSE 'A' END  AS [Type]
            , COALESCE (p.Title, ownr.Title)                    AS [Title]
            , p.Body
            , COALESCE (p.Tags, ownr.Tags)                      AS [Tags]
            , p.CreationDate
FROM        Posts p
LEFT JOIN   Posts ownr      ON ownr.ID = p.ParentId
WHERE       p.OwnerUserId   = ##UserId##
AND         p.PostTypeId    IN (1, 2)  -- Question, answer
ORDER BY    p.OwnerUserId DESC,  p.CreationDate

-- 这也提供了更具可读性的结果 -- 尤其是在WHERE删除该子句时。

但是，如果您可以事先通过用户进行限制；你得到一个更有效的查询：

WITH usersOfInterest AS (
    SELECT      TOP 10
                u.Id                                                AS [UserId]
                , u.DisplayName
    FROM        Users u
    ORDER BY    u.Reputation DESC
)
SELECT      
            [User] = 'site://u/' + CAST(u.UserId AS NVARCHAR) + '|' + CAST(u.UserId AS NVARCHAR)
            , p.Id                                              AS [Post Id]
            , CASE WHEN p.PostTypeId = 1 THEN 'Q' ELSE 'A' END  AS [Type]
            , COALESCE (p.Title, ownr.Title)                    AS [Title]
            , p.Body
            , COALESCE (p.Tags, ownr.Tags)                      AS [Tags]
            , p.CreationDate
FROM        usersOfInterest u
INNER JOIN  Posts p         ON u.UserId = p.OwnerUserId
LEFT JOIN   Posts ownr      ON ownr.Id  = p.ParentId
WHERE       p.PostTypeId    IN (1, 2)   -- Question, answer
ORDER BY    u.UserId DESC,  p.CreationDate

（此查询为用户 ID添加了一个方便的超链接。）

请注意，只有前 10 名用户的帖子超过 50K。