我有一个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 ： $ 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;
更多推荐
选择组中的第一个和最后一个值
发布评论