SQL骚套路笔记

1.在统计函数中传入判断语句

在count()函数中嵌套if判断语句，如果符合条件则为1，不符合则为null。效果等同于先where过滤，再count(*).

--使用if作为count的参数
select count(if(year='2020',1,null)) from pos_rival;

--先过滤，再count
select count(1) from pos_rival where year='2020';

--两者结果一致

基于此，可以使用一条语句统计多个count或sum等结果，如：

--统计year=2020且brand=10的数据条数
--统计year=2020的所有数据的price的总和
select 
    count(if(year='2020' and brand='10',1,null)) as count,
    sum(case when year='2020' then price else 0 end) as sum
from
    pos_rival;

2.使用正则排除字段

hive和spark-sql支持使用正则选择字段，如：

hive参数为：set hive.support.quoted.identifiers=none;

--需开启支持正则配置，否则会报错
SET spark.sql.parser.quotedRegexColumnNames=true;

--选择除了rk以外的所有字段
SELECT `(rk)?+.+`
FROM
pos_rival
;

3.调整hive和spark-sql产生的文件数量

spark的并行度和hive的reduce数量都可以影响文件数量，但是修改并行度和reduce数量都会影响性能。而使用纯sql，则不能使用spark的colesce算子来减少分区。

可以使用DISTRIBUTE BY调整生成的文件个数。

--DISTRIBUTE BY 一个常数，则将所有数据放入一个分区，即产生一个文件
INSERT OVERWRITE TABLE ldldwd.pos_rival

SELECT *
FROM ldlsrc.pos_rival

DISTRIBUTE BY 1
;

--DISTRIBUTE BY 一个随机数，将数据随机放入一个分区，产生分区个数相同的文件数
--以下代码CAST(RAND() * 5 AS INT)，随机生成整数0~4，即生成五个文件
INSERT OVERWRITE TABLE ldldwd.pos_rival

SELECT *
FROM ldlsrc.pos_rival

DISTRIBUTE BY CAST(RAND() * 5 AS INT)
;

4.使用with as提高sql可读性

从语法上看，with as就是将SQL语句中单独的子查询语句提取出来，作为一个单独的命名进行组织。在原来的语句中，就可以使用新的别名来替代。

如：

--将从pos_rival中查询出的结果命名为a
--将从pos_rival_brand中查询出的结果命名为b
--将a 与 b 进行join


with a as (select * from pos_rival where dt>='2020-01-01'),
with b as (select * from pos_rival_brand where dt>='2020-01-01') 
select * from a left join b on a.brand=b.brand;

5.将yyyy/MM/dd转换成yyyy-MM-dd

select  date_format(from_unixtime(unix_timestamp('2020/08/01','yyyy/mm/dd')),'yyyy-MM-dd');

6. max、avg、count函数对于null值的判断

create table cbd.zixuan_test_avg (
    a int
)
stored as parquet;


select * from cbd.zixuan_test_avg ;

select avg(a), count(1), count(a), count(*), sum(a) from cbd.zixuan_test_avg ;



avg(a)	    10
count(1)	9
count(a)	7
count(*)	9
sum(a)	    70