具有内部联接的 MySql 开始和结束价格(最小、最大)

Question

我有一个价格表，有 2 种类型。金属1和金属2。

我已经成功地获取了每天每种金属组的最高、最低价格。

我如何也选择每天的开始(第一)和结束(最后)？

我已经快到了，但很难获得这两个最终价格......

My SQL 摆弄示例数据: http://sqlfiddle.com/#!9/ca4867/1

到目前为止我的查询:

    select 
       highp.metal_price_datetime_IST AS high_price_metal_price_datetime_IST
     , highp.metal_price as highest_price
     , lowp.report_term
     , lowp.metal_id
     , lowp.metal_price as lowest_price
     , lowp.metal_price_datetime_IST AS low_price_metal_price_datetime_IST
from (select @report_term:=concat(day(metal_price_datetime_IST), ' ', monthname(metal_price_datetime_IST), ' ', year(metal_price_datetime_IST)) as report_term
           , metal_price_datetime_IST
           , metal_price
           , metal_id
           , case when @report_term=@old_report_term then @rn1:=@rn1+1  else @rn1:=1 end as rn
           , @old_report_term:=@report_term
      from metal_prices
      cross join (select @rn1:=0, @old_report_term:='') inituservar1
      where metal_price_datetime_IST BETWEEN '2018-02-01' AND LAST_DAY('2018-02-01')
      order by metal_id, report_term, metal_price asc) lowp
inner join (select @report_term2:=concat(day(metal_price_datetime_IST), ' ', monthname(metal_price_datetime_IST), ' ', year(metal_price_datetime_IST)) as report_term
           , metal_price_datetime_IST
           , metal_price
           , metal_id
           , case when @report_term2=@old_report_term2 then @rn2:=@rn2+1  else @rn2:=1 end as rn
           , @old_report_term2:=@report_term2
      from metal_prices
      cross join (select @rn2:=0, @old_report_term2:='') inituservar1
      where metal_price_datetime_IST BETWEEN '2018-02-01' AND LAST_DAY('2018-02-01')
      order by metal_id, report_term, metal_price desc) highp
 on lowp.rn=highp.rn
 and lowp.metal_id = highp.metal_id
 and lowp.report_term = highp.report_term
 and lowp.rn = 1
 and (lowp.metal_id = 1 or lowp.metal_id = 2)
 order by lowp.metal_price_datetime_IST DESC

Answer 1

您在 fiddle 中的查询对于需要完成的操作来说似乎太复杂了。我已经重构并重写了查询。基本上，查询分为两部分。第一个 maxminprice 确定每种金属每天的最高和最低价格。非常坦率的。第二部分 firSTLastprice 有点复杂。它找出每天每种金属的最大和最小时间戳。然后连接回主表以获取这些时间戳的值。其中的 case 语句用于合并最大和最小(第一次和最后一次)时间的结果，这样我们就不必执行两次查询。

SELECT maxminprice.metal_id, 
       maxminprice.metal_price_datetime, 
       maxminprice.max_price, 
       maxminprice.min_price, 
       firstlastprice.first_price, 
       firstlastprice.last_price 
FROM   (SELECT metal_id, 
               DATE(metal_price_datetime) metal_price_datetime, 
               MAX(metal_price)           max_price, 
               MIN(metal_price)           min_price 
        FROM   metal_prices 
        GROUP  BY metal_id, 
                  DATE(metal_price_datetime) 
        ORDER  BY metal_id, 
                  DATE(metal_price_datetime)) maxminprice 
       INNER JOIN (SELECT mp.metal_id, 
                          day_range.metal_price_datetimefl, 
                          SUM(CASE 
                                WHEN TIME(mp.metal_price_datetime) = first_time 
                              THEN 
                                mp.metal_price 
                                ELSE NULL 
                              END) first_price, 
                          SUM(CASE 
                                WHEN TIME(mp.metal_price_datetime) = last_time 
                              THEN 
                                mp.metal_price 
                                ELSE NULL 
                              END) last_price 
                   FROM   metal_prices mp 
                          INNER JOIN (SELECT metal_id, 
                                             DATE(metal_price_datetime) 
                                             metal_price_datetimefl, 
                                             MAX(TIME(metal_price_datetime)) 
                                             last_time, 
                                             MIN(TIME(metal_price_datetime)) 
                                             first_time 
                                      FROM   metal_prices 
                                      GROUP  BY metal_id, 
                                                DATE(metal_price_datetime)) 
                                     day_range 
                                  ON mp.metal_id = day_range.metal_id 
                                     AND DATE(mp.metal_price_datetime) = 
                                         day_range.metal_price_datetimefl 
                                     AND TIME(mp.metal_price_datetime) IN 
                                         ( last_time, first_time ) 
                   GROUP  BY mp.metal_id, 
                             day_range.metal_price_datetimefl) firstlastprice 
               ON maxminprice.metal_id = firstlastprice.metal_id 
                  AND maxminprice.metal_price_datetime = 
                      firstlastprice.metal_price_datetimefl