MySQL中怎么实现开窗函数

MySQL中怎么实现开窗函数

MYSQL不像SQL SERVER那样能够直接使用rank over()，sum over()，等的开窗函数，那么怎么运用现成的语句实现这个函数呢，我们一起做下

准备工作

初始数据

// An highlighted block
CREATE TABLE test_rownum (
	ID int,
	Ke CHAR(1),
	val INT
);

INSERT INTO test_rownum
SELECT 1, 'A', 1 UNION ALL
SELECT 2, 'A', 2 UNION ALL
SELECT 3, 'A', 3 UNION ALL
SELECT 4, 'B', 1 UNION ALL
SELECT 5, 'B', 2 UNION ALL
SELECT 6, 'B', 1 UNION ALL
SELECT 7, 'C', 3 UNION ALL
SELECT 8, 'C', 2 UNION ALL
SELECT 9, 'C', 2;


--rownum 用于显示序号
--ID 字段用于 标记 原有的序号位置.

实现row_number，rank，dense_rank

SELECT
CASE
	WHEN @ke != ke THEN @rownum:= 1
ELSE @rownum:= @rownum + 1
END AS ROW_NUMBER,
CASE
	WHEN @ke != ke THEN @rank:= 1
	WHEN @val = val THEN @rank
ELSE @rank:= @rownum
END AS RANK,
CASE
	WHEN @ke != ke THEN @dense_rank:= 1
	WHEN @val = val THEN @dense_rank
ELSE @dense_rank:= @dense_rank + 1
END AS DENSE_RANK,
id,
@ke := ke AS ke,
@val := val AS val
FROM
	(SELECT @ke:='') k,
	(SELECT @val:=0) v,
	(SELECT @rownum:=0) r,
	(SELECT @rank:=0) r2,
	(SELECT @dense_rank:=0) d,
	test_rownum main
ORDER BY
ke, val;

实现sum() over

SELECT
CASE
	WHEN @ke != ke THEN @media := val 
ELSE @media := @media + val --media为中间变量储存累计值
END AS sum_over,
@ke := ke,
@val := val 
FROM
	test_rownum a,
	( SELECT @ke  := "", @val  := 0, @media = 0 ) b 
ORDER BY
	ke,val

多分组sum() over

按照ke和val分组求累计和

SELECT
CASE
	WHEN @ke != ke THEN @media := id 
	when @val !=val THEN @media := id 
ELSE @media := @media + id 
END AS sum_over,
@ke := ke,
@id := id,
@val:= val
val
FROM
	test_rownum a
ORDER BY
	ke,val,id

尾记

大量实践后发现在语句中，其实不用在from后对变量进行定义赋值，直接用也不会报错，或许这正是MySQL灵活之处

参考

[转载]MySQL 下 ROW_NUMBER / DENSE_RANK / RANK 的实现