MySQL-每月的最小值和最大值与每日相反? [英] Mysql - Min and Max per month as oppose to daily?

问题描述

下面的查询执行其工作，以选择给定月份中每天的最低，最高，开始和最后价格.

The query below does its job to select the min, max, start and last price per day in a given month.

我想选择相同但整个月的数据，如显示给定月份而不是每天的总体效果.

I would like to select the same but for the whole month, as in show the overall performance for the given month instead of on a daily basis.

提琴: http://sqlfiddle.com/#!9/ca4867/10

    SELECT maxminprice.metal_id, 
       maxminprice.metal_price_datetime_IST, 
       maxminprice.max_price, 
       maxminprice.min_price, 
       firstlastprice.first_price, 
       firstlastprice.last_price
FROM   (SELECT metal_id, 
               DATE(metal_price_datetime) metal_price_datetime_IST, 
               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_IST)) maxminprice 
       INNER JOIN (SELECT mp.metal_id, 
                          day_range.metal_price_datetimefl, 
                          SUM(CASE 
                                WHEN TIME(mp.metal_price_datetime_IST) = first_time 
                              THEN 
                                mp.metal_price 
                                ELSE NULL 
                              END) first_price, 
                          SUM(CASE 
                                WHEN TIME(mp.metal_price_datetime_IST) = last_time 
                              THEN 
                                mp.metal_price 
                                ELSE NULL 
                              END) last_price 
                   FROM   metal_prices mp 
                          INNER JOIN (SELECT metal_id, 
                                             DATE(metal_price_datetime_IST) 
                                             metal_price_datetimefl, 
                                             MAX(TIME(metal_price_datetime_IST)) 
                                             last_time, 
                                             MIN(TIME(metal_price_datetime_IST)) 
                                             first_time 
                                      FROM   metal_prices 
                                      GROUP  BY metal_id, 
                                                DATE(metal_price_datetime_IST)) 
                                     day_range 
                                  ON mp.metal_id = day_range.metal_id 
                                     AND DATE(mp.metal_price_datetime_IST) = 
                                         day_range.metal_price_datetimefl 
                                     AND TIME(mp.metal_price_datetime_IST) 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_IST = 
                      firstlastprice.metal_price_datetimefl
                  AND maxminprice.metal_price_datetime_IST BETWEEN '2018-02-01' AND LAST_DAY('2018-02-01')
                      ORDER BY  metal_id, metal_price_datetime_IST DESC

推荐答案

以下是更改为每个月工作的查询.我没有更改列名，因此您可能需要考虑这样做以获得更好的维护.另外，我没有使用数月或数年的数据对其进行测试，因此您应该在开始使用它之前进行此测试.

Here is the query changed to work for each month. I have not changed the column names so you might need to look into doing that for better maintenance. Also, I have not tested this with data for multiple months or over years so you should do that before you start using it.

SELECT maxminprice.metal_id, 
       maxminprice.metal_price_datetime_IST, 
       maxminprice.max_price, 
       maxminprice.min_price, 
       firstlastprice.first_price, 
       firstlastprice.last_price
FROM   (SELECT metal_id, 
               DATE_FORMAT(metal_price_datetime_IST, "%Y%m")  metal_price_datetime_IST, 
               MAX(metal_price)           max_price, 
               MIN(metal_price)           min_price 
        FROM   metal_prices 
        GROUP  BY metal_id, 
                 DATE_FORMAT(metal_price_datetime_IST, "%Y%m") 
        ORDER  BY metal_id, 
                  DATE_FORMAT(metal_price_datetime_IST, "%Y%m")) maxminprice 
       INNER JOIN (SELECT mp.metal_id, 
                          day_range.metal_price_datetimefl, 
                          SUM(CASE 
                                WHEN mp.metal_price_datetime_IST = first_time 
                              THEN 
                                mp.metal_price 
                                ELSE NULL 
                              END) first_price, 
                          SUM(CASE 
                                WHEN mp.metal_price_datetime_IST = last_time 
                              THEN 
                                mp.metal_price 
                                ELSE NULL 
                              END) last_price 
                   FROM   metal_prices mp 
                          INNER JOIN (SELECT metal_id, 
                                             DATE_FORMAT(metal_price_datetime_IST, "%Y%m") 
                                             metal_price_datetimefl, 
                                             MAX(metal_price_datetime_IST) 
                                             last_time, 
                                             MIN(metal_price_datetime_IST)
                                             first_time 
                                      FROM   metal_prices 
                                      GROUP  BY metal_id, 
                                                DATE_FORMAT(metal_price_datetime_IST, "%Y%m")) day_range 
                                  ON mp.metal_id = day_range.metal_id 
                                     AND DATE_FORMAT(mp.metal_price_datetime_IST, "%Y%m") = 
                                         day_range.metal_price_datetimefl 
                                     AND mp.metal_price_datetime_IST 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_IST = 
                      firstlastprice.metal_price_datetimefl

这篇关于MySQL-每月的最小值和最大值与每日相反?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！