今天遇到一个业务需求，要分析代发工资客户连续代发的最长次数，连续代发即连续每个月都有代发。
假设代发工资的表如下
create dfjl (
work_dt varchar(8),
id varchar(20),
amount decimal(20,2)
)
其中work_dt表示代发日期，amount表示代发金额,id为代发客户证件号。
首先对dfjl表进行处理，按每个id每月进行汇总，保证每个id每月最多有一条记录。
select
id,
date(left(work_dt,4)||'-'|| substr(work_dt,5,2) || '-01') as work_dt_new,
sum(amount) as amount_tol
from dfjl
group by id,
date(left(work_dt,4)||'-'|| substr(work_dt,5,2) || '-01')
以上代码将每个id每个月的代发金额进行汇总，并统一设置为月初第一天。假设经过上面处理过后的表为cte。
select
id,
max(consecutive_counts) max_counts
from
(
select
id,
drk,
count(*) as consecutive_counts
from
(
select
id,
work_dt_new,
work_dt_new - INT(rank() over (partition by id order by workdate_dt_new) month drk
from cte
) as t1
group by id,drk
) as t2
group by id
首先构造辅助列drk，如果某段时间有连续代发，则其drk应该是相同的，然后根据id和drk计算每组drk的连续代发次数，最后再通过max计算每个id的最长代发次数。以上代码在db2 数据库下可直接运行，其他数据库可自行修改。
