如果没有信誉上限，则用户总数代表MET ON META
前几名失去了信誉上限的百分比1000个用户
如果没有信誉上限，则是评价最高的用户
所有这些查询（还有其他查询）似乎都遭受着相同的两个缺陷...（...到现在为止，此问题中的查询....）
用户的实际声誉受复杂情况的影响，例如帖子被删除，迁移到网站上以及从网站上迁移网站，起始代表奖金等。
如果不知道投票发生的顺序，就无法计算一个人的声誉损失。
了解为什么迁移和删除职位是重要的是，您必须了解信誉上限每天都会发生。人们使自己的代表最大化的天数（对于非双向飞碟而言）要比删除事物的次数低得多（等等）。除了rep-max天以外，还有其他事情会影响您的声誉，这些事的投票记录在数据资源管理器中不可用。您的用户记录记录了您的总信誉，但是无法汇总所有信誉事件并重新创建。因此，试图弄清楚丢失多少rep的任何尝试都会短暂或长时间，这会导致诸如负损失声誉之类的有趣事情。
要了解订单为何重要，请考虑以下可怜的用户'Bob'：
幸运的一天，最终得到一个好的答案恰好是200个代表。
有人再次投票，他失去了10个代表上限。
有人投票失败，他输了2（198）
有人再次投票表决，他又输了2
现在他的代表是196，这一天结束了...抱歉，对您来说没有灰泥板，而您损失了10个代表
如果顺序不同，则可能是幸运的“比尔”：
幸运的一天，得到一个很好的答案，结果恰好是200 rep。
有人投票赞成，他再次输掉2（198）
，有人再次输掉了票，他又输了2（196）
有人再次输了票，他获得了4分，并且在上限上输了6个代表。
现在他的代表是200，这一天结束了...庆祝！
这些计算比其他方法更常见，但是，如果您想想当用户提供赏金时投票指示的含义……那是很大的事情。
计算每日销售代表的最准确方法是重新创建信誉事件的顺序。但是仍然存在一些问题：
没有办法找出人们何时拒绝答案，这是-1命中率。
没有办法找出答案。如果用户点击了rep-max，丢失了一些rep，然后删除了他获得声誉的答案，那么声誉就消失了。
因此，受实际计算上限的复杂性的启发，我将这个查询放在一起，它为特定用户或先前赢得过的每个用户重建信誉事件的进程灰泥板徽章。
要审查：
如何改进此代码：
光标众所周知效率低，但是在那里更好的方法吗？
是否有遗漏的边缘情况？
欢迎提出任何改进建议....（并随时派发查询并试一试！）
declare @userid as int = ##UserId:int?-1##
declare @userrep as int
declare @epoch as date = '1 jan 2099'
declare @pdate as date
declare @rdate as date
declare @rtime as datetime
declare @raction as varchar(10)
declare @rrep as int
declare @rcnt as int
declare @rtmp as int
declare @message as varchar(250)
declare @rollingrep as int
declare @rollingbonus as int
declare @rollingcap as int
;
create table #REPDAY (
UserId int not null,
Reputation int not null,
RepDate DATE not null,
CapLimited int not null,
UnCapped int not null,
Discarded int not null)
;
-- loop through all users who have earned mortarboard
-- which is an appoximate list of people who have lost rep.
-- or, if a userid is specified, use that user.
declare TOPUSERS cursor for
select Users.Id as UserId, Users.Reputation as Reputation
from Users, Badges
where @userid < 0
and badges.Name = 'Mortarboard'
and Badges.UserId = Users.id
UNION ALL
select Id, Reputation
from Users
where Id = @userid
open TOPUSERS
fetch next from TOPUSERS into @userid, @userrep
while @@FETCH_STATUS = 0
begin
-- for each user, reset the accumulators
select @pdate = @epoch -- something different
select @rcnt = 0
select @rollingrep = 0
select @rollingbonus = 0
select @rollingcap = 0
declare USERVOTES cursor for
-- NOTE: convert to DATE truncates the Time-part!!!
select convert(DATE, tstamp) as tdate, tstamp, action, rep
from (
--Approved suggested edits.
select se.ApprovalDate as tstamp, 'edit' as action, 2 as rep
from SuggestedEdits se
where se.OwnerUserId = @userid
and se.ApprovalDate is not null
UNION ALL
-- Up and Down votes on Q's and A's
select v.CreationDate as tstamp,
'invote' as action,
case when v.VoteTypeId = 1 then 15
when v.VoteTypeId = 2 then p.PostTypeId * 5 -- cheeky 5 or 10 for question/answer
when v.VoteTypeId = 3 then -2
when v.VoteTypeId = 8 then v.BountyAmount
else 0
end as rep
from Posts p, Votes v
where v.PostId = p.Id
and p.OwnerUserId = @userid
and p.PostTypeId in (1,2)
and v.VoteTypeId in (1,2,3,8)
UNION ALL
-- Bounties that were offered....
select v.CreationDate as tstamp, 'bounty' as action, -1 * v.BountyAmount as rep
from Votes v
where v.VoteTypeId = 9
and v.UserId = @userid
) as tdata
order by tstamp
open USERVOTES
fetch next from USERVOTES into @rdate, @rtime, @raction, @rrep
while @@FETCH_STATUS = 0
begin
if @pdate <> @rdate and @rcnt > 0
begin
-- break point, new day, save old day's data
insert into #repday
values (@userid, @userrep, @pdate, @rollingrep, @rollingbonus, @rollingcap)
-- reset our accumulators
select @rollingrep = 0, @rollingcap = 0, @rollingbonus = 0, @pdate = @rdate
end
select @rcnt = @rcnt + 1
if @rrep > 10 or @raction = 'bounty'
-- things that score > 10 (accept) are not capped - assume bonus never <= 10
select @rollingbonus = @rollingbonus + @rrep
else
-- things that score 10 or less are subject to cap
select @rollingrep = @rollingrep + @rrep
if @rollingrep > 200
begin
-- last action passed the cap... but how much past?
select @rtmp = @rollingrep - 200
-- set the value back to 200, and add the difference to the lost rep
select @rollingrep = 200,
@rollingcap = @rollingcap + @rtmp
select @message = 'Vote Maxed ' + Convert(Varchar(20), @rdate)
+ ' ' + @raction + ' rep ' + Convert(varchar(3), @rrep)
+ ' ' + Convert(varchar(3), @rtmp) + ' rep wasted'
print @message
end
fetch next from USERVOTES into @rdate, @rtime, @raction, @rrep
end
close USERVOTES
deallocate USERVOTES
-- save away the last day's values that were not break-processed
insert into #repday
values (@userid, @userrep, @rdate, @rollingrep, @rollingbonus, @rollingcap)
select @message = 'Replayed user ' + Convert(varchar(10), @userid)
+ ' with rep ' + Convert(varchar(10), @userrep)
+ ' and repcnt ' + Convert(varchar(10), @rcnt)
print @message
fetch next from TOPUSERS into @userid, @userrep
END
close TOPUSERS
deallocate TOPUSERS
;
with UserLost as (
select UserId as LostId,
sum(Discarded) as LostAmt
from #REPDAY
group by UserId
)
select UserId as [User Link],
Reputation as [Current Rep],
Convert(Varchar(12), RepDate, 107) as [Date],
CapLimited as [Actual Regular],
UnCapped as [Accepts Bonuses],
CapLimited + UnCapped as [Day Rep],
Discarded as [Lost Rep],
LostAmt as [Total Lost],
Convert(Decimal(8,2), 100.0 * (convert(real,LostAmt) / convert(real,Reputation + LostAmt)) ) as [Lost%]
from #REPDAY,
UserLost
where LostId = UserId
and ( CapLimited + UnCapped >= 200 -- days which count for Mortarboard
or Discarded > 0) -- days with lost rep
order by Reputation DESC, RepDate ASC
#1 楼
再次浏览此代码，似乎有一些地方可以改进。使用#Temp表进行用户选择
而不是进行廉价的UNION select要处理一组用户，正确的方法是创建一个临时表，然后有条件地填充它：
create table #TopUsers (
UserId int not null,
Reputation int not null)
if @userid < 0
begin
insert into #TopUsers
select Users.Id as UserId, Users.Reputation as Reputation
from Users, Badges
where badges.Name = 'Mortarboard'
and Badges.UserId = Users.id
end else begin
insert into #TopUsers
select Id, Reputation
from Users
where Id = @userid
end
declare TOPUSERS cursor for
select *
from #TopUsers
这种方法比较冗长，但是
显式联接和隐式联接JOIN
自SQL-92标准以来，显式联接已在SQL中可用。此问题中的代码使用旧版SQL-89标准的编码标准。
代码中使用的隐式连接语法在技术上没有错，但是它是老式的，并且使用显式的join语法使较大查询中的连接条件更加明显。例如，更改如下：
select Users.Id as UserId, Users.Reputation as Reputation
from Users, Badges
where @userid < 0
and badges.Name = 'Mortarboard'
and Badges.UserId = Users.id
将变为：
select Users.Id as UserId, Users.Reputation as Reputation
from Users
inner join Badges
on Badges.UserId = Users.id
where @userid < 0
and badges.Name = 'Mortarboard'