两个查询语句的比较在一个查询中

huangapple go评论67阅读模式
英文:

Comparison of two select queries in one query

问题

我正在尝试查找这两个查询(count)的结果,找出它们的差异,如果不相等则输出1,否则输出0。因此,我想要一个单一的查询,如果有差异则给我1,如果没有差异则给我0。

我的第一个查询

select sum (mass) as firstquerycount from
 (
  SELECT safe_cast(sum(column1) as int) as mass,'dummy' FROM `dataset.table1` WHERE DATE(dateTime) = current_date()-1
union all
  SELECT safe_cast(sum(anothercol) as int) as mass,'dummy' FROM `dataset.table2` WHERE DATE(dateTime) =
  current_date() -1
union all
SELECT safe_cast(sum(column3) as int) as mass,'dummy' FROM `dataset.table3`  WHERE DATE(dateTime) = current_date()-1
 ) x

我的第二个查询

SELECT ifnull(safe_cast(sum(colmass) as int),0) as secondquerycount FROM `dataset1.table4`
WHERE DATE(timeInterval) = (current_date -1)
and country in (SELECT ctry FROM `dataset2.table5` WHERE DATE(bqInsertTime) = current_date()  and county='Utopia' and contactType in ('call','sms','outlook'))
英文:

I am trying to find out the results of these two queries(count), find the difference, output 1 if it is not equal, otherwise output 0. So I wanted a single query which gives me 1 if there is a difference, 0 if there is no difference.

my select firstquery

select sum (mass) as firstquerycount from
 (
  SELECT safe_cast(sum(column1) as int) as mass,'dummy' FROM `dataset.table1` WHERE DATE(dateTime) = current_date()-1
union all
  SELECT safe_cast(sum(anothercol) as int) as mass,'dummy' FROM `dataset.table2` WHERE DATE(dateTime) =
  current_date() -1
union all
SELECT safe_cast(sum(column3) as int) as mass,'dummy' FROM `dataset.table3`  WHERE DATE(dateTime) = current_date()-1
 ) x

my secondquery

SELECT ifnull(safe_cast(sum(colmass) as int),0) as secondquerycount FROM `dataset1.table4`
WHERE DATE(timeInterval) = (current_date -1)
and country in (SELECT ctry FROM `dataset2.table5` WHERE DATE(bqInsertTime) = current_date()  and county='Utopia' and contactType in ('call','sms','outlook') )

答案1

得分: 1

SELECT CASE WHEN firstquerycount = secondquerycount THEN 1 ELSE 0 END as result
FROM
(
select sum (mass) as firstquerycount from
(
SELECT safe_cast(sum(column1) as int) as mass,'dummy' FROM dataset.table1 WHERE DATE(dateTime) = current_date()-1
union all
SELECT safe_cast(sum(anothercol) as int) as mass,'dummy' FROM dataset.table2 WHERE DATE(dateTime) =
current_date() -1
union all
SELECT safe_cast(sum(column3) as int) as mass,'dummy' FROM dataset.table3 WHERE DATE(dateTime) = current_date()-1
) x
) dt1,
(
SELECT ifnull(safe_cast(sum(colmass) as int),0) as secondquerycount FROM dataset1.table4
WHERE DATE(timeInterval) = (current_date -1)
and country in (SELECT ctry FROM dataset2.table5 WHERE DATE(bqInsertTime) = current_date() and county='Utopia' and contactType in ('call','sms','outlook') )
) dt2

英文:

Since both of these have a single row result set, you can cross-join them together and do your math:

SELECT CASE WHEN firstquerycount = secondquerycount THEN 1 ELSE 0 END as result
FROM 
   (
      select sum (mass) as firstquerycount from
      (
        SELECT safe_cast(sum(column1) as int) as mass,'dummy' FROM `dataset.table1` WHERE DATE(dateTime) = current_date()-1
        union all
        SELECT safe_cast(sum(anothercol) as int) as mass,'dummy' FROM `dataset.table2` WHERE DATE(dateTime) =
        current_date() -1
        union all
        SELECT safe_cast(sum(column3) as int) as mass,'dummy' FROM `dataset.table3`  WHERE DATE(dateTime) = current_date()-1
      ) x
   ) dt1,
   (
      SELECT ifnull(safe_cast(sum(colmass) as int),0) as secondquerycount FROM `dataset1.table4`
      WHERE DATE(timeInterval) = (current_date -1)
      and country in (SELECT ctry FROM `dataset2.table5` WHERE DATE(bqInsertTime) = current_date()  and county='Utopia' and contactType in ('call','sms','outlook') )
   ) dt2

   )

huangapple
  • 本文由 发表于 2023年6月5日 21:45:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/76407056.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定