使用case语句作为if语句 [英] Using a case statement as an if statement

问题描述

我试图在BigQuery中创建一个IF语句。我已经构建了一个可以工作的概念，但它不会从表中选择数据，我只能让它显示1或0

示例：

pre $选择-AS STRUCT
CASE
当（
选择计数（1）FROM（ - If记录相同，则返回= 0，如果记录不相同，则> 1
从`gas中选择不同的ESCO，SOURCE，LDCTEXT，STATUS，DDR_DATE，TempF，HeatingDegreeDays，DecaTherms
-ddr.gas_ddr_outbound.LexingtonDDRsOutbound_onchange_Prior_Filtered`
除了不同
从`gas-ddr.gas_ddr_outbound.LexingtonDDRsOutbound_onchange_Latest_Filtered` $ b选择不同的ESCO，SOURCE，LDCTEXT，STATUS，DDR_DATE，TempF，HeatingDegreeDays，DecaTherms
$ b）
）= 0
THEN
（从`gas-ddr.gas_ddr_outbound.Le xingtonDDRsOutbound_onchange_Latest`） - 这不是
工作标量子查询不能有多个列，除非使用SELECT AS
STRUCT在[16：4]处构建STRUCT值END




SELECT --AS STRUCT
CASE
WHEN（
）选择Count（1）FROM（ - 如果记录相同，则返回= 0，如果记录不相同，则> 1
从'gas-ddr.gas_ddr_outbound.LexingtonDDRsOutbound_onchange_Prior_Filtered`选择不同的ESCO，SOURCE，LDCTEXT，STATUS，DDR_DATE，TempF，HeatingDegreeDays，DecaTherms

除了不同的
选择不同的ESCO， SOURCE，LDCTEXT，STATUS，DDR_DATE，TempF，HeatingDegreeDays，DecaTherms
from`gas-ddr.gas_ddr_outbound.LexingtonDDRsOutbound_onchange_Latest_Filtered`
）
）= 0
THEN 1 --- This does工作

其他
0
END

如何获取此查询以返回现有表中的结果？

解决方案

您的问题仍然存在很少泛型，所以我的答案也一样 - 只是模仿你的用例扩展我可以从你的意见反向工程它

所以，在贝尔ow代码 - project.dataset.yourtable 模仿你的表格;而
project.dataset.yourtable_Prior_Filtered 和 project.dataset.yourtable_Latest_Filtered 模仿您的相应视图

#standardSQL
with`project.dataset.yourtable` AS （
SELECT'aaa'cols，'prior'filter UNION ALL
SELECT'bbb'cols，'latest'filter
），`project.dataset.yourtable_Prior_Filtered` AS（
SELECT cols FROM`project.dataset.yourtable` WHERE filter ='prior'
），`project.dataset.yourtable_Latest_Filtered` AS（
SELECT cols FROM`project.dataset.yourtable` WHERE filter ='latest '
），检查AS（
SELECT COUNT（1）> 0已更改FROM（
SELECT DISTINCT cols FROM`project.dataset.yourtable_Latest_Filtered`
EXTINPT DISTINCT
SELECT DISTINCT cols FROM`project.dataset.yourtable_Prior_Filtered`
）
）
SELECT t。* FROM`project.dataset.yourtable` t
CROS S JOIN检查WHERE check.changed


结果是

行列过滤器
1 aaa before
2 bbb latest


如果您将表格更改为

  WITH project.dataset.yourtable` AS（
 SELECT'aaa'cols，'prior'filter UNION ALL 
 SELECT'aaa'cols，'latest'filter 
）.. .... 
  

结果将是
< pre class =lang-sql prettyprint-override> 行列过滤器
查询返回的零记录。


我希望这可以给您正确的方向

增加了更多解释：

我可能是错的 - 但是根据您的问题 - 看起来您有一个表 project.dataset.yourtable 和两个视图 project.dataset.yourtable_Prior_Filtered 和项目。 dataset.yourtable_Latest_Filtered 你现在的表格在事件发生前后的状态你在你的问题中描述的观点。
他们在这里，所以你可以看到概念，并可以在没有任何额外工作的情况下使用它，然后再根据实际使用情况进行调整。

对于真实的用例，您应该忽略它们并使用您的真实表和视图名称以及任何具有的列。

因此，你要玩的是：
$ b $ prefle =lang-sql prettyprint-override> #standardSQL
WITH check AS（
SELECT COUNT（1）> 0已更改FROM（
SELECT DISTINCT cols FROM`project.dataset.yourtable_Latest_Filtered`
EXTINPT DISTINCT
SELECT DISTINCT cols FROM`project.dataset.yourtable_Prior_Filtered`


SELECT t。* FROM`project.dataset.yourtable` t
CROSS JOIN check WHERE check.changed


它应该是任何语言中非常简单的IF语句。它不能用简单的 IF 来完成，如果你认为它合适，你可以提交一个功能请求给BigQuery团队，无论您认为有什么意义

I am attempting to create an IF statement in BigQuery. I have built a concept that will work but it does not select the data from a table, I can only get it to display 1 or 0

Example:

SELECT --AS STRUCT 
 CASE
 WHEN (
       Select Count(1) FROM (  --  If the records are the same, then return = 0, if the records are not the same then > 1
                              Select Distinct ESCO, SOURCE, LDCTEXT, STATUS,DDR_DATE, TempF, HeatingDegreeDays, DecaTherms
                              from `gas-ddr.gas_ddr_outbound.LexingtonDDRsOutbound_onchange_Prior_Filtered` 
                                Except Distinct
                              Select Distinct ESCO, SOURCE, LDCTEXT, STATUS,DDR_DATE, TempF, HeatingDegreeDays, DecaTherms
                              from `gas-ddr.gas_ddr_outbound.LexingtonDDRsOutbound_onchange_Latest_Filtered` 
                           )
      )= 0 
  THEN 
(Select * from `gas-ddr.gas_ddr_outbound.LexingtonDDRsOutbound_onchange_Latest`)  -- This Does not 
work  Scalar subquery cannot have more than one column unless using SELECT AS 
STRUCT to build STRUCT values at [16:4] END




 SELECT --AS STRUCT 
CASE
 WHEN (
       Select Count(1) FROM (  --  If the records are the same, then return = 0, if the records are not the same then > 1
                              Select Distinct ESCO, SOURCE, LDCTEXT, STATUS,DDR_DATE, TempF, HeatingDegreeDays, DecaTherms
                              from `gas-ddr.gas_ddr_outbound.LexingtonDDRsOutbound_onchange_Prior_Filtered` 
                                Except Distinct
                              Select Distinct ESCO, SOURCE, LDCTEXT, STATUS,DDR_DATE, TempF, HeatingDegreeDays, DecaTherms
                              from `gas-ddr.gas_ddr_outbound.LexingtonDDRsOutbound_onchange_Latest_Filtered` 
                           )
      )= 0 
  THEN 1 --- This does work

Else 0 END

How can I Get this query to return results from an existing table?

解决方案

You question is still a little generic, so my answer same as well - and just mimic your use case at extend I can reverse engineer it from your comments

So, in below code - project.dataset.yourtable mimics your table ; whereas project.dataset.yourtable_Prior_Filtered and project.dataset.yourtable_Latest_Filtered mimic your respective views

#standardSQL
WITH `project.dataset.yourtable` AS (
  SELECT 'aaa' cols, 'prior' filter UNION ALL  
  SELECT 'bbb' cols, 'latest' filter 
), `project.dataset.yourtable_Prior_Filtered` AS (
  SELECT cols FROM `project.dataset.yourtable` WHERE filter = 'prior'
), `project.dataset.yourtable_Latest_Filtered` AS (
  SELECT cols FROM `project.dataset.yourtable` WHERE filter = 'latest'
), check AS (
  SELECT COUNT(1) > 0 changed FROM (
    SELECT DISTINCT cols FROM `project.dataset.yourtable_Latest_Filtered`
    EXCEPT DISTINCT
    SELECT DISTINCT cols FROM `project.dataset.yourtable_Prior_Filtered`
  )
)
SELECT t.* FROM `project.dataset.yourtable` t
CROSS JOIN check WHERE check.changed

the result is

Row cols    filter   
1   aaa     prior    
2   bbb     latest   

if you changed your table to

WITH `project.dataset.yourtable` AS (
  SELECT 'aaa' cols, 'prior' filter UNION ALL  
  SELECT 'aaa' cols, 'latest' filter 
) ......

the result will be

Row cols    filter   
Query returned zero records.

I hope this gives you right direction

Added more explanations:

I can be wrong - but per your question - it looks like you have one table project.dataset.yourtable and two views project.dataset.yourtable_Prior_Filtered and project.dataset.yourtable_Latest_Filtered which present state of your table prior and after some event

So, first three CTE in the answer above just mimic those table and views which you described in your question. They are here so you can see concept and can play with it without any extra work before adjusting this to your real use-case.

For your real use-case you should omit them and use your real table and views names and whatever columns the have.
So the query for you to play with is:

#standardSQL
WITH check AS (
  SELECT COUNT(1) > 0 changed FROM (
    SELECT DISTINCT cols FROM `project.dataset.yourtable_Latest_Filtered`
    EXCEPT DISTINCT
    SELECT DISTINCT cols FROM `project.dataset.yourtable_Prior_Filtered`
  )
)
SELECT t.* FROM `project.dataset.yourtable` t
CROSS JOIN check WHERE check.changed   

It should be a very simple IF statement in any language.
Unfortunately NO! it cannot be done with just simple IF and if you see it fit you can submit a feature request to BigQuery team for whatever you think makes sense

这篇关于使用case语句作为if语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！