MySQL 选择组中的第一个和最后一个值

Selecting first and last values in a group

提问by Zishan

I have a MySql table consisting of daily stock quotes (open, high, low, close and volume) which I'm trying to convert into weekly data on the fly. So far, I have the following function, which works for the highs, lows, and volume:

我有一个 MySql 表，由每日股票报价（开盘价、最高价、最低价、收盘价和成交量）组成，我试图将其即时转换为每周数据。到目前为止，我有以下功能，适用于高点、低点和成交量：

SELECT MIN(_low), MAX(_high), AVG(_volume),
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

I need to select the first instance of _open in the above query. So for example, if there was a holiday on Monday (in a particular week) and stock market opened on Tuesday, _open value should be selected from the Tuesday that's grouped into its week. Similarly, the close value should be the last _close from that week.

我需要在上面的查询中选择 _open 的第一个实例。因此，例如，如果周一（特定周）有假期并且股市周二开盘，则 _open 值应从分组到该周的周二中选择。同样，收盘价应该是那一周的最后一个 _close。

Is it possible to select something like FIRST() and LAST() in MySql so that the above could be wrapped up within a single SELECT rather than using nested select queries?

是否可以在 MySql 中选择 FIRST() 和 LAST() 之类的东西，以便上述内容可以包含在单个 SELECT 中，而不是使用嵌套的选择查询？

Here's my table's create statement to get an idea of the schema:

这是我的表的 create 语句以了解架构：

delimiter $$
CREATE TABLE `mystockdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `symbol_id` int(11) NOT NULL,
  `_open` decimal(11,2) NOT NULL,
  `_high` decimal(11,2) NOT NULL,
  `_low` decimal(11,2) NOT NULL,
  `_close` decimal(11,2) NOT NULL,
  `_volume` bigint(20) NOT NULL,
  `add_date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Symbol_Id` (`symbol_id`,`add_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8$$

Update: There are no nulls, wherever there's a holiday/weekend, the table does not carry any record for that date.

更新：没有空值，只要有假期/周末，表格就没有该日期的任何记录。

回答by fthiella

If you are using MySQL 8, the preferable solution would make use of the window functions FIRST_VALUE()and/or LAST_VALUE(), which are now available. Please have a look at Lukas Eder's answer.

如果您使用的是 MySQL 8，最好的解决方案是使用窗口函数FIRST_VALUE()和/或LAST_VALUE()，它们现在可用。请看看Lukas Eder 的回答。

But if you're using an older version of MySQL, those functions are not supported. You have to simulate them using some kind of workarounds, for example you could make use of the aggregated string function GROUP_CONCAT()that creates a set of all _openand _closevalues of the week ordered by _datefor _openand by _date descfor _close, and extracting the first element of the set:

但是，如果您使用的是旧版本的 MySQL，则不支持这些功能。您可以选择使用某种解决方法来模拟它们，例如你可以利用聚集串功能的GROUP_CONCAT（）创建一组所有的_open和_close一周的值下令_date对_open和_date desc对_close和提取的第一要素放：

select
  min(_low),
  max(_high),
  avg(_volume),
  concat(year(_date), "-", lpad(week(_date), 2, '0')) AS myweek,
  substring_index(group_concat(cast(_open as CHAR) order by _date), ',', 1 ) as first_open,
  substring_index(group_concat(cast(_close as CHAR) order by _date desc), ',', 1 ) as last_close
from
  mystockdata
group by
  myweek
order by
  myweek
;

Another solution would make use of subqueries with LIMIT 1in the SELECTclause:

另一种解决方案是LIMIT 1在SELECT子句中使用子查询：

select
  min(_low),
  max(_high),
  avg(_volume),
  concat(year(_date), "-", lpad(week(_date), 2, '0')) AS myweek,
  (
    select _open
    from mystockdata m
    where concat(year(_date), "-", lpad(week(_date), 2, '0'))=myweek
    order by _date
    LIMIT 1
  ) as first_open,
  (
    select _close
    from mystockdata m
    where concat(year(_date), "-", lpad(week(_date), 2, '0'))=myweek
    order by _date desc
    LIMIT 1
  ) as last_close
from
  mystockdata
group by
  myweek
order by
  myweek
;

Please note I added the LPAD()string function to myweek, to make the week number always two digits long, otherwise weeks won't be ordered correctly.

请注意，我将LPAD()字符串函数添加到myweek，以使周数始终为两位数，否则将无法正确订购周数。

Also be careful when using substring_index in conjunction with group_concat(): if one of the grouped strings contains a comma, the function might not return the expected result.

将 substring_index 与 group_concat() 结合使用时也要小心：如果分组字符串之一包含逗号，则该函数可能不会返回预期结果。

回答by Lukas Eder

Starting with MySQL 8, you would ideally use window functionsfor the task:

从 MySQL 8 开始，您最好为任务使用窗口函数：

WITH 
  t1 AS (
    SELECT _low, _high, _volume, CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
    FROM mystockdata
  ),
  t2 AS (
    SELECT 
      t1.*, 
      FIRST_VALUE(_open) OVER (PARTITION BY myweek ORDER BY _date) AS first_open,
      FIRST_VALUE(_close) OVER (PARTITION BY myweek ORDER BY _date DESC) AS last_close
    FROM t1
  )
SELECT MIN(_low), MAX(_high), AVG(_volume), myweek, MIN(first_open), MAX(last_close)
FROM t2
GROUP BY myweek
ORDER BY myweek;

回答by Mike Brant

You will likely need to COALESCEfunction to get the first value. However, you need to make sure that days without data (weekends and holidays) have a null value for _openon those days without data.

您可能需要COALESCE运行才能获得第一个值。但是，您需要确保没有数据的日子（周末和假期）_open在没有数据的日子里有一个空值。

Usage would be:

用法是：

SELECT MIN(_low), MAX(_high), AVG(_volume), COALESCE(_open)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

For the last() value, I can only think of a pretty hacky solution, which would be to use GROUP_CONCATand then string manipulation to get the last value from the list. So perhaps something like this:

对于 last() 值，我只能想到一个非常hacky的解决方案，即使用GROUP_CONCAT然后字符串操作从列表中获取最后一个值。所以也许是这样的：

SELECT MIN(_low), MAX(_high), AVG(_volume), COALESCE(_open), SUBSTRING_INDEX(GROUP_CONCAT(_close), ',', -1)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

Note you could also use the GROUP_CONCATapproach for the first item instead of coalesce if you wanted consistent looking query

请注意GROUP_CONCAT，如果您想要一致的查询，您也可以使用第一项的方法而不是合并

SELECT MIN(_low), MAX(_high), AVG(_volume), SUBSTRING_INDEX(GROUP_CONCAT(_open), ',', 1), SUBSTRING_INDEX(GROUP_CONCAT(_close), ',', -1)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

For GROUP_CONCATto work properly you also need to make sure the dates without values have null in _openand _closefields.

为了GROUP_CONCAT正常工作，您还需要确保没有值的日期在_open和_close字段中为空。

回答by charlesqwu

Basically, what you need to do:

基本上，你需要做的是：

1. group by PRODUCTID
2. within each group, order by LOCATION
3. select the FIRST price for the same product as ordered by LOCATION

1. 按 PRODUCTID 分组
2. 在每个组内，按位置排序
3. 选择与 LOCATION 订购的相同产品的第一个价格

Putting them together, you can use the following query:

将它们放在一起，您可以使用以下查询：

SELECT PRODUCTID, 
   SUBSTRING_INDEX(GROUP_CONCAT(CAST(LOCATION AS CHAR) ORDER BY LOCATION DESC), ',', 1) AS LOCATION,
   SUBSTRING_INDEX(GROUP_CONCAT(CAST(PRICE AS CHAR) ORDER BY LOCATION DESC), ',', 1) AS PRICE
FROM ProductLocation
GROUP BY PRODUCTID;

Note that MySQL does not have FIRST() and LAST() aggregate functions for GROUP BY but such FIRST() AND LAST() can be simulated by using GROUP_CONCAT() and SUBSTRING_INDEX() functions.

请注意，MySQL 没有用于 GROUP BY 的 FIRST() 和 LAST() 聚合函数，但可以使用 GROUP_CONCAT() 和 SUBSTRING_INDEX() 函数模拟此类 FIRST() 和 LAST()。