mysql - 使用组和最新条目计算平均分数

Question

我希望能够使用 MySql 获得每个联系人的最新 3 个 CampaignId 的平均分数。

   [id], [ContactId], [CampaignId], [Score]
     1    1             100             5
     2    1             100             7
     3    1             101             1
     4    1             102             3
     5    1             103             2
     6    1             103             2
     7    2             100             1
     8    2             103             2
     9    3             103             1
     10   3             104             3
     11   3             105             2
     12   3             106             4
     13   4             101             5

所以结果是:-

[ContactId], [AvgScore]   (worked out by)
      1          2.66     (1 + 3 + 2 + 2 ) /3 
      2          1.50     (1 + 2) / 2  (as there are only two campaigns)
      3          3.00     (3 + 2 + 4) / 3 
      4          5.00     (5) / 1  (as there is only one campaign)

编辑我已经设法获得了单个联系人的结果，但也想尝试为所有联系人计算出结果。

select ContactId, sum(Score), sum(Count)
from  (
  select 
    ContactId, CampaignId, sum(Score) Score, count(distinct CampaignId) Count
    from stats
  where
     ContactId = 1
  group by
      CampaignId, ContactId
order by CampaignId DESC limit 3) a;

Answer 1

   DROP TABLE IF EXISTS stats;

   CREATE TABLE stats
   (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
   ,ContactId INT NOT NULL
   ,CampaignId INT NOT NULL
   ,Score INT NOT NULL
   );

   INSERT INTO stats VALUES
   (1,1,100,5),
   (2,1,100,7),
   (3,1,101,1),
   (4,1,102,3),
   (5,1,103,2),
   (6,1,103,2),
   (7,2,100,1),
   (8,2,103,2),
   (9,3,103,1),
   (10,3,104,3),
   (11,3,105,2),
   (12,3,106,4),
   (13,4,101,5);

   SELECT x.* 
     FROM stats x 
     JOIN stats y 
       ON y.contactid = x.contactid 
      AND y.campaignid >= x.campaignid 
    GROUP 
       BY x.id 
   HAVING COUNT(DISTINCT y.campaignid) <=3;
   +----+-----------+------------+-------+
   | id | ContactId | CampaignId | Score |
   +----+-----------+------------+-------+
   |  3 |         1 |        101 |     1 |
   |  4 |         1 |        102 |     3 |
   |  5 |         1 |        103 |     2 |
   |  6 |         1 |        103 |     2 |
   |  7 |         2 |        100 |     1 |
   |  8 |         2 |        103 |     2 |
   | 10 |         3 |        104 |     3 |
   | 11 |         3 |        105 |     2 |
   | 12 |         3 |        106 |     4 |
   | 13 |         4 |        101 |     5 |
   +----+-----------+------------+-------+

   SELECT contactid
        , SUM(score)/COUNT(DISTINCT campaignid) avgscore 
     FROM 
        ( SELECT x.* 
            FROM stats x 
            JOIN stats y 
              ON y.contactid = x.contactid 
             AND y.campaignid >= x.campaignid 
           GROUP 
              BY x.id 
          HAVING COUNT(DISTINCT y.campaignid) <=3
        ) a 
    GROUP 
       BY contactid;
   +-----------+----------+
   | contactid | avgscore |
   +-----------+----------+
   |         1 |   2.6667 |
   |         2 |   1.5000 |
   |         3 |   3.0000 |
   |         4 |   5.0000 |
   +-----------+----------+