hiveSQL面试题--14__hiveSQL统计断电次数及断电时长【物联网场景按事件变化后分桶算法的应用】（蔚来汽车）

KG大数据

已于 2022-10-11 16:38:26 修改

阅读量667

点赞数

分类专栏： hiveSQL面试1000道文章标签： hive sql 大数据面试

于 2021-08-30 15:42:49 首次发布

本文链接：https://blog.youkuaiyun.com/CLKTOY/article/details/119996690

版权

0-业务场景

统计外电网各个设备的断电次数及断电时长，其中外电网正常状态为0，断电状态为1。统计断电次数，即是外电网的状态从0-1的变化次数。msg_time表示上报消息的时间，一分钟上报一次。

数据格式：设备ID - 时间戳 - 外电网状态。
在这里插入图片描述

类似的有统计开关量的使用次数，使用时长等

问题：
（1）求断电次数

（2）求每次断电后断电时长（断电多久后被恢复，时间为分钟数要求保留一位小数）

（3）求每次断电后状态开始时间和结束时间

1-数据准备

vim opg.txt

1	2021-06-18 10:11:12	0
1	2021-06-18 10:12:10	1
1	2021-06-18 10:13:00	1
1	2021-06-18 10:14:01	1
1	2021-06-18 10:15:00	1
1	2021-06-18 10:16:02	0
1	2021-06-18 10:17:01	1
1	2021-06-18 10:18:00	1
1	2021-06-18 10:19:03	1
1	2021-06-18 10:20:04	0
2	2021-06-18 10:11:13	0
2	2021-06-18 10:11:00	0
2	2021-06-18 10:11:00	1
2	2021-06-18 10:11:00	1
2	2021-06-18 10:11:00	0

（2）建表

    drop table opg;
     
    create table opg(
            sensor_id string,
            msg_time string,
            opg_state int
        )
        row format delimited fields terminated by '\t'

（3）加载数据

load data local inpath "/home/centos/dan_test/opg.txt" into table opg;

（4）数据展示

hive> select * from opg;
OK
1       2021-06-18 10:11:12     0
1       2021-06-18 10:12:10     1
1       2021-06-18 10:13:00     1
1       2021-06-18 10:14:01     1
1       2021-06-18 10:15:00     1
1       2021-06-18 10:16:02     0
1       2021-06-18 10:17:01     1
1       2021-06-18 10:18:00     1
1       2021-06-18 10:19:03     1
1       2021-06-18 10:20:04     0
2       2021-06-18 10:11:13     0
2       2021-06-18 10:12:00     0
2       2021-06-18 10:13:00     1
2       2021-06-18 10:14:00     1
2       2021-06-18