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

Question

我有一个 MySql 表，其中包含每日股票报价(开盘价、最高价、最低价、收盘价和交易量)，我正试图将其动态转换为每周数据。到目前为止，我有以下功能，适用于高点、低点和音量:

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

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

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

这是我的表的创建语句，用于了解架构:

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$$

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

Answer 1

如果您使用的是 MySQL 8，最好的解决方案是使用窗口函数 FIRST_VALUE()和/或 LAST_VALUE() ，现在可用。请查看Lukas Eder's answer .

但是，如果您使用的是旧版本的 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
;

另一种解决方案是在 SELECT 子句中使用带有 LIMIT 1 的子查询:

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
;

请注意，我添加了 LPAD() myweek 的字符串函数，使周数始终为两位数，否则将无法正确排序周数。

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