Group By : -
Example : I need fetch avg time between 08:00:00 to 09:00:00
# STATION_ID, DATA_DATE, HUMIDITY
'9201', '2020-01-01 00:00:00', '93'
'9201', '2020-01-01 01:00:00', '93'
'9201', '2020-01-01 02:00:00', '94'
'9201', '2020-01-01 03:00:00', '94'
'9201', '2020-01-01 04:00:00', '95'
'9201', '2020-01-01 05:00:00', '95'
'9201', '2020-01-01 06:00:00', '96'
'9201', '2020-01-01 07:00:00', '96'
'9201', '2020-01-01 08:00:00', '96'
'9201', '2020-01-01 09:00:00', '96'
'9201', '2020-01-01 10:00:00', '96'
'9201', '2020-01-01 11:00:00', '95'
'9201', '2020-01-01 12:00:00', '95'
'9201', '2020-01-01 13:00:00', '95'
'9201', '2020-01-01 14:00:00', '94'
'9201', '2020-01-01 15:00:00', '92'
'9201', '2020-01-01 16:00:00', '89'
'9201', '2020-01-01 17:00:00', '89'
'9201', '2020-01-01 18:00:00', '90'
'9201', '2020-01-01 19:00:00', '91'
'9201', '2020-01-01 20:00:00', '93'
'9201', '2020-01-01 21:00:00', '93'
'9201', '2020-01-01 22:00:00', '94'
'9201', '2020-01-01 23:00:00', '96'
Example:- Filter data 08:00:00 and 09:00:00
select
ID,avg(humidity) as HR_830 from ( select id,humidity,Time(datadate) as
datatime from WEATHER_DATA where Time(data_date)>='08:00:00' and
Time(data_date)<='09:00:00' ) T1 group by id;
example
#Retrive data from date wise and use ID or Count data per day
select date(DATA_DATE),ID,count(*) from WEATHER_DATA
where ID in (11204,10980) and DATE between '2020-07-01 00:00:00'
and '2020-08-24 23:50:50'
group by date(DATE),ID
# ID, DATA_DATE Temp_Max Temp_Min
'89', '2020-08-20 00:00:00', '22.28' '22.36'
'89', '2020-08-20 01:00:00', '25.59' '25.71'
'89', '2020-08-20 02:00:00', '24.77' '25.25'
'89', '2020-08-20 03:00:00', '22.47' '22.68'
'89', '2020-08-20 04:00:00', '29.67' '30.25'
'89', '2020-08-20 05:00:00', '23.86' '24.13'
'75', '2020-08-20 00:00:00', '22.28' '22.36'
'75', '2020-08-20 01:00:00', '25.59' '25.71'
'75', '2020-08-20 02:00:00', '24.77' '25.25'
'75', '2020-08-20 03:00:00', '22.47' '22.68'
'75', '2020-08-20 04:00:00', '29.67' '30.25'
'75', '2020-08-20 05:00:00', '23.86' '24.13'
Note : Group by always used with aggregate function like MAX() ,MIN(),AVG()
Table column name is ID and DATA_DATE or Temp_Max,Temp_Min .So we have to convert to DATA_DATE in DATE .
select ID ,DATE(DATA_DATE) ,max( Temp_Max ) ,min(Temp_Min) from workstation group by DATE(DATA_DATE) ;
Output:-
# ID, DATE Temp_Max, Temp_Min
89 2020-08-11 '26.68' '26.88'
It is give only one ID data .If you pass also ID in group by statement . return all data all station and date wise.
example:
select ID ,DATE(DATA_DATE) ,max( Temp_Max ) ,min(Temp_Min) from workstation group by ID , DATE(DATA_DATE) ;
Output:-
# ID, DATE Temp_Max, Temp_Min
75', '2020-08-11 '25.02', '25.25'
89', '2020-08-11 '26.68' '26.88'
It is give output all ID base or date wise data.
Real World Example
drop table if exists test.Temperature_data ;
create table test.Temperature_data
select id , date(start_date) as datadate,max(Temp) as Tmax,min(Temp) as Tmin,max(HUMIDITY) H_Max,round(min(HUMIDITY),2) H_MIN,max(WIND_MAX) as Max_wind,sum(RAIN) Total_Rain from weather_data group by station_id,date(Start_date);
No comments:
Post a Comment