1.percent_rank()函数
PERCENT_RANK()函数计算分区或结果集中行的百分位数排名
这PERCENT_RANK()是一个窗口函数,
用于计算分区或结果集中行的百分位数。
以下显示了PERCENT_RANK()函数的语法:
PERCENT_RANK()
OVER (
PARTITION BY expr,...
ORDER BY expr [ASC|DESC],...
)
PERCENT_RANK()函数返回一个从0到1的数字。
对于指定的行,PERCENT_RANK()计算行的等级减1,
除以评估的分区或查询结果集中的行数减1:
(rank - 1) / (total_rows - 1)
在此公式中,rank是指定行的等级,total_rows是要计算的行数。
PERCENT_RANK()对于分区或结果集中的第一行,
函数始终返回零。重复的列值将接收相同的PERCENT_RANK()值。
与其他窗口函数类似,PARTITION BY子句将行分配到分区中,
ORDER BY子句指定每个分区中行的逻辑顺序。
PERCENT_RANK()为每个有序分区独立计算函数。
两个PARTITION BY和ORDER BY子句都是可选项。
但是,它PERCENT_RANK()是一个顺序敏感函数,
因此,您应始终使用ORDER BY子句。(注意)
入门实战:(4. 未完成率较高的50%用户近三个月答卷情况 )
https://blog.csdn.net/weixin_44464850/article/details/124698259
让我们创建一个名为新表productLineSales
基础上的orders
,orderDetails
以及products
从表中示例数据库:
CREATE TABLE productLineSales
SELECT
productLine,
YEAR(orderDate) orderYear,
quantityOrdered * priceEach orderValue
FROM
orderDetails
INNER JOIN
orders USING (orderNumber)
INNER JOIN
products USING (productCode)
GROUP BY
productLine ,
YEAR(orderDate);
productLineSales
表存储销售数据的摘要,包括产品系列,订单年份和订单值。
+------------------+-----------+------------+
| productLine | orderYear | orderValue |
+------------------+-----------+------------+
| Vintage Cars | 2013 | 4080.00 |
| Classic Cars | 2013 | 5571.80 |
| Trucks and Buses | 2013 | 3284.28 |
| Trains | 2013 | 2770.95 |
| Ships | 2013 | 5072.71 |
| Planes | 2013 | 4825.44 |
| Motorcycles | 2013 | 2440.50 |
| Classic Cars | 2014 | 8124.98 |
| Vintage Cars | 2014 | 2819.28 |
| Trains | 2014 | 4646.88 |
| Ships | 2014 | 4301.15 |
| Planes | 2014 | 2857.35 |
| Motorcycles | 2014 | 2598.77 |
| Trucks and Buses | 2014 | 4615.64 |
| Motorcycles | 2015 | 4004.88 |
| Classic Cars | 2015 | 5971.35 |
| Vintage Cars | 2015 | 5346.50 |
| Trucks and Buses | 2015 | 6295.03 |
| Trains | 2015 | 1603.20 |
| Ships | 2015 | 3774.00 |
| Planes | 2015 | 4018.00 |
+------------------+-----------+------------+
21 rows in set (0.02 sec)
percent_rank()
在查询结果集上使用MySQL
以下查询按订单值查找每个产品系列的百分位数排名:
WITH t AS (
SELECT
productLine,
SUM(orderValue) orderValue
FROM
productLineSales
GROUP BY
productLine
)
SELECT
productLine,
orderValue,
ROUND(
PERCENT_RANK()
OVER (
ORDER BY orderValue
)
,2) percentile_rank
FROM
t;
在这个例子中:
- 首先,我们使用公用表表达式按产品线汇总订单值。
- 其次,我们用它
PERCENT_RANK()
来计算每种产品的订单价值的百分等级。此外,我们使用ROUND()
函数将值舍入为2十进制,以获得更好的表示。
这是输出:
+------------------+------------+-----------------+
| productLine | orderValue | percentile_rank |
+------------------+------------+-----------------+
| Trains | 9021.03 | 0.00 |
| Motorcycles | 9044.15 | 0.17 |
| Planes | 11700.79 | 0.33 |
| Vintage Cars | 12245.78 | 0.50 |
| Ships | 13147.86 | 0.67 |
| Trucks and Buses | 14194.95 | 0.83 |
| Classic Cars | 19668.13 | 1.00 |
+------------------+------------+-----------------+
7 rows in set (0.01 sec)
以下是输出中的一些分析:
- 订单价值
Trains
并不比任何其他产品线更好,后者用零表示。 Vintage Cars
表现优于50%的其他产品。Classic Cars
表现优于任何其他产品系列,因此其百分比等级为1或100%
percent_rank()
在分区上使用MySQL
以下语句按年度中的订单值返回产品系列的百分位数排名:
SELECT
productLine,
orderYear,
orderValue,
ROUND(
PERCENT_RANK()
OVER (
PARTITION BY orderYear
ORDER BY orderValue
),2) percentile_rank
FROM
productLineSales;
这是输出:
+------------------+-----------+------------+-----------------+
| productLine | orderYear | orderValue | percentile_rank |
+------------------+-----------+------------+-----------------+
| Motorcycles | 2013 | 2440.50 | 0.00 |
| Trains | 2013 | 2770.95 | 0.17 |
| Trucks and Buses | 2013 | 3284.28 | 0.33 |
| Vintage Cars | 2013 | 4080.00 | 0.50 |
| Planes | 2013 | 4825.44 | 0.67 |
| Ships | 2013 | 5072.71 | 0.83 |
| Classic Cars | 2013 | 5571.80 | 1.00 |
| Motorcycles | 2014 | 2598.77 | 0.00 |
| Vintage Cars | 2014 | 2819.28 | 0.17 |
| Planes | 2014 | 2857.35 | 0.33 |
| Ships | 2014 | 4301.15 | 0.50 |
| Trucks and Buses | 2014 | 4615.64 | 0.67 |
| Trains | 2014 | 4646.88 | 0.83 |
| Classic Cars | 2014 | 8124.98 | 1.00 |
| Trains | 2015 | 1603.20 | 0.00 |
| Ships | 2015 | 3774.00 | 0.17 |
| Motorcycles | 2015 | 4004.88 | 0.33 |
| Planes | 2015 | 4018.00 | 0.50 |
| Vintage Cars | 2015 | 5346.50 | 0.67 |
| Classic Cars | 2015 | 5971.35 | 0.83 |
| Trucks and Buses | 2015 | 6295.03 | 1.00 |
+------------------+-----------+------------+-----------------+
21 rows in set (0.01 sec)
在此示例中,我们按订单年划分产品线的订单值。将PERCENT_RANK()
然后被施加到每个分区。
例如,2013年Vintage Cars
表现优于其他产品线的50%,而在2014年,船舶表现优于其他产品的50%。
在本教程中,您学习了如何使用MySQL PERCENT_RANK()
函数计算分区或结果集中行的百分位数。
2.rank()函数
RANK()
函数为结果集的分区中的每一行分配一个排名。行的等级由一加上前面的等级数指定。
以下显示了RANK()
函数的语法:
RANK() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)
在这个语法中:
- 首先,
PARTITION BY
子句将结果集划分为分区。RANK()
功能在分区内执行,并在跨越分区边界时重新初始化。 - 其次,
ORDER BY
子句按一个或多个列或表达式对分区内的行进行排序。
与ROW_NUMBER()
函数不同,RANK()
函数并不总是返回连续的整数。
CREATE TABLE rankDemo (
val INT
);
INSERT INTO rankDemo(val)
VALUES(1),(2),(2),(3),(4),(4),(5);
SELECT *
FROM rankDemo
+------+
| val |
+------+
| 1 |
| 2 |
| 2 |
| 3 |
| 4 |
| 4 |
| 5 |
+------+
7 rows in set (0.01 sec)
以下语句使用RANK()
函数为rankDemo
表中结果集的每一行分配一个排名:
SELECT
val,
RANK() OVER (
ORDER BY val
) my_rank
FROM
rankDemo;
+------+---------+
| val | my_rank |
+------+---------+
| 1 | 1 |
| 2 | 2 |
| 2 | 2 |
| 3 | 4 |
| 4 | 5 |
| 4 | 5 |
| 5 | 7 |
+------+---------+
7 rows in set (0.02 sec)
如您所见,第二行和第三行具有相同的关系,因此它们获得相同的等级2。
第四行具有等级4,因为RANK()
功能跳过等级3。
MySQL RANK() 函数示例
让我们使用sales
我们在窗口函数教程中创建的表进行演示。
以下是sales
表:
+----------------+-------------+--------+
| sales_employee | fiscal_year | sale |
+----------------+-------------+--------+
| Alice | 2016 | 150.00 |
| Alice | 2017 | 100.00 |
| Alice | 2018 | 200.00 |
| Bob | 2016 | 100.00 |
| Bob | 2017 | 150.00 |
| Bob | 2018 | 200.00 |
| John | 2016 | 200.00 |
| John | 2017 | 150.00 |
| John | 2018 | 250.00 |
+----------------+-------------+--------+
9 rows in set (0.01 sec)
以下声明使用RANK()
功能按销售额按每年的销售额排名:
SELECT
sales_employee,
fiscal_year,
sale,
RANK( ) OVER (
PARTITION BY fiscal_year
ORDER BY sale DESC
) sales_rank
FROM
sales;
输出结果:
+----------------+-------------+--------+------------+
| sales_employee | fiscal_year | sale | sales_rank |
+----------------+-------------+--------+------------+
| John | 2016 | 200.00 | 1 |
| Alice | 2016 | 150.00 | 2 |
| Bob | 2016 | 100.00 | 3 |
| Bob | 2017 | 150.00 | 1 |
| John | 2017 | 150.00 | 1 |
| Alice | 2017 | 100.00 | 3 |
| John | 2018 | 250.00 | 1 |
| Alice | 2018 | 200.00 | 2 |
| Bob | 2018 | 200.00 | 2 |
+----------------+-------------+--------+------------+
9 rows in set (0.01 sec)
在这个例子中:
- 首先,
PARTITION BY
子句按财务年度将结果集分成多个分区。 - 然后,
ORDER BY
子句按销售额按降序对销售员工进行排序。
MySQL RANK函数与CTE示例
以下语句使用RANK()
函数查找每年最高的三个最高价值订单:
WITH order_values AS(
SELECT
orderNumber,
YEAR(orderDate) order_year,
quantityOrdered*priceEach AS order_value,
RANK() OVER (
PARTITION BY YEAR(orderDate)
ORDER BY quantityOrdered*priceEach DESC
) order_value_rank
FROM
orders
INNER JOIN orderDetails USING (orderNumber)
)
SELECT
*
FROM
order_values
WHERE
order_value_rank <=3;
这是输出:
+-------------+------------+-------------+------------------+
| orderNumber | order_year | order_value | order_value_rank |
+-------------+------------+-------------+------------------+
| 10196 | 2013 | 9571.08 | 1 |
| 10206 | 2013 | 9568.73 | 2 |
| 10201 | 2013 | 9394.28 | 3 |
| 10312 | 2014 | 10286.40 | 1 |
| 10348 | 2014 | 9974.40 | 2 |
| 10304 | 2014 | 9467.68 | 3 |
| 10403 | 2015 | 11503.14 | 1 |
| 10405 | 2015 | 11170.52 | 2 |
| 10407 | 2015 | 10723.60 | 3 |
+-------------+------------+-------------+------------------+
9 rows in set (0.04 sec)
在这个例子中:
- 首先,我们使用公用表表达式(CTE)来获取订单号,订单年份和排名。为了按照每年的订单价值对订单进行排名,我们使用按
RANK()
行年划分行的功能,并按降序对订单值进行排序。 - 然后,我们只选择排名小于或等于3的订单。
3.row_number() over()函数
MySQL ROW_NUMBER() 语法
MySQL ROW_NUMBER()
从8.0版开始引入了功能。这ROW_NUMBER()
是一个窗口函数或分析函数,它为从1开始应用的每一行分配一个序号。
请注意,如果你使用MySQL版本低于8.0,你可以效仿的一些功能ROW_NUMBER()
函数使用各种技术。
以下显示了ROW_NUMBER()
函数的语法:
ROW_NUMBER() OVER (<partition_definition> <order_definition>)
partition_definition
partition_definition
语法如下:
PARTITION BY <expression>,[{,<expression>}...]
PARTITION BY
子句将行分成更小的集合。表达式可以是将在GROUP BY
子句中使用的任何有效表达式。您可以使用以逗号分隔的多个表达式。
PARTITION BY
条款是可选项。如果省略它,则整个结果集被视为分区。但是,当您使用PARTITION BY
子句时,每个分区也可以被视为一个窗口。
order_definition
的order_definition
语法如下所示:
ORDER BY <expression> [ASC|DESC],[{,<expression>}...]
ORDER BY
子句的目的是设置行的顺序。此ORDER BY
子句独立ORDER BY
于查询的子句。
MySQL ROW_NUMBER() 函数示例
让我们使用示例数据库中的products
表进行演示:
1)为行分配序号
以下语句使用ROW_NUMBER()
函数为products
表中的每一行分配一个序号:
SELECT
ROW_NUMBER() OVER (
ORDER BY productName
) row_num,
productName,
msrp
FROM
products
ORDER BY
productName;
这是输出:
+---------+---------------------------------------------+--------+
| row_num | productName | msrp |
+---------+---------------------------------------------+--------+
| 1 | 18th century schooner | 122.89 |
| 2 | 18th Century Vintage Horse Carriage | 104.72 |
| 3 | 1900s Vintage Bi-Plane | 68.51 |
| 4 | 1900s Vintage Tri-Plane | 72.45 |
| 5 | 1903 Ford Model A | 136.59 |
| 6 | 1904 Buick Runabout | 87.77 |
| 7 | 1911 Ford Town Car | 60.54 |
| 8 | 1912 Ford Model T Delivery Wagon | 88.51 |
| 9 | 1913 Ford Model T Speedster | 101.31 |
| 10 | 1917 Grand Touring Sedan | 170.00 |
| 11 | 1917 Maxwell Touring Car | 99.21 |
| 12 | 1926 Ford Fire Engine | 60.77 |
| 13 | 1928 British Royal Navy Airplane | 109.42 |
...
2)找到每组的前N行
您可以将ROW_NUMBER()
功能用于查找每个组的前N行的查询,例如,每个销售渠道的前三名销售员工,每个类别的前五名高性能产品。
以下语句查找每个产品系列中库存最高的前三种产品:
WITH inventory
AS (SELECT
productLine,
productName,
quantityInStock,
ROW_NUMBER() OVER (
PARTITION BY productLine
ORDER BY quantityInStock DESC) row_num
FROM
products
)
SELECT
productLine,
productName,
quantityInStock
FROM
inventory
WHERE
row_num <= 3;
在这个例子中,
- 首先,我们使用
ROW_NUMER()
函数对每个产品系列中的所有产品的库存进行排序,方法是按产品线划分所有产品,并按库存数量按降序排序。结果,每个产品根据其库存数量分配一个等级。并为每个产品系列重置排名。 - 然后,我们只选择等级小于或等于3的产品。
以下显示输出:
+------------------+----------------------------------------+-----------------+
| productLine | productName | quantityInStock |
+------------------+----------------------------------------+-----------------+
| Classic Cars | 1995 Honda Civic | 9772 |
| Classic Cars | 2002 Chevy Corvette | 9446 |
| Classic Cars | 1976 Ford Gran Torino | 9127 |
| Motorcycles | 2002 Suzuki XREO | 9997 |
| Motorcycles | 1982 Ducati 996 R | 9241 |
| Motorcycles | 1969 Harley Davidson Ultimate Chopper | 7933 |
| Planes | America West Airlines B757-200 | 9653 |
| Planes | American Airlines: MD-11S | 8820 |
| Planes | ATA: B757-300 | 7106 |
| Ships | The USS Constitution Ship | 7083 |
| Ships | The Queen Mary | 5088 |
| Ships | 1999 Yamaha Speed Boat | 4259 |
| Trains | 1950's Chicago Surface Lines Streetcar | 8601 |
| Trains | Collectable Wooden Train | 6450 |
| Trains | 1962 City of Detroit Streetcar | 1645 |
| Trucks and Buses | 1964 Mercedes Tour Bus | 8258 |
| Trucks and Buses | 1957 Chevy Pickup | 6125 |
| Trucks and Buses | 1980鈥檚 GM Manhattan Express | 5099 |
| Vintage Cars | 1932 Model A Ford J-Coupe | 9354 |
| Vintage Cars | 1912 Ford Model T Delivery Wagon | 9173 |
| Vintage Cars | 1937 Lincoln Berline | 8693 |
+------------------+----------------------------------------+-----------------+
21 rows in set (0.03 sec)
3)删除重复的行
您可以使用ROW_NUMBER()
它将非唯一行转换为唯一行,然后删除重复行。请考虑以下示例。
首先,创建一个包含一些重复值的表:
DROP TABLE IF EXISTS rowNumberDemo;
CREATE TABLE rowNumberDemo (
id INT,
name VARCHAR(10) NOT NULL
);
INSERT INTO rowNumberDemo(id,name)
VALUES(1,'A'),
(2,'B'),
(3,'B'),
(4,'C'),
(5,'C'),
(6,'C'),
(7,'D');
其次,使用ROW_NUMBER()
函数将行划分为所有列的分区。对于每个唯一的行集,将重新开始行号。
SELECT
id,
name,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS row_num
FROM rowNumberDemo;
+------+------+---------+
| id | name | row_num |
+------+------+---------+
| 1 | A | 1 |
| 2 | B | 1 |
| 3 | B | 2 |
| 4 | C | 1 |
| 5 | C | 2 |
| 6 | C | 3 |
| 7 | D | 1 |
+------+------+---------+
7 rows in set (0.02 sec)
从输出中可以看出,唯一的行是行号等于1的行。
第三,您可以使用公用表表达式(CTE)返回要删除的重复行和delete语句:
WITH dups AS (SELECT
id,
name,
ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) AS row_num
FROM rowNumberDemo)
DELETE rowNumberDemo FROM rowNumberDemo INNER JOIN dups ON rowNumberDemo.id = dups.id
WHERE dups.row_num <> 1;
+------+------+
| id | name |
+------+------+
| 1 | A |
| 2 | B |
| 4 | C |
| 7 | D |
+------+------+
4 rows in set (0.01 sec)
请注意,MySQL不支持基于CTE的删除,因此,我们必须将原始表与CTE一起作为一种解决方法。
4)使用
ROW_NUMBER()
函数分页
因为ROW_NUMBER()
为结果集中的每一行指定一个唯一的数字,所以可以将其用于分页。
假设您需要显示每页包含10个产品的产品列表。要获取第二页的产品,请使用以下查询:
SELECT *
FROM
(SELECT productName,
msrp,
row_number()
OVER (order by msrp) AS row_num
FROM products) t
WHERE row_num BETWEEN 11 AND 20;
这是输出:
+------------------------------------------+-------+---------+
| productName | msrp | row_num |
+------------------------------------------+-------+---------+
| 1936 Mercedes-Benz 500K Special Roadster | 53.91 | 11 |
| 1954 Greyhound Scenicruiser | 54.11 | 12 |
| Pont Yacht | 54.60 | 13 |
| 1970 Dodge Coronet | 57.80 | 14 |
| 1962 City of Detroit Streetcar | 58.58 | 15 |
| 1911 Ford Town Car | 60.54 | 16 |
| 1936 Harley Davidson El Knucklehead | 60.57 | 17 |
| 1926 Ford Fire Engine | 60.77 | 18 |
| 1971 Alpine Renault 1600s | 61.23 | 19 |
| 1950's Chicago Surface Lines Streetcar | 62.14 | 20 |
+------------------------------------------+-------+---------+
10 rows in set (0.02 sec)
在本教程中,您学习了如何使用MySQL ROW_NUMBER()
函数为结果集中的每一行生成序列号。
入门实战:( 3.找出每个学校GPA最低的同学)
https://blog.csdn.net/weixin_44464850/article/details/124386872
4. dense_rank()函数
DENSE_RANK()
是一个窗口函数,它为分区或结果集中的每一行分配排名,而排名值没有间隙。
行的等级从行前的不同等级值的数量增加1。
DENSE_RANK()
函数的语法如下:
DENSE_RANK() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)
在这个语法中:
- 首先,
PARTITION BY
子句将FROM
子句生成的结果集划分为分区。DENSE_RANK()
函数应用于每个分区。 - 其次,
ORDER BY
子句指定DENSE_RANK()
函数操作的每个分区中的行顺序。
如果分区具有两个或更多具有相同排名值的行,则将为这些行中的每一行分配相同的排名。
与RANK()
函数不同,DENSE_RANK()
函数始终返回连续的排名值。
假设我们有一个t
包含一些样本数据的表,如下所示:
CREATE TABLE rankDemo (
val INT
);
INSERT INTO rankDemo(val)
VALUES(1),(2),(2),(3),(4),(4),(5);
SELECT
*
FROM
rankDemo;
+------+
| val |
+------+
| 1 |
| 2 |
| 2 |
| 3 |
| 4 |
| 4 |
| 5 |
+------+
7 rows in set (0.02 sec)
以下语句使用DENSE_RANK()
函数为每行分配排名:
SELECT
val,
DENSE_RANK() OVER (
ORDER BY val
) my_rank
FROM
rankDemo;
这是输出:
+------+---------+
| val | my_rank |
+------+---------+
| 1 | 1 |
| 2 | 2 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 4 | 4 |
| 5 | 5 |
+------+---------+
7 rows in set (0.03 sec)
MySQL DENSE_RANK() 函数示例
我们将使用窗口函数教程sales
中创建的表进行演示。
mysql> select * from sales;
+----------------+-------------+--------+
| sales_employee | fiscal_year | sale |
+----------------+-------------+--------+
| Alice | 2016 | 150.00 |
| Alice | 2017 | 100.00 |
| Alice | 2018 | 200.00 |
| Bob | 2016 | 100.00 |
| Bob | 2017 | 150.00 |
| Bob | 2018 | 200.00 |
| John | 2016 | 200.00 |
| John | 2017 | 150.00 |
| John | 2018 | 250.00 |
+----------------+-------------+--------+
9 rows in set (0.00 sec)
以下声明使用DENSE_RANK()
功能按销售额对销售员工进行排名。
SELECT
sales_employee,
fiscal_year,
sale,
DENSE_RANK() OVER (PARTITION BY
fiscal_year
ORDER BY
sale DESC
) sales_rank
FROM
sales;
输出如下:
+----------------+-------------+--------+------------+
| sales_employee | fiscal_year | sale | sales_rank |
+----------------+-------------+--------+------------+
| John | 2016 | 200.00 | 1 |
| Alice | 2016 | 150.00 | 2 |
| Bob | 2016 | 100.00 | 3 |
| Bob | 2017 | 150.00 | 1 |
| John | 2017 | 150.00 | 1 |
| Alice | 2017 | 100.00 | 2 |
| John | 2018 | 250.00 | 1 |
| Alice | 2018 | 200.00 | 2 |
| Bob | 2018 | 200.00 | 2 |
+----------------+-------------+--------+------------+
9 rows in set (0.01 sec)
在这个例子中:
- 首先,
PARTITION BY
子句使用会计年度将结果集划分为分区。 - 其次,
ORDER BY
条款按销售额的降序指定了销售员工的顺序。 - 第三,
DENSE_RANK()
函数应用于具有ORDER BY
子句指定的行顺序的每个分区。
在本教程中,您学习了如何使用MySQL DENSE_RANK()
函数对结果集的每个分区中的行进行排名。
入门实战:(3.近三个月未完成试卷数为0的用户完成情况)
https://blog.csdn.net/weixin_44464850/article/details/124698259
了解更多:MySQL官网
https://www.begtut.com/mysql/mysql-lead-function.html