MYSQL单表中MAX和MIN之间的时差

I have a table as outlined below and I'm trying to get the MAX and MIN values for a specific Id but only if the MAX entry date is greater than the MIN entry date.

我有一个如下所述的表格,我正在尝试获取特定Id的MAX和MIN值,但前提是MAX输入日期大于MIN输入日期。

table:

ID  ENTRY_DATE              NAME       PRICE
1   2012-01-23 16:09:35     MONKEY     99.33
2   2012-01-23 11:04:09     MONKEY     97.65
3   2012-01-23 09:31:19     MONKEY     93.05
4   2012-01-23 15:12:14     DICE       30.49
5   2012-01-23 12:01:24     DICE       32.00
6   2012-01-23 08:01:24     DICE       31.00

So what I'm trying to do is grab the MAX(price) and MIN(price) when the MAX(price) entry_date is greater than the MIN(price) entry_date. From the table above, my result would be:

所以我想要做的是当MAX(价格)entry_date大于MIN(价格)entry_date时,获取MAX(价格)和MIN(价格)。从上表中,我的结果是:

NAME      MaxPrice     MinPrice   PriceDiff   
MONKEY    99.33        93.05      6.28

'Dice' would not show up because the MAX date/time happens before the MIN.

'Dice'不会显示,因为MAX日期/时间发生在MIN之前。

I have tried a LEFT JOIN but I cant seem to figure it out. Here is what I have right now, but it obviously grabs all results as I cant seem to figure out how to compare the entry dates...

我尝试过LEFT JOIN,但我似乎无法弄明白。这就是我现在所拥有的,但它显然抓住了所有结果,因为我似乎无法弄清楚如何比较输入日期......

SELECT name,
MAX(price) as MaxPrice,
MIN(price) as MinPrice,
MAX(price)-MIN(price) AS PriceDiff,
FROM products
WHERE DATE(entry_date) = DATE(NOW()) 
GROUP BY name
ORDER BY PriceDiff DESC

Thanks for the help!

谢谢您的帮助!

1 个解决方案

#1

To select the max price per group (& date corresponding to that) you do:

要选择每组的最高价格(与之对应的日期),您可以:

SELECT p.NAME, p.ENTRY_DATE as dateofMax, p.PRICE as maxPrice
FROM products p
LEFT JOIN products p2
 ON p.NAME=p2.NAME AND p.PRICE<p2.PRICE
WHERE p2.PRICE IS NULL;

What this does is JOINs products to itself on the name, forming every possible pair of prices within each name, where p.PRICE<p2.PRICE. Since this is a left join, if there is a price in p for which there is no greater price in p2, p2.PRICE will be set to NULL.

这样做就是在名称上加入产品,在每个名称中形成每一对可能的价格,其中p.PRICE 。由于这是左连接,如果p中的价格在p2中没有更高的价格,p2.price将被设置为null。

So this query selects the row where there is no greater price, ie the row of the max price (per group, being NAME).

因此,此查询选择没有更高价格的行,即最大价格的行(每个组,即NAME)。

To select the min price per group (& corresponding date) you change the < to a > in the LEFT JOIN, and so we pick the row for which we can't find any smaller price in the table for that name.

要选择每组的最低价格(和相应的日期),您可以在LEFT JOIN中更改 <到a> ,因此我们选择在该表中找不到该名称的任何较小价格的行。

Since you wish to pick both the max and the min, these two tables need to be JOINed together.

由于您希望同时选择最大值和最小值,因此需要将这两个表连接在一起。

Solution

This means 3 self-joins of product to itself, where two product tables are used to calculate the max price/date, and the other two are used are used to calculate the min.

这意味着产品与自身的3个自连接,其中两个产品表用于计算最大价格/日期,而另外两个用于计算最小值。

In the query below, all lines but the last will produce a table with the max price & its date, and the min price & its date. The last enforces your "price of max happens after price of min" condition.

在下面的查询中,除了最后一行之外的所有行都将生成一个表格,其中包含最高价格及其日期,以及最低价格及其日期。最后一次执行你的“最高价格发生在最低价格之后”的条件。

The p,p2 tables are used to calculate the max price, and the p3,p4 to calculate the min price. It would still work if p3 was joined to p ON p.NAME=p3.NAME only, but the extra condition p3.price<=p.price prunes out rows we're not interested in (there's no point looking for the min price on rows for which the price is greater than the max!).

p,p2表用于计算最高价,而p3,p4用于计算最低价。如果p3仅加入p ON p.NAME = p3.NAME,它仍然可以工作,但额外条件p3.price <= p.price会删除我们不感兴趣的行(没有必要寻找最低价格在价格大于最大值的行上!)。

-- select max price & date of max & min price & date of min:
SELECT p.NAME, p.ENTRY_DATE as dateofMax, p.PRICE as maxPrice, 
       p3.ENTRY_DATE as dateofMin, p3.PRICE as minPrice,
       p.PRICE-p3.PRICE as PriceDiff
FROM products p
LEFT JOIN products p2
 ON p.NAME=p2.NAME AND p.PRICE<p2.PRICE
LEFT JOIN products p3
 ON p.NAME=p3.NAME AND p3.price<=p.price
LEFT JOIN products p4
 ON p.NAME=p4.NAME AND p3.price>p4.price
WHERE p2.PRICE IS NULL
AND p4.PRICE IS NULL
AND p.ENTRY_DATE>p3.ENTRY_DATE; -- make sure dateOfMax>dateofMin

which yields

+--------+---------------------+----------+---------------------+----------+-----------+
| NAME   | dateofMax           | maxPrice | dateofMin           | minPrice | PriceDiff |
+--------+---------------------+----------+---------------------+----------+-----------+
| MONKEY | 2012-01-23 16:09:35 |    99.33 | 2012-01-23 09:31:19 |    93.05 | 6.28      |
+--------+---------------------+----------+---------------------+----------+-----------+