sql请求检索每天最大、最小的第一条和最后一条记录

qxsslcnc  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(437)

我想得到最小值和最大值,我的数据库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
juzqafwq

juzqafwq1#

如果对你有用的话,我建议 group_concat() / substring_index() 技巧:

SELECT MIN(low), MAX(high),
       CONCAT(YEAR(date), '-', WEEK(date)) AS myweek,
       SUBSTRING_INDEX(GROUP_CONCAT(open ORDER BY date), ',', 1) as first_open,
       SUBSTRING_INDEX(GROUP_CONCAT(close ORDER BY date DESC), ',', 1) as last_close
FROM prices
GROUP BY myweek
ORDER BY date;

笔记:
这个 first_open 以及 last_close 列将是字符串。
假设是 open 以及 close 没有逗号。如果有,请使用不同的分隔符。 GROUP_CONCAT() 具有大约1000字节的默认内部限制。您可以使用服务器参数来调整它。

相关问题