MySQL:给定外键和特定日期范围的列值增长

Question

我的数据库中有一个表格，每天都会更新该表格，其中包含给定 URL (page_id) 上 Pinterest 上的图钉数量。 如何编写一个查询来返回给定日期范围内增长最大的 page_id？

通常，我计划设置 @StartDate = YESTERDAY 并设置 @EndDate = Today，但我希望能够灵活地调整查询中的日期。

这是我的引脚表的当前架构:

mysql> describe pins;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| page_id | int(11) | NO   | MUL | NULL    |                |
| date    | date    | YES  |     | NULL    |                |
| pins    | int(11) | YES  |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+

这是我需要您帮助的查询所需的输出:

mysql> [fancy-mysql-query] ORDER BY Pins_DayOverDay DESC LIMIT 1;

+-------------------+---------------------------+
| page_id           | Pins_DayOverDay           |
+-------------------+---------------------------+
|             12345 |                     12345 |
+-------------------+---------------------------+

任何帮助将不胜感激!

Answer 1

如果pins列是每日增量，即在给定日期添加/删除的pins数量的净变化，则:

SELECT @StartDate = DATE_FORMAT(NOW(),'%Y-%m-%d') + INTERVAL -1 DAY
SELECT @EndDate   = DATE_FORMAT(NOW(),'%Y-%m-%d')

SELECT t.page_id
     , SUM(t.pins) AS `Pins_DayOverDay`
  FROM mytable t
 WHERE t.date >= @StartDate
   AND t.date <= @EndDate
 GROUP
    BY t.page_id
 ORDER BY `Pins_DayOverDay` DESC
 LIMIT 1 

如果pins列是累积的并且包含有效pins的总数(前几天完成的pins加上今天添加/删除的pins)，假设(page_id,date ) 是唯一的:

SELECT t.page_id
     , (t.pins - s.pins) AS `Pins_DayOverDay`
  FROM mytable t
  JOIN mytable s
    ON s.page_id = t.page_id
 WHERE t.date = @EndDate
   AND s.date = @StartDate
 GROUP
    BY t.page_id
 ORDER BY `Pins_DayOverDay` DESC
 LIMIT 1

上面的查询假设(page_id,date)是唯一的。如果同一个 page_id 可以有多行具有相同的日期，那么我们需要添加聚合函数。 (如果您需要，我会提供该查询。)