由刚刚在bigquery中创建的列减去

laawzig2 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(257)

我的查询应该是使用bigquery根据数据中的关键字创建新列。例如，如果在数据中包含“mike”，它将创建mike列，“john”将创建john列，列表将继续。。但是，我想创建一个'other'列，它是我刚刚创建的列的全名的减法。
我的代码示例（在减函数处出错）：

SELECT 
   COUNT(Name) as n_name,
        SUM(CASE WHEN Name LIKE '%MIKE%' THEN 1 ELSE 0 END) AS Mike,
        SUM(CASE WHEN Name LIKE '%JOHN%' THEN 1 ELSE 0 END) AS John,
        SUM(CASE WHEN Name LIKE '%MICHAEL%' THEN 1 ELSE 0 END) AS Michael,
        .....
        SUBSTRACT (n_name ,Mike and John)  AS Others
     FROM t

有没有什么方法可以用我刚创建的专栏做一些删减？

sql google-bigquery

来源：https://stackoverflow.com/questions/63108946/substraction-by-the-column-just-created-in-bigquery

2条答案

按热度按时间

uyhoqukh1#

您可以使用以下方法（bigquery标准sql）

SELECT *, 
  n_name - Mike - John - Michael AS Other
FROM (
  SELECT 
    COUNT(Name) AS n_name,
    COUNTIF(Name LIKE '%MIKE%') AS Mike,
    COUNTIF(Name LIKE '%JOHN%') AS John,
    COUNTIF(Name LIKE '%MICHAEL%') AS Michael,
  FROM t 
)

赞(0）回复(0）举报 2021-07-29

d4so4syb2#

SELECT 
     *
     ,n_name - Mike - John - Michael AS Other
     FROM 
        (
            SELECT 
                COUNT(Name) AS n_name
                ,COUNT(CASE WHEN Name like '%MIKE%' THEN 1 END) AS Mike
                ,COUNT(CASE WHEN Name LIKE '%JOHN%' THEN 1 END) AS John
                ,COUNT(CASE WHEN Name LIKE '%MICHAEL%' THEN 1 END) AS Michael
            FROM 
                t
        ) aa

赞(0）回复(0）举报 2021-07-29

我来回答

由刚刚在bigquery中创建的列减去

2条答案

相关问题

热门标签

最新问答