作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
这是我在 BigQuery 中对公共(public)数据集的查询:
SELECT RANGE_BUCKET(reputation, [400000, 500000, 600000, 700000, 800000, 900000, 1000000, 1100000, 1200000]) AS reputation_group, COUNT(*) AS count
FROM `bigquery-public-data.stackoverflow.users`
Where reputation > 200000
GROUP BY 1
ORDER By 1
0: [0-400000]
1: [400001-500000]
2: [500001-600000]
....
SELECT bucket,
FORMAT('%i - %i', IFNULL(ranges[SAFE_OFFSET(bucket - 1)] + 1, 0), ranges[SAFE_OFFSET(bucket)]) AS reputation_group,
COUNT(*) AS COUNT
FROM `bigquery-public-data.stackoverflow.users`,
UNNEST([STRUCT([200000, 400000, 500000, 600000, 700000, 800000, 900000, 1000000, 1100000, 1200000] AS ranges)]),
UNNEST([RANGE_BUCKET(reputation, ranges)]) bucket
WHERE reputation > 200000
GROUP BY 1, 2
ORDER BY bucket
200001 - 400000
而不是
0 - 400000
最佳答案
以下是 BigQuery 标准 SQL
#standardSQL
SELECT bucket,
FORMAT('%i - %i', IFNULL(ranges[SAFE_OFFSET(bucket - 1)] + 1, 0), ranges[SAFE_OFFSET(bucket)]) AS reputation_group,
COUNT(*) AS COUNT
FROM `bigquery-public-data.stackoverflow.users`,
UNNEST([STRUCT([400000, 500000, 600000, 700000, 800000, 900000, 1000000, 1100000, 1200000] AS ranges)]),
UNNEST([RANGE_BUCKET(reputation, ranges)]) bucket
WHERE reputation > 200000
GROUP BY 1, 2
ORDER BY bucket
Row bucket reputation_group COUNT
1 0 0 - 400000 198
2 1 400001 - 500000 23
3 2 500001 - 600000 13
4 3 600001 - 700000 12
5 4 700001 - 800000 4
6 5 800001 - 900000 5
7 6 900001 - 1000000 2
8 8 1100001 - 1200000 1
关于google-bigquery - 在 bigquery 中使用 RANGE_BUCKET 时如何显示存储桶名称,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/60235775/
这是我在 BigQuery 中对公共(public)数据集的查询: SELECT RANGE_BUCKET(reputation, [400000, 500000, 600000, 700000, 8
我是一名优秀的程序员,十分优秀!