BigQuery SQL:平均值、几何平均值、剔除异常值、中值
BigQuery SQL:平均值、几何平均值、剔除异常值、中值
提问于 2018-08-23 08:27:17
我正在计算在堆栈溢出上得到答复的平均时间，结果是没有意义的。
#standardSQL
WITH question_answers AS (
SELECT *
, timestamp_diff(answers.first, creation_date, minute) minutes
FROM (
SELECT creation_date
, (SELECT AS STRUCT MIN(creation_date) first, COUNT(*) c
FROM `bigquery-public-data.stackoverflow.posts_answers` b
WHERE a.id=b.parent_id
) answers
, SPLIT(tags, '|') tags
FROM `bigquery-public-data.stackoverflow.posts_questions` a
WHERE EXTRACT(year FROM creation_date) > 2015
), UNNEST(tags) tag
WHERE tag IN ('java', 'javascript', 'google-bigquery', 'firebase', 'php')
AND answers.c > 0
)
SELECT tag
, COUNT(*) questions
, ROUND(AVG(minutes), 2) first_reply_avg_minutes
FROM question_answers
GROUP BY tag
我该如何计算平均时间？
回答 1
Stack Overflow用户
发布于 2018-08-23 08:27:17
更新2019年:分享一些持久化公共UDF怎么样？
第一，中位数：
SELECT fhoffa.x.median([1,1,1,2,3,4,5,100,1000])
3.0实际上--在超过100小时(>6000分钟)的堆栈溢出上获得答案的平均时间似乎是错误的--而且很大程度上是由异常值驱动的。
而不是做一个简单的AVG()，你可以得到：
- 几何平均：
EXP(AVG(LOG(GREATEST(minutes,1)))) - 去除异常值后的平均值：
AVG(q) FROM (SELECT q FROM QUANTILES(q, 100) LIMIT 80 OFFSET 2))。 - 中位数：
all_minutes[OFFSET(CAST(ARRAY_LENGTH(all_minutes)/2 AS INT64))]
如果您使用这些替代方案中的任何一个，那么这些结果就更有意义了：
正如你在这里所看到的，在这个例子中，除去离群值会给出类似于几何平均值的结果，而中间值报告的数字甚至更低。用哪一种？你自己选吧。
WITH question_answers AS (
SELECT *
, timestamp_diff(answers.first, creation_date, minute) minutes
FROM (
SELECT creation_date
, (SELECT AS STRUCT MIN(creation_date) first, COUNT(*) c
FROM `bigquery-public-data.stackoverflow.posts_answers` b
WHERE a.id=b.parent_id
) answers
, SPLIT(tags, '|') tags
FROM `bigquery-public-data.stackoverflow.posts_questions` a
WHERE EXTRACT(year FROM creation_date) > 2015
), UNNEST(tags) tag
WHERE tag IN ('java', 'javascript', 'google-bigquery', 'firebase', 'php', 'sql', 'elasticsearch', 'apache-kafka', 'tensorflow')
AND answers.c > 0
)
SELECT * EXCEPT(qs, all_minutes)
, (SELECT ROUND(AVG(q),2) FROM (SELECT q FROM UNNEST(qs) q ORDER BY q LIMIT 80 OFFSET 2)) avg_no_outliers
, all_minutes[OFFSET(CAST(ARRAY_LENGTH(all_minutes)/2 AS INT64) )] median_minutes
FROM (
SELECT tag
, COUNT(*) questions
, ROUND(AVG(minutes), 2) avg_minutes
, ROUND(EXP(AVG(LOG(GREATEST(minutes,1)))),2) first_reply_avg_minutes_geom
, APPROX_QUANTILES(minutes, 100) qs
, ARRAY_AGG(minutes IGNORE NULLS ORDER BY minutes) all_minutes
FROM question_answers
GROUP BY tag
)
ORDER BY 2 DESC奖金MEDIAN() Elliott的UDF函数。
CREATE TEMP FUNCTION MEDIAN(arr ANY TYPE) AS ((
SELECT
IF(
MOD(ARRAY_LENGTH(arr), 2) = 0,
(arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2) - 1)] + arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2))]) / 2,
arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2))]
)
FROM (SELECT ARRAY_AGG(x ORDER BY x) AS arr FROM UNNEST(arr) AS x)
));页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51981353
复制相关文章
相似问题
腾讯云开发者
