hive案例 SQL题 连续登录的天数 连续命中次数 编号函数 row_numbew rank dense_rank

连续登录的天数

数据如下：

name login_date
a,2020-11-01
a,2020-11-05
a,2020-11-04
b,2020-11-04
b,2020-11-05
a,2020-11-03
a,2020-11-02
b,2020-11-08
a,2020-11-07
b,2020-11-01
b,2020-11-02
a,2020-11-06
b,2020-11-06

1、创建表，导入数据

create  table login(
name string ,
login_date string
)
row format delimited  fields  terminated by ',' ;
load  data local  inpath  '/root/login'  into  table login ;

2、逻辑

select
name,max(cnt)
from
	(select
	name,
	count(1) cnt
	from
		(select 
		name,login_date,
		date_sub(login_date,rn) dd  --日期相减，第一个参数 日期，第二个参数 减去的天数 如果是连续登录，得到的日期是相等的
		from
			(select 
			*,
			row_number() over(partition by name order by login_date) rn
			from login)t
		)t2
	group by name,dd
	having cnt>=2
	)t3
group by name;

3、结果如下：

+-------+------+
| name  | _c1  |
+-------+------+
| a     | 7    |
| b     | 3    |
+-------+------+

连续命中次数

m=1代表命中，m=0代表没有命中  
求每个人的命中次数
数据： 
uid,seq,m
u01,1,1
u01,2,0
u01,3,1
u01,6,1
u02,5,1
u02,6,0
u02,7,0
u02,1,1
u02,2,1
u03,4,1
u03,5,1
u03,6,0
u02,3,0
u02,4,1
u02,8,1
u01,4,1
u01,5,0
u02,9,1
u03,1,1
u03,2,1
u03,3,1

1、创建表，导入数据

create table  tb_ds(
uid string ,
seq int  ,
m int 
)
row format delimited fields terminated by "," ;
load data local inpath "/data/ds/" into table  tb_ds ;

2、逻辑

select
uid,max(cnt) n
from
	(select
	uid,count(1) cnt
	from
		(select 
		uid,seq,
		row_number() over(partition by uid order by seq) rn,
		(seq-row_number() over(partition by uid order by seq)) diff
		from dadishu
		where m = 1  --把命中的记录筛选出来)t
	group by uid,diff
	having cnt>=2) t2
group by uid;

3、结果：

+------+----+
| uid  | n  |
+------+----+
| u01  | 2  |
| u02  | 2  |
| u03  | 5  |
+------+----+

编号函数

数据：
孙悟空	语文	87
孙悟空	数学	95
娜娜	英语	84
宋宋	语文	64
孙悟空	英语	68
宋宋	英语	84
婷婷	语文	65
娜娜	语文	94
宋宋	数学	86
婷婷	数学	85
娜娜	数学	56
婷婷	英语	78

建表，导入数据

create table  tb_sub(
name string ,
subject string ,
score double
)
row format delimited fields terminated by "\t" ;
load data local inpath "/root/subject" into table  tb_sub ;

需求：对每门科目的成绩进行排名

1、row_number() 仅仅定义组内的数据进行编号

select
* ,
row_number()  over(partition by subject  order by score desc) rn
from
tb_sub ;

结果：
+--------------+-----------------+---------------+-----+
| tb_sub.name  | tb_sub.subject  | tb_sub.score  | rn  |
+--------------+-----------------+---------------+-----+
| 孙悟空          | 数学              | 95.0          | 1   |
| 宋宋           | 数学              | 86.0          | 2   |
| 婷婷           | 数学              | 85.0          | 3   |
| 娜娜           | 数学              | 56.0          | 4   |
| 宋宋           | 英语              | 84.0          | 1   |
| 娜娜           | 英语              | 84.0          | 2   |
| 婷婷           | 英语              | 78.0          | 3   |
| 孙悟空          | 英语              | 68.0          | 4   |
| 娜娜           | 语文              | 94.0          | 1   |
| 孙悟空          | 语文              | 87.0          | 2   |
| 婷婷           | 语文              | 65.0          | 3   |
| 宋宋           | 语文              | 64.0          | 4   |
+--------------+-----------------+---------------+-----+

2、rank()

select
*,
rank() over(partition by subject order by score desc)
from 
tb_sub

结果：
+--------------+-----------------+---------------+----------------+
| tb_sub.name  | tb_sub.subject  | tb_sub.score  | rank_window_0  |
+--------------+-----------------+---------------+----------------+
| 孙悟空          | 数学              | 95.0          | 1              |
| 宋宋           | 数学              | 86.0          | 2              |
| 婷婷           | 数学              | 85.0          | 3              |
| 娜娜           | 数学              | 56.0          | 4              |
| 宋宋           | 英语              | 84.0          | 1              |
| 娜娜           | 英语              | 84.0          | 1              |
| 婷婷           | 英语              | 78.0          | 3              |
| 孙悟空          | 英语              | 68.0          | 4              |
| 娜娜           | 语文              | 94.0          | 1              |
| 孙悟空          | 语文              | 87.0          | 2              |
| 婷婷           | 语文              | 65.0          | 3              |
| 宋宋           | 语文              | 64.0          | 4              |
+--------------+-----------------+---------------+----------------+

3、dense_rank()

select
* ,
dense_rank()  over(partition by subject  order by score desc) rn
from
tb_sub ;

结果
+--------------+-----------------+---------------+-----+
| tb_sub.name  | tb_sub.subject  | tb_sub.score  | rn  |
+--------------+-----------------+---------------+-----+
| 孙悟空          | 数学              | 95.0          | 1   |
| 宋宋           | 数学              | 86.0          | 2   |
| 婷婷           | 数学              | 85.0          | 3   |
| 娜娜           | 数学              | 56.0          | 4   |
| 宋宋           | 英语              | 84.0          | 1   |
| 娜娜           | 英语              | 84.0          | 1   |
| 婷婷           | 英语              | 78.0          | 2   |
| 孙悟空          | 英语              | 68.0          | 3   |
| 娜娜           | 语文              | 94.0          | 1   |
| 孙悟空          | 语文              | 87.0          | 2   |
| 婷婷           | 语文              | 65.0          | 3   |
| 宋宋           | 语文              | 64.0          | 4   |
+--------------+-----------------+---------------+-----+