窗口函数避坑总结方法技巧
一 sql五字句顺序
五字句 where< group by < having < order by <limit
二 sql 窗口字段顺序(坑)
FROM
WHERE
GROUP BY
聚合函数
HAVING
窗口函数
SELECT
DISTINCT
UNION
ORDER BY
OFFSET
LIMIT
1 不能使用窗口函数的情况
① 不能在where 字句中使用窗口函数 :窗口函数于where后执行
② 不能在having字句中使用窗口函数 : 窗口函数于having后执行
③ 不能在group by 中使用窗口函数 : 解决方法 使用子查询
栗子:
错误sql:
SELECT
id,
NTILE(4) over(order by price)
from tableA
ORDER BY NTILE(4) over(order by price);
正确sql:
SELECT
id,
NTILE(4) over(ORDER BY price)
FROM (
SELECT
id,
NTILE(4) over(ORDER BY price) as qua
FROM tableA)
ORDER BY qua;
2窗口函数与group by一起使用
注意: 这是聚合函数嵌套使用的唯一场景
问题原因 :窗口函数在group by 或者having后面进行聚合后执行,所以窗口函数后面处理的数据不是原始数据.
栗子 :
错误sql:
SELECT
id,
max(price),
avg(price) over()
FROM tableA
GROUP BY id; # group by分组后结果只有一列id,此时执行窗口函数,数据不存在price
正确sql:
SELECT
id,
max(price),
avg(max(price) over())
FROM tableA
GROUP BY id;
3rank时使用聚合函数
我们可以在聚合函数的结果上使用rank函数,看下面的栗子.
SELECT
name,
count(id),
RANK() over(ORDER BY count(id))
FROM table
GROUP BY country;
4利用group by 计算环比
SELECT
ended,
sum(id) as `sum`,
LAG(sum(id)) over(ORDER BY ended),
sum(id)-LAG(sum(id)) over(ORDER BY ended)
FROM auction
GROUP BY ended
ORDER BY ended;
5对group by 分组后的数据使用 partition by
使用group by 之后使用窗口函数 只能处理分组之后的数据,而不是处理原始数据
小结
- 1窗口函数只能出现在select 和order by 字句中
- 2如果查询的其他部分( where,GROUP BY, having)需要窗口函数,请使用子查询,在子查询使用窗口函数
- 3 如果查询使用聚合或者group by 请记住窗口函数只能处理分组后的结果,而不是原始的表数据.
三 排序函数
- 最基本的排序函数: RANK() OVER(ORDER BY column1,
column2…) . - 通过排序获取序号的函数介绍了如下三个:
1 RANK() – 返回排序后的序号 rank ,有并列的情况出现时序号不连
续
2 DENSE_RANK() – 返回 ‘连续’ 序号
3 ROW_NUMBER() – 返回连续唯一的行号,与排序 ORDER BY 配合返
回的是连续不重复的序号 - NTILE(x) – 将数据分组,并为每组添加一个相同的序号
四 分析函数
- LEAD(x) 和 LAG(x) 分别返回传入的列x对于当前行的下一行/前一行的值
- LEAD(x,y) 和 LAG(x,y) 分别返回传入的列x对于当前行的后y行/前y行的值
- FIRST_VALUE(x) 和 LAST_VALUE(x) 分别返回列x 的第一个值/最后一个值
- NTH_VALUE(x,n) 返回 x 列的 第n个值
- LAST_VALUE 和 NTH_VALUE 通常要求把window frame修改成 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
五 order by/group by 多字段顺序
- order by 字段1 字段2 排序 : 先对第一个字段排序 ,再对第二个字段排序
- group by 字段 字段2 排序 :看做一个整体
六 partition by / group by 的区别
PARTITION BY
的作用与 GROUP BY
类似:将数据按照传入的列进行分组,
和 GROUP BY
的区别是, PARTITION BY
不会改变结果的行数。
① group by是分组函数,partition by是分析函数
②在执行顺序上:from > where > group by > having > order by,
而partition by应用在以上关键字之后,可以简单理解为 就 是在执行完select之后,在所得结果集之上进行partition by分组
③ partition by相比较于group by,能够在保留全部数据的基础上,只对其中某些字段做分组排序(类似excel中的操作),而group by则只保留参与分组的字段和聚合函数的结果(类似excel中的pivot透视表)
七 window frames(窗口框架)
窗口框架(Window frames) 可以以当前行为基准,精确的自定义要选取的数据范围。
对上限无限制 unbounded preceding
对下限无限制 unbounded following
仅当前行 current row
当前行之前的第n行 n preceding
当前行之后的第n行 n fowllowing
默认的 window frames 自定义窗口
① 如果有 over 中order by 则所有的行视为一个 windows frames
② 如果over 中指定了 order by 字句,则会默认添加 'range unbounded preceding'(值 对上限不限制)
栗子:
-- 统计到当前行为止的累计下单金额(running_total),以及前后3天下单金额总和(sum_3_before_after)。
SELECT
sum(total_price) over(order by placed rows unbounded preceding)as `running_total`,
sum(total_price) over(order by placed rows BETWEEN 3 preceding and 3 following)as `sum_3_before_after`
from
single_order;
-- 需求:按下单日期排序,统计订单日期,下单日期,到当前行为止的累计下单数量
SELECT
id,
placed,
COUNT(id) over(ORDER BY placed asc rows BETWEEN unbounded preceding and current row) as `下单总量`
FROM
single_order;
-- 仓库发货时需要手工拣货。 对于order_id = 5的订单,计算未分拣的商品数量总和。 对于该订单中的每种商品,按升序查询起出货明细中的ID,产品ID,产品数量和剩余未拣货商品的数量(包括当前行)
SELECT
id,
product_id,
quantity,
sum(quantity) over(ORDER BY id rows BETWEEN current row AND unbounded following)
from
order_position
where order_id = 5;
-- 10 统计每件商品的上架日期,以及截至值该日期,上架商品种类数量
SELECT
id,
name,
introduced,
COUNT(id) over(ORDER BY introduced rows unbounded preceding) as `商品种类数量`
FROM
product;
-- 针对每一笔订单,统计下单日期,订单总价,每5笔订单计算一次平均价格(当前行,前后各两行,按下单日期排序),并计算当前订单价格和每5笔订单平均价格的比率
--
SELECT
placed,
total_price,
avg(total_price) over(order by placed rows BETWEEN 2 preceding AND 2 following) as `平均价格`,
total_price / avg(total_price) over(order by placed rows BETWEEN 2 preceding AND 2 following) * 100
FROM
single_order;
-- 需求:统计product_id 为3的商品库存变化情况,按照进出库日期排序,并统计库存变化当日的累计库存
-- 结果包括字段:`id`, `changed` (库存变化日期), `quantity`(总量),`sum` (累计库存)
SELECT
id,
changed,
quantity,
SUM(quantity) over(ORDER BY changed rows unbounded preceding) as `库存`
from
stock_change
where product_id = 3;
-- 需求:统计每个订单的下单日期,总价,每4个订单的平均价格(当前行以及前3行,按下单日期排序)
SELECT
placed,
total_price,
avg(total_price) over(ORDER BY placed rows BETWEEN 3 preceding AND current row)
from
single_order;
八 rows /range 的区别
- rows :范围考虑的是行 (row_number 函数)
- range :范围考虑的是具体的值 (rank() 函数)
九 CTE—WITH语句(巨重要top问题) CTE和parttion by order by 结合
应用:
cte === WITH语句
求每个分组中成绩 最高 的成绩
面试: 学生表 成绩表 学科表
求每个学科成绩最好的学员信息 /最好的前三名学员信息
栗子:
我们可以在 CTE 中使用PARTITION BY ORDER BY 将数据进一步分组,对每组进一步排序。
求价格前三名信息.
WITH ranking as
(
SELECT
country,
city,
RANK() over(partition by country ORDER BY rating desc) `rank`
FROM
store
)
SELECT
country,
city
FROM
ranking
where `rank` <=3;