SEDE顶级赞助商

Question

我想看看这个网站的顶级赞助商--那些在他们不拥有的问题上支付了赏金的用户。

我从一个与赏金相关的现有查询开始，将详细信息选择到子查询中，然后按赞助商分组，最后使用这个查询，该查询与我开始使用的查询不再有任何共同之处：

select Sponsor,
       SponsorRep,
       round(sum(cast(BountyClose as float))/(SponsorRep + sum(cast(BountyOpen as float)))*100,2) PctSponsorRep,
       count(*) Bounties,
       sum(BountyOpen)/count(*) AvgBountyPaid,
       sum(BountyOpen) BountyPaid, 
       sum(BountyClose) BountyAwarded,
       avg(cast(DaysOpen as float)) AvgDaysOpen,
       max(BountyCloseDate) LastBountyClosed
from (
select 
       u.DisplayName Sponsor,
       u.Reputation SponsorRep,
       bo.BountyAmount BountyOpen,
       datediff(day, bo.CreationDate, bc.CreationDate) DaysOpen,
       bc.BountyAmount BountyClose,
       bc.CreationDate BountyCloseDate
from Posts q 
  inner join Votes bo on q.Id = bo.PostId 
                     and q.PostTypeId = 1 -- Questions
                     and bo.VoteTypeId = 8 -- BountyOpen
  inner join Users op on q.OwnerUserId = op.Id
  left join Posts a on a.ParentId = q.Id
                     and a.PostTypeId = 2 -- Answers
  left join Votes bc on a.Id = bc.PostId
                     and bc.VoteTypeId = 9 -- BountyClose
  inner join Users u on bo.UserId = u.Id -- bounty owner
where q.ClosedDate is null
  and bc.BountyAmount is not null
  and op.Id != u.Id
  ) subquery
group by Sponsor, SponsorRep
order by
  sum(BountyClose) desc,
  max(BountyCloseDate) desc

还有什么我可以做得更好的吗？

Answer 1

我看过这个查询/报告，从一开始我就觉得它一定遗漏了什么。我查看了SQL，但无法直接识别它，所以我想我将构建自己的查询，并查看它们之间的比较。我得到的结果完全不同..。:(

编辑:发现问题

你不能把两个外接连接起来..。考虑对缺少的Quentin选票的查询：

我们从替代查询和昆廷的概要文件中得知，他悬赏150英镑。这150不会出现在您的查询中。

下面是一个基本查询、这与您的查询相匹配，并且应该显示此赏金.。

但事实并非如此。但是，如果我们将别名a和别名bc上的最后外部联接转换为equi-联接，并使其成为with语句，它突然起作用了.

原因是我们需要两个外部连接，而第一个成功。第一个得到问题的答案(可能没有答案，所以我们需要外部连接)。第二个外联队寻找赏金票，奖金可能不会被授予。

我不知道为什么这不起作用，可能是SQLServer的错误吗？

第二次更新这个问题也解释了从200_success失踪的2赏金.因为他们的问题是有答案的，但答案却没有得到赏金。

这也解释了为什么一些提供但未获奖励的赏金正在工作，因为他们的问题没有答案。

替换查询此处

我把这份报告整理好了，BountifulII。显然，我的报告也可能有问题(把它作为一个问题？-周末挑战？)

请注意，我的报告中有一些奇怪的--firewall fix注释，请参见这个MSO问题 .;-)

我的报告做了一个更一般的过程，计算谁提供赏金，谁被授予赏金。它把奖励分开--奖励给那些被授予自己问题的人，以及那些授予任何问题的赏金)。在最后的报告中，它列出了Promotions和PromotedAmount。这些值应该与报表中的值相匹配.但他们没有。

下面是修改后的SQL：

WITH Bounties AS (
    SELECT
       UserID AS UserID,
       0 AS GetCount,
       0 AS GetAmount,
       COUNT(BountyAmount) AS GiveCount,
       SUM(BountyAmount) AS GiveAmount,
       SUM(case when Posts.OwnerUserId = Votes.UserId then 1 else 0 end) AS SelfCount,
       SUM(case when Posts.OwnerUserId = Votes.UserId then BountyAmount else 0 end) AS SelfAmount
    FROM Votes,
         Posts
    WHERE Votes.PostId = Posts.Id
      AND VoteTypeId = 8
    GROUP --firewall fix
    BY UserID
   UNION
    SELECT
       Posts.OwnerUserID AS UserID,
       COUNT(BountyAmount) AS GetCount,
       SUM(BountyAmount) AS GetAmount,
       0 AS GiveCount,
       0 AS GiveAmount,
       0 AS SelfCount,
       0 AS SelfAmount
    FROM Votes,
         Posts
    WHERE Votes.PostId = Posts.ID
      AND VoteTypeId = 9
    GROUP --firewall fix
    BY Posts.OwnerUserID
  ) 

SELECT UserID AS [User Link],
       SUM(GiveCount - SelfCount) AS Pomotions,
       SUM(GiveAmount - SelfAmount) AS PromotedAmount,
       SUM(GetCount) AS GetCount,
       SUM(GetAmount) AS GetAmount,
       SUM(GiveCount) AS GiveCount,
       SUM(GiveAmount) AS GiveAmount,
       SUM(SelfCount) AS SelfCount,
       SUM(SelfAmount) AS SelfAmount,
       SUM(GiveCount + GetCount) AS EventCount,
       SUM(GetAmount - GiveAmount) AS NetBenefit
FROM Bounties
GROUP -- firewall fix
BY UserID
ORDER BY PromotedAmount DESC, EventCount DESC, NetBenefit DESC

Answer 2

您不需要在op上加入Users，因为您可以直接比较问题中的用户ID。删除该联接并在where子句中替换

and op.Id != u.Id

使用

and q.OwnerUserId != bo.UserId

或者将其移动到Votes上的第一个联接。

Answer 3

我会改变一些事情。

这是：

inner join Votes bo on q.Id = bo.PostId 
                     and q.PostTypeId = 1 -- Questions
                     and bo.VoteTypeId = 8 -- BountyOpen

有些东西需要放在where语句中。ID上的联接是可以的，因为这就是表的关联方式，但其他两种情况和情况是用于过滤结果的。这应该在where声明中。

在where语句中

AND op.ID <> u.Id  

这应该在一个联接中，更具体地说是一个FULL OUTER JOIN，或者更好的一个LEFT OUTER JOIN (LEFT JOIN)。

注意事项

我之所以说最后一句，是因为我做了一些研究。

我看到了几个问题，他们在where子句中有一个<>，他们在问性能问题。

结果表明，SQL在where语句中使用<>或NOT IN子句的方式减缓了查询的速度，最好在可能的情况下在该列上尝试并使用OUTER JOIN或LEFT JOIN。

我在我的回答中更多地提到了这一点。

在答案中，我提到了对这个问题的堆栈溢出回答。

这在您的查询中可能有用，也可能无法使用，但这是一个很好的了解。