分区函数partition by的基本用法【转载】

未分类 1周前程序员胖胖胖虎阿

17 0 0

本篇文章将深入探讨MySQL中的分区函数PARTITION BY的使用方法。让我们直接进入正题。

注意： 请确保您的MySQL版本支持窗口函数（MySQL 8.0及以上版本支持）。如果您的MySQL版本低于8.0，则无法使用窗口函数。

一、建表语句

-- 创建商品表
CREATE TABLE `commodity` (
  `id` int NOT NULL COMMENT '主键',
  `position` varchar(50) DEFAULT NULL COMMENT '位置(商品放置的货架)',
  `type` varchar(50) DEFAULT NULL COMMENT '类型',
  `price` decimal(10,2) DEFAULT NULL COMMENT '价格',
  PRIMARY KEY (`id`)
) COMMENT='商品表';

-- 初始化数据
INSERT INTO commodity VALUES (1, '1-001', '苹果', 8.00);
INSERT INTO commodity VALUES (2, '2-002', '苹果', 10.00);
INSERT INTO commodity VALUES (3, '3-003', '苹果', 12.00);
INSERT INTO commodity VALUES (6, '1-001', '橘子', 5.00);
INSERT INTO commodity VALUES (7, '1-001', '橙子', 6.00);
INSERT INTO commodity VALUES (8, '3-003', '橙子', 8.00);
INSERT INTO commodity VALUES (10, '2-002', '菠萝', 10.00);
INSERT INTO commodity VALUES (9, '2-002', '香蕉', 5.00);
INSERT INTO commodity VALUES (4, '1-001', '苹果', 12.00);
INSERT INTO commodity VALUES (5, '1-001', '香蕉', 5.00);

二、窗口函数

1、`PARTITION BY`窗口函数与`GROUP BY`分组的区别

PARTITION BY关键字是分析函数的一部分，与聚合函数（如GROUP BY）的主要区别在于，PARTITION BY可以返回一个分组中的多条记录，而聚合函数通常只返回一条反映统计值的记录。

PARTITION BY用于对结果集进行分组，如果未指定，则整个结果集被视为一个分组。

与GROUP BY不同，PARTITION BY返回的是分组中的每条数据，并且可以对分组数据进行排序操作。GROUP BY只能返回聚合后的组的数据统计值的记录。

PARTITION BY与GROUP BY的主要区别在于，前者在保留全部数据的基础上，只对其中某些字段进行分组排序（类似于Excel中的操作），而GROUP BY则只保留参与分组的字段和聚合函数的结果；简而言之，窗口函数对部分数据进行排序、计算等操作，GROUP BY对一组值进行聚合，即窗口函数是每一行都会保留，GROUP BY是从多行浓缩为少数行。

2、窗口函数基本语法

<窗口函数> over (PARTITION BY<用于分组的列名> ORDER BY <用于排序的列名>)

3、窗口函数

专用窗口函数：RANK(), DENSE_RANK(), ROW_NUMBER()
聚合函数：SUM(), MAX(), MIN(), COUNT(), AVG() 等

三、窗口函数的使用

1、OVER函数的写法

-- 先对type中相同的进行分区，在type中相同的情况下对price进行排序
OVER(PARTITION BY type ORDER BY price DESC)

2、专用窗口函数RANK()、ROW_NUMBER()以及DENSE_RANK()

SELECT *, RANK() OVER(PARTITION BY type ORDER BY price DESC) AS mm FROM commodity;
SELECT *, ROW_NUMBER() OVER(PARTITION BY type ORDER BY price DESC) AS mm FROM commodity;
SELECT *, DENSE_RANK() OVER(PARTITION BY type ORDER BY price DESC) AS mm FROM commodity;