0


被CTO推荐的SQL总结

更多文章欢迎关注公众号:stackoverflow,图片上传压缩后不清晰,可加微信isea_you,发PDF版给你😄

下面是关于SQL在引擎内部执行的顺序的简易版/必记版:

from 某表,group by 某字段,开窗 ,聚合函数,having,distinct , order by , limit ,尤其注意当group by 和 开窗相遇时,一定是分组groupBy优先

1️⃣ hive的架构

如下图是Hive的架构图,即解析器-编译器-优化器-执行器,区别于MySQL的,连接器-分析器-优化器-执行器
在这里插入图片描述

metastrore是存储元数据的数据库,默认使用的是derby,可以更改为MySQL,元数据指的是将结构化数据映射成一张表的表名,表所属的数据库(默认为default),表的拥有者,表的列,分区字段,表的类型(是否为外部表)表所在的目录等。Hive只是和RDB只是在SQL语句上有着类似之处

2️⃣ 一些hive中的函数

2.1-collect_x

在使用这个函数时,需要设置

set hive.map.aggr = false;

否则可能会发生

IllegalArgumentException Size requested for unknown type: java.util.Collection

的异常

select collect_set(col_a)  as          set_a
     , collect_list(col_a) as          list_a
     , sort_array(collect_list(col_a)) sort_list_a
from (
         select 'a' col_a
         union all
         select 'b' col_a
         union all
         select 'a' col_a
         union all
         select 'a' col_a
     ) t

在这里插入图片描述

2.2-日期/时区

select date_format('2019-02-10','yyyy-MM');  
2019-02

select date_add('2019-02-10',-1),date_add('2019-02-10',1);
2019-02-09    2019-02-11
-- (1)取当前天的下一个周一
select next_day('2019-02-12','MO')
2019-02-18
-- 说明:星期一到星期日的英文(Monday,Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday)

-- (2)取当前周的周一   
select date_add(next_day('2019-02-12','MO'),-7);
2019-02-11

-- (3)取当前周的周日   
select date_add(next_day('2019-06-09','mo'),-1);
2019-06-09

-- (4)求当月最后一天日期
select last_day('2019-02-10');
2019-02-28
-- 将北京时间转为巴西时间
select from_utc_timestamp(to_utc_timestamp("2021-05-09 22:14:30",'GMT+8'),"GMT-3")
2021-05-09 11:14:30.0

select date_format(from_utc_timestamp(to_utc_timestamp("2021-05-09 22:14:30",'GMT+8'),"GMT-3"),'yyyy-MM-dd HH:mm:ss')
2021-05-09 11:14:30
-- 求上个月select substr(add_months(current_date(),-1),1,7) method_one
, substr(date_sub(from_unixtime(unix_timestamp()), dayofmonth(from_unixtime(unix_timestamp()))),1,7)as method_two

2.3-字符串处理

substr

replace

等就不赘述了

-- 1)select regexp_extract('http://a.m.taobao.com/i41915173660.html','i([0-9]+)',0), regexp_extract('http://a.m.taobao.com/i41915173660.html','i([0-9]+)',1)-- i41915173660    ,   41915173660-- 0是显示与之匹配的整个字符串; 1是显示第一个括号里面的-- 2)select  regexp_replace('a1b2c3d4','[0-9]','-');-- a-b-c-d--- 3)-- 某字符串是另外一个字符串的子串
instr(string string, string substring)-- 返回查找字符串string中子字符串substring出现的位置,如果查找失败将返回0,如果任一参数为Null将返回null,位置为从1开始。

2.4-除数为0处理

这里我们将比较不同的引擎是如何处理除数为0的问题的,如下图:

在这里插入图片描述

对于除数为0问题,优先使用

nullif

函数来进行处理,但是该函数Hive2.2才有对应实现

3️⃣ 一些hive中的语法

3.1-sum() + over()

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IDCihZXQ-1664462446005)(img/hive/6.jpg)]

  • over() 全局求和
  • over(order by) 全局累积求和
  • over(partition by ) 分区内全局求和
  • over(partition by order by) 分区内累积求和

内网环境下,下面的脚本分别在impala,hive,holo,pg , mysql ,执行以下,看下是什么情况

with tmp1 as(select'a'as a,1as b 
    unionallselect'a'as a ,2as b
    unionallselect'b'as b ,10as b
)select a ,avg(b)over(partitionby a orderby b) x ,sum(b)over(partitionby a orderby b) y
from tmp1 
;

关于聚合函数+窗口函数的描述,GreeksforGreeks上的那个博文是错的

3.2-侧写视图(lateral view)

🎈侧写试图主要用来处理行转列的问题

select col_b,col_c
from (
    select 'a/b/c/d' as col_b
) t lateral view explode(split(col_B, '/')) x as col_c

col_bcol_ca/b/c/daa/b/c/dba/b/c/dca/b/c/dd

3.3-lag+over的使用

在这里插入图片描述

  • lag 落后是获取上一个
  • lead 领导是获取下一个

SQLStructuredQueryLanguage标准认为当前行的上一个行是后面,下一行是前面,即

在这里插入图片描述

3.4-一周内连续3天活跃

在这里插入图片描述

with tmp1 as(select0as mid_id,'2020-02-12'as dt unionallselect0as mid_id,'2020-02-16'as dt  unionallselect0as mid_id,'2020-02-17'as dt  unionallselect0as mid_id,'2020-02-18'as dt  unionallselect1as mid_id,'2020-02-11'as dt  unionallselect1as mid_id,'2020-02-13'as dt  unionallselect1as mid_id,'2020-02-14'as dt  unionallselect1as mid_id,'2020-02-17'as dt
),
tmp2 as(select mid_id
            , dt
            , row_number()over(partitionby mid_id orderby dt)               rk
            , date_sub(dt, row_number()over(partitionby mid_id orderby dt)) diff
       from tmp1
       where dt between date_sub('2020-02-18',7)and'2020-02-18')select mid_id
from tmp2
groupby mid_id,diff
havingcount(*)>=3;

对于类似的连续X天的问题,最优的解决方案是使用开窗函数,另外的一种解题思路是自关联,关联时,使用

t1.mid_id = t2.mid and t1.dt = date_sub(t2.dt,x)

的方式

3.5-left semi join

关于

left semi join

注意2点:

🅰️

left semi join

要严格区分于

left outer join(left join)

🅱️

t1 left semi join t2 

选列时,不允许出现t2 的字段

select t1.id, t1.fieldA
from`table_A` t1
where t1.id in(select id
    from`table_B`);-- A 和 B的 交集-- 可改写为exists的方式select t1.*from`table_A` t1
whereexists(select t2.id
    from`table_B` t2
    where t1.id = t2.id
)-- 还可改写为select t1.*from`table_A` t1
leftjoin`table_B` t2
on t1.id = t2.id
where t2.id isnotnull-- A 和 B 的交集;-- 改写为 ,这种方式更加高效select t1.*-- 不允许出现t2 的字段from`table_A` t1
left semi join`table_B` t2
on t1.id = t2.id;

同理对于

not exist
select t1.*from`table_A` t1
leftjoin`table_B` t2
on t1.id = t2.id
where t2.id isnull-- A中有B中没有;-- 我们换成下面的写法select t1.*from`table_A` t1
wherenotexists(select t2.id
    from`table_B` t2
    where t1.id = t2.id
)-- A中有B中没有-- 或者换成下面的写法select t1.*from`table_A` t1
wherenotin(select t2.id
    from`table_B` t2
    where t1.id = t2.id
)-- A中有B中没有

3.6-distinct

🅰️ distinct 和 order by 的结合

先执行distinct ,后执行order by ,最后limit

在这里插入图片描述

🅱️ distinct 多个字段

distinct

多个字段对所有字段都起作用,并不是一个;如

select distinct field_a,field_b from table;
a1,b1;
a1,b2;
a2,b2;-- 只要有不同就会被选择出来

3.7-limit offset

limit x offset y

,

    y
   
  
  
   y
  
 
y是

 
  
   
    x
   
  
  
   x
  
 
x的倍数出现,可以恰好将数据取完,
limit x offset y

等效于

limit y,x

在这里插入图片描述

select*from(select1 a
    unionallselect2 a
    unionallselect3 a
    unionallselect4 a
    unionallselect5 a
) t
orderby a desclimit3offset3;

最后一个截图的SQL语句,我在Hive2.1.1中的执行结果是:

在这里插入图片描述

说明在Hive中

offset

的排序是从1开始的x取0等于于x=1

3.8-ntile+over

ntile(x)

将数据划均分为x个桶,并且返回桶编号,如果有多的元素,优先进入第一个桶

在这里插入图片描述

with tmp1 as(select'a'as name ,'one'as claz,1as score unionallselect'b'as name ,'two'as claz,2as score unionallselect'c'as name ,'two'as claz,3as score unionallselect'd'as name ,'one'as claz,4as score unionallselect'e'as name ,'one'as claz,5as score unionallselect'f'as name ,'two'as claz,6as score unionallselect'g'as name ,'one'as claz,7as score unionallselect'h'as name ,'one'as claz,8as score unionallselect'i'as name ,'two'as claz,9as score unionallselect'j'as name ,'two'as claz,0as score
)select*, ntile(2)over(partitionby claz orderby score) rn
from tmp1;

3.9-like多个字段

在这里插入图片描述

第一个截图是

mysql

3.10-窗口函数的范围选择

注意

range

rows

之间的使用区别

agg_func over(orderby col_name rowsbetween1 proceding and1following)-- col_name的前后1行
agg_func over(orderby col_name range between1 proceding and1following)-- col_name值的(+/- 1) 的值

agg_func over(orderby col_name rowsbetweenunboundedprecedingandunboundedfollowing)-- 全部行
agg_func over(orderby col_name rowsbetweenunboundedprecedingandcurrentrow)-- 开头到当前行

在这里插入图片描述

在这里插入图片描述

4️⃣ 引擎的一些不同

4.1-select 非 group by 字段

MySQL支持,Hive,Impala,PostgreSQL 不支持

对于下面这一段SQL

select dept
     , emp
     ,max(sal)as max_sal
from(select'A'as dept,'a1'as emp,10as sal unionallselect'A'as dept,'a2'as emp,20as sal unionallselect'B'as dept,'b2'as emp,100as sal unionallselect'B'as dept,'b1'as emp,200as sal
) t
groupby dept

1️⃣MySQL 通过

在这里插入图片描述
MySQL 选择记录中的第一个记录(从实验结果来看,是记录的第一行)

2️⃣postgreSQL:

[42803] ERROR: column"t.emp" must appear in the GROUPBY clause or be used in an aggregate function

3️⃣Hive:

Error while compiling statement: FAILED: SemanticException [Error 10025]: line 2:7 Expression notinGROUPBYkey'emp'

4️⃣Impala:

AnalysisException: select list expression not produced by aggregation output (missing fromGROUPBY clause?): emp

4.2-having过滤是否支持别名

MySQL和Hive是支持的, impala和postgreSQL不支持

select a,count(*)as cnt
from(select5as a
    unionallselect4as a
    unionallselect4as a
    unionallselect3as a
    unionallselect3as a
) t
groupby a
having cnt >1;

上述的SQL在MySQL 和 hive中执行都是没问题的,在impala和postgreSQL报错

column "cnt" does not exist

,需要下面的写法

select a,count(*)as cnt
from(select5as a
    unionallselect4as a
    unionallselect4as a
    unionallselect3as a
    unionallselect3as a
) t
groupby a
havingcount(*)>1;

🎈:推荐无论何时都不使用别名进行分组后过滤

4.3-order by 字符串

select a
from(select'a'as a unionall-- 97select''as a unionall-- 66select' 'as a unionall-- 32selectnullas a                -- 0) t
orderby a desc;

对于以上查询和排序,Hive和MySQL认为NULL是最小;Impala和PostgresSQL认为NULL最大,如果使用

explain

命令查看SQL的执行计划的话,会明显看到编译器会给SQL添加1个

null first / null last

的明亮,这个取决于具体的引擎,感兴趣的读者可以自己test下,比如Hive会将null设为最小,impala会将null设为最大

在这里插入图片描述

4.4-

    24
   
   
    /
   
   
    5
   
  
  
   24/5
  
 
24/5的结果

DB/Program LanguagevalueJava / PostgreSQL4Hive / Impala / MySQL4.8

4.5-窗口函数是否支持

distinct
select  A, B ,count(distinct A)over()from(select1as A ,'a'as B unionallselect2as A ,'b'as B unionallselect1as A ,'c'as B unionallselect3as A ,'d'as B
) t

比如以上的SQL查询:Hive是支持的,Impala,MySQL,PostgreSQL暂时没有实现

4.6-窗口嵌套

窗口函数的嵌套,只Hive2.1.1中是支持的,PostgreSQL(

window functions are not allowed in window definitions

),MySQL,Impala 中只能多嵌套一层

在这里插入图片描述

4.7-字符串写入数值类型

createtableifnotexists  business (
    name stirng,
    order_date string,
    cost float);insertinto business values('xioaming','2021-08-22','');

Hive 会将字符串转为null写入;Impala,MySQL,PostgreSQL会进行类型检查(即报错)

5️⃣ 一些注意点

5.1-

null

,

x

关联

在这里插入图片描述

在任何SQL(MySQL,PostgreSQL,Hive,Impala)引擎中,**

null

和任意值都无法关联无法相互关联,包括其本身**

PostgreSQL中有类型探测,执行以上关联会发生:Failed to find conversion function from unknown to text

5.2-返回1行&返回0行

在这里插入图片描述
如左图所示

5.3-union all 的类型

任何引擎,

union all

的类型必须保持一致

5.4-组合主键非

null

对于

test01

表,字段

a

和字段

b

在作为联合主键时,在字段

a

null

,字段

b

null

的时候

1️⃣

kudu

将不会写入该记录,不会抛异常

2️⃣

mysql

插入时抛出异常 类似(

primary key not null

)

3️⃣

postgresql

插入时抛出异常 类似(

primary key not null

)

5.5-时间戳

⚠️时间是人可识别的,时间戳基本是机器识别的,比如2022-01-01 00:00:001640966400,前者是时间,后者是时间戳

1️⃣ 获取时间戳

--mysqlselect unix_timestamp('2022-01-01 00:00:00');-- hive select unix_timestamp('2022-01-01 00:00:00');

2️⃣获取时间

-- mysqlselectnow();-- hiveselect from_unixtime( unix_timestamp());-- impalaselectnow(),  utc_timestamp(),current_timestamp(),from_unixtime( unix_timestamp());-- pgselectnow(),current_timestamp;

5.6-去掉文本中的换行符/回车符/制表符/空格

select regexp_replace(input_content,'\\s+','')as after_content

由于特殊字符导致表错位串行的问题描述

在这里插入图片描述

select'1\r2\t\3\n4\0015',regexp_replace('1\r2\t\3\n4','\\s+',''),regexp_replace('1     \r2\t\3\n4\0015','\\s+',''),regexp_replace('1     \r2\t\3\n4\0015','\t|\n|\001|\r','')

5.7-impala upsert

本质是

insert

+

update

的结合

  • 主键存在时,全字段更新
  • 主键不存在时,插入

5.8-不使用

order by

找到工资第二的员工

select
    e.emp_no,
    salary,
    last_name,
    first_name
from employees e
innerjoin salaries s
on e.emp_no = s.emp_no
where s.to_date ='9999-01-01'and s.salary =(select 
        s1.salary
    from salaries s1
    innerjoin salaries s2
    on s1.salary <= s2.salary
    where s1.to_date ='9999-01-01'and s2.to_date ='9999-01-01'groupby s1.salary
    havingcount(distinct s2.salary)=2)

在这里插入图片描述
最大值只能小于等于最大值;次大值只能小于等于最大值和本身

更多文章欢迎关注公众号:stackoverflow


标签: sql hive 数据库

本文转载自: https://blog.csdn.net/qq_31807385/article/details/127115325
版权归原作者 屏占比 所有, 如有侵权,请联系我们删除。

“被CTO推荐的SQL总结”的评论:

还没有评论