Hive函数--sql-行列转换--三

1.计算好友占比

两个表t1,t2，字段[cust_id] ,sex。计算t1,t2共同好友占t1的比例

-- sql server
--使用left join 
 select 
  sum(case when t2.[cust_id] is not null then 1 else 0 end),count(t1.[cust_id]),
  sum(case when t2.[cust_id] is not null then 1.0 else 0.0 end)/(count(t1.[cust_id]))
from t1
left join  t2
on t1.[cust_id]=t2.[cust_id]

1.1 每个分组按时间升序前五名的信息-列转行

包含device_id,time,event,转化成device_id,【event1,event2,event3,event4,event5】的形式

--取出前五的数据
select t2.* from
(
select device_id,event,
row_number()over (partition by device_id order by time) as rank
from t1
)t2
where t2.rank<=5

-- 推荐
--列转行 运用concat_ws、collect_set函数
select device_id,e,concat_ws(',',collect_set(event)) as event
from (
select t2.* from
(
select device_id,event,
row_number()over (partition by device_id order by time) as rank
from t1
)t2
where t2.rank<=5
) t3
group by t3.device_id

--列转行 运用group_concat函数
select t3.device_id,group_concat(t2.event order by t2.rank separator ',') 
from (
select t2.* from
(
select device_id,event,
row_number()over (partition by device_id order by time) as rank
from t1
)t2
where t2.rank<=5
) t3
group by t3.device_id

1.3 行转列

uid stage
1 未婚，有房
2 已婚，有房
3 未婚无房
统计各stage下的人数

select t2.stage2,count(uid)
from
(
select uid,stage2 from table1 lateral view explode(split(stage,',') tmp as stage2
) t
group by uid

2.collect_list和collect_set区别(可用于列转行）

Hive中collect相关的函数有collect_list和collect_set。
它们都是将分组中的某列转为一个数组返回，不同的是collect_list不去重而collect_set去重。


参考链接添加链接描述

3.hivesql行转列/列转行

1.行转列（图一–图二）
函数：lateral view explore()
建表导数据

create table life(uid int,stage string)
row format delimited
fields terminated by ',';
load data local inpath '/root/mytest/life.dat' into table life;

变成我们想要的形式：

select uid,ustage from life lateral view explode(split(stage,':')) temp as ustage;

2.列转行（图二–图一）
函数：concat_ws(‘:’,collect_set(stage))
建表导数据

create table life2(uid int,stage string)
row format delimited
fields terminated by ',';
load data local inpath '/root/mytest/life2.dat' into table life2;

进行连接处理

select uid,
concat_ws(':',collect_set(stage)) as stages 
from life2 group by uid;

concat_ws(‘:’,collect_set(stage)) ‘:’ 表示你合并后用什么分隔，collect_set(stage)表示要合并表中的那一列数据
collect_set()和collect_list()都是对列转成行，区别就是list里面可重复而set里面是去重的。
转载自：添加链接描述
可参考链接添加链接描述

3.计算留存率

1-7日留存
计算DAU，次日留存
一、问题1：留存率计算
字段及表说明：
表名：user_log
字段名：
log_day:登录日期
device_id:用户设备id
app_id:用户app的id，其中device_id和app_id确定唯一的用户e
二、算某日留存率（次日、3日、7日、30日）

select *,
	concat(round(100 * 次日留存用户数/活跃用户数, 2), '%') 次日留存率,
	concat(round(100 * 三日留存用户数/活跃用户数, 2), '%') 三日留存率,
	concat(round(100 * 七日留存用户数/活跃用户数, 2), '%') 七日留存率
from (
	select 
		a.log_day 日期,
		count(distinct(concat(a.device_id, a.app_id))) 活跃用户数,
		count(distinct(concat(b.device_id, b.app_id))) 次日留存用户数,
		count(distinct(concat(c.device_id, c.app_id))) 三日留存用户数,
		count(distinct(concat(d.device_id, d.app_id))) 七日留存用户数
	from user_log a
	left join user_log b on concat(a.device_id, a.app_id) = concat(b.device_id, b.app_id) and b.log_day = a.log_day + 1
	left join user_log c on concat(a.device_id, a.app_id) = concat(c.device_id, c.app_id) and c.log_day = a.log_day + 3
	left join user_log d on concat(a.device_id, a.app_id) = concat(d.device_id, d.app_id) and d.log_day = a.log_day + 7
	group by a.log_day
	) p;

转载自：常见问题 留存率/连续登陆等（SQL & Hive）的评论

4.lateral view explore() 、explore() 语法

参考自
1、首先创建一张表

create table t1 (id int,name string)
insert into t1 (id,name) values (1,'a,b,c'),(2,'d,e,f')

2.explore() --行转列函数

执行
SELECT  explode(split(t.name,',')) from t1 t

3.lateral view explore()
如果我们想把id为1的name值a,b,c和1组合时就要用到lateral view函数

SELECT id,colAliasName  from  t1 t LATERAL VIEW explode(split(t.name,',')) tableAliasName as colAliasName

4.lateral view UDTF(expression) tableAliasName as colAliasName

**其中UDTF(expression)**表示表生成函数说白了就是行转列的函数，即一行变为多行的函数，比如explode，当然也可以通过UDF自定义函数把一行转为多行，或者UDF返回Array,再通过explode炸成多行
tableAliasName表示表的别名,colAliasName表示表的列的别名
原理是:通过lateral view UDTF(expression)函数把一行转换为多行，会生成一个临时表，把这些数据放入这个临时表中，然后使用这个临时表和base表做inner join 使用的条件就是原始表的关系

5.SQL函数

5.1 SQL里使用正则表达式

• regexp_like(匹配)
• regexp_instr(包含)
• regexp_replace(替换)
• regexp_substr(提取)

5.2 SQL中的函数

算法函数、字符串函数、日期函数、 转换函数

1、算术函数
abs()：绝对值
mod()：取余
round()：四舍五入，2个参数：字段名,小数位
2、字符串函数
concat()：合并多个字符串
length()：计算字段长度，汉字计3个，其他计1个
char_length()：计算字段长度，都计1个
lower()：小写
upper()：大写
replace()：3个参数：要替换的，被查找替换的，替换成哪个字符串
substring()：截取字符串，3个参数：被截取的字符串，开始截取的位置，想截取的字符串长度
3、SQL中的日期函数
注意：date日期的格式必须是 yyyy-mm-dd，有的时候日期也会存储为字符串格式。
current_date()：当前日期
current_time()：当前时间
current_timestamp()：日期 + 时间
extract()：抽取具体的年月日
date()
year()
month()
day()
hour()
minute()
second()
4、SQL中的转换函数：
cast()：表达式通过 as 分为2个参数：原始数据和目标数据类型
coalesce()：返回第一个非空数值
注：presto中的转换函数：
cast(value as type)
try_cast(value as type) ：与cast相似，区别是转换失败返回null
转载自：添加链接描述

6 hive 的 left semi join 讲解

添加链接描述

7.concat函数,concat_ws函数，group_concat函数之间的区别

转载自：添加链接描述
1.CONCAT（）函数
CONCAT（）函数用于将多个字符串连接成一个字符串。
语法及使用特点：
CONCAT(str1,str2,…)
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。可以有一个或多个参数。
使用示例：
SELECT CONCAT(id, ‘，’, name) AS con FROM info LIMIT 1;

2.CONCAT_WS函数
指定参数之间的分隔符
使用语法为：CONCAT_WS(separator,str1,str2,…)
CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。

• 分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。
• 函数会忽略任何分隔符参数后的 NULL 值。但是CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
  举例：SELECT CONCAT_WS(‘,’,‘First name’,NULL,‘Last Name’);返回结果为
  ±---------------------------------------------+
  | CONCAT_WS(‘,’,‘First name’,NULL,‘Last Name’) |
  ±---------------------------------------------+
  | First name,Last Name |
  ±---------------------------------------------+
  3.GROUP_CONCAT（）函数
  转载自
  前言：在有group by的查询语句中，select指定的字段要么就包含在group by语句的后面，作为分组的依据，要么就包含在聚合函数中。（有关group by的知识请戳：浅析SQL中Group By的使用）。
  例5：
  
  该例查询了name相同的的人中最小的id。如果我们要查询name相同的人的所有的id呢？
  当然我们可以这样查询：
  例6：
  
  但是这样同一个名字出现多次，看上去非常不直观。有没有更直观的方法，既让每个名字都只出现一次，又能够显示所有的名字相同的人的id呢？——使用group_concat()
  1、功能：
  将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
  2、语法：

group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )

说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。
3、举例：
例7：使用group_concat()和group by显示相同名字的人的id号：

例8：将上面的id号从大到小排序，且用’_'作为分隔符：

例9：上面的查询中显示了以name分组的每组中所有的id。接下来我们要查询以name分组的所有组的id和score：

----------------------以下暂时不用---------
GROUP_CONCAT函数返回一个字符串结果，该结果由分组中的值连接组合而成。
使用语法及特点：
GROUP_CONCAT([DISTINCT] expr [,expr …]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col …]]
[SEPARATOR str_val])
在 MySQL 中，你可以得到表达式结合体的连结值。通过使用 DISTINCT 可以排除重复值。如果希望对结果中的值进行排序，可以使用 ORDER BY 子句。
SEPARATOR 是一个字符串值，它被用于插入到结果值中。缺省为一个逗号 (“,”)，可以通过指定 SEPARATOR “” 完全地移除这个分隔符。
可以通过变量 group_concat_max_len 设置一个最大的长度。在运行时执行的句法如下： SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer;
如果最大长度被设置，结果值被剪切到这个最大长度。如果分组的字符过长，可以对系统参数进行设置：SET @@global.group_concat_max_len=40000;
举例