黑洞

这里藏着一些独特的想法

0%

SQL学习笔记

空值(NULL)

不能用 =
应该用 is nullis not null

使用 ifnull(col, default) 来处理空值

  • col : 列名
  • default : 默认值

order by 排序

不仅能对数字大小排序 也可以根据字母顺序排序

  • asc 升序
  • desc 降序

默认升序排列

在排序过程中NULL视为最小值

多字段排序时 优先级为左到右

1
2
# 先按年龄 再按身高
select * from person order by age, height;

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
2
# 同年龄的各有多少人
select age, count(1) from t_person group by age;

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
2
3
4
5
6
7
8
9
# 查询评分排名第二的游戏
with ranking as (
select
name,
dense_rank() over(order by editor_rating desc) as `rank`
from game
)

select name from ranking where `rank` = 2;

自定义窗口

作用:以 当前行 为基准,精确控制要选取的数据范围(开窗大小)

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)

下限不能简写!

rangerows 不同,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_valueover() 取所有数据排序后的最后一个值,则需要定义窗口大小为如下。

last_value(col1) over(order by col2 [rows|range] between unbounded preceding and unbounded following)

返回指定列的第几行的值

  • nth_value(x, n)
    • x 列名
    • n 第几行
如果觉得文章写得不错或对您有帮助,请我喝杯柠檬茶吧!