产品经理常用SQL函数整理

背景

不会写sql的产品经理,一定活的很艰难。

具体说明

1、left join on 后and 和 where 的区别


1
2
3
4
5
6
Table A left join Table B on Condition and
Table A left join Table B on Condition where

#在使用left jion时,on和where条件的区别如下:
1、 on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。

2、group里面每个类型值取top n


1
2
3
4
5
6
7
8
9
10
11
12
SELECT  *
FROM
(
  SELECT  
  group_field,
  order_field,
  row_number() over(partition by group_field order by order_field desc) rank
  from table
  where xxx
  group by group_field
)t1
where rank  小于等于 n

3、将一个json字符串拆开


1
2
3
4
5
6
7
8
9
10
11
12
13
#从json里拿字段
get_json_object(json_field,'$.field')

#按照某个字符拆开成多行的新表
lateral view  explode(split(json_field,'分隔符')) table_name as field_name

#regexp_extract
语法:    regexp_extract(string subject,  string pattern,  int index)
返回值: string
说明:  将字符串subject按照pattern正则表达式的规则拆分,返回index指定的字符。
第一参数:   要处理的字段
第二参数:    需要匹配的正则表达式
第三个参数:0是显示与之匹配的整个字符串,1 是显示第一个括号里面的,2 是显示第二个括号里面的字段

4、select里的if


1
2
3
IF( expr1 , expr2 , expr3 )
expr1 的值为 TRUE,则返回值为 expr2
expr1 的值为FALSE,则返回值为 expr3

5、几种取留存的写法


1
2
3
4
5
6
7
8
SELECT
t1.ds,
count(distinct t1.guid),
count(distinct if(datediff(t1.date,t2.date)=1,t2.guid,null)) as ciri,
count(distinct if(datediff(t1.date,t2.date)=2,t2.guid,null)) as erri,
count(distinct if(datediff(t1.date,t2.date)=7,t2.guid,null)) as qiri,
from t1 left join t2 on t1.uid=t2.uid
group by t1.ds


1
2
3
4
5
6
7
8
9
with t0 as(select *** where date=day0),
t1 as(select *** where date=day1),
t2 as(select *** where date=day2),
t7 as(select *** where date=day7)
select count(distinct t0.uid),count(distinct t1.uid),
count(distinct t2.uid),count(distinct t7.uid)
from t0 left join t1 on t0.uid=t1.uid
left join t0.guid=t2.guid
left join t0.guid=t7.guid


1
2
3
4
5
6
7
8
9
10
11
SELECT
t0.date,
count(distinct t0.uid),
count(distinct a.guid),
count(distinct b.guid),
count(distinct c.guid)
from city345
left join t a on t0.uid= a.guid and a.date=t0.date+1
left join t b on t0.uid= b.guid and a.date=t0.date+2
left join t c on t0.uid= c.guid and a.date=t0.date+7
group by t0.date
技术相关 2020-04-06
上一篇:

评论已关闭。