mysql - 在分组依据(MYSQL)中选择最大值时需要一种更好的计算速率的方法

Question

我正在尝试获取表格中统计数据的每日差异，以便绘制数据。我有一个 facebook_stats 表，它每隔 5 分钟拍摄一次“快照”。

我试图获取每天的最后一个条目并计算连续几天之间的差异，例如，2 月 4 日我有 20 个条目，2 月 5 日我有 50 个条目。

获取2月4日的最后一天，2月5日的最后一天，计算page_likes的差异。如果我开始在我的 table 上为最后一个条目抛出一个标志，也许这会更容易？也许我在这个 MYSQL 上做的比我需要做的更多？我对MYSQL了解不够，所以我在这里问一下，看看有没有更好的方法来完成这个。

下面的代码得到了预期的结果，但它非常昂贵并且在我的开发中需要大约 2-3 秒。

   SELECT t1.timestamp, (t2.page_likes - t1.page_likes) as rate
    FROM facebook_stats t1
    INNER JOIN (SELECT `timestamp`, max(id) id, page_likes, handshake_id 
                 FROM facebook_stats
             GROUP BY DATE(`timestamp`) , handshake_id ) t2
    ON t1.handshake_id = t2.handshake_id
    AND DATE(t1.timestamp) + 1 = DATE(t2.timestamp)
    WHERE t1.id IN (
        SELECT t1.id
        FROM facebook_stats t1
        INNER JOIN (SELECT `timestamp`, max(id) id, page_likes, handshake_id 
                        FROM facebook_stats
        GROUP BY DATE(`timestamp`) , handshake_id ) t2
        ON t1.id = t2.id
        GROUP BY DATE(t1.timestamp), t1.handshake_id
    )
    GROUP BY DATE(t1.timestamp), t1.handshake_id;

我考虑过运行内部查询，并在午夜刚过就用标志“last_entry”或实际汇率本身或类似的东西更新记录。我还考虑过使用 PHP 进一步过滤结果(没有 where，查询速度足够快)。

我想知道哪种解决方案基于惯例和效率是最合适的。我倾向于制作一个“费率”列并且大部分为空，而当天的最后一个包含费率。然后我可以只获取所有具有非空值的列。建议？谢谢

如果人们好奇的话，这是表结构:

facebook_stats

+---------------+------------------+------+-----+-------------------+----------------+
| Field         | Type             | Null | Key | Default           | Extra          |
+---------------+------------------+------+-----+-------------------+----------------+
| id            | int(11) unsigned | NO   | PRI | NULL              | auto_increment |
| timestamp     | timestamp        | YES  | MUL | CURRENT_TIMESTAMP |                |
| handshake_id  | int(11) unsigned | NO   | MUL | NULL              |                |
| page_likes    | int(11)          | YES  |     | NULL              |                |
| status_likes  | int(11)          | YES  |     | NULL              |                |
| status_shares | int(11)          | YES  |     | NULL              |                |
+---------------+------------------+------+-----+-------------------+----------------+

---

编辑

我可能不清楚我要收集的内容。

page_likes 数据是我运行该过程时(每 5 分钟)该页面的总点赞数。这不是一个附加过程；它是 Facebook 页面上当前内容的快照。

话虽如此，我更关心每天的变化率，即今天结束的 page_likes - 表格中每一天的昨天结束的 page_likes。

最终目标是创建一个图表，显示 Facebook 页面点赞数每天的变化率。示例数据集可能如下所示:

array(
    "2014-02-5" => 20, 
    "2014-02-6" => 5, 
    "2014-02-7" => -1,
    "2014-02-7" => 2,
     ...
);

所以在 2 月 5 日，页面在一天结束时有 500 个赞(最后一个条目)。 4 日收盘时有 480 点。 500 - 480 = 20。

希望这能澄清我想收集的更多信息。

Answer 1

像这样的动态聚合已经在这里多次提出，每个人的情况似乎有些不同，因此根据他们的特殊需求再次提出这个问题。我会提供的答案是在这些情况下进行非规范化。

既然你想要从一天结束到第二天的总数，我会提供一些类似的东西，比如在表中添加一列“likes_yesterday”(int)来跟踪总的喜欢/不喜欢等，这样可以代表

然后，基于插入、更新、删除的触发器，按照...做一些事情

if the date of timestamp before the update is the day prior to the new update
   update the Likes_YesterDay = page_likes,
              Page_Likes = 1,
              timestamp with current time
else
   update the Page_Likes = Page_Likes +1,
              timestamp with current time

对于给定的 ID/握手。是的，由于缺少值的上下文和您正在跟踪的内容，上面的实际触发器示例是不准确的。

您还可以出于任何目的使用另一列，例如“FirstLikeForDay”也是一个时间戳列，以便在您将当前喜欢移动到昨天喜欢的同时设置它，并且可以在日期滚动时标记它 -迎接新的一天。