目录
第1步：先按照条件开窗分组排序，得到分组排序的结果。排序字段值 减去 排序序列号
第2步：按照用户id 和 差值gid 分组。聚合得到一个总行数count，再按照连续值去过滤总行数count。
1. 连续值
例如连续7天登录的用户，连续3天消费的用户等。
这主要是要有转换的思想。
将连续性转换为 ==》 差值相同
将连续值转换为 ==》 按照差值分组聚合的行数
step A- 需要开窗，在窗口内进行分组排序，上排序序列号gid
step B- 使用当前的排序字段值a 减去 排序序列号gid ( a - gid) ，得到新的一列。
step C- 再将连续值转换为判断差值大小，按照用户和差值分组。差值相同说明日期是连续的。根据求的连续值，去过滤步骤2得到的值，得到最终的结果集。
例如：求连续7天登录的用户。
数据： 第1列为用户ID， 第2列为登录日期，第3列为登录结果(1为成功，0为失败，即没有登录)
-- user_id dt status(1 正常登录，0 异常)
1 2019-07-11 1
1 2019-07-12 1
1 2019-07-13 1
1 2019-07-14 1
1 2019-07-15 1
1 2019-07-16 1
1 2019-07-17 1
1 2019-07-18 1
2 2019-07-11 1
2 2019-07-12 1
2 2019-07-13 0
2 2019-07-14 1
2 2019-07-15 1
2 2019-07-16 0
2 2019-07-17 1
2 2019-07-18 0
3 2019-07-11 1
3 2019-07-12 1
3 2019-07-13 1
3 2019-07-14 0
3 2019-07-15 1
3 2019-07-16 1
3 2019-07-17 1
3 2019-07-18 1
按照步骤，
第1步：先按照条件开窗分组排序，得到分组排序的结果。排序字段值 减去 排序序列号
这里求得是用户，即按照用户分组，连续7天，按照登录日期排序。日期字段值 - 排序序列号。这个差值如果相同，说明日期连续
-- 开窗排序， 开窗函数 over()
over(partiton by user_id order by dt)
-- 排名函数，给局部排序的上序列号，
-- rank()，排名可以重复，相同排名的后续会有空位。即 1,2,3,3,5
-- DENSE_RANK(), 排名可以重复，相同排名的后续不会有空位，1,2,3,3,4,5
-- 这里排名 顺序增加 且 排名不会重复。使用 row_number()
row_number() over(partiton by user_id order by dt)
-- 求排序字段的当前值 和 排序序列号 的差值。 这里是日期的求值，直接使用date_sub
date_sub(dt,row_number() over(partition by user_id order by dt))
-- 最终第一步的sql是
select user_id,
dt,
date_sub(dt,row_number() over(partition by user_id order by dt)) gid
from user_login
where status = 1第2步：按照用户id 和 差值gid 分组。聚合得到一个总行数count，再按照连续值去过滤总行数count。
-- 根据 当前字段值 和 排序序列号的差值，和用户分组。 最后过滤
with tmp as (
select user_id,
dt,
date_sub(dt,row_number() over(partition by user_id order by dt)) gid
from user_login
where status =1
)
select user_id,
count(*) as login_count -- 连续登录的天数
from tmp
group by user_id,gid
having login_count >= 7 -- having是在聚合函数count()后面执行，where则是之前执行。所以这里用2. topN
求前几名排名。
这个排名不是全局，全局只要直接order by 字段就好了。指的是条件范围内的topN，即局部的topN，可能是局部的时间，局部的部门等。
比如求每个班级的前面三名，分数一样的并列，同时求出名次间的分差(即相邻排名的分差)
前面3名 ==》 连续值问题
相邻名词的分差 ==》 上下行的差值? ==》 将一列整体向上或者向下移动一行
--student_id,class,score
1 1901 90
2 1901 90
3 1901 83
4 1901 60
5 1902 66
6 1902 23
7 1902 99
8 1902 67
9 1902 87
?期待的结果
class score rnk lagscore
1901 90 1 0
1901 90 1 0
1901 83 2 -7
1901 60 3 -23
1902 99 1 0
1902 87 2 -12
1902 67 3 -20
实现思路：
step a. 求得是每个班级的，分数排名 ==》 按照班级分组，分数排序，再上排名函数。
step b. 两个相邻排名间的分差 ==》 M(score) 减去 M-1(score) ==》 需要分数，需要排名号
step c. 分数相同，并列排名, 后续的排名不会空 ==》 排名函数用 dense_rank()
sql实现：
第1步：开窗分组排序
-- 第一步，得到分组排序的排名
-- over() 开窗函数，按照班级分组，分数排序
over(partition by class order by score desc)
-- 前面3名，降序排名，排名重复不为空
dense_rank() over(partition by class order by score desc)
-- 分组排序的排名
select student_id,
class,
score,
dense_rank() over(partition by class order by score desc) as rnk -- 降序排名
from student_table
第2步：求相邻排名间的分差，
相邻排名 ==》 相当于求连续值 ==》 对排名值字段进行开窗, 得到新的一列排名分数值
排名也是按照班级分组，分数排名 ==》 over(partition by class order by score desc)
相邻排名的分差 ==》 理解为把 一列的值整体移动，可以将减数那列上移一格，或者被减数那一列往下移动一格。 这里 采用 被减数下移一格
-- 第一步得到的分班排名
with tmp as (
select sno,
class,
score,
dense_rank() over (partition by class order by score desc) as rnk
from student_table
)
-- 再次开窗，对班级分组，分数排名。 ==》 分组排名的分数
over(partition by class order by score desc)
-- 新增一行它的后续排名分数，他的值是 原来的排名分数列往下移动一行 ==》 lag() 函数往下移动一行
lag(score) over(partition by class order by score desc)
-- 求分差，空补0
nvl(score - lag(score) over(partition by class order by score desc),0)
-- 最终
select class,
score,
nvl(score - lag(score) over (partition by class order by score desc), 0) lagscore
from tmp
where rank<=3; -- 前面3名
3. 行转列
?翻转转换
?原表名为 user_skill
-- 数据：user_id skill
1 java
1 hadoop
1 hive
1 hbase
2 java
2 hive
2 spark
2 flink
3 java
3 hadoop
3 hive
3 kafka求每个用户的组件技能情况，即期待输出
id java hadoop hive hbase spark flink kafka
1 1 1 1 1 0 0 0
2 1 0 1 0 1 1 0
3 1 1 1 0 0 0 1
step a. 按照用户分组
step b. 使用case when 新增字段，并赋值。
select id,
sum(case when course="java" then 1 else 0 end) as java,
sum(case when course="hadoop" then 1 else 0 end) as hadoop,
sum(case when course="hive" then 1 else 0 end) as hive,
sum(case when course="hbase" then 1 else 0 end) as hbase,
sum(case when course="spark" then 1 else 0 end) as spark,
sum(case when course="flink" then 1 else 0 end) as flink,
sum(case when course="kafka" then 1 else 0 end) as kafka
from user_skill
group by user_id;
分组聚拢
相同的列数，将多个行值聚拢成一个行值
原表名为 row2line
-- 数据。id1 id2 flag
a b 2
a b 1
a b 3
c d 6
c d 8
c d 8期待输出
-- 编写sql实现如下结果
id1 id2 flag
a b 2|1|3
c d 6|8
思路： id1，id2的值没变 ==> 按照id1，id2分组
?????????? flag的值是组合值? ==》 将原来分组好的flag值，聚拢起来，在拼接到一起
step a. 按照用户分组
step b. 使用concat_ws等函数进行聚拢操作。
-- 分组聚拢
-- 分组group by
-- 聚拢可以使用 collect_set，collect_list。set会去重，这里的输出没有两个8，所以使用set
select id1,
id2,
collect_set(flag) flag
from row2line
-- 将 集合中的值，拼接起来 ==》 concat_ws("拼接符", string or array)
select id1,id2,
concat_ws("|",collect_set(cast (flag as string))) flag
from row2line4. 列转行
将一行的值，拆分成多个行值? ==》? 炸裂行数 explode() + lateral view
这个配合json的解析，会是实际工作中最常遇到的情况。
本例是上述的反转。即将
-- 编写sql实现如下结果
id1 id2 flag
a b 2|1|3
c d 6|8
转变为
-- 数据。id1 id2 flag
a b 2
a b 1
a b 3
c d 6
c d 8
c d 8explode()， udtf函数，它炸开字段时，不能与其他列在同一级
select id1,id2,explode(flag), 是不允许的。
lateral view： lateral view udtf[expression]? tableAlias as columnAlias
-- 主要是关注explode的用法，搭配 udtf函数
-- udtf函数 lateral view的用法
select id1,id2,newflag
from line2row
lateral view explode(split(flag,"\\|")) t1 as newflag
