从另一张表创建视图

Question

我希望能够从另一张表创建一个视图。

当前的视图包含如下内容（见图片1）：

尝试获取上述视图的输出结果，如图所示（见图片2），即对这些列中的数值进行分组：

- Angela

---

你目前尝试了什么？ - Elliott Brossard

单独的查询是有效的，例如： select w1,count(*) from 'table_name' group by w1 等等。但我不确定如何组合这些查询以获得此输出。 - Angela

Answer 1

一种方法是将数据进行解构（unpivot），然后重新聚合：

select w,
       sum(case when i = 0 then 1 else 0 end) as w1,
       sum(case when i = 1 then 1 else 0 end) as w2,
       sum(case when i = 2 then 1 else 0 end) as w3,
       sum(case when i = 3 then 1 else 0 end) as w4
from (select array[w1, w2, w3, w4] ws
      from t
     ) t cross join
     unnest(ws) w with offset i
group by w;

这是一个更完整的例子：

with t as (
      select 1 as slno, '<5' as w1, '<5' as w2, '<5' as w3, '<5' as w4 union all
      select 1 as slno, '<5' as w1, '5 to 10' as w2, '<5' as w3, '5 to 10' as w4 
     )
select w,
       sum(case when i = 0 then 1 else 0 end) as w1,
       sum(case when i = 1 then 1 else 0 end) as w2,
       sum(case when i = 2 then 1 else 0 end) as w3,
       sum(case when i = 3 then 1 else 0 end) as w4
from (select array[w1, w2, w3, w4] as ws
      from t
     ) t cross join
     unnest(ws) w with offset i
group by w;

- Gordon Linoff

---

这只是我用五行给出的一个例子。然而，实际表格大约有1k行。是否有其他方法可以对大数量的值求和？ - Angela

@user10173721... 行数在此并不起作用。所有行都被加总。 - Gordon Linoff

Answer 2

以下内容适用于BigQuery标准SQL。

#standardSQL
SELECT bucket,
  COUNTIF(col = 0) AS w1,
  COUNTIF(col = 1) AS w2,
  COUNTIF(col = 2) AS w3,
  COUNTIF(col = 3) AS w4
FROM `project.dataset.your_table`, 
  UNNEST([w1, w2, w3, w4]) bucket WITH OFFSET col
GROUP BY bucket

- Mikhail Berlyant