likun.pm

背景

不会写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