Datetime varchar
2015/2/25 09:34:00 1.97
2015/2/25 09:36:00 1.96
2015/2/25 09:54:00 1.85
2015/2/25 09:56:00 1.83
2015/2/25 09:58:00 1.82
2015/2/25 10:00:00 1.81
2015/2/25 10:02:00 1.81
2015/2/25 10:26:00 1.67
2015/2/25 10:28:00 1.66
2015/2/25 10:30:00 1.65
2015/2/25 10:32:00 1.63
2015/2/25 10:38:00 1.6
2015/2/25 10:40:00 1.59
2015/2/25 10:54:00 1.49
2015/2/25 10:56:00 1.48
2015/2/25 10:58:00 1.47
2015/2/25 11:00:00 1.46
2015/2/25 11:02:00 1.45
2015/2/25 11:04:00 1.44
2015/3/12 05:00:00 3.15
2015/3/12 05:02:00 3.15
2015/3/12 05:04:00 3.15
2015/3/12 05:06:00 3.15
2015/3/12 05:08:00 3.15
2015/3/12 05:10:00 3.15
2015/3/12 05:12:00 3.15
2015/3/12 05:14:00 3.15
2015/3/12 05:16:00 3.15
2015/3/12 05:18:00 3.14
2015/3/12 05:20:00 3.14
2015/3/12 05:22:00 3.14
2015/3/12 05:28:00 3.13
2015/3/12 05:30:00 3.13
2015/3/12 05:32:00 3.11
2015/3/12 05:34:00 3.1
2015/3/12 05:36:00 3.1
Average per hour, the format for
Yyyy/m/dd hh: 00:00 avg
CodePudding user response:
Will part time field to hours, and then grouped accumulation, and then calculate the average, need 2 SQL, you said is the average days 24 hours on average, or existing hours on average? 24 hours if it is many days on average, need to get maximum and minimum date is presupposed X24 again calculate the average,CodePudding user response:
; With TBL as
(
Select the CONVERT (varchar (13), dt, 120) + ': 00:00' as dt, val from data table
)
Select the CONVERT (datetime, dt), avg (val) as (avg) from TBL
Group by dt
CodePudding user response:
Support the answer above,CodePudding user response:
Verified positive solution on the second floor