使用谷歌bigquery来建立直方图图形 [英] Use google bigquery to build histogram graph

问题描述

如何编写一个使直方图图表渲染更容易的查询？

例如，我们有1亿个年龄段的人，我们想绘制直方图/桶年龄0-10,11-20,21-30等...查询是什么样的？

有没有人做过？您是否尝试将查询结果连接到Google电子表格来绘制直方图？

解决方案

子查询的思路是有效的，CASE WHEN，然后通过以下方式进行分组：

  SELECT SUM（field1），存储桶
 FROM（
当年龄≥0且年龄<10则1 1 
当年龄≥10并且年龄<20则2 
当年龄≥20且年龄<30则选择字段1，情况3 
 ... 
 ELSE -1 END as桶
 FROM table1）
 GROUP BY存储桶
  

或者，如果存储桶是常规的 -​​ 您可以分割并转换为整数：

  SELECT SUM（field1），bucket 
 FROM（
 SELECT field1，INTEGER（age / 10）as bucket FROM table1）
 GROUP BY存储桶
  

How can write a query that makes histogram graph rendering easier?

For example, we have 100 million people with ages, we want to draw the histogram/buckets for age 0-10, 11-20, 21-30 etc... What does the query look like?

Has anyone done it? Did you try to connect the query result to google spreadsheet to draw the histogram?

解决方案

The subquery idea works, as does "CASE WHEN" and then doing a group by:

SELECT SUM(field1), bucket 
FROM (
    SELECT field1, CASE WHEN age >=  0 AND age < 10 THEN 1
                        WHEN age >= 10 AND age < 20 THEN 2
                        WHEN age >= 20 AND age < 30 THEN 3
                        ...
                        ELSE -1 END as bucket
    FROM table1) 
GROUP BY bucket

Alternately, if the buckets are regular -- you could just divide and cast to an integer:

SELECT SUM(field1), bucket 
FROM (
    SELECT field1, INTEGER(age / 10) as bucket FROM table1)
GROUP BY bucket

这篇关于使用谷歌bigquery来建立直方图图形的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！