空值(NULL)
不能用 =
应该用 is null
或 is not null
使用 ifnull(col, default)
来处理空值
col
: 列名default
: 默认值
order by 排序
不仅能对数字大小排序 也可以根据字母顺序排序
asc
升序desc
降序
默认升序排列
在排序过程中NULL视为最小值
多字段排序时 优先级为左到右
1 | # 先按年龄 再按身高 |
limit
limit N
: 返回 N 条记录limit N, M
: 相当于limit M offset N
从第 N 条记录开始 返回 M 条记录
聚合函数
max()
求列中最大值min()
求列中最小值avg()
求列的平均值count()
求列的总行数count(*)
统计所有行数量,包括空值count(colname)
统计colname这列,空值不计count(distinct colname)
统计colname这列,非空且唯一的数量
sum()
求列的和
聚合函数会忽略空值(NULL)
解决办法 avg(ifnull(score, 0))
group by 分组查询
当使用分组查询的时候 select
后面只能跟 group by
的字段名
或者用聚合函数
1 | # 同年龄的各有多少人 |
having 过滤
没有使用 group by
分组时 having
可替代 where
使用 group by
分组时 使用 having
进行二次过滤
窗口函数
where
条件执行之后,才会执行窗口函数。
窗口函数不能在 where
中使用
group by
是分组函数 只保留分组字段和聚合函数的结果partition by
是分析函数 保留原有数据
使用多重分组来保证每组的唯一性与数据的准确性
例如 over(partition by name, tel)
排序函数
rank()
允许排名并列 存在序号不连续的情况dense_rank()
允许排名并列 序号一定连续row_number()
不允许排名并列,按查询到的顺序排序(索引)ntile(x)
将数据分为x组,并给每组分配一个数字
例如:ntile(3) 按照排序分为3组,1代表上等,2代表中等,3代表下等
多重排序
字段1相等时 按字段2排序rank() over(order by col1, col2)
with 子查询
作用:创建临时表(不写入数据库)
由于不能直接获取窗口函数的结果,因此需要创建临时表来进行子查询
1 | # 查询评分排名第二的游戏 |
自定义窗口
作用:以 当前行
为基准,精确控制要选取的数据范围(开窗大小)
rows
物理窗口range
逻辑窗口
unbounded preceding
对上限无限制n preceding
当前行之前的第n行current row
仅当前行n following
当前行之后的第n行unbounded following
对下限无限制between lower_bound and upper_bound
当前行加下限与上限的区间之内
简写sum(price) over(order by `date` rows unbounded preceding)
等价于
sum(price) over(order by `date` rows between current row and unbounded preceding)
下限不能简写!
range
和 rows
不同,range
的范围取决于跟在 order by
后的字段的值!
假设函数为
over(order by size range between 2 preceding and 2 following)
则窗口大小是字段size的值在 [size-2, size+2]
内的行
like 模糊查询
%
0或多个任意字符_
一个任意字符
分析函数
作用:计算增量或转化率
向下面行取
lead(x, y, z)
- x 要获取的字段
- y 偏移(省略则默认为1)
- z 默认值(省略则默认为NULL)
lead(x, 2) over()
当前行
为第一行时,获取第3行的x字段值,若没有则返回z
向上面行取
lag(x, y, z)
lag(x, 2) over()
当前行
为第3行时,获取第1行的x字段值,若没有则返回z
lead(...) over(order by ...)
等价于
lag(...) over(order by ... desc)
返回指定列的第一个值
first_value(x)
返回指定列的最后一个值
last_value(x)
当 over
子句中包含 order by
时, 如果不显式定义窗口大小,SQL会带上默认的语句 range unbounded preceding
,意味着查询范围被限定在第一行至当前行!
想要通过 last_value
与 over()
取所有数据排序后的最后一个值,则需要定义窗口大小为如下。
last_value(col1) over(order by col2 [rows|range] between unbounded preceding and unbounded following)
返回指定列的第几行的值
nth_value(x, n)
- x 列名
- n 第几行