这是一个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。
#1 楼
TOP 1
选择与常量引用的唯一字段(
OwnerUserId
)相对应的值时,您实际上只是选择一个字段,这意味着多余。UVN
无法解释的变量名
不要使用诸如
Q
和A
之类的名称,它们充其量是令人困惑的,而在最坏的情况下是不可读的。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
评论
将SO中的代码片段混入程序中直到它起作用为止,这听起来像是程序员所做工作的非常简单的描述。希望您能在两者之间进行一些分析和调试。@SimonForsberg当然,当我写那封信时我并不完全认真