mysql5.x版本如何实现窗口函数效果

前言：窗口函数是mysql8.0新增功能，但考虑业务需求、成本评估及风险控制等因素，仍有许多企业在使用mysql5.x版本，这就导致有些新增功能无法在旧版本中使用。但由于业务需求，又必须要实现类似效果（如窗口函数），这种情况该怎么办？下面我们来详细介绍。

一、窗口函数
二、数据准备
三、如何在不支持窗口函数的版本下实现窗口函数效果

一、窗口函数

1.1 简介

窗口函数是SQL和数据分析中一类强大的函数，它能够在不减少行数的情况下，对数据的特定"窗口"（一组相关行）执行计算。与聚合函数不同，窗口函数不会将多行合并为一行，而是为每一行返回一个计算结果。

1.2 核心概念

窗口（Window）：定义函数计算的数据范围
分区（PARTITION BY）：将数据分成多个组，在每个组内独立计算
排序（ORDER BY）：确定窗口内行的顺序
框架（FRAME）：定义窗口的具体范围（如当前行的前N行到后N行）

1.3 常用窗口函数类型

排序函数
ROW_NUMBER()：为每行分配唯一序号（1,2,3...）
RANK()：相同值有相同排名，后续排名跳过（1,2,2,4...）
DENSE_RANK()：相同值有相同排名，后续排名不跳过（1,2,2,3...）
取值函数
FIRST_VALUE()/LAST_VALUE()：获取窗口第一行/最后一行的值
LAG()/LEAD()：访问当前行之前/之后的行
NTH_VALUE()：获取窗口中的第N个值
聚合函数作为窗口函数
SUM()/AVG()/COUNT()/MIN()/MAX()等聚合函数也可以作为窗口函数使用

二、数据准备

DROP TABLE IF EXISTS `ecommerce_orders`;
CREATE TABLE `ecommerce_orders` (
  `order_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `payment_time` timestamp NULL DEFAULT NULL,
  `product_name` varchar(50) DEFAULT NULL,
  `product_category` varchar(30) DEFAULT NULL,
  `payment_amount` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `ecommerce_orders` VALUES ('ORD20230001', '2023-01-05 09:15:22', '无线蓝牙耳机', '电子产品', 299.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230002', '2023-01-05 11:30:45', '纯棉T恤', '服装', 89.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230003', '2023-01-06 14:22:10', '智能手表', '电子产品', 799.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230004', '2023-01-06 14:25:33', '运动跑鞋', '鞋类', 459.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230005', '2023-01-07 10:05:17', '不锈钢保温杯', '家居用品', 129.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230006', '2023-01-08 16:48:29', '有机大米5kg', '食品', 68.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230007', '2023-01-08 16:50:12', '无线蓝牙耳机', '电子产品', 299.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230008', '2023-02-09 09:33:44', '纯棉T恤', '服装', 89.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230009', '2023-02-10 13:15:30', '智能手表', '电子产品', 799.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230010', '2023-02-10 13:18:05', '有机大米5kg', '食品', 68.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230011', '2023-02-11 15:22:18', '便携充电宝', '电子产品', 159.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230012', '2023-01-12 10:05:42', '男士牛仔裤', '服装', 199.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230013', '2023-01-12 10:08:15', '空气炸锅', '家用电器', 429.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230014', '2023-01-13 14:35:20', '有机大米5kg', '食品', 68.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230015', '2023-02-14 09:12:37', '无线蓝牙耳机', '电子产品', 299.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230016', '2023-01-14 11:45:08', '女士羊毛围巾', '服装', 129.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230017', '2023-01-15 16:30:55', '智能手表', '电子产品', 799.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230018', '2023-02-16 08:20:14', '运动跑鞋', '鞋类', 459.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230019', '2023-01-16 13:15:33', '不锈钢保温杯', '家居用品', 129.00);
INSERT INTO `ecommerce_orders` VALUES ('ORD20230020', '2023-02-17 17:05:47', '便携充电宝', '电子产品', 159.00);

三、如何在不支持窗口函数的版本下实现窗口函数效果

3.1 排序函数（以rank()为例）

需求1：查找每个类目中销量最好的产品，并展示出该商品对应销量

3.1.1 使用窗口函数

-- 使用窗口函数，此时注意，若有销量相同情况，根据业务实际场景判断具体使用哪个排名函数，这里以不跳过为例展示
select
  产品名称
  ,订单量 
from (
  select
    product_category 产品类目
    ,product_name 产品名称
    ,count( order_id ) 订单量
    ,dense_rank() over ( PARTITION BY product_category ORDER BY count( order_id ) DESC ) rn 
  from
    ecommerce_orders 
  group by product_category,product_name 
) a 
where rn = 1
;

结果：

3.1.2 不使用窗口函数

-- 不使用窗口函数
-- 思路：我们可以先汇总商品维度销量，然后再计算每个类目的最高销量
with product_sale as (
  select
  	product_category 产品类目
  	,product_name 产品名称
  	,count(order_id) 订单量
  from ecommerce_orders
  group by product_category,product_name
)
select
  a.产品名称
  ,a.订单量
from product_sale a
inner join (
  select 
    产品类目
    ,max(订单量) 最高销量
  from product_sale
  group by 产品类目
) b on a.产品类目 = b.产品类目 and a.订单量 = b.最高销量
;

结果：

3.2 聚合函数（以sum()为例）

需求2：计算1月各产品的销量渗透、GMV渗透 (产品销量/该产品所在类目销量)

3.2.1 使用窗口函数

-- 使用窗口函数
select 
  产品类目
  ,产品名称
  ,订单量
  ,订单量/sum(订单量) over (partition by 产品类目) 销量渗透
  ,销售额
  ,销售额/sum(销售额) over (partition by 产品类目) GMV渗透
from (
  select 
    product_category 产品类目
    ,product_name 产品名称
    ,count(order_id) 订单量
    ,sum(payment_amount) 销售额
  from ecommerce_orders
  where date_format(payment_time,'%Y-%m') = '2023-01'
  group by product_category,product_name
) a
;

结果：

3.2.2 不使用窗口函数

-- 不使用窗口函数
-- 思路：
--   既然没有窗口函数，我们可以从计算逻辑上考虑。
--   渗透指标口径为：产品维度/类目维度，那么可以考虑分别聚合产品维度和类目维度，然后进行join操作
select 
  a.产品类目
  ,a.产品名称
  ,a.订单量
  ,a.订单量/b.订单量 销量渗透
  ,a.销售额
  ,a.销售额/b.销售额 GMV渗透
from (
  -- 产品维度
  select 
    product_category 产品类目
    ,product_name 产品名称
    ,count(order_id) 订单量
    ,sum(payment_amount) 销售额
  from ecommerce_orders
  where date_format(payment_time,'%Y-%m') = '2023-01'
  group by product_category,product_name
) a
left join (
-- 类目维度
  select 
    product_category 产品类目
    ,count(order_id) 订单量
    ,sum(payment_amount) 销售额
  from ecommerce_orders
  where date_format(payment_time,'%Y-%m') = '2023-01'
  group by product_category
) b on a.产品类目 = b.产品类目
;

结果：

3.3 取值函数（以lag()为例）

需求3：计算每个类目月销量环比

3.3.1 使用窗口函数

-- 使用窗口函数
select 
  销售月
  ,产品类目
  ,本月销量
  ,上月销量
  ,本月销量/上月销量-1 销量环比
from (
  select 
    date_format(payment_time,'%Y-%m') 销售月
    ,product_category 产品类目
    ,count(order_id) 本月销量
    ,lag(count(order_id)) over (partition by product_category order by date_format(payment_time,'%Y-%m')) 上月销量
  from ecommerce_orders
  group by 销售月,产品类目
) a
;

结果：

3.3.2 不使用窗口函数

-- 不使用窗口函数
-- 思路：
--   我们要计算环比，其实最重要的一步就是拿到上个周期的数据对应到本周期数据行中，最直接的可以考虑自连接。
--   要拿到上月数据和本月数据放在一行，可以考虑将本月日期+1个月与原本日期关联，即可实现类似lag()的效果。
with sale_mm as (
  select
    date_format(payment_time,'%Y-%m-01') 销售月
    ,product_category 产品类目
    ,count(order_id) 本月销量
  from ecommerce_orders
  group by 销售月,产品类目
)

select 
  a.销售月
  ,a.产品类目
  ,b.本月销量
  ,a.本月销量 上月销量
  ,b.本月销量/a.本月销量-1 销量环比
from sale_mm a
left join sale_mm b on date_add(a.销售月,interval 1 month) = b.销售月 and a.产品类目 = b.产品类目
;

结果：

posted @ 2025-04-27 22:18 夏悠然h 阅读(194) 评论(0) 收藏举报

刷新页面返回顶部

isnotyou