Data is extracted in minutes and averaged

Ask:

Now there is a table, the content is every 10 seconds one (1 days for 6*60*24=8640), and then I have to do is 5 minutes to a point in time to fetch the data (one day for 12*24=288), once all the values of the conditions are taken out. What should I write?

A: The SQL statement is as follows:

Extract data for each minute of the unit:

Select Node_id,avg (Ad1_value) as Ad1_value,avg (Ad2_value) as Ad2_value,date_format (Collect_date, '%Y-%m-%d%H:%i ') as Collect_date

From Realtime_data_3
where collect_date> ' 2014-12-06 12:00:00 '
and collect_date< ' 2014-12-07 12:00:00 '

Group BY Date_format (Collect_date, '%y-%m-%d%h:%i ');

5 divided into units: Select Floor (recdt/500) *500 as Gt,avg (Nowvalue) from Reg_conditigroup by the GT ORDER by GT;

10 divided into units: Select Floor (recdt/1000) *1000 as Gt,avg (Nowvalue) from Reg_conditigroup by the GT ORDER by GT;

The syntax for Date_format is as follows:

Definition and usage

The Date_format () function is used to display date/time data in different formats.

Grammar

Date_format (Date,format)

The date parameter is a valid day. format Specifies the date/time in which the output is formatted.

The formats you can use are:

format	Description
%a	Abbreviated Week name
%b	Abbreviated month name
%c	Month, value
%d	Day of the month with English prefixes
%d	Day of the month, value (00-31)
%e	Day of the month, value (0-31)
%f	Microseconds
%H	Hours (00-23)
%h	Hours (01-12)
%I	Hours (01-12)
%i	Minutes, value (00-59)
%j	Days of the Year (001-366)
%k	Hours (0-23)
%l	Hours (1-12)
%M	Month Name
%m	month, value (00-12)
%p	AM or PM
%r	Time, 12-hour (Hh:mm:ss AM or PM)
%s	Seconds (00-59)
%s	Seconds (00-59)
%T	Time, 24-hour (HH:MM:SS)
%u	Week (00-53) Sunday is the first day of the week
%u	Week (00-53) Monday is the first day of the week
%V	Week (01-53) Sunday is the first day of the week, with%x
%v	Week (01-53) Monday is the first day of the week, with%x
%W	Week name
%w	Day of the week (0= Sunday, 6 = Saturday)
%x	year, of which Sunday was the first day of the week, 4-bit, with%V used
%x	year, of which Monday was the first day of the week, 4-bit, with%v used
%Y	Year, 4 guests
%y	Year, 2 guests

Instance

The following script uses the Date_format () function to display different formats. We use now () to get the current date/time:

Date_format (now (), '%b%d%Y%h:%i%p ') Date_format (now (), '%m-%d-%y ') Date_format (now (), '%d%b%Y ') Date_format (now (), '% D%b%Y%t:%f ')

The results are similar:

Dec 11:45 pm12-29-200829 Dec 0829 Dec 2008 16:25:46.635

Data is extracted in minutes and averaged