HiveSQL练习题：计算近一个月活跃、连续活跃、沉默用户需求

Hive数据处理：计算连续活跃用户与沉默天数

原创

已于 2023-07-02 11:06:41 修改 · 1.4k 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#hive #sql #database

于 2023-07-01 12:29:32 首次发布

set hive.exec.mode.local.auto=true;
开启hive的local模式

一、新的需求建表

1.1 建表语句：

create table tmp.test(
   deviceid   string,
   dt         string
)row format delimited 
fields terminated by ','

1.2 插入语句：

INSERT INTO tmp.test VALUES
	('deviceid1','2022-11-01'),
	('deviceid1','2022-11-01'),
	('deviceid1','2022-11-02'),
	('deviceid1','2022-11-02'),
	('deviceid1','2022-11-03'),
	('deviceid1','2022-11-05'),
	('deviceid1','2022-11-05'),
	('deviceid1','2022-11-05'),
	('deviceid1','2022-11-06'),
	('deviceid1','2022-11-07'),
	('deviceid1','2022-11-07'),
	('deviceid1','2022-11-08'),
	('deviceid1','2022-11-09'),
	('deviceid1','2022-11-10'),
	('deviceid2','2022-11-01'),
	('deviceid2','2022-11-01'),
	('deviceid2','2022-11-02'),
	('deviceid2','2022-11-02'),
	('deviceid2','2022-11-03'),
	('deviceid2','2022-11-05'),
	('deviceid2','2022-11-05'),
	('deviceid2','2022-11-05'),
	('deviceid2','2022-11-06'),
	('deviceid2','2022-11-07'),
	('deviceid2','2022-11-07'),
	('deviceid2','2022-11-08'),