英文:
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
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论