按BigQuery中的值范围分组 [英] Group by range of values in bigquery

问题描述

Bigquery中有什么方法可以按绝对值而不是按值范围进行分组?

Is there any way in Bigquery to group by not the absolute value but a range of values?

我有一个查询，查询带有4个不同数字分组依据的产品表.我正在寻找的是一种高效的分组方式，例如:按"A±1000"等或"A±10％ofA"分组.

I have a query that looks in a product table with 4 different numeric group by's. What I am looking for is an efficient way to group by in a way like: group by "A±1000" etc. or "A±10%ofA".

预先感谢，

推荐答案

您可以将列生成为命名范围"，然后按该列分组.以您的 A + -1000 案例为例:

You can generate a column as a "named range" then group by the column. As an example for your A+-1000 case:

with data as ( 
select 100 as v union all
select 200 union all
select 2000 union all
select 2100 union all
select 2200 union all
select 4100 union all
select 8000 union all
select 8000
)
select count(v), ARRAY_AGG(v), ranges
FROM data, unnest([0, 2000, 4000, 6000, 8000]) ranges
WHERE data.v >= ranges - 1000 AND data.v < ranges + 1000
GROUP BY ranges

输出:

+-----+------------------------+--------+
| f0_ |          f1_           | ranges |
+-----+------------------------+--------+
|   2 |          ["100","200"] |      0 |
|   3 | ["2000","2100","2200"] |   2000 |
|   1 |               ["4100"] |   4000 |
|   2 |        ["8000","8000"] |   8000 |
+-----+------------------------+--------+

这篇关于按BigQuery中的值范围分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！