sql - 我需要更改什么才能从 SEDE 中获取已删除、锁定的帖子的数量？

Question

我正在尝试查询一些关于有多少帖子因垃圾邮件/辱骂而被删除的统计信息。我得到了我认为应该起作用的东西，但它抛出的数字没有意义。

由于以这种方式删除的帖子具有被删除和锁定的特点，因此我通过查看 PostHistory 表来查询这些属性。

我的初始查询如下所示:

SELECT
  COUNT(DISTINCT ph0.PostId)
FROM
  PostHistory ph0
INNER JOIN
  PostHistory ph1
  ON
    ph0.PostId = ph1.PostId AND
    ph1.PostHistoryTypeId = 12
WHERE
  ph0.PostHistoryTypeId = 14

那个人的计数是 397，这没有意义。有at least 6485 posts在 Stack Overflow 上已被识别为垃圾邮件。因此，为了检查该查询，我使用了一个输出帖子正文的调试查询:

SELECT
  Body
FROM
  PostsWithDeleted
WHERE
  Id IN
    (SELECT
        DISTINCT ph0.PostId
      FROM
        PostHistory ph0
      INNER JOIN
        PostHistory ph1
        ON
          ph0.PostId = ph1.PostId AND
          ph1.PostHistoryTypeId = 12
      WHERE
        ph0.PostHistoryTypeId = 14)

输出普通邮件的正文不是垃圾邮件 - 当然不是我以前见过的那种。示例:

All I can say is that you need to subclass UIView and make it a delegate of UIGestureRecognizerDelegate and UICollectionViewDelegate, then in your UIView subclass, do the following, I can't give out anymore information on this because the code, although owned by myself, is proprietary to the point of probably enraging quite a few organizations that I've used this for, so here's the secret...

I'm looking for an application or a social wall plugin to be added to a project. After looking at Wordpress and finally sifting through all the plugins (maybe all), I have come to a conclusion the plugins are not giving me enough customization options. For example, customizing the registration form. I need to add javascript for a combo box in order to display different options depend

I added a movieclip here, and a number. To get an effect, like a star with a number...

The message is "starCount is not a child of a caller". I don't know...

那么，我做错了什么意味着我没有选择已删除、锁定的帖子，我需要做什么来修复它？

Answer 1

尽管您的查询输出与我的完全相同，但我相信我的尝试可以清楚地说明您要实现的目标。

如果您发现这没有为您提供所需的输出，则必须有更多的逻辑来查找您提到的这些帖子。

下面的查询返回了一些帖子，这些帖子在其历史记录中至少被标记为锁定和删除一次。

SELECT COUNT(*)
FROM (
  SELECT
    ph.PostId
  FROM
    PostHistory ph
    INNER JOIN PostHistoryTypes pht ON
      ph.PostHistoryTypeId = pht.id
  WHERE
    pht.Name IN ('Post Locked', 'Post Deleted')
  GROUP BY ph.PostId
  HAVING COUNT(DISTINCT ph.PostHistoryTypeId) >= 2
  ) foo

您已包含以下消息，您的查询似乎没问题。

Since posts deleted this way are characterized by being both deleted and locked, I'm querying for those attributes by looking at the PostHistory table.