sql - 加权平均死锁 : Value depending on value

Question

一些背景有助于首先解释这个难题:

我有一个数据库，通过将用户提交的值与全局平均值进行比较来估计用户的可靠性。值介于 0 和 1 之间。所以，在哪里:

• 该特定用户的可靠性 = r
• 此特定用户提交值的平均值 = a
• 全局，“商定”平均值 = g

可靠性:

r = 1 - ABS(g - a)

每个用户的可靠性是这样计算的。现在，“商定”的全局平均值 g 是使用加权平均值计算的，其中权重为 r，值为 a。如果总共有3个用户:

  g = ((r1 * a1) + (r2 * a2) + (r3 * a3)) / (r1 + r2 + r3)

问题是，一旦用户有了很高的可信度，他们就完全垄断了，没有新的值(value)观可以改变这一点。举个例子:

g was initially 0.5
user1 r was initially 0.5
user2 r was initially 0.5
user3 r was initially 0.5

现在，他们将一个一个地提交值，然后观察会发生什么:

user1 a is submitted, 1.0
user1 reliability goes slightly down because it differs from g (0.5)
user2 a is submitted, 1.0
user1 and user2 reliability go up to 100%, g is now 1.0.
user3 a is submitted, 0.0
user3 reliability goes down to 0%. g is still 1.0.

由于 user3 的可靠性非常低，因此权重对 g 没有任何影响。 User3 的可靠性下降，因为提交的值与全局平均值完全不同。怎样做才能使 user3 的提交对最终值产生一些影响？也许我需要添加一些常数，以便可靠性永远不会完全为零(但接近)？

现在，对于 SQL 代码。我添加了一个演示问题的 SQL fiddle : http://sqlfiddle.com/#!3/d3fd1/21我已经抽象了代码以使其尽可能短，但它仍然很长。

表创建、存储过程和触发器:

-- Stores user info
CREATE TABLE dbo.Users(
    [UserID] [int] NOT NULL,
    [Reliability] [float] NOT NULL
  )

-- Contains global averages from all users who submitted data
CREATE TABLE dbo.GlobalSubmission(
    GlobalSubmissionID  [int] NOT NULL,
    Name [varchar](50) NULL,
    GlobalAverage [float] NOT NULL,
)

CREATE TABLE dbo.UserSubmission(
    SubValue float NOT NULL,
    GlobalSubmissionID int NOT NULL,
    UserID int NOT NULL,
)


GO

--Calculate the "ideal value", used for GlobalSubmission.
CREATE FUNCTION dbo.IdealValueCalc(@globalSubmissionID INT)
RETURNS int
AS
BEGIN

DECLARE @tmpReliability TABLE (SubValue float, Reliability float)


INSERT INTO @tmpReliability
    SELECT AVG(us.SubValue) as SubValue, usr.Reliability Reliability FROM UserSubmission us
    JOIN Users usr 
    ON us.UserID = usr.UserID
    WHERE GlobalSubmissionID = @GlobalSubmissionID
    GROUP BY us.UserID, usr.Reliability

--Perform weighted mean calculations.
Return (SELECT SUM(SubValue * Reliability) / SUM(Reliability) FROM @tmpReliability)
END
go


--Calculate the reliability of one user.
CREATE FUNCTION dbo.GetReliabilityForUser
(@userID int)
Returns Float
AS BEGIN
Return (SELECT 1 - AVG(ABS(db.userAvg - db.GlobalAverage))
    FROM (
      SELECT pmd.UserID,
            gs.GlobalAverage, 
            AVG(pmd.SubValue) as userAvg
      FROM UserSubmission pmd
      -- Joins average value for each user with "ideal" value from GlobalSubmission
      JOIN GlobalSubmission gs 
        ON gs.GlobalSubmissionID = pmd.GlobalSubmissionID
        WHERE pmd.UserID = 1
      GROUP BY pmd.UserID, gs.GlobalSubmissionID, gs.GlobalAverage
     ) db
     GROUP BY db.UserID)
End
go



CREATE TRIGGER trg_SubmissionComputation
ON UserSubmission 
AFTER INSERT, UPDATE
AS BEGIN
    --Calculate this uer's reliability
    DECLARE @userID int = (SELECT TOP(1) UserID FROM inserted)
    DECLARE @userReliability float = dbo.GetReliabilityForUser(@userID)

    UPDATE Users
    SET Reliability=@userReliability
    WHERE UserID = @userID

    --Recalculate globalSubmission values:
    DECLARE @globalSubmissionID int = (SELECT TOP(1) GlobalSubmissionID FROM inserted)
    DECLARE @globalAverage float = dbo.IdealValueCalc(@globalSubmissionID)
        --The global average for this set of submissions has been recalculated. Now inserting:

    UPDATE GlobalSubmission
    SET GlobalAverage = @globalAverage 
    WHERE GlobalSubmissionID = @globalSubmissionID
END
GO

测试它:

--Creating 3 new users
INSERT INTO Users
(UserID, Reliability)
values 
(1, 0.5),
(2, 0.5),
(3, 0.5)
GO

--Creating a new GlobalSubmission
INSERT INTO GlobalSubmission
(GlobalSubmissionID, NAME, GlobalAverage)
values (1, 'BOILER2B' , 0.5)
GO

--First, we will submit values of 1 for two users:
INSERT INTO UserSubmission values (1.0, 1, 1); -- Value: 1.0, User 1, Submission 1
GO
INSERT INTO UserSubmission values (1.0, 1, 2); -- Value: 1.0, User 2, Submission 1
GO
INSERT INTO UserSubmission values (1.0, 1, 1); -- Value: 1.0, User 1, Submission 1
GO
INSERT INTO UserSubmission values (1.0, 1, 2); -- Value: 1.0, User 2, Submission 1
GO


--Now, we will submit values of 0 for the third user:
INSERT INTO UserSubmission values (0.0, 1, 3); -- Value: 0.0, User 3, Submission 1
GO
INSERT INTO UserSubmission values (0.0, 1, 3); -- Value: 0.0, User 3, Submission 1
GO

SELECT * FROM Users -- This results in 0% reliability for the last user.

--If we create new users and add them, the reliability won't budge:
INSERT INTO Users
(UserID, Reliability)
values 
(4, 0.5),
(5, 0.5),
(6, 0.5),
(7, 0.5),
(8, 0.5)
GO


INSERT INTO UserSubmission values (0, 1, 4); -- Value: 0, User 4, Submission 1
GO
INSERT INTO UserSubmission values (0, 1, 5); -- Value: 0, User 5, Submission 1
GO
INSERT INTO UserSubmission values (0, 1, 6); -- Value: 0, User 6, Submission 1
GO
INSERT INTO UserSubmission values (0, 1, 7); -- Value: 0, User 7, Submission 1
GO
INSERT INTO UserSubmission values (0, 1, 8); -- Value: 0, User 8, Submission 1
GO
INSERT INTO UserSubmission values (0, 1, 4); -- Value: 0, User 4, Submission 1
GO
INSERT INTO UserSubmission values (0, 1, 5); -- Value: 0, User 5, Submission 1
GO
INSERT INTO UserSubmission values (0, 1, 6); -- Value: 0, User 6, Submission 1
GO
INSERT INTO UserSubmission values (0, 1, 7); -- Value: 0, User 7, Submission 1
GO
INSERT INTO UserSubmission values (0, 1, 8); -- Value: 0, User 8, Submission 1
GO


SELECT * FROM Users -- Even though we've added loads of new users suggesting 0 as value, the final value
-- is remaining 1.0, because when a new value (0) is submitted, it varies too much from the global average
--(1), causing the reliability of that user to go down, and that user ends up making no influence on the
-- global average!

Answer 1

这是一个替代估计，它仍然有点临时但不会产生权重 0。

1) 为每个用户生成一个指数衰减的平方误差估计。从一个可调的任意估计 K 开始。然后每次用户产生一个值 a 并且组均值为 g 时产生一个平方误差 E = (a - g) * (a - g) 并将平方误差的估计从之前更改为after = before * x + E * (1 - x) 其中 x 是另一个介于 0 和 1 之间的可调常数，它调整旧估计衰减的速度。这个估计值永远不会完全降到零，但由于下一步的原因，最好不要让它下降到某个可调值以下。

2) 要获得新的全局估计值，请像以前一样使用加权平均值，但使权重成为该用户当前平方误差估计值的倒数。

如果所有用户都是无偏的，那么指数衰减的估计最终可能会成为每个用户平均平方误差的合理估计，然后权重将是估计的线性组合，它可以最小化全局估计的预期平方误差。检查:如果不同用户 i 从同一来源提交了 Ni 估计值的平均值，那么每个用户估计值的均方误差将为 1/Ni，因此乘以它的倒数会将他们的平均值变成每个用户产生的原始估计值之和用户和加权估计最终只会合并估计。