选择组中的第一个和最后一个值 [英] 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;
另一个解决方案就是使用子查询和 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: 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: 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 Another solution would be this, that uses subqueries with
这篇关于选择组中的第一个和最后一个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋! 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;
SELECT MIN(_low), MAX(_high), AVG(_volume),
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;
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$$
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;
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;