如何查询日期和时间之间有多少项目的状态发生了更改

Question

我有一个类似的历史表

id, name, is_active, mod_date
1, 'name1', 0, 2020-06-09 21:00:00
1, 'name1', 1, 2020-06-09 22:00:00
2, 'name2', 1, 2020-06-09 20:00:00
2, 'name2', 0, 2020-06-09 20:10:00
2, 'name3', 1, 2020-06-09 20:20:00
3, 'name4', 0, 2020-06-09 20:00:00

上表是数据示例。这意味着在mod_date，id1将状态从0更改为1，id2将状态从1更改为0，随后将名称更改为name3，并将is_active翻转回1。然而，id3只是对name4的名称更改

我想查询有多少项更改了is_active列。所以答案是

id1, 1
id2, 2

id1更改is_active列1次，ids2更改两次。

这在SQL中可能吗？我从这里开始，但我不知道如何告诉SQL比较前一行。

select c.id, c.is_active, c.mod_date
from customer_h c
where c.mod_date between '2020-06-09' and '2020-06-10'
order by c.ad_id, c.mod_date

Answer 1

您需要跟踪每个id的is_active值更改的次数。您可以使用窗口函数和聚合：

select id, count(*) cnt_is_active_changes
from (
select 
h.*, 
lag(is_active) over(partition by id order by mod_date) lag_is_active
from history h
) h
where is_active <> lag_is_active
group by id

DB Fiddle上的演示：

id|cnt_is_active_changes-：|--------------------：1|12|2

Answer 2

使用LAG()窗口函数获取上一个状态和聚合：

select id, sum(changed) counter
from (
select id, 
abs(is_active - lag(is_active) over (partition by id order by mod_date)) changed
from customer_h 
) t
group by id
having sum(changed) > 0

请参阅演示
结果：

| id  | counter |
| --- | ------- |
| 1   | 1       |
| 2   | 2       |