常用SQL总结

1.时间函数

2.字符串处理

3.开窗函数

1.开窗函数简介

与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。

2.为什么需要开窗函数

在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在 2003 年 ISO SQL 标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。

分数表-创建临时表的sql

with score_table as(

SELECT

1 AS class_id,

1001 AS student_id,

90 AS score

UNION

SELECT

1 AS class_id,

1002 AS student_id,

100 AS score

UNION

SELECT

1 AS class_id,

1003 AS student_id,

100 AS score

UNION

SELECT

2 AS class_id,

1004 AS student_id,

100 AS score

UNION

SELECT

2 AS class_id,

1005 AS student_id,

80 AS score

)

select class_id,student_id,score from score_table order by 1,3;

分数表：

1. 求每个班级的的学生人数与平均分

使用group by ：https://dt.mi.com/workspace/#/workspace/10033/adHoc/1135233/undefined

1. 在每条分数数据上，加上本班级的平均分等

1.使用group by求出班级平均分，然后再和分数表进行关联：https://dt.mi.com/workspace/#/workspace/10033/adHoc/1135276/undefined

2.聚合开窗函数 ：https://dt.mi.com/workspace/#/workspace/10033/adHoc/1135361/undefined

1. 每个学生在班级内的排名

排序开窗函数 ：https://dt.mi.com/workspace/#/workspace/10033/adHoc/1135418/undefined

3.开窗函数语法

开窗函数格式： 函数名(列) OVER(选项)

例如：

row_number() over(partition by ... order by ...)

rank() over(partition by ... order by ...)

dense_rank() over(partition by ... order by ...)

count() over(partition by ... order by ...)

max() over(partition by ...)

min() over(partition by ...)

sum() over(partition by ...)

avg() over(partition by ...)

first_value() over(partition by ... order by ...)

last_value() over(partition by ... order by ...)

lag() over(partition by ... order by ...)

lead() over(partition by ... order by ...）

OVER 关键字表示把函数当成开窗函数而不是聚合函数。

PARTITION BY 子句：

开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响

ORDER BY子句：

开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则，而且有的开窗函数还要求必须指定排序规则。使用ORDER BY子句可以对结果集按照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算

select

name,

score,

sum(score) over(order by score range between 2 preceding and 2 following) s1, -- 当前行的score值加减2的范围内的所有行

sum(score) over(order by score rows between 2 preceding and 2 following) s2, -- 当前行+前后2行，一共5行

sum(score) over(order by score range between unbounded preceding and unbounded following) s3, -- 全部行，不做限制

sum(score) over(order by score rows between unbounded preceding and unbounded following) s4, -- 全部行，不做限制

sum(score) over(order by score) s5, -- 第一行到当前行（和当前行相同score值的所有行都会包含进去）

sum(score) over(order by score rows between unbounded preceding and current row) s6, -- 第一行到当前行（和当前行相同score值的其他行不会包含进去，这是和上面的区别）

sum(score) over(order by score rows between 3 preceding and current row) s7, -- 当前行+往前3行

sum(score) over(order by score rows between 3 preceding and 1 following) s8, --当前行+往前3行+往后1行

sum(score) over(order by score rows between current row and unbounded following) s9 --当前行+往后所有行

from

stu_score

order by

score;

4.常用的开窗函数

-- 聚合开窗函数

count(); -- 窗口内总条数

sum(); -- 窗口内数据的和

min(); -- 窗口内最小值

max(); -- 窗口内最大值

avg(); -- 窗口内的平均值

-- 排序开窗函数

row_number(); -- 从1开始，按照顺序，生成分组内记录的序列

rank(); -- 生成数据项在分组中的排名，排名相等会在名次中留下空位

dense_rank(); -- 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

ntile(n); -- 将分区中已排序的行划分为大小尽可能相等的指定数量的排名的组，并返回给定行所在的组的排名。

percent_rank(); -- 计算给定行的百分比排名。可以用来计算超过了百分之多少的人。(当前行的rank值-1)/(分组内的总行数-1),如360小助手开机速度超过了百分之多少的人。

cume_dist(); -- 计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。

-- 其他窗口函数

FIRST_VALUE(); -- 返回分区中的第一个值。

LAST_VALUE(); -- 返回分区中的最后一个值。

LAG(col,n,default); -- 用于统计窗口内往上第n个值。

LEAD(col,n,default); -- 用于统计窗口内往下第n个值。

4.行列转换

• 列转行

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合

select

name,

cast(bind_id as int) as province_id

from

(

select

name,

split(regexp_extract(sale_region, '^\\\[(.*)]$', 1), ',') as sale_region

from info.ods_xmbuyn_xm_branch_company

) t lateral view explode(t.sale_region) myTable as bind_id;

结果对比：

--查询sale_region：

+---------+--------------+--+

| name | sale_region |

+---------+--------------+--+

| 安徽分公司 | [13] |

| 福建分公司 | [14] |

| 甘肃分公司 | [29,30,31] |

| 广东分公司 | [20,22] |

--转换后结果：

+---------+--------------+--+

| name | province_id |

+---------+--------------+--+

| 安徽分公司 | 13 |

| 福建分公司 | 14 |

| 甘肃分公司 | 29 |

| 甘肃分公司 | 30 |

| 甘肃分公司 | 31 |

| 广东分公司 | 20 |

| 广东分公司 | 22 |

• 行转列

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段，可以搭配group合并同一组内数据；

COLLECT_LIST(col)：他和COLLECT_SET不同的是不会对数据去重。

select

t.base,

concat_ws('|', collect_set(t1.name)) name

from

(select

name,

concat(星座, ",", 血型) base

from person_info) t

group by

t.base;

结果对比：相当于列转行反向转换

5.可读性类

with

with... as...这个语法会将查询的结果集保存在内存中，不重复查询；

with子句必须在引用的select语句之前定义,同级with关键字只能使用一次,多个只能用逗号分割；最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来.

with table_a as (

select

city_id,

city_name

from table_a

),

table_b as (

select * from table_c

)

select * from table_b limit 22;

with... as... 也是可以嵌套使用的

with table_a as (

with tmp_a as (

select

city_id,

city_name

from table_a

)

select * from tmp_a

),

table_b as (

select * from table_c

)

select * from table_b limit 22;

cache

spark语法，将查出来的结果缓存到内存中，后续多次使用

cache table c_sku as select * from tmp_a；

...

uncache table c_sku;

temporary view

spark语法，如果在整个会话中多次使用某次的查询结果的话，可以把该次查询结果创建为临时视图，方便后续使用。保存的仅仅是一段查询语句的逻辑，而不是查询的结果集，使用一次就触发一次查询，如果逻辑复杂，不建议使用。

create temporary view t1 as

select

trade_date,

sec_code,

sec_name

from tra_stock_info_ss;

6.其他技巧

• union/union all:在查询几个相同结构数据源的时候，可以使用union/union all 来合并结果集，他们都会按默认的字段排序，所以查询语句要求同序列的字段顺序相同；union：相同的记录合并去重返回，union all不会去重；
• Case when : 一旦满足了某一个WHEN, 则这一条数据就会退出CASE WHEN , 而不再考虑 其他CASE;
• distinct/group by:都可以实现去重的效果，但是查询多个字段的去重指标的时候，建议使用group by，distinct 是针对要查询的全部字段去重，而 group by 可以针对要查询的全部字段中的部分字段去重，它的作用主要是：获取数据表中以分组字段为依据的其他统计数据。
• coalesce( value1,value2,… ) 取第一个非null数据
• if( value1 is null, value2, value1) 有妙用，另类列转行

-- T+1的分公司销量

select 分公司,

sum(销售额) 月销售额,

sum(if substr(cast(ymd as string),7,2)="01",销售额,0) 销售额01,

sum(if substr(cast(ymd as string),7,2)="02",销售额,0) 销售额02,

.....

sum(if substr(cast(ymd as string),7,2)="31",销售额,0) 销售额31,

from xxx

where ymd <= from_unixtime(unix_timestamp() - 86400,'yyyyMMdd') -- 昨日

and ymd >= from_unixtime(unix_timestamp() - 86400,'yyyyMM01') -- 昨日的月

group by 1 order by 2 desc;

• ....

7.查询引擎的选取

默认presto会比较快，预发略有差别

大表presto查不出来的，选用spark引擎查询

很大搞不定的，考虑写个spark任务把数据计算出来落到一个临时表里面

8.SQL调优

大表最好是限定条件过滤条件，inner join等来过滤数据