我想取时间列的平均值并将输出显示为时间列
我写了一个SQL查询,它给了我错误的输出。
SELECT
SOD_EOD_Job_Ran_Date,
cast(avg(SOD_End_Time) as time),
avg(SOD_End_Time)
FROM aap_auto
group by SOD_EOD_Job_Ran_Date;
这是输入
SOD_EOD_Job_Ran_Date, SOD_End_Time
2019-08-09 00:00:00, 05:45:00
2019-08-09 00:00:00, 05:50:00
2019-08-09 00:00:00, 06:00:00
2019-08-09 00:00:00, 05:40:00
2019-08-09 00:00:00, 05:48:00
2019-08-09 00:00:00, 05:55:00
2019-08-09 00:00:00, 05:35:00
2019-08-09 00:00:00, 05:38:00
2019-08-09 00:00:00, 05:42:00
2019-08-09 00:00:00, 05:36:00
这是输出
SOD_EOD_Job_Ran_Date, cast(avg(SOD_End_Time) as time), avg(SOD_End_Time)
2019-08-09 00:00:00, null, 54890.0000
这里 'cast' 给出 null 作为输出。 在某些情况下,此查询会给出正确的输出
SOD_EOD_Job_Ran_Date, cast(avg(SOD_End_Time) as time), avg(SOD_End_Time)
2019-08-10 00:00:00, 05:46:20, 54620.0000
如果尝试过SOD_End_Time作为 varchar 和时间,我从来没有得到你的时间
CREATE TABLE aap_auto
(`SOD_EOD_Job_Ran_Date` datetime, `SOD_End_Time` varchar(8))
;
INSERT INTO aap_auto
(`SOD_EOD_Job_Ran_Date`, `SOD_End_Time`)
VALUES
('2019-08-09 00:00:00', '05:45:00'),
('2019-08-09 00:00:00', '05:50:00'),
('2019-08-09 00:00:00', '06:00:00'),
('2019-08-09 00:00:00', '05:40:00'),
('2019-08-09 00:00:00', '05:48:00'),
('2019-08-09 00:00:00', '05:55:00'),
('2019-08-09 00:00:00', '05:35:00'),
('2019-08-09 00:00:00', '05:38:00'),
('2019-08-09 00:00:00', '05:42:00'),
('2019-08-09 00:00:00', '05:36:00')
;
你和
SELECT
SOD_EOD_Job_Ran_Date,
TIME_FORMAT(SEC_TO_TIME(avg(TIME_TO_SEC(SOD_End_Time))),'%H:%i%s' ),
avg(SOD_End_Time)
FROM aap_auto
group by SOD_EOD_Job_Ran_Date;
以下结果
SOD_EOD_Job_Ran_Date SEC_TO_TIME(avg(TIME_TO_SEC(SOD_End_Time))) avg(SOD_End_Time)
2019-08-09T00:00:00Z 05:44:54 5.1