他们经常问MySQL中是否有分析（窗口）函数的类似物。
注意事项 在撰写本文时，还没有此类类似物，但是从解析使用MySQL变量的原始方法的角度来看，本文仍具有学术兴趣。为了替换分析功能，经常使用自连接查询，复杂的子查询等。 这些解决方案中的大多数在性能方面均无效。
同样在MySQL中没有递归。 但是，某些通常由解析函数或递归解决的任务可以由MySQL工具处理。
这些工具之一是其他DBMS在SQL查询中使用变量的独特机制。 我们可以在查询中声明一个变量，更改其值，然后在SELECT中将其替换为输出。 此外，可以在自定义排序中设置处理请求中行的顺序以及结果，将值分配给变量的顺序！
警告 本文假定SELECT子句中的表达式处理是从左到右执行的，但是，MySQL文档中没有官方确认此处理顺序。 更改服务器版本时必须牢记这一点。 为了确保一致性，可以使用伪CASE或IF语句。
递归模拟
考虑一个生成斐波那契数列的简单示例（在斐波那契数列中，每个项等于前两个项的和，而前两个项等于一个）：
SELECT IF(X=1, Fn_1, Fn_2) F FROM( SELECT @I := @I + @J Fn_1, @J := @I + @J Fn_2 FROM (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)a, (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)b, (SELECT @I := 1, @J := 1)IJ )T, (SELECT 1 X UNION ALL SELECT 2)X;
该查询生成18个斐波那契数，不计算前两个数：
2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181,6765
现在让我们看看它是如何工作的。
在第5）6）行中，生成了9条记录。 这里没什么异常。
在第7行中，我们声明两个变量@ I，@ J并将它们分配给1。
在第3行上，发生了以下情况：首先，为变量@I分配了两个变量的和。 然后考虑到@I的值已经更改的事实，将其分配给变量@J。
换句话说，SELECT中的计算从左到右执行-另请参见本文开头的备注。
此外，变量的更改是在我们的9条记录中的每条记录中进行的，即 在处理每行新行时，变量@I和@J将包含通过处理前一行计算得出的值。
为了在其他DBMS的帮助下解决相同的问题，我们将不得不编写一个
递归查询！注意事项：必须在单独的子查询中声明变量（第7行），如果我们在SELECT子句中声明了变量，则很可能只对变量进行1次评估（尽管具体行为取决于服务器的版本）。 变量的类型由其初始化值决定。 此类型可以动态更改。 如果将变量设置为NULL，则其类型将为BLOB。如上所述，在SELECT中处理行的顺序取决于自定义排序。 给定顺序的行编号的简单示例：
SELECT val, @I:=@I+1 Num FROM (SELECT 30 val UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50)a, (SELECT @I := 0)I ORDER BY val;
Val Num 10 1 20 2 30 3 50 4
解析函数的类似物
变量也可以用来代替解析函数。 以下是一些示例。 为简单起见，我们假定所有字段都不为空，并且对一个字段进行排序和分区（PARTITION BY）。 使用NULL值和更复杂的排序将使示例更加繁琐，但本质不会改变。
例如，创建TestTable表：
CREATE TABLE TestTable( group_id INT NOT NULL, order_id INT UNIQUE NOT NULL, value INT NOT NULL );
在哪里
group_id-组标识符（分析功能窗口的类似物）；
order_id-用于排序的唯一字段；
值是一些数值。
用测试数据填写表格：
INSERT TestTable(order_id, group_id, value) SELECT * FROM( SELECT 1 order_id, 1 group_id, 1 value UNION ALL SELECT 2, 1, 2 UNION ALL SELECT 3, 1, 2 UNION ALL SELECT 4, 2, 1 UNION ALL SELECT 5, 2, 2 UNION ALL SELECT 6, 2, 3 UNION ALL SELECT 7, 3, 1 UNION ALL SELECT 8, 3, 2 UNION ALL SELECT 9, 4, 1 UNION ALL SELECT 11, 3, 2 )T;
替换某些分析功能的示例。
1）ROW_NUMBER（）个以上（ORDER BY order_id）
SELECT T.*, @I:=@I+1 RowNum FROM TestTable T,(SELECT @I:=0)I ORDER BY order_id;
group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 4
2 5 2 5
2 6 3 6
3 7 1 7
3 8 2 8
4 9 1 9
3 11 2 10
2）ROW_NUMBER（）个（PARTITION BY group_id ORDER BY order_id）
SELECT group_id, order_id, value, RowNum FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNum, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;
group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 1
2 5 2 2
2 6 3 3
3 7 1 1
3 8 2 2
3 11 2 3
4 9 1 1
3）总和（值）OVER（PARTITION BY group_id ORDER BY order_id）
SELECT group_id, order_id, value, RunningTotal FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+value, @I:=value) RunningTotal, @last_group_id := group_id FROM TestTable T, (SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;
group_id order_id value RunningTotal
1 1 1 1
1 2 2 3
1 3 2 5
2 4 1 1
2 5 2 3
2 6 3 6
3 7 1 1
3 8 2 3
3 11 2 5
4 9 1 1
4）滞后（值）OVER（PARTITION BY group_id ORDER BY order_id）
SELECT group_id, order_id, value, LAG FROM( SELECT T.*, IF(@last_group_id = group_id, @last_value, NULL) LAG, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL
对于LEAD，一切都相同，只需要将排序更改为ORDER BY group_id，order_id DESC
对于函数COUNT，MIN，MAX，一切都有些复杂，因为在我们分析组（窗口）中的所有行之前，我们将无法找出函数的值。 例如，MS SQL为此目的“假脱机”窗口（暂时将窗口行放在隐藏的缓冲区表中以再次访问它们），而在MySQL中则没有这种可能性。 但是我们可以为每个窗口计算给定排序的最后一行中函数的值（即在分析整个窗口之后），然后以相反的顺序对窗口中的行进行排序，将计算出的值放到整个窗口中。
因此，我们需要两种排序。 为了使最终排序与上面的示例相同，我们首先按字段group_id ASC，order_id DESC进行排序，然后按字段group_id ASC，order_id ASC进行排序。
5）COUNT（*）个以上（按group_id划分）
在第一类中，我们仅对条目编号。 在第二个中，我们将最大数量分配给窗口的所有行，这将对应于窗口中的行数。
SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxRowNum, @MaxRowNum := RowNumDesc) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNumDesc, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxRowNum:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1
函数MAX和MIN通过类推计算。 我将仅以MAX为例：
6）MAX（值）OVER（PARTITION BY group_id）
SELECT group_id, order_id, value, MaxVal FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxVal, @MaxVal := MaxVal) MaxVal, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, GREATEST(@MaxVal, value), @MaxVal:=value) MaxVal, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1
7）COUNT（DISTINCT值）OVER（PARTITION BY group_id）
有趣的事情在MS SQL Server中不可用，但是可以通过从RANK中获取MAX来使用子查询来计算。 我们将在这里做同样的事情。 在第一类中，我们计算RANK（）OVER（PARTITION BY group_id ORDER BY值DESC），然后在第二类中，将最大值应用于每个窗口中的所有行：
SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @Rank, @Rank := Rank) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, IF(@last_value = value, @Rank, @Rank:=@Rank+1) , @Rank:=1) Rank, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL, @Rank:=0)I ORDER BY group_id, value DESC, order_id DESC )T,(SELECT @last_group_id:=NULL, @Rank:=NULL)I ORDER BY group_id, value, order_id )T;
group_id order_id value Cnt
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1
性能表现
首先，我们比较使用自连接和变量的查询中行编号的性能。
1）自连接的经典方式
SELECT COUNT(*)N, T1.* FROM TestTable T1 JOIN TestTable T2 ON T1.order_id >= T2.order_id GROUP BY T1.order_id;
表TestTable中的10000条记录会产生什么：
持续时间/获取
16.084秒/0.016秒
2）使用变量：
SELECT @N:=@N+1 N, T1.* FROM TestTable T1, (SELECT @N := 0)M ORDER BY T1.order_id;
它产生：
持续时间/获取
0.016秒/0.015秒
结果不言而喻。 但是，必须理解，使用变量计算的值并不是在过滤条件中最佳使用的。 尽管最终只需要一小部分，但所有行都将进行排序和计算。
让我们通过此类任务的示例来更详细地考虑：
为每个group_id值打印TestTable表的前2行，并按order_id排序。这是在支持分析功能的DBMS中如何解决此任务的方法：
SELECT group_id, order_id, value FROM( SELECT *, ROW_NUMBER()OVER(PARTITION BY group_id ORDER BY order_id) RowNum FROM TestTable )T WHERE RowNum <= 2;
但是，MySQL优化器对计算RowNum字段所依据的规则一无所知。 他将必须为所有行编号，然后才选择必要的行。
现在想象一下，我们有100万条记录和20个唯一的group_id值。 即 选择40行，MySQL将计算一百万行的RowNum值！ 对于MySQL中的单个查询，没有完美的解决方案。 但是您首先可以获取唯一的group_id值的列表，例如，如下所示：
SELECT DISTINCT group_id FROM TestTable;
然后，使用任何其他编程语言，生成以下形式的查询：
SELECT * FROM TestTable WHERE group_id=1 ORDER BY order_id LIMIT 2 UNION ALL SELECT * FROM TestTable WHERE group_id=2 ORDER BY order_id LIMIT 2 UNION ALL … SELECT * FROM TestTable WHERE group_id=20 ORDER BY order_id LIMIT 2;
20个简单的查询比计算一百万行的RowNum更快。