场景
问题
求每辆车车门的开闭次数
事例数据
vin | cardoorstatus | date |
---|---|---|
1 | 0 | 2021-08-10 00:00:12.123 |
1 | 1 | 2021-08-10 00:01:12.222 |
1 | 0 | 2021-08-10 00:04:12.823 |
1 | 0 | 2021-08-10 00:05:12.333 |
1 | 1 | 2021-08-10 00:07:12.124 |
1 | 1 | 2021-08-10 00:08:12.125 |
1 | 1 | 2021-08-10 00:10:12.123 |
2 | 1 | 2021-08-10 02:10:12.123 |
2 | 0 | 2021-08-10 02:15:12.123 |
注意
其中0代表车门关闭,1代表车门开启,开一次、关一次算作一次开闭次数
思路
1、使用hive的窗口函数lead函数,
2、如果第一列和第二列数据不相等,则记录一次窗口开或者关,然后求出开关的count值除以2就是开闭次数
select
vin,
count(1)/2
from(
select
vin
,date
,cardoorstatus
,lead(cardoorstatus,1,0) over(partition by vin order by date asc) nlcardoorstatus
from table)
where cardoorstatus <> nlcardoorstatus