我想得到最小值和最大值,我的数据库mysql中的第一个和最后一个记录。我正在寻找一个不超过15分钟的高性能查询。
我测试过:
SELECT
MIN(low),
MAX(high),
CONCAT(YEAR(date), "-", WEEK(date)) AS myweek,
(select open from prices m where WEEK(m.date)=WEEK(prices.date) order by date LIMIT 1) as first_open,
(select close from prices m where WEEK(m.date)=WEEK(prices.date) order by date desc LIMIT 1) as last_close
FROM prices
GROUP BY myweek
ORDER BY date;
但我有个错误:
Erreur dans la requête (1055): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'siteinfo.prices.date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
我无法更正此错误,因为我没有访问conf文件的权限,也没有用户superadmin。
我也测试过:
select
DATE_FORMAT(date, "%Y" "%m" "%d") as datetime,
(select open from prices m where m.date like CONCAT(YEAR(datetime), "-[0]", Month(datetime), "-[0]", DAY(datetime), "%") and fk_cryptoid = 'f2a0ba90-93df-11e8-af1b-5968de90d63b' order by date ASC LIMIT 1) as open,
(select close from prices m where m.date like CONCAT(YEAR(datetime), "-[0]", Month(datetime), "-[0]", DAY(datetime), "%") and fk_cryptoid = 'f2a0ba90-93df-11e8-af1b-5968de90d63b' order by date DESC LIMIT 1) as close,
min(low) as low,
max(high) as high
from prices
where fk_cryptoid = 'f2a0ba90-93df-11e8-af1b-5968de90d63b'
GROUP BY datetime;
但是concat()不返回日期和月份,并且加上零:2018-1-2,而不是2018-01-02。那么这个请求需要太多时间。
表价格模型:
id int(11) Incrément automatique
close double NULL
open double NULL
low double NULL
high double NULL
date datetime NULL
createdAt datetime
updatedAt datetime
fk_cryptoid char(36) NULL
1条答案
按热度按时间juzqafwq1#
如果对你有用的话,我建议
group_concat()
/substring_index()
技巧:笔记:
这个
first_open
以及last_close
列将是字符串。假设是
open
以及close
没有逗号。如果有,请使用不同的分隔符。GROUP_CONCAT()
具有大约1000字节的默认内部限制。您可以使用服务器参数来调整它。