关于

这是一个SEDE查询,它将计算您在网站上的活动和参与情况。它适用于“编程难题”和“代码高尔夫”,其中问题更多是值,但也可以修改值以适合一般的Q&A SE网站。

在线试用! />,所以我做了所有程序员的工作,然后我在Stack Overflow上查找了大部分内容,并将SO中的代码片段混入了我的程序中,直到它起作用为止。接受用户的用户ID的输入,并通过以下公式计算其得分:

1  * <# of Upvotes>
2  * <# of Downvotes>
10 * <# of Questions>
5  * <# of answers>
1  * <# of Comments>


<# of ...>乘以的实际值存储在变量中,您可以看到我ve声明。


然后在FROM中对Upvotes,Downvotes等进行计数。使用了最外面的SELECT,所以我也可以操作这些代码。

代码

DECLARE @UID int = ##UserId##

DECLARE @Up      int = 1
DECLARE @Down    int = 2
DECLARE @Q       int = 10
DECLARE @A       int = 5
DECLARE @Comment int = 1

SELECT Score = Upvotes   * @Up +
          Downvotes * @Down +
          Questions * @Q +
          Answers   * @A +
          Comments  * @Comment,
       Upvotes, Downvotes, Questions, Answers, Comments
FROM (
  SELECT TOP 1
    (
      SELECT UpVotes
      From Users
      WHERE Id = @UID
    ) as Upvotes,
    (
      SELECT DownVotes
      From Users
      WHERE Id = @UID
    ) as Downvotes,
    (
      SELECT COUNT(*)
      FROM Posts
      WHERE OwnerUserId = @UID and PostTypeId = 1
    ) as Questions,
    (
      SELECT COUNT(*)
      FROM Posts
      WHERE OwnerUserId = @UID and PostTypeId = 2
    ) as Answers,
    (
      SELECT COUNT(*)
      FROM Comments
      WHERE UserId = @UID
    ) as Comments
  FROM Users, Comments, Posts
) data


我希望对编写此程序的更好方法发表意见,如果我可以更好地重组它,格式化以及其他任何东西,因为我很喜欢SQL。

评论

将SO中的代码片段混入程序中直到它起作用为止,这听起来像是程序员所做工作的非常简单的描述。希望您能在两者之间进行一些分析和调试。

@SimonForsberg当然,当我写那封信时我并不完全认真

#1 楼

TOP 1

选择与常量引用的唯一字段(OwnerUserId)相对应的值时,您实际上只是选择一个字段,这意味着多余。


UVN

无法解释的变量名

不要使用诸如QA之类的名称,它们充其量是令人困惑的,而在最坏的情况下是不可读的。


DECLARE @Q
DECLARE @A



DECLARE @Questions
DECLARE @Answers



亲爱的,听说你喜欢SELECT s


SELECT --...
FROM (
  SELECT TOP 1
    (
      SELECT UpVotes
      From Users
      WHERE Id = @UID
    ) as Upvotes,



不能那样使用SQL。

您不需要创建伪表来选择来存储数据。

您可以使用基表,然后指定从中调用其他表的某些字段。

SELECT
  UpVotes + DownVotes as [Total Votes],
  (SELECT COUNT(*) FROM Posts WHERE OwnerUserId = Users.Id) as [Post Count]
FROM Users



格式化

这种格式有点奇怪:


SELECT Score = Upvotes   * @Up +
          Downvotes * @Down +
          Questions * @Q +
          Answers   * @A +
          Comments  * @Comment,



您可能想将它们分解一些。

命名

这些不是实际值,而是重新输入权重,因此我将单词权重添加到变量名称的末尾:


DECLARE @Up      int = 1
DECLARE @Down    int = 2
DECLARE @Q       int = 10
DECLARE @A       int = 5
DECLARE @Comment int = 1



到:

DECLARE @UpVotesWeight     int = 1
DECLARE @DownVotesWeight   int = 2
DECLARE @QuestionsWeight   int = 10
DECLARE @AnswersWeight     int = 5
DECLARE @CommentsWeight    int = 1


评论


\ $ \ begingroup \ $
“充其量是令人困惑的,最糟糕的是它不可读。” IMO,不可读和混乱的应该交换
\ $ \ endgroup \ $
–下山羊
16-2-26在4:11

\ $ \ begingroup \ $
最佳情况是混乱,最坏情况是完全不可读的
\ $ \ endgroup \ $
– Quill
16-2-26在4:12

#2 楼

对于评论来说,这太多了,加到@Quill的答案中:

在您的Select中,使用FROM Users, Comments, Posts,这是不受约束的交叉联接,即,每个表A的每一行都连接到表B的每一行而没有连接条件,有效地将答案集中每个表的行数相乘。

我刚刚尝试过,每个表的行数为:

 53968 posts
121357 comments
 36457 users


结果为53968 * 121,357 * 36,457 = 238,771,278,057,232行

当然,优化器不是那么愚蠢(并且有一个TOP 1),实际上创建了该行数,但是计划是检查“包含执行计划”选项并运行它)令人恐惧。

永远不要在生产系统上编写类似的查询。

将其更改为:

FROM (
  SELECT Upvotes, Downvotes,
    (
      SELECT COUNT(*)
      FROM Posts
      WHERE OwnerUserId = @UID and PostTypeId = 1
    ) as Questions,
    (
      SELECT COUNT(*)
      FROM Posts
      WHERE OwnerUserId = @UID and PostTypeId = 2
    ) as Answers,
    (
      SELECT COUNT(*)
      FROM Comments

    ) as Comments
  FROM Users -- no addtional tables
  WHERE Id = @UID
) data


查看修改后的查询并比较执行计划:-)

评论


\ $ \ begingroup \ $
交叉连接确实很不错,++!
\ $ \ endgroup \ $
– ran
16-2-26在12:14

#3 楼

加上已经说明的内容,您可以将子查询移到CTE中以提高可读性(这可能有助于您分出代码的每个步骤)。我们还可以将对Posts的请求合并为一个过程。我之所以要进行LEFT JOIN,是因为某些用户可能没有评论或帖子,并且出于相同原因将NULL处理放入计算中。

DECLARE @UID INT = ##UserId##

DECLARE @UpVoteWeight   INT = 5
DECLARE @DownVoteWeight INT = 8
DECLARE @QuestionWeight INT = 25
DECLARE @AnswerWeight   INT = 10
DECLARE @CommentWeight  INT = 5

;WITH agg_posts AS (
  SELECT 
      OwnerUserId AS UserId,
      SUM(IIF(PostTypeID=1,1,0)) AS Questions,
      SUM(IIF(PostTypeID=2,1,0)) AS Answers
  FROM 
      Posts
  GROUP BY
      OwnerUserID
)
, agg_comments AS (
  SELECT
      UserId,
      COUNT(1) AS Comments
  FROM
      Comments
  GROUP BY
      UserId
)
SELECT 
    u.Id AS UserId,
    ISNULL(u.Upvotes,0)   * @UpVoteWeight +
    ISNULL(u.Downvotes,0) * @DownVoteWeight +
    ISNULL(p.Questions,0) * @QuestionWeight +
    ISNULL(p.Answers,0)   * @AnswerWeight +
    ISNULL(c.Comments,0)  * @CommentWeight AS Score,
    ISNULL(u.Upvotes,0) AS Upvotes, 
    ISNULL(u.Downvotes,0) AS Downvotes, 
    ISNULL(p.Questions,0) AS Questions, 
    ISNULL(p.Answers,0) AS Answers, 
    ISNULL(c.Comments,0) AS Comments
FROM
    Users u
LEFT JOIN
    agg_posts p ON p.UserId = u.Id
LEFT JOIN
    agg_comments c ON c.UserId = u.Id
WHERE
    u.Id = @UID