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

I'm trying to query a DB to find the closest date to the parameter I passed in.

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

I have a problem I've been working on in IRC and lists for a while and can't find a solution. I'm using a python DBAPI (sqlalchemy) and am accessing the data both through CLI and a Flask App (but this should not impact the SQL). The database is MySQL with an InnoDB engine.

我有一个问题,我一直在IRC和列表工作一段时间,无法找到解决方案。我正在使用python DBAPI(sqlalchemy)并通过CLI和Flask App访问数据(但这不应该影响SQL)。该数据库是带有InnoDB引擎的MySQL。

The query will depend on the a factor called target_expiration, but for which the value will frequently not exist in the DB. I need to find which value is nearest. For example, I will pass in target_date='2015-06-02' and would expect it to return to me both the 2015-06-03 result and the 2015-06-01 result that is one day away from my target_date.

查询将取决于名为target_expiration的因子,但该值通常不存在于DB中。我需要找到哪个值最接近。例如,我将传递target_date ='2015-06-02'并期望它返回给我2015-06-03结果和距离我的target_date一天的2015-06-01结果。

Attempt at a solution:

尝试解决方案:

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

This returns no rows. I can run the same MIN(ABS(DATEDIFF(...))) to return a scalar() and it succeeds and prints a value.

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

I tried using the HAVING function in order to use an aggregate function. I'm looking to scan the DB and find out what is my closest Expiration date to my target date. If it's zero, I'm done - I've found the date and I then just want to return all dates that match. If it's a day away, I don't know if it's a day forward or a day backwards (I'll deal with adding more functionality to choose later, but for now I'd just like to return all values that are n days away forwards or backwards).

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

Example Data: table1

示例数据:table1

This is an extremely limited sample of the data, in reality I have ~40 columns and billions of rows, so speed is a factor.

这是一个极其有限的数据样本,实际上我有大约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  |
+------------+-------------+-------+

2 个解决方案

#1

For a single result:

对于单个结果:

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

If you're worried about having a "tie":

如果你担心有“平局”:

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

Note however, these queries will never be fast; the 2nd requires every row in the table to be inspected twice, and in both the function usage prevents indexing from providing any help.

但请注意,这些查询永远不会很快;第二行要求对表中的每一行进行两次检查,并且在这两种功能使用中都会阻止索引提供任何帮助。

#2

How about simply

怎么样简单

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

Or, if the table is big, and you have INDEX(Expiration), this should be faster:

或者,如果表很大,并且您有INDEX(到期),这应该更快:

( SELECT  *, 
        ABS(DATEDIFF(Expiration, '2015-06-02')) AS diff
    FROM  table1
    WHERE Expiration >= '2015-06-02'
    ORDER BY Expiration ASC LIMIT 1 )
UNION ALL
( SELECT  *, 
        ABS(DATEDIFF(Expiration, '2015-06-02')) AS diff
    FROM  table1
    WHERE Expiration < '2015-06-02'
    ORDER BY Expiration DESC LIMIT 1 )
ORDER BY diff 
LIMIT 1;

#1