SQL查询 - 查找日常最小值，最大值和时间的最小值和最大值发生

Question

我有一个表，看起来像这样的：

_Datetime   | Value 
2015-05-01 06:00:00 | 12.3 
2015-05-01 06:20:00 | 12.5 
2015-05-01 06:40:00 | 12.3 
2015-05-01 07:00:00 | 13.5 
2015-05-01 07:20:00 | 14.5 
2015-05-01 07:40:00 | 14.3 
2015-05-01 08:00:00 | 18.2 
2015-05-01 08:20:00 | 15.0 
2015-05-01 08:40:00 | 15.0 

2015-05-02 06:00:00 | 19.2 
2015-05-02 06:20:00 | 7.3 
2015-05-02 06:40:00 | 11.4 
2015-05-02 07:00:00 | 9.5 
2015-05-02 07:20:00 | 7.6 
2015-05-02 07:40:00 | 6.6 
2015-05-02 08:00:00 | 10.4 
2015-05-02 08:20:00 | 19.3 
2015-05-02 08:40:00 | 15.4 

2015-05-03 06:00:00 | 8.7 
2015-05-03 06:20:00 | 8.6 
2015-05-03 06:40:00 | 8.6 
2015-05-03 07:00:00 | 21.5 
2015-05-03 07:20:00 | 12.4 
2015-05-03 07:40:00 | 7.3 
2015-05-03 08:00:00 | 10.8 
2015-05-03 08:20:00 | 12.5 
2015-05-03 08:40:00 | 10.6 

我想：

-select分钟，最大和平均每天

- 选择时间，其中最大和最小值发生（我只想选择一个行 - 第一次发生）

_Date  | _Min | _MinTime | _Max | _MaxTime | Avg | 
2015-05-01 | 12.3 | 06:00:00 | 18.2 | 08:00:00 | 14.18 | 
2015-05-02 | 6.6 | 07:40:00 | 19.3 | 08:20:00 | 11.86 | 
2015-05-03 | 7.3 | 07:40:00 | 21.5 | 07:00:00 | 11.22 | 

我可以很容易地得到最小值，最大值和平均值，但我陷入了困境。

Answer 1

试试这个 - 应返回只有一组每天的值。 （编辑 - 增加平均值，按评论中的建议四舍五入）。

; with cte as (
select * 
    , cast (_datetime as date) as [DateFormat] 
    , cast (_datetime as time) as [TimeFormat] 
    , row_number() over (partition by cast (_datetime as date) order by Value, _datetime) RNmin 
    , row_number() over (partition by cast (_datetime as date) order by Value desc, _datetime) RNmax 
    , Avg(value) over (partition by cast (_datetime as date)) as AvgVal 
from MyTable 
) 
select a.DateFormat, a.value as MinValue, a.TimeFormat as MinTime 
    , b.value as MaxValue, b.TimeFormat as MaxTime 
    , cast(a.AvgVal as decimal(5,2)) as AverageValue 
from Cte a 
join cte b 
on a.DateFormat = B.Dateformat and a.RNmin = 1 and b.RNmax = 1 

Answer 2

-

this will get you the mins..... 
create table table1 
(_date datetime, 
value float); 

insert into table1 values (
'2015-05-01 06:00:00', 12.3) 
,('2015-05-01 06:20:00',12.5) 
<('2015-05-02 06:00:00', 19.2) 
,('2015-05-02 06:20:00', 7.3) 

select * from 
(select min(value) as min1 ,cast(_date as date) as date1 from table1 group by cast(_date as date)) 
as i1 
inner join 
(select cast(_date as date) _date1,value, cast(_date as time) _time1 from table1) as i2 

on i1.min1= i2.value and 
cast(i2._date1 as date)= i1.date1 

Answer 3

SELECT x.* 
    , min.datetime min_time 
    , max.datetime max_time 
    FROM 
    (SELECT DATE(datetime) date 
      , MIN(value) min_value 
      , MAX(value) max_value 
      , AVG(value) 
     FROM my_table 
     GROUP 
      BY DATE(datetime) 
    ) x 
    JOIN my_table min 
    ON DATE(min.datetime) = x.date 
    AND min.value = x.min_value 
    JOIN my_table max 
    ON DATE(max.datetime) = x.date 
    AND max.value = x.max_value;