python - SQL - DATEDIFF 和 WHERE/HAVING 的聚合函数

Question

我正在尝试查询数据库以查找与我传入的参数最接近的日期。

我有一个问题，我已经在 IRC 中处理并列出了一段时间，但找不到解决方案。我正在使用 python DBAPI (sqlalchemy) 并通过 CLI 和 Flask 应用程序访问数据(但这不应影响 SQL)。数据库是带有InnoDB引擎的MySQL。

查询将取决于名为target_expiration的因素，但数据库中通常不存在该因素的值。我需要找到最接近的值。例如，我将传入 target_date='2015-06-02' 并期望它返回 2015-06-03 结果和 >2015-06-01 结果距离我的 target_date 还差一天。

尝试解决方案:

SELECT * FROM table1 HAVING MIN(ABS(DATEDIFF(table1.Expiration, 
'2015-06-02'))) = ABS(DATEDIFF(table1.Expiration, '2015-06-02'));

这不会返回任何行。我可以运行相同的 MIN(ABS(DATEDIFF(...))) 来返回 scalar() 并且它会成功并打印一个值。

我尝试使用HAVING函数来使用聚合函数。我正在扫描数据库并找出最接近目标日期的到期日期。如果它为零，我就完成了 - 我找到了日期，然后我只想返回所有匹配的日期。如果还有一天，我不知道是向前一天还是向后一天(我将处理添加更多功能以供稍后选择，但现在我只想返回所有 值向前或向后 n 天)。

示例数据:表1

这是一个极其有限的数据样本，实际上我有大约 40 列和数十亿行，因此速度是一个因素。

+------------+-------------+-------+
| Expiration | ProductType | Price |
+------------+-------------+-------+
| 2015-06-01 |      2      |   25  |
+------------+-------------+-------+
| 2015-06-03 |      1      |   22  |
+------------+-------------+-------+
| 2015-05-28 |      1      |   22  |
+------------+-------------+-------+
| 2015-05-28 |      2      |   28  |
+------------+-------------+-------+
| 2015-05-28 |      1      |   22  |
+------------+-------------+-------+
| 2015-06-04 |      2      |   28  |
+------------+-------------+-------+
| 2015-05-25 |      1      |   22  |
+------------+-------------+-------+
| 2015-05-25 |      2      |   28  |
+------------+-------------+-------+

Answer 1

对于单个结果:

SELECT * 
FROM table1 
ORDER BY ABS(DATEDIFF(table1.Expiration, '2015-06-02')) ASC
LIMIT 1;

如果您担心有“领带”:

SELECT * 
FROM table1 
WHERE ABS(DATEDIFF(table1.Expiration, '2015-06-02')) = (
   SELECT MIN(ABS(DATEDIFF(table1.Expiration, '2015-06-02')))
   FROM table1
);

但请注意，这些查询永远不会很快；第二个要求对表中的每一行进行两次检查，并且在这两种情况下，函数的使用都会阻止索引提供任何帮助。