sql 高级函数

目录

• with as,lag
  • 经典题：连续登录三天的用户
• Mysql中concat()、concat_ws()和 group_concat()的用法

with as,lag

使用with as有如下好处
1、可以轻松构建一个临时表，通过对这个表数据进行再处理。但是他比临时表更强大，临时表在会话结束才会自动被清除，但with as临时表查询完成后就被清除了

2、复杂的查询会产生很大的sql，with as语法可以把一些公共查询提出来，也可以作为一个中间结果，可以使整个sql语句显得有条理些，提高可读性

lag:窗口向下顺延n个
lead: 窗口向上顺延n个
语法： lag/lead(login_date,n,default_value) 

经典题：连续登录三天的用户

WITH user_login AS (
		  SELECT 1 AS user_id,'2020-02-01' AS login_date
UNION ALL SELECT 1 AS user_id,'2020-02-02' AS login_date
UNION ALL SELECT 1 AS user_id,'2020-02-03' AS login_date
UNION ALL SELECT 1 AS user_id,'2020-02-04' AS login_date
UNION ALL SELECT 1 AS user_id,'2020-02-05' AS login_date
UNION ALL SELECT 2 AS user_id,'2020-02-01' AS login_date
UNION ALL SELECT 2 AS user_id,'2020-02-03' AS login_date
UNION ALL SELECT 3 AS user_id,'2020-02-02' AS login_date
UNION ALL SELECT 3 AS user_id,'2020-02-03' AS login_date
UNION ALL SELECT 3 AS user_id,'2020-02-04' AS login_date
UNION ALL SELECT 4 AS user_id,'2020-02-01' AS login_date
UNION ALL SELECT 4 AS user_id,'2020-02-02' AS login_date
UNION ALL SELECT 4 AS user_id,'2020-02-03' AS login_date
UNION ALL SELECT 5 AS user_id,'2020-02-02' AS login_date
UNION ALL SELECT 5 AS user_id,'2020-02-03' AS login_date
)
SELECT 
	DISTINCT t1.user_id
FROM (SELECT user_id, login_date, lag(login_date,2,NULL) over(PARTITION BY user_id ORDER BY login_date ) AS lag_col FROM user_login) t1
WHERE t1.login_date = DATE_ADD(lag_col,INTERVAL 2 DAY);

Mysql中concat()、concat_ws()和 group_concat()的用法

Mysql中concat()、concat_ws()和 group_concat()的用法
concat: CONCAT(str1,str2,…) 正常拼接，逗号分隔，其中常量字符串要用单引号包起来
concat_ws：CONCAT_WS(separator,str1,str2,…) 第一个是分隔符，后面是内容
group_concat 集合中进行添加分隔符，与 hive 中的collect_list 作用相同：列转行