1.基础语法
1.select …A… from …B… where …C…
A:列名
B:表明
C:筛选条件
如果该表是一个分区表，则where条件中必须对分区字段进行限制。
2.group by … having
3.常用聚合函数
1.count():计数 count(distinct … …)去重计数
2.sum():求和
3.avg():平均值
4.max():最大值
5.min():最小值
4.order by
2.执行顺序
from–>join–>on–>where–>group by–>with–>having–>select–>distinct–>order by–>limit
3.常用函数
1.将时间戳转化为日期
from_unixtime(bigint unixtime,‘string format’)
format:
1.yyyy-MM-dd hh:mm:ss MM代表月份，mm代表分钟
2.yyyy-MM-dd HH:mm:ss HH代表24小时制，hh代表12小时制
3.yyyy-MM-dd hh
4.yyyy-MM-dd
转化成标准的形式后可以用to_date(),year(),month()等函数获取日期、年、月等
2.计算日期间隔
datediff(string enddate,string startdate)
日期增加、减少函数
date_add(string startdate, int days)
date_sub(string startdate, int days)
3.条件函数
1.case when
例如
case when age<20 then ‘20岁以下’
when age>=20 and age<30 then ‘20-30岁’
when age>=30 and age<40 then ‘30-40岁’
else ‘40岁以上’ end as age_type
2.if
例如
if(level>5,‘高’，‘低’) as level_type
if(a,b,c) a为判断条件，为True则为b，为False则为c。
4.字符串函数
substr(string A,int start,int len)
指定切割字符串，起始位置（从1开始）与切割长度
5.聚合统计函数
例如
datediff(max(time),min(time))
但是聚合函数不允许嵌套avg(count(*))
4.表连接
1.内连接
(inner) join
表连接必须进行重命名
每一次join都必须使用on
2.左连接
left join
以左边的表为全集，返回能匹配上右边表的结果，没有匹配则显示NULL。
right join类似，且可以由left join代替。
3.取出在表1不在表2的数据
select *
from a left join b
on a.qqq=b.qqq
where b.qqq is null;
4.全连接
full join
先以left join匹配，再将左表匹配不上的显示NULL
可以使用coalesce(expression_1,expresssion_2,…expression_n)一次代表各参数表达式，遇到非null值返回该值，直到所有表达式都是空最终返回一个空值。它有去重的功能
例如用full join
再select coalesce(a.user_name,b.user_name)可以得到所有名字。
5.union all
将字段按顺序整合
名称必须一致否则没有意义，没有连接条件，连接后字段要重命名
union all与union的区别是
union会去重且排序（按字段顺序）
union all不去重，不排序，效率更高
select user_id
user_name
from a
union all
select user_id
user_name
from b5.窗口函数
聚合函数只生成一个结果
窗口函数每一行都生成一个结果，有时既需要聚合前的数据又需要聚合后的数据，就可以使用窗口函数。
窗口函数在select时执行，位于order by之前。
1.累计计算
sum(…A…) over(partition by …B… order by …C… rows between …D1… and …D2…)
A:需要计算的字段
B:分组的字段名称
C:排序的字段名称
D:计算的行数范围
D可以为
rows between unbounded preceding and current row——之前所有行和本行
rows between current row and unbounded following——本行和之后所有行
rows between 3 preceding and 1 following——从前三行到下一行
不设置则为全部行
类似的可以用avg、max、min代替sum
2.分区排序
row_number() over() (as …)
rank() over() (as …)
dense_rank() over() (as …)
分区前一个括号没有任何内容，over里与上文类似
row_number从1开始+1排序
rank从1开始，当1和2相等，3不等时排序为1，1，3
dense_rank从1开始，当1和2相等，3不等时排序为1，1，2
3.分组排序
ntile(n) over() (as …)
n代表切分的片数，即将字段切成n份，第一份为1、第2份为2
切分不均的时候会尽量保持均匀。
例如总共18个，且4份，则5个1、5个2、5个3、4个4、4个5。
不支持rows between
4.偏移分析
lag和lead可以取出同一字段的前N行数据和后N行数据作为独立的列。
实际运用时可以使用join但是lag与lead更简洁，效率更高。
lag(exp_str,offset,defval) over()
lead(exp_str,offset,defval) over()
exp_str是字段名称
offset是偏移量，默认值为1，即往前1行。
defval为超出表范围时返回的值，默认为NULL。
6.常见错误
1.标点符号错误
使用全角符号，英文字符
2.没有对子查询的表进行重命名
3.使用错误的字段名
4.分区表没有设置where条件
5.忽略执行顺序，使用别名的字段运算
例如在同一个查询内select后命名字段不应在group by中使用
因为先执行group by再执行select。
