选择组中的第一个和最后一个值 [英] Selecting first and last values in a group

问题描述

我有一个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值应该是该周的最后一个_close。

是否可以在MySql中选择类似FIRST（）和LAST（）的方法，被封装在一个SELECT中而不是使用嵌套的select查询？

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

 分隔符$$ 
 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 $$ 
  

更新：没有空位，无论有假期还是周末，表格中都没有记录f或者那个日期。

解决方案

MySql没有 First（）或 Last（）集合函数。但是你可以使用 GROUP_CONCAT 来模拟它们，它会创建一组所有 _open 和 _close < _open 和 _date _date desc 为 _close ，并提取集合的第一个元素：

  SELECT 
 MIN（_low），
 MAX（_high），
 AVG（_volume），
 CONCAT（YEAR（_date）， -  WEEK（_date））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 _date; 
  

另一个解决方案就是使用子查询和 LIMIT 1 SELECT 子句中的c $ c>：
$ b $ pre $ SELECT
MIN（_low），
MAX（_high），
AVG（_volume），
CONCAT（YEAR（_date）， - ，WEEK（_date））as myweek，
（从mystockdata m选择_open，其中WEEK（m._date）= WEEK（mystockdata._date）order by _date LIMIT 1）作为first_open，
（从mystockdata m选择_close，其中WEEK（m._date）= WEEK mydatedata._date）order by _date desc LIMIT 1）as last_close
FROM mystockdata
GROUP BY myweek
ORDER BY _date;


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:

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.

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?

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

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.

解决方案

MySql does not have a First() or a Last() aggregate function. But you could simulate them using GROUP_CONCAT, that creates a set of all _open and _close values of the week ordered by _date for _open and by _date desc for _close, and extracting the first element of the set:

SELECT
  MIN(_low),
  MAX(_high),
  AVG(_volume),
  CONCAT(YEAR(_date), "-", WEEK(_date)) 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 _date;

Another solution would be this, that uses subqueries with LIMIT 1 in the SELECT clause:

SELECT
  MIN(_low),
  MAX(_high),
  AVG(_volume),
  CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek,
  (select _open from mystockdata m where WEEK(m._date)=WEEK(mystockdata._date) order by _date LIMIT 1) as first_open,
  (select _close from mystockdata m where WEEK(m._date)=WEEK(mystockdata._date) order by _date desc LIMIT 1) as last_close
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

这篇关于选择组中的第一个和最后一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！