2021-10-16

近期看了自己的sql代码，发现一些需要修改的地方，一个是跟不同情况下的分组聚合查询结果，还有个是时间区间写了好多的case when，比较影响整段代码的可阅读行。

1、首先是对于不同情况下的分组聚合查询，我是直接分组查询然后将不同情况下的结果union all在了一起，其实完全可以使用grouping set、rollup、cube等提高代码的可阅读性。

--我想对不同维度下的字段进行分组查询
--原来的写法
	select
		nvl(line1,'未知'),  --也可以使用coalesce(line1,'未知')
		nvl(line2,'未知'),
		'未知' as line3,
		sum(line4)
	from
		table_name t1
	where 
		line = '****'
	group by
		line1,
		line2
	union all
	select
		nvl(line1,'未知'),
		'未知' as line2,
		nvl(line3,'未知'),
		sum(line4)
	from
		table_name t1
	where 
		line = '****'
	group by
		line1,
		line3;
--新写法
	select
		nvl(line1,'未知'),
		nvl(line2,'未知'),
		nvl(line3,'未知'),
		sum(line4)
	from
		table_name t1
	where 
		line = '****'
	group by --group by之后可以不加字段，但是select之后出现字段都必须要在sets之后的括号里包含
		line1,
		line2,
		line3
	grouping sets
	(
		(line1,line2),
		(line1,line3)
	)；

2、第二个是图方便用到了很多的case when，具体需求是我需要把每天的时间进行分组拼接成 [00-01]、[01-02]…这种组合，而一天24个小时就需要很多的case when，导致代码看起来就很弱智。

--原来写法
select
	case 
		when hour = 0 then '(00-01]',
		when hour = 1 then '(01-02]',
		when hour = 2 then '(02-03]',
		else '(03-24]'
from 
	(
	select
		datepart(datetime,'hh') hour --datetime字段需要是datetime类型
	from
		table_name
	);
	
--后来写法是利用lpad函数，Lpad(字段,全字段的长度,需要补充的数据内容)   
select
	concat('(',lpad(hours,2,00),'-',lpad(hours+1,2,00),']')
from 
	(
	select
		cast(datepart(datetime,'hh') as int) hours --datetime字段需要是datetime类型
	from
		table_name
	)

3、第三个是对一个字符串做提取，只需要其中的中文和英文，用来和关键字识别，做这个第一个想到就是like，但是提取字段中可能有特殊符号就需要对字符串做一个处理，然后匹配关键字，这里就是用到了正则表达式

select
	--中括号里还有两个中括号，其中以 | 作为区分，代表或者的意思，前一个中括号是剔除特殊字符，后一个中括号是剔除数字
	--也可以加上[a-z|A-Z]剔除英文字符
	REGEXP_REPLACE(remark,'[[（）。，！￥【】{}!”#$%&’()*+,./:;<=>? @\^_`{|}~-]|[0-9]]*','') keyword
from table_name

4、还有就是多表关联时，odps不允许直接使用不等条件关联，这里提供两个方法

--第一种直接加注释，告诉编译器我使用的是mapjoin，但是这种方法适用于大表关联小表，t2为小表
select /*+mapjoin(t2)*/
	t1.a,
	t1.c
from 
	table1 t1
left join
	table2 t2
on t1.a > t2.b
;
--第二种是添加一个字段强制关联
select 
	t1.a,
	t1.c
from 
	(select a,c,1 as joi from table1) t1
left join
	(select b,1 as joi from table2) t2
on t1.a > t2.b
	and t1.joi = t2.joi
;