mysql - 每天计算推文，按同一张表中的其他 3 列进行加权

Question

(这是一篇很长的文章，但我认为这个问题很容易解决，而且我已经准备好了 SQLFiddle)请考虑下表:

----------------------------------------------------------------------
tweet_id sp100_id nyse_date   user_id class_id retweets quality follow
----------------------------------------------------------------------
1        1        2011-03-12  1       1        0        2.50    5.00
2        1        2011-03-13  1       2        2        2.50    5.00
3        1        2011-03-13  1       2        1        2.50    5.00
4        1        2011-03-13  2       2        0        0.75    1.00
5        1        2011-03-13  2       3        3        0.75    1.00
6        2        2011-03-12  2       2        3        0.75    1.00
7        2        2011-03-12  2       2        0        0.75    1.00
8        2        2011-03-12  1       3        5        2.50    5.00
9        2        2011-03-13  2       2        0        0.75    1.00
----------------------------------------------------------------------

该表的期望输出是每个 _date 每个 sp100_id 的列表，每个 _date 的正(类=2)和负(类=3)推文加权 转推、质量和关注:

--------------------------------------------------------------------------------
sp100_id  nyse_date  pos-rt pos-quality pos-follow neg-rt neg-quality neg-follow
--------------------------------------------------------------------------------
1         2011-03-11 0      0           0          0      0           0
1         2011-03-12 0      0           0          0      0           0
1         2011-03-13 3 (1)  5.75 (2)    11.00 (3)  3 (4)  0.75        1.00
2         2011-03-11 0      0           0          0      0           0
2         2011-03-12 3      1.50        10.00      5.00   2.50        2.50
2         2011-03-13 0      0.75        1.00       0      0           0
--------------------------------------------------------------------------------

On 2011-03-13, 3 positive tweets for sp100_id 1:

(1) 1 tweet retweeted 2 times, 1 tweets retweeted 1 time and 
    1 tweet retweeted 0 times = 1 x 2 + 1 x 1 + 1 x 0 = 3
(2) 2 tweets with quality 2.50 and 1 tweet with quality 0.75 =
    2 x 2.50 + 1 x 0.75 = 5.75
(3) 2 tweets with follow 5 and 1 tweet with follow 1 =
    2 x 5.00 + 1 x 1.00 = 11.00

On 2011-03-13, 1 negative tweets for sp100_id 1:

(4) 1 tweet retweeted 3 times = 1 x 3 = 3

etc...

我在 SQLFiddle 上有一个演示与必要的其他表(我需要将它链接到日期范围表，因为我还想包括全零的记录集)。我的查询也有一个输出，但我不明白为什么它与所需的输出不同:

--------------------------------------------------------------------------------
sp100_id  nyse_date  pos-rt pos-quality pos-follow neg-rt neg-quality neg-follow
--------------------------------------------------------------------------------
1         2011-03-11 0      0           0          0      0           0
1         2011-03-12 3      2           2          5      3           5
1         2011-03-13 3      8           12         3      1           1
2         2011-03-11 0      0           0          0      0           0
2         2011-03-12 3      2           2          5      3           5
2         2011-03-13 3      8           12         3      1           1
--------------------------------------------------------------------------------

我看不出问题出在哪里。你？非常感谢您的帮助:-)

Answer 1

它没有返回预期值的原因是因为您还需要在 LEFT JOIN 条件中包含 sp100.sp100_id = tweets.sp100_id 以及日期.

通过只加入日期，它将加入表中的任何日期值，而不管 sp100_id。这就是为什么您的结果总和被丢弃的原因，因为对于每个 sp100_id，它都在 SUM() 中包含了所有其他 sp100_id 的值>s.

我还对您的查询进行了一些清理(仅在美学方面):

SELECT     a.sp100_id,
           b._date AS nyse_date,
           SUM(IF(c.class=2, c.retweets, 0)) AS 'pos-rt',
           SUM(IF(c.class=2, c.quality,  0)) AS 'pos-quality',
           SUM(IF(c.class=2, c.follow,   0)) AS 'pos-follow',
           SUM(IF(c.class=3, c.retweets, 0)) AS 'neg-retweet',
           SUM(IF(c.class=3, c.quality,  0)) AS 'neg-quality',
           SUM(IF(c.class=3, c.follow,   0)) AS 'neg-follow'
FROM       sp100 a
CROSS JOIN daterange b
LEFT JOIN  tweets c ON a.sp100_id = c.sp100_id 
                   AND b._date = c .nyse_date
GROUP BY   a.sp100_id, 
           nyse_date

---

SQLFiddle Demo