Mysql8.0以下版本是不支持窗口函数的,下面简单演示了mysql如何通过@变量实现rank和sum over窗口函数。各位读者可以以此举一反三。
数据准备:
CREATE TABLE sc (
SId VARCHAR(10),
CId VARCHAR(10),
score INT
);
INSERT INTO sc (SId, CId, score)
VALUES
('01', '01', 80),
('01', '02', 90),
('01', '03', 99),
('02', '01', 70),
('02', '02', 60),
('02', '03', 80),
('03', '01', 80),
('03', '02', 80),
('03', '03', 80),
('04', '01', 50),
('04', '02', 30),
('04', '03', 20),
('05', '01', 76),
('05', '03', 77);
CREATE TABLE t_access_times (
USER VARCHAR(10),
DATE DATE,
COUNT INT
);
INSERT INTO t_access_times (USER, DATE, COUNT)
VALUES
('A', '2015-01-02', 5),
('A', '2015-01-03', 15),
('B', '2015-01-01', 5),
('A', '2015-01-04', 8),
('B', '2015-01-05', 25),
('A', '2015-01-06', 5),
('A', '2015-02-02', 4),
('A', '2015-02-06', 6),
('B', '2015-02-06', 10),
('B', '2015-02-07', 5);
实现rank
不分组全局排名
SELECT s.*,@r :=@r + 1 AS rank
FROM sc s,(SELECT @r := 0) r
ORDER BY score DESC;
按照班级分组的每个学生成绩排名
SELECT a.cid,a.sid,a.score,a.rank
FROM
(
SELECT s.*,
IF(@p=cid,@r:=@r+1,@r:=1) AS rank,
@p:=cid
FROM sc s,(SELECT @p:=0,@r:=0)r
ORDER BY cid,score DESC
)a;
更多关于rank的示例可以参考:
分组排序求前三?TopN问题?一文教你MySQL各类排序操作
引用自:https://blog.csdn.net/weixin_41261833/article/details/103511400
实现sum over
全表累积求和
SELECT t.*,@c :=@c + COUNT AS accumulate
FROM t_access_times t,(SELECT @c := 0) c
ORDER BY USER,DATE;
分组的累积求和
SELECT a.user,a.date,a.count,a.accumulate
FROM
(
SELECT t.*,
IF(@p=USER,@r:=@r+COUNT,@r:=COUNT) AS accumulate,
@p:=USER
FROM t_access_times t,(SELECT @p:=0,@c:=0)c
ORDER BY USER,DATE
)a;