如何在Google Big Query中使列与行一致 [英] How to conform Columns to rows in Google Big Query

问题描述

我正在执行数据QA测试。

我使用此查询来建立源表和目标表之间的任何错误。

select 
count(case when coalesce(x.col1,1) = coalesce(y.col1,1) then null else 1 end) as cnt_col1,
count(case when coalesce(x.col2,"1") = coalesce(y.col2,"1") then null else 1 end) as cnt_col2
from
`DatasetA.Table` x
OUTER JOIN
`DatasetB.Table` y
on x.col1 = y.col1

此查询的输出如下所示：

col1, col2
null, null
null, null
1, null
null, 1

我有200个表需要执行此测试，cols的数量是动态的。上表只有两列，有些列有50列。

我已经有了表的查询，但是我需要将所有测试的输出统一到单个输出中。我的计划是将每个查询整合成一个统一的输出，并使用UNION ALL将它们连接在一起。

输出集应为：

COLUMN, COUNT_OF_ERRORS
cnt_col1, 1
cnt_col2, 1
...
cnt_col15, 0

我的问题是这样的。 如何反向透视此对象，以便能够实现所需的输出。

谢谢

推荐答案

如何反向透视以实现我要查找的输出。

假设您有表`data`

col1    col2    col3
----    ----    ----     
null    null    null     
null    null    1    
null    1       1    
1       null    1    
1       null    1    
1       null    1   

并且您需要将其反向透视到

column      count_of_errors  
--------    ---------------
cnt_col1    3    
cnt_col2    1    
cnt_col3    5    

下面是针对BigQuery标准SQL的，它就是这样做的

#standardSQL
WITH `data` AS (
  SELECT NULL AS col1, NULL AS col2, NULL AS col3 UNION ALL
  SELECT NULL, NULL, 1 UNION ALL
  SELECT 1, NULL, 1 UNION ALL
  SELECT NULL, 1, 1 UNION ALL
  SELECT 1, NULL, 1 UNION ALL
  SELECT 1, NULL, 1
)
SELECT r.* FROM (
  SELECT 
    [
    STRUCT<column STRING, count_of_errors INT64>
      ('cnt_col1', SUM(col1)),
      ('cnt_col2', SUM(col2)),
      ('cnt_col3', SUM(col3))
    ] AS row
  FROM `data`
), UNNEST(row) AS r   

它足够简单和友好，可以调整到初始`data`表中可能包含的任何列数(您只需添加相应的('cnt_colN', SUM(colN)),列数)，这可以手动完成，也可以只编写简单的脚本来生成这些行(或整个查询)

这篇关于如何在Google Big Query中使列与行一致的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！