{"id":301,"date":"2007-08-29T00:00:00","date_gmt":"2007-08-24T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-puzzle-of-rating-decomposition\/"},"modified":"2021-09-29T16:22:16","modified_gmt":"2021-09-29T16:22:16","slug":"the-puzzle-of-rating-decomposition","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/the-puzzle-of-rating-decomposition\/","title":{"rendered":"The Puzzle of ‘Rating Decomposition’"},"content":{"rendered":"

When you are planning to buy a camera or to read an article, you will always face a dilemma: how will you find the best product from the available products on the market or what article should you pick for reading from a variety of different electronic journals?<\/p>\n

Usually, in order to make an informed choice, people start looking for additional information about the product, such as consumer reviews or ratings. This strategy works pretty well and gives the person an idea about the product’s quality. However, the average rating can be ambiguous and can indicate slightly different things.<\/p>\n

For instance, if a camera has a rating of 4.15 out of 5 and has a sufficient number of votes, you might suppose that most people think that the quality of the camera is above average, but not excellent. However, it might be that most people gave camera the highest rating, but a few people found the location of the control buttons inconvenient and too dissimilar to their old cameras and gave the camera a very low mark, so dragging down the average rating.<\/p>\n

An average rating on its own does not give you the full information about the product. If you knew how many points each separate voter gave to the camera, the information could be more precise and your decision of what type to buy could be much easier.<\/p>\n

But, how do you get that information, if you only know the average rating and the number of votes? Well, you might be surprised to learn that you can figure it out using SQL. <\/code><\/p>\n

Before we start, create and load an auxiliary table that stores only sequential numbers (see Listing1):<\/p>\n

Listing1. Create and Load an Auxiliary Table<\/b><\/p>\n

\n

SET NOCOUNT ON;
DECLARE    @i INT, @maxi INT;
SELECT @i = 0, @maxi = 1000;
IF EXISTS(SELECT * FROM sysobjects 
   WHERE ID = (OBJECT_ID(‘dbo.sequence’)) AND xtype = ’U’) 
DROP TABLE dbo.sequence;
CREATE TABLE dbo.sequence(num INT NOT NULL);
WHILE(@i <= @maxi) 
   BEGIN
      INSERT INTO dbo.sequence VALUES(@i);
      SET @i = @i + 1;        
   END<\/p>\n<\/div>\n

Step1: Build Subsets of Possible Marks <\/h4>\n

We need to build a framework that will help us to find and examine the possible readers’ votes. Our first step will be to find the subsets of possible marks<\/p>\n

Let’s start with the number of votes, which on its own can give you a lot of information.<\/p>\n

For example, if 15 readers voted for the article, then any of the following predicates can be true:<\/p>\n

    \n
  • 0 readers rated the article 1 out of 5; <\/li>\n
  • 1 reader rated the article 1 out of 5; <\/li>\n
  • 2 readers rated the article 1 out of 5; <\/li>\n
  • . . . . . . . . . . . . . . . . . ; <\/li>\n
  • 15 readers rated the article 1 out 5; <\/li>\n
  • 0 readers rated the article 2 out of 5; <\/li>\n
  • 1 reader rated the article 2 out of 5; <\/li>\n
  • 2 readers rated the article 2 out of 5; <\/li>\n
  • . . . . . . . . . . . . . . . . . ; <\/li>\n
  • 15 readers rated the article 2 out of 5; <\/li>\n
  • 0 readers rated the article 3 out of 5; <\/li>\n
  • . . . . . . . . . . . . . . . . . ; <\/li>\n<\/ul>\n

    And so on.<\/p>\n

    If you translate this logic into T-SQL, you will get five subsets of possible marks. The code in Listing2 shows haw you can build the subsets;<\/p>\n

    Listing2. Build Subsets of Possible Marks<\/b><\/p>\n

    \n

    IF EXISTS(SELECT * FROM sysobjects 
       WHERE ID = (OBJECT_ID(‘spu_createVoteTables’)) AND xtype = ’P’) 
    DROP PROCEDURE spu_createVoteTables;
    GO
    CREATE PROCEDURE spu_createVoteTables  
       @numVotes INT
    AS
    – create and load table for OneS
    IF EXISTS(SELECT * FROM sysobjects 
                 WHERE ID = (OBJECT_ID(‘dbo.OneS’)) AND xtype = ’U’) 
    DROP TABLE dbo.OneS;
    CREATE TABLE dbo.OneS
       (point INT NOT NULL,
           votes INT NOT NULL,
           sumPoints dec(8,4) NOT NULL); <\/p>\n

    INSERT INTO dbo.OneS 
    SELECT 1, num, 1*num FROM sequence WHERE num <= @numVotes;
    SELECT * FROM dbo.OneS; <\/p>\n

    – create and load table for TwoS
    IF EXISTS(SELECT * FROM sysobjects 
                 WHERE ID = (OBJECT_ID(‘dbo.TwoS’)) AND xtype = ’U’) 
    DROP TABLE dbo.TwoS;
    CREATE TABLE dbo.TwoS
         (point INT NOT NULL,
         votes INT NOT NULL,
         sumPoints dec(8,4) NOT NULL); <\/p>\n

    INSERT INTO dbo.TwoS 
    SELECT 2, num, 2*num FROM sequence WHERE num <= @numVotes;
    SELECT * FROM dbo.TwoS <\/p>\n

    – create and load table for ThreeS
    IF EXISTS(SELECT * FROM sysobjects 
       WHERE ID = (OBJECT_ID(‘dbo.ThreeS’))
       AND xtype = ’U’) 
    DROP TABLE dbo.ThreeS;
    CREATE TABLE dbo.ThreeS
       (point INT NOT NULL,
         votes INT NOT NULL,
         sumPoints dec(8,4) NOT NULL); <\/p>\n

    INSERT INTO dbo.ThreeS 
    SELECT 3, num, 3*num FROM sequence WHERE num <= @numVotes;
    SELECT * FROM dbo.ThreeS <\/p>\n

    – create and load table for FourS
    IF EXISTS(SELECT * FROM sysobjects 
                 WHERE ID = (OBJECT_ID(‘dbo.FourS’)) AND xtype = ’U’) 
    DROP TABLE dbo.FourS;
    CREATE TABLE dbo.FourS
       (point INT NOT NULL,
           votes INT NOT NULL,
           sumPoints dec(8,4) NOT NULL); <\/p>\n

    INSERT INTO dbo.FourS 
    SELECT 4, num, 4*num FROM sequence WHERE num <= @numVotes;
    SELECT * FROM dbo.FourS <\/p>\n

    – create and load table for FiveS
    IF EXISTS(SELECT * FROM sysobjects 
                 WHERE ID = (OBJECT_ID(‘dbo.FiveS’)) AND xtype = ’U’) 
    DROP TABLE dbo.FiveS;
    CREATE TABLE dbo.FiveS
       (point INT NOT NULL,
           votes INT NOT NULL,
           sumPoints dec(8,4) NOT NULL); <\/p>\n

    INSERT INTO dbo.FiveS 
    SELECT 5, num, 5*num FROM sequence WHERE num <= @numVotes;
    SELECT * FROM dbo.FiveS
    GO
    EXEC spu_createVoteTables @numVotes = 15<\/p>\n<\/div>\n

    The results look like this:<\/p>\n

    \n

    point       votes       sumPoints
    ———– ———– ————-
    1           0           0.0000
    1           1           1.0000
    1           2           2.0000
    .  .  .  .  .  .  .  .  .  .  .
    1           15          15.0000<\/p>\n

    point       votes       sumPoints
    ———– ———– ————-
    2           0           0.0000
    .  .  .  .  .  .  .  .  .  .  .
    2           15          30.0000<\/p>\n

    point       votes       sumPoints
    ———– ———– ————-
    3           0           0.0000
    .  .  .  .  .  .  .  .  .  .  .
    3           15          45.0000<\/p>\n

    point       votes       sumPoints
    ———– ———– ————-
    4           0           0.0000
    4           1           4.0000
    .  .  .  .  .  .  .  .  .  .  .
    4           15          60.0000<\/p>\n

    point       votes       sumPoints
    ———– ———– ————-
    5           0           0.0000
    5           1           5.0000
    .  .  .  .  .  .  .  .  .  .  .
    5           14          70.0000
    5           15          75.0000<\/p>\n<\/div>\n

    Notice that the number of generated sets (tables) is equal to five and the number of rows in each table is equal to 15.<\/p>\n

    In general, the number of sets (tables) is equal to a base of the voting system (for the base-5 voting system, the number of tables will be five). The number of rows in each table is equal to the number of votes. <\/p>\n

    Since each magazine, electronic journal or consumers’ website has its own voting system and that system can change very rarely, the number of the tables will be a static component of our framework. Each time when we analyze the new rating, we will reload these tables. Each time the number of rows in the tables will be equal to the number of votes that produced the rating.<\/p>\n

    Throughout the rest of this article, we will consider the base-5 voting system only.<\/p>\n

    Step2: Find Possible Total Points <\/h4>\n

    Generally, calculating averages is a very simple task. All you need to do is to find the sum of all attends and then divide that total by the number of attends. The result of division then needs to be rounded to a whole number or to one (two) digits after the decimal point.<\/p>\n

    Our task, however, is different. We will try the inverse operation: calculating the possible sum total(s) for a known average and known number of attends.<\/p>\n

    Consider the next example:<\/p>\n

    You are going to read an article that has a rating of 4 when the number of votes is 15. Therefore, the total score will be 4*15=60. However, 60 is not the only possible total. Taking any number between 53 and 67, dividing by 15, and then rounding the result to a whole number, will give an average of 4.<\/p>\n

    Therefore, the inverse operation brings multiple results and, as you will see later, depends on the precision that was used to calculate the average.<\/p>\n

    The following script (Listing3) shows how to find all of the possible totals for the given rating and number of votes. <\/p>\n

    Listing3. Find Possible Total Points<\/p>\n

    \n

    IF EXISTS(SELECT * FROM sysobjects 
       WHERE ID = (OBJECT_ID(‘spu_findPossibleTotals’)) AND xtype = ’P’) 
    DROP PROCEDURE spu_findPossibleTotals;
    GO
    CREATE PROCEDURE spu_findPossibleTotals  
       @rating dec(8,4), 
       @numVotes dec(8,4),
       @precision INT
    AS
    SET NOCOUNT ON;
    DECLARE @calcPoints dec(8,4), @calcRating dec(8,4); <\/p>\n

    IF EXISTS(SELECT * FROM sysobjects 
       WHERE ID = (OBJECT_ID(‘possiblePoints’)) AND xtype = ’U’) 
    DROP TABLE possiblePoints;
    CREATE TABLE possiblePoints
       (TotalPoints dec(8,4) NOT NULL,
         calcRaiting dec(8,4) NOT NULL,
         roundedRating dec(8,4) NOT NULL) <\/p>\n

    SELECT @calcPoints = ROUND(@rating * @numVotes, 0);
    SELECT @calcRating = @rating
    WHILE(ROUND(@calcRating,@precision) = @rating)
    BEGIN
       INSERT INTO possiblePoints 
           VALUES(@calcPoints, @calcRating, ROUND(@calcRating,@precision));
       SELECT @calcPoints = @calcPoints + 1;
       SELECT @calcRating = @calcPoints\/@numVotes;
    END <\/p>\n

    SELECT @calcPoints = MIN(TotalPoints) - 1 FROM possiblePoints
    SELECT @calcRating = @calcPoints\/@numVotes
    WHILE(ROUND(@calcRating,@precision) = @rating)
    BEGIN
       INSERT INTO possiblePoints 
           VALUES(@calcPoints, @calcRating, ROUND(@calcRating,@precision));
       SELECT @calcPoints = @calcPoints - 1;
       SELECT @calcRating = @calcPoints\/@numVotes;
    END
    SELECT * FROM possiblePoints ORDER BY TotalPoints
    GO<\/p>\n<\/div>\n

    If you test the procedure for @rating = 4, @numVotes = 15 and @precision = 0, where precision is the number of digits after the decimal point, you will get the next result:<\/p>\n

    \n

    EXEC spu_findPossibleTotals @rating = 4, @numVotes = 15, @precision = 0  <\/p>\n

    TotalPoints     calcRaiting       roundedRating
    ———–     ———–       ————-
    53.0000         3.5333            4.0000
    54.0000         3.6000            4.0000
    55.0000         3.6667            4.0000
    56.0000         3.7333            4.0000
    57.0000         3.8000            4.0000
    58.0000         3.8667            4.0000
    59.0000         3.9333            4.0000
    60.0000         4.0000            4.0000
    61.0000         4.0667            4.0000
    62.0000         4.1333            4.0000
    63.0000         4.2000            4.0000
    64.0000         4.2667            4.0000
    65.0000         4.3333            4.0000
    66.0000         4.4000            4.0000
    67.0000         4.4667            4.0000 <\/p>\n<\/div>\n

    When you change the precision from 0 to 1, you will get only one possible total:<\/p>\n

    \n

    EXEC spu_findPossibleTotals @rating = 4, @numVotes = 15, @precision = 1  <\/p>\n

    TotalPoints     calcRaiting       roundedRating
    ———–     ———–       ————-
    60.0000         4.0000            4.0000<\/p>\n<\/div>\n

    You can test the correctness of the last result very easily:<\/p>\n

    Add 1 to the result, divide the new sum by 15 and then round the quotient to one digit after the decimal point: (60 + 1) \/ 15 = 4.067 = 4.1. Since 4.1 is greater than the given average of 4.0, the sum of all of the points cannot be 61.<\/p>\n

    Similarly, (60-1) \/ 15 = 3.93 = 3.9, which is less than the given average 4.0 and then 59 cannot be a possible total as well.<\/p>\n

    You will see later that the precision of the average (the number of digits after a decimal point), can significantly increase or decrease the entropy of the end result.<\/p>\n

    Step3: Remove Invalid Data <\/h4>\n

    We have now built the framework that we will use to analyze the readers’ votes. However, before we start the actual processing, we need to remove some invalid data from tables OneS<\/b>, TwoS<\/b>, ThreeS<\/b>, FourS<\/b> and FiveS<\/b>.<\/p>\n

    The following example will explain the logic used to find incorrect data.<\/p>\n

    Suppose you are going to read an article with an average rating of 4 and a precision of 0, when the number of votes is 15. <\/p>\n

    Run the procedures that we had previously built (see Lisitng2 and Lisitng3):<\/p>\n

    \n

    EXEC spu_createVoteTables @numVotes = 15;
    GO
    EXEC spu_findPossibleTotals @rating = 4, @numVotes = 15, @precision = 0
    GO<\/p>\n<\/div>\n

    The first procedure loads data into the tables OneS<\/b>, TwoS,<\/b> ThreeS<\/b>, FourS<\/b>, FiveS<\/b>. <\/p>\n

    The second procedure finds all possible totals. For the given rating and the number of votes, they will be in the range from 53 to 67, inclusively.<\/p>\n

    Now, analyze the data in table OneS. If all 15 readers give article 1 point, then the sum of all marks would be 15 * 1 = 15. Since 15 is smaller than the minimal possible total (which is 53), the situation, when each voter supposedly gave the article 1 point, is incorrect. Therefore, the row where the sumPoints<\/b> column is equal to 15 can be deleted from table OneS.<\/p>\n

    Similarly, if 14 readers give the article 1 point each, and the 15th reader gives article the highest mark 5, the total would be 14 + 5 = 19, which is still less than minimum possible total. So, the row where the sumPoints<\/b> column is equal to 14, can also be deleted from table OneS.<\/b><\/p>\n

    Apply the same logic to the rest of the rows of table OneS<\/b> and continue with tables TwoS<\/b>, ThreeS<\/b>, FourS<\/b> and FiveS<\/b>. The number of the rows in all of the tables will be reduced, in some cases significantly.<\/p>\n

    Now consider a slightly different logic. Consider the case where all 15 readers give the article 5 points each (table FiveS<\/b>). The sum of all of the points would be 15 * 5 = 75, which is greater than the maximum possible total (which is 67). Therefore, the row, where the sumPoints<\/b> column is equal to 75, should be deleted from the table FiveS<\/b>.<\/p>\n

    If 14 readers give the article 5 points each, the total will be 14 * 5 = 70. The 15th reader can give the article the lowest mark, but this will not change anything: 70 or 71 is greater than maximum possible total (67). The row, where the sumPoints<\/b> column is equal to 70 can be deleted from the table FiveS<\/b>.<\/p>\n

    However, the case where 13 readers give article 5 points each is valid, because 13 * 5 = 65. If two more readers rate the article 1 out of 5 the total would be 13 * 5 + 2 * 1 = 67, which is a valid total. Therefore, the row, where the sumPoints<\/b> column is equal to 65, should stay in table FiveS<\/b>.<\/p>\n

    Applying the same logic, you will remove some more rows from tables OneS<\/b>, TwoS<\/b>, ThreeS<\/b> and FourS<\/b>.<\/p>\n

    Listing4 shows how you could implement the logic that was just explained: <\/p>\n

    Listing4. Remove Invalid Data<\/b><\/p>\n

    \n

    IF EXISTS(SELECT * FROM sysobjects 
       WHERE ID = (OBJECT_ID(‘spu_removeInvalidData’)) AND xtype = ’P’) 
    DROP PROCEDURE spu_removeInvalidData;
    GO
    CREATE PROCEDURE spu_removeInvalidData  
       @numVotes dec(8,4)
    AS
    DECLARE @minTotalPoints dec(8,4), @maxTotalPoints dec(8,4);
    IF EXISTS (SELECT TOP 1 * FROM possiblePoints)
    BEGIN 
       SELECT @minTotalPoints = MIN(totalPoints), 
           @maxTotalPoints = MAX(totalPoints) 
       FROM possiblePoints;
    END
    ELSE
    BEGIN
       PRINT ’There is no data in possiblePoints table.’
       RETURN
    END    <\/p>\n

    – Remove invalid data from table OneS
    DELETE OneS 
       WHERE sumPoints + (@numVotes - votes)*5 < @minTotalPoints
       OR
       sumPoints + (@numVotes - votes)*1 > @maxTotalPoints;
    SELECT * FROM Ones; <\/p>\n

    – Remove invalid data from table TwoS
    DELETE TwoS 
       WHERE sumPoints + (@numVotes - votes)*5 < @minTotalPoints
       OR
       sumPoints + (@numVotes - votes)*1 > @maxTotalPoints;
    SELECT * FROM TwoS; <\/p>\n

    – Remove invalid data from table ThreeS
    DELETE ThreeS 
       WHERE sumPoints + (@numVotes - votes)*5 < @minTotalPoints
       OR
       sumPoints + (@numVotes - votes)*1 > @maxTotalPoints;
    SELECT * FROM ThreeS; <\/p>\n

    – Remove invalid data from table FourS
    DELETE FourS 
       WHERE sumPoints + (@numVotes - votes)*5 < @minTotalPoints
       OR
       sumPoints + (@numVotes - votes)*1 > @maxTotalPoints;
    SELECT * FROM FourS; <\/p>\n

    – Remove invalid data from table FiveS
    DELETE FiveS 
       WHERE sumPoints + (@numVotes - votes)*5 < @minTotalPoints
       OR
       sumPoints + (@numVotes - votes)*1 > @maxTotalPoints;
    SELECT * FROM FiveS;
    GO
    EXEC spu_removeInvalidData @numVotes = 15<\/p>\n

    Results:<\/p>\n

    point       votes       sumPoints
    ———– ———– ———-
    1           0           0.0000
    1           1           1.0000
    .  .  .  .  .  .  .  .  .  .  .
    1           4           4.0000
    1           5           5.0000<\/p>\n

    point       votes       sumPoints
    ———– ———– ———-
    2           0           0.0000
    2           1           2.0000
    .  .  .  .  .  .  .  .  .  .  .
    2           6           12.0000
    2           7           14.0000<\/p>\n

    point       votes       sumPoints
    ———– ———– ———-
    3           0           0.0000
    3           1           3.0000
    .  .  .  .  .  .  .  .  .  .  .
    3           10          30.0000
    3           11          33.0000<\/p>\n

    point       votes       sumPoints
    ———– ———– ———-
    4           1           4.0000
    4           2           8.0000
    .  .  .  .  .  .  .  .  .  .  .
    4           14          56.0000
    4           15          60.0000<\/p>\n

    point       votes       sumPoints
    ———– ———– ———-
    5           0           0.0000
    5           1           5.0000
    .  .  .  .  .  .  .  .  .  .  .
    5           12          60.0000
    5           13          65.0000<\/p>\n<\/div>\n

    Solution: How the Readers Voted <\/h4>\n

    In order to find out how the readers voted, we now:<\/p>\n

      \n
    1. Join tables OneS, TwoS, ThreeS, FourS and FiveS, using the Cross (or Cartesian) join. <\/li>\n
    2. Apply the following predicates to each intermediate join:\n
        \n
      • The summary of the votes in the join cannot exceed the given number of votes; <\/li>\n
      • The summary of the points in the join cannot be greater than the maximum total points.<\/li>\n<\/ul>\n<\/li>\n
      • Include into the result (the outmost join) only the combinations, where the summary of the votes equals to the given number of votes. <\/li>\n<\/ol>\n

        Running the script shown in Listing5 will show you how the readers voted in our example:<\/p>\n

        Listing5. Get Final Result<\/p>\n

        \n

        IF EXISTS(SELECT * FROM sysobjects 
           WHERE ID = (OBJECT_ID(‘spu_getFinalResult’)) AND xtype = ’P’) 
        DROP PROCEDURE spu_getFinalResult;
        GO
        CREATE PROCEDURE spu_getFinalResult  
           @numVotes INT
        AS
        DECLARE @minTotalPoints float, @maxTotalPoints float;
        IF EXISTS (SELECT TOP 1 * FROM possiblePoints)
        BEGIN 
           SELECT @minTotalPoints = MIN(totalPoints), 
                  @maxTotalPoints = MAX(totalPoints) 
           FROM possiblePoints;
        END
        ELSE
        BEGIN
           PRINT ’There is no data in possiblePoints table.’
           RETURN
        END   
        IF EXISTS(SELECT * FROM sysobjects 
           WHERE ID = (OBJECT_ID(‘result’)) AND xtype = ’U’) 
        DROP TABLE result; <\/p>\n

        SELECT t1234.Ones, 
               t1234.Twos, 
               t1234.Threes, 
               t1234.Fours, 
               t5.votes Fives,
              (t1234.votes_1234 + t5.votes) AS votes_12345, 
              (t1234.sumPoints_1234 + t5.sumPoints) AS sumPoints_12345
          INTO result
          FROM   
         (SELECT t123.Ones, t123.Twos, t123.Threes, t4.votes AS Fours, 
                  (t123.votes_123 + t4.votes) AS votes_1234, 
                  (t123.sumPoints_123 + t4.sumPoints) AS sumPoints_1234  
            FROM
           (SELECT t12.Ones, t12.Twos, t3.votes AS Threes, 
                  (t12.votes_12 + t3.votes) AS votes_123, 
                  (t12.sumPoints_12 + t3.sumPoints) AS sumPoints_123
              FROM
             (SELECT t1.votes Ones, t2.votes Twos, 
                    (t1.votes + t2.votes) AS votes_12, 
                    (t1.sumPoints + t2.sumPoints) AS sumPoints_12
               FROM OneS t1 CROSS JOIN TwoS t2
               WHERE t1.votes + t2.votes <= @numVotes 
                AND (t1.sumPoints + t2.sumPoints) <= ROUND(@maxTotalPoints,0)) t12
             CROSS JOIN ThreeS t3
             WHERE t12.votes_12 + t3.votes <= @numVotes 
             AND t12.sumPoints_12 + t3.sumPoints <= ROUND(@maxTotalPoints,0)) t123
           CROSS JOIN FourS t4
           WHERE t123.votes_123 + t4.votes <= @numVotes 
           AND t123.sumPoints_123 + t4.sumPoints <= ROUND(@maxTotalPoints,0)) t1234
         CROSS JOIN FiveS t5
         WHERE t1234.votes_1234 + t5.votes = @numVotes 
         AND t1234.sumPoints_1234 + t5.sumPoints BETWEEN ROUND(@minTotalPoints,0) 
        AND ROUND(@maxTotalPoints,0);
        SELECT * FROM result; <\/p>\n

        GO
        EXEC spu_getFinalResult @numVotes = 15;<\/p>\n

        Results:<\/p>\n

        Ones    Twos    Threes  Fours  Fives  totalVotes  totalPoints
        ——- ——- ——- —— —— ———– ———–
        0       0       0       15     0      15          60.0000
        0       0       1       14     0      15          59.0000
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
        3       3       0       1      8      15          53.0000
        4       0       0       3      8      15          56.0000
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
        2       0       1       0      12     15          65.0000
        2       0       0       0      13     15          67.0000<\/p>\n

        (871 row(s) affected)<\/p>\n<\/div>\n

        The result of the calculation is in the table result. Each row of that table represents one possible voting combination. Columns One to Five store the number of readers that rated the article 1 to 5, out of 5, respectively. <\/p>\n

        The number of rows in the table, i.e. the number of possible voting combinations is equal to 871 and this is probably the most frustrating part of the result. Indeed, only one combination from 871 possible ones is the correct answer, but there is no way to say which one. Therefore, for the given input (15 votes and the average rating 4, rounded to a whole number) it is impossible to uncover how readers voted. <\/p>\n

        Does this mean, however, that the framework that we built, and all of the results, is useless? Well, no, because you still can get some interesting statistics from the result.<\/p>\n

        For example, try the following query:<\/p>\n

        \n

        SELECT COUNT(OneS) FrequencyPerOnes, Ones 
           FROM result 
           GROUP BY Ones 
           ORDER BY Ones <\/p>\n

        Results:<\/p>\n

        FrequencyPerOnes Ones
        —————- ———–
        377              0
        254              1
        146              2
        67               3
        23               4
        4                5<\/p>\n<\/div>\n

        From this we can deduce that:<\/p>\n

          \n
        1. There are no voting combinations where the number of ones exceeds 5; <\/li>\n
        2. There are 377 combinations, where no readers gave the article 1 point; <\/li>\n
        3. About 90% of all voting combinations are combinations where 2 or less readers rated the article 1 out of 5; <\/li>\n<\/ol>\n

          Our technique can provide much more accurate results when the average rating has a higher precision (in most electronic journals and websites, the precision of the rating is one or two digits after the decimal point).<\/p>\n

          For 15 votes and an average rating of 4.1 (rounded to one digit after the decimal point), you will get only 86 voting combinations (compare this to 871 combinations for the average rating 4):<\/p>\n

          \n

          EXEC spu_createVoteTables @numVotes = 15;
          GO
          EXEC spu_findPossibleTotals @rating = 4.1, @precision = 1, @numVotes = 15; 
          GO
          EXEC spu_removeInvalidData @numVotes = 15;
          GO
          EXEC spu_getFinalResult @numVotes = 15;<\/p>\n

          Results:<\/p>\n

          Ones  Twos  Threes  Fours  Fives  totalVotes  totalPoints
          —– —– ——- —— —— ———– ————
          0     0     0       14     1      15          61.0000
          0     0     0       13     2      15          62.0000
          . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
          2     2     0       0      11     15          61.0000
          3     0     0       1      11     15          62.0000
          3     0     1       0      11     15          61.0000<\/p>\n

          (86 row(s) affected)<\/p>\n<\/div>\n

          For 15 votes with the average rating 4.13 (rounded to two digits after the decimal point), you will get only 39 voting combinations<\/p>\n

          \n

          EXEC spu_createVoteTables @numVotes = 15;
          GO
          EXEC spu_findPossibleTotals @rating = 4.13, @precision = 2, @numVotes = 15; 
          GO
          EXEC spu_removeInvalidData @numVotes = 15;
          GO
          EXEC spu_getFinalResult @numVotes = 15;<\/p>\n

          Results:<\/p>\n

          Ones  Twos  Threes  Fours  Fives  totalVotes  totalPoints
          —– —– ——- —— —— ———– ————
          0     0     0       13     2      15          62.0000
          0     0     1       11     3      15          62.0000
          . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
          2     1     1       0      11     15          62.0000
          3     0     0       1      11     15          62.0000
          1     3     0       0      11     15          62.0000<\/p>\n

          (39 row(s) affected)<\/p>\n<\/div>\n

          This is actually as good as it gets. Use of a more precise average rating (with three or more digits after decimal point) will not reduce the number of possible voting combinations. <\/p>\n

          However, if you have some additional information about the voting, you can narrow down your search still further.For example, the article has been rated by 15 readers and has a rating of 4.13 with the precision of 2. If, somehow, you know that at least one reader gave the article 1 point, you will get 18 possible combinations instead of 39:<\/p>\n

          \n

          SELECT * FROM result WHERE Ones > 0;<\/p>\n

          Results:<\/p>\n

          Ones  Twos  Threes  Fours  Fives  totalVotes  totalPoints
          —– —– ——- —— —— ———– ————
          1     0     0       9      5      15          62.0000
          1     0     1       7      6      15          62.0000
          . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
          3     0     0       1      11     15          62.0000
          1     3     0       0      11     15          62.0000<\/p>\n

          (18 row(s) affected)<\/p>\n<\/div>\n

          Furthermore, if you know that another reader gave the article 2 points, you will get only 9 possible combinations:<\/p>\n

          \n

          SELECT * FROM result WHERE Ones > 0 AND Twos > 0;  <\/p>\n

          Results:<\/p>\n

          Ones  Twos  Threes  Fours  Fives  totalVotes  totalPoints
          —– —– ——- —— —— ———– ————
          1     1     0       6      7      15          62.0000
          1     1     1       4      8      15          62.0000
          . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
          2     1     1       0      11     15          62.0000
          1     3     0       0      11     15          62.0000<\/p>\n

          (9 row(s) affected)<\/p>\n<\/div>\n

          You may be thinking that rating decomposition always results in multiple voting combinations, but this is not always the case. For some inputs, you can get just a few or even one voting combination.<\/p>\n

          The following example for 8 votes and a rating of 4.75 will retrieve only two possible voting combinations:<\/p>\n

          \n

          EXEC spu_createVoteTables @numVotes = 8;
          GO
          EXEC spu_findPossibleTotals @rating = 4.75, @precision = 2, @numVotes = 8 
          GO
          EXEC spu_removeInvalidData @numVotes = 8;
          GO
          EXEC spu_getFinalResult @numVotes = 8;<\/p>\n

          Results:<\/p>\n

          Ones  Twos  Threes  Fours  Fives  totalVotes  totalPoints
          —– —– ——- —— —— ———– ————
          0     0     0       2      6      8           38.0000
          0     0     1       0      7      8           38.0000<\/p>\n<\/div>\n

          As a rule of thumb, the accuracy of decomposition depends on the number of votes and on the precision that was used to calculate the rating. The more votes and the fewer digits after the decimal points in the rating, the more entropy (more possible voting combinations) will be in the result.<\/p>\n

          Performance Considerations <\/h4>\n

          Our solution was based on cross or Cartesian join, which in turn generates the Cartesian product -a huge number of the rows in the result. Since the solution uses cross-joins four times, it will definitely experience performance problems for large number of votes. <\/p>\n

          Indeed, when I ran the procedures on my single CPU desktop, for 87 votes and an average rating of 3.43, I got the result in 3 min. and 43 sec. For votes = 127, rating = 2.77, the execution time was 16 min. and 38 sec. <\/p>\n

          You can get better execution time if you just generate the result, but do not insert it into the table result (as in the Listing5). In that case, the procedure spu_getFinalResult<\/b> will run almost two times faster. Placing indexes on tables OneS<\/b>, TwoS<\/b>, ThreeS<\/b>, FourS<\/b> and FiveS<\/b> may also improve the performance.<\/p>\n

          Basically, you should be careful when using this solution for hundreds or thousands of votes as the algorithm we use here may become inefficient. Some good news, though, is that in real life, the number of votes for the articles (products, books) rarely goes over 100. <\/p>\n

          <\/b><\/p>\n

          Conclusions <\/h3>\n

          How should you treat this article? Was it a fun puzzle? Was it something specific that can be applied only to very limited situations? <\/p>\n

          Yes, ultimately, you should consider this article as a puzzle, although the algorithm provided can be very useful for some specific cases.<\/p>\n

          For example, say you know the value of an aggregate function (SUM<\/b> or COUNT<\/b> or AVG<\/b> and so on) and a few (from many) values that participated in the aggregate creation. If you do not have an access to a database (data) that produced the aggregate, how would you find the values that assembled the aggregate? This is the sort of situation where this algorithm can help.<\/p>\n","protected":false},"excerpt":{"rendered":"

          When reading rating information, how do you you knew how many points each separate voter gave if you only know the average rating and the number of votes? Well, you might be surprised to learn that you can figure it out using SQL
          \n…<\/p>\n","protected":false},"author":221828,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4816,4252],"coauthors":[],"class_list":["post-301","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-sql-server-tsql","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/301","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221828"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=301"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/301\/revisions"}],"predecessor-version":[{"id":39859,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/301\/revisions\/39859"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=301"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=301"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=301"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=301"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}