在BigQuery中使用时间格式时,如何计算平均时间? [英] How to calculate average time when it is used TIME format in Bigquery?
本文介绍了在BigQuery中使用时间格式时,如何计算平均时间?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试获取AVG时间,但是AVG函数不支持该时间格式。我试着使用强制转换函数,就像在一些帖子中解释的那样,但它似乎无论如何都不起作用。谢谢
WITH october_fall AS
(SELECT
start_station_name,
end_station_name,
start_station_id,
end_station_id,
EXTRACT (DATE FROM started_at) AS start_date,
EXTRACT(DAYOFWEEK FROM started_at) AS start_week_date,
EXTRACT (TIME FROM started_at) AS start_time,
EXTRACT (DATE FROM ended_at) AS end_date,
EXTRACT(DAYOFWEEK FROM ended_at) AS end_week_date,
EXTRACT (TIME FROM ended_at) AS end_time,
DATETIME_DIFF (ended_at,started_at, MINUTE) AS total_lenght,
member_casual
FROM
`ciclystic.cyclistic_seasonal_analysis.fall_202010` AS fall_analysis
ORDER BY
started_at DESC)
SELECT
COUNT (start_week_date) AS avg_start_1,
AVG (start_time) AS avg_start_time_1, ## here is where the problem start
member_casual
FROM
october_fall
WHERE
start_week_date = 1
GROUP BY
member_casual
推荐答案
在下面尝试
SELECT
COUNT (start_week_date) AS avg_start_1,
TIME(
EXTRACT(hour FROM AVG(start_time - '0:0:0')),
EXTRACT(minute FROM AVG(start_time - '0:0:0')),
EXTRACT(second FROM AVG(start_time - '0:0:0'))
) as avg_start_time_1
member_casual
FROM
october_fall
WHERE
start_week_date = 1
GROUP BY
member_casual
另一个选项是
SELECT
COUNT (start_week_date) AS avg_start_1,
PARSE_TIME('0-0 0 %H:%M:%E*S', '' || AVG(start_time - '0:0:0')) as avg_start_time_1
member_casual
FROM
october_fall
WHERE
start_week_date = 1
GROUP BY
member_casual
这篇关于在BigQuery中使用时间格式时,如何计算平均时间?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文