需要帮助从sql数据库获取高值和低值及其对应的日期吗

zpgglvta 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(286)

我试图使用这个，但它只是超时，并不断加载永远。我想得到一个弹头的高点，低点，和高点和低点对应的日期

SELECT usd,date,slug,datediff(CURDATE(), date) as datediff FROM historical h 
inner join(
SELECT MIN(usd + 0) as low FROM historical GROUP BY slug
)i 
inner join(
SELECT MAX(usd + 0) as high FROM historical GROUP BY slug
)j 
WHERE h.usd = i.low OR h.usd = j.high

样本数据：

slug    |   usd     |    date .     
------------------------------------        
gold    |   435    |  01-05-2015 . 
gold    |   5      |  01-12-2015 . 
gold    |   251    |  01-06-2015 . 
gold    |   353    |  01-07-2015 . 
silver  |   5      |  01-08-2015 . 
silver  |   4      |  01-09-2015 . 
silver  |   78     |  01-15-2015 . 
silver  |   100    |  01-25-2015 .

我想要的是：

slug     |  high   |    highdate   |   low    |  lowdate 
------------------------------------------------------------
gold     |  435   |   01-05-2015  |   5     |  01-12-2015
silver   |  100   |   01-25-2015  |   4     |  01-09-2015

当我试着把它用于低值时，它100%完美地工作

SELECT usd,btc,date,slug,datediff(CURDATE(), date) as datediff FROM historical h 
inner join(
    SELECT MIN(usd + 0) as low FROM historical GROUP BY slug
)i 
WHERE h.usd = i.low

sql mysql

来源：https://stackoverflow.com/questions/52012647/need-help-getting-high-and-low-values-and-their-corresponding-dates-from-an-sql

1条答案

按热度按时间

h43kikqp1#

我想你想要：

select h.slug,
       min(case when replace(usd, '$', '') + 0 = min_usd then usd end) as min_usd,
       min(case when replace(usd, '$', '') + 0 = min_usd then usd end) as min_date,
       max(case when replace(usd, '$', '') + 0 = max_usd then usd end) as max_usd,
       min(case when replace(usd, '$', '') + 0 = max_usd then usd end) as max_date
from historical h join
     (select slug, min(replace(usd, '$', '') + 0) as min_usd,
             max(replace(usd, '$', '') + 0) as max_usd
      from historical 
      group by slug
     ) s
     on h.slug = s.slug
group by h.slug;

计算 min() 以及 max() 这相当棘手，因为您将值存储为字符串。我强烈建议您将值存储为十进制/数字类型。这对货币来说是合适的。
编辑：
问题稍有变化。适当的查询似乎是：

select h.slug,
       min(case when usd + 0 = min_usd then usd end) as min_usd,
       min(case when usd + 0 = min_usd then usd end) as min_date,
       max(case when usd + 0 = max_usd then usd end) as max_usd,
       min(case when usd + 0 = max_usd then usd end) as max_date
from historical h join
     (select slug, min(usd + 0) as min_usd,
             max(usd + 0) as max_usd
      from historical 
      group by slug
     ) s
     on h.slug = s.slug
group by h.slug;

所有的 + 0 只有当 usd 存储为字符串。否则数值比较就可以了。

赞(0）回复(0）举报 2021-06-21

我来回答

需要帮助从sql数据库获取高值和低值及其对应的日期吗

1条答案

相关问题

热门标签

最新问答