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