比较两个SQL表并返回更改后的行数 [英] Compare two SQL tables and return count of rows with changes

问题描述

我有一个包含num_key记录的SQL表有两个分区.我需要比较和统计2月记录与1月记录的变化.

I have two partitions from an SQL table containing num_key records. I need to compare and count changes in the February records versus the January records.

示例数据和所需结果:

ptn_dt ='2019-01-31'(一月)

ptn_dt = '2019-01-31' (January)

<身体>

num_key	active_indicator
111	true
112	false
113	false
114	false
115	true
116	true

ptn_dt ='2019-02-28'(2月)

ptn_dt = '2019-02-28' (February)

<身体>

num_key	active_indicator
111	true
112	false
113	true
114	true
115	true
116	true
117	true
118	false
119	true

期望的输出:

我可以使用哪种SQL查询?我需要获取2月分区中所有active_indicator = true的计数，但要分为3个输出(新条目，从1月至2月为false到true，从1月至2月为true).

What SQL query could I use? I need to get the count of all active_indicator=true in the February partition but divided into 3 outputs (new entries, false to true from Jan to Feb, and true to true from Jan to Feb).

推荐答案

使用完全联接(完全联接将返回已联接的记录，再加上未从左侧表进行联接，再加上未从右侧表进行联接).用例表达式带有count():

Use full join (Full join returns joined records, plus not joined from left table, plus not joined from right table). Use case expressions with count():

select
       count(case when t1.num_key is null then 1 else null end) as cnt_new,
       count(case when t1.active_indicator = false and t2.active_indicator = true then 1 else null end) as cnt_false_to_true,
       count(case when t1.active_indicator = true and  t2.active_indicator = true then 1 else null end) as cnt_true_not_changed
 from (select * from table t1 where t1.ptn_dt = '2019-01-31') t1
      full join (select * from table t2 where ptn_dt = '2019-02-28' ) t2
           on t1.num_key = t2.num_key   

这篇关于比较两个SQL表并返回更改后的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！