时间戳

首页 > 解决方案 > 如何查询日期时间之间有多少项目改变了状态？

sql - 如何查询日期时间之间有多少项目改变了状态？

问题描述

我有一个这样的历史表

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

上表是数据示例。这意味着id1将状态从 0 更改为id21，并在 mod_date 将状态从 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

标签： sqlpostgresqlcountwindow-functionsgaps-and-islands

解决方案

您希望跟踪is_active每个id. 您可以使用窗口函数和聚合：

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 上的演示：

编号 | cnt_is_active_changes
-: | --------------------:
 1 | 1
 2 | 2

推荐阅读