sql统计最大连续增加条数

趋势特征是业务人员比较青睐的特征，其中涉及到连续变化期数，例如连续增大、减少期数等。这里写一下可以实现的思路。

1.使用变量

这个方法在我常用的sql平台中只有mysql支持。pgsql和hive无法实现。
在查询里声明select @var:=0,通过逻辑语句将起始位置置1，连续行处递增，和结束位置置0。得到连续变化的次数。

2.增长序列字符串化，统计子串长度

此方法在支持行列转换语法的数据库中都能实现。核心思想是构建增长的序列，然后拆分，统计最长串的长度。
大致的步骤有：
1.使用开窗函数判断每一行是否是增长，标记1和0，作为tag列
2.把tag列转压缩转换成行内字段，例如10011101101。
3.使用正则切分，从0处切分，得到1,111,11,1，类似4个元素的数组的数据类型。
4.将数组再展成行，统计每个元素字符串的长度。取max(长度)即可。

该方法涉及到行列转换，步骤也算很多，在大数据量下效率预计比较低，代码也不算易读。

3.使用开窗函数

返璞归真的做法，这个方法的适用性、可读性、性能、扩展性都为最佳。只是要想明白两个事情。
1.构建一个辅助的排序列，用期数减去排序列得到连续段。
2.如何使用开窗函数构建辅助排序列。
举个例子：

期	金额	是否增长	辅助排序	段
1	3
2	2	0
3	4	1	1	2
4	6	1	2	2
5	8	1	3	2
6	1	0
7	2	1	4	3

假如我只有“期”和“金额”两列，现在需要求金额连续增长的期数。
第一步：使用开窗函数lag，判断是否增长，构建是否增长列。
第二步：使用开窗函数，要求对是否增长列为1的期进行排序。构建辅助排序列
第三步：使用期列的数值减去辅助排序列的数值，得到段列。
第四步：求段字段中相同值最大的数量。

其中第二步是操作难点，第三步是个思路上的难点。
如何实现第二步对给定条件的行进行row_number排序？操作方法如下：

case when 是否增长 = 1 then 
	row_number()over(partition by 
				case when 是否增长 = 1 then 1 else 0 end 
			order by 期)
end

这里的原理是，通过在partition by 后使用case when end语句构建出是否符合增长条件的组，然后在两个组中分别进行期数的排序。再在外面使用case when end语句选择符合增长条件的组内排序。

总结

三种方法，推荐方法三。优势有：

• 适用性：对比方法1，支持开窗函数的数据库都可以使用
• 扩展性：对比方法2，可以对一张表中的多个字段求取连续变化期数，只需要构建各自的辅助列。可以在一段sql语句中完成
• 性能和可读性：对比方法2，不需要进行行列转换，只需通过开窗逻辑运算构建辅助列即可完成。没有复杂的sql嵌套结构，因此可读性佳。