根据A表的a字段表示客户标识,B字段表示12个月份,c表示是否逾期(0否,1是),现在的需求是根据判断出某个客户从1月份到12月份内到某个月连续逾期的最大期数,表结构和语句如下,用函数和sql语句实现都可以。源表结构A
A B C
t1 1 0
t1 2 1
t1 3 1
t1 4 0
t1 5 1
t1 6 0
t1 7 1
t1 8 1
t1 9 1
t1 10 0
t1 11 1
t1 12 0
T2 1 1
T2 2 0
T2 3 0
T2 4 0
T2 5 1
T2 6 1
T2 7 0
T2 8 0
T2 9 0
T2 10 1
T2 11 1
T2 12 1要求的结果表
A B C D
t1 1 0 0
t1 2 1 1
t1 3 1 2
t1 4 0 2
t1 5 1 2
t1 6 0 2
t1 7 1 2
t1 8 1 2
t1 9 1 3
t1 10 0 3
t1 11 1 3
t1 12 0 3
T2 1 1 1
T2 2 0 1
T2 3 0 1
T2 4 0 1
T2 5 1 1
T2 6 1 2
T2 7 0 2
T2 8 0 2
T2 9 0 2
T2 10 1 2
T2 11 1 2
T2 12 1 3表结构及语句如下
WITH A AS (
SELECT 't1' A,'1' B, '0' C FROM DUAL UNION
-- SELECT 't1' ,'1','0' FROM DUAL UNION
SELECT 't1' ,'2','1' FROM DUAL UNION
SELECT 't1' ,'3','1' FROM DUAL UNION
SELECT 't1' ,'4','0' FROM DUAL UNION
SELECT 't1' ,'5','1' FROM DUAL UNION
SELECT 't1' ,'6','0' FROM DUAL UNION
SELECT 't1' ,'7','1' FROM DUAL UNION
SELECT 't1' ,'8','1' FROM DUAL UNION
SELECT 't1' ,'9','1' FROM DUAL UNION
SELECT 't1' ,'10','0' FROM DUAL UNION
SELECT 't1' ,'11','1' FROM DUAL UNION
SELECT 't1' ,'12','0' FROM DUAL UNION
SELECT 'T2' ,'1','1' FROM DUAL UNION
SELECT 'T2' ,'2','0' FROM DUAL UNION
SELECT 'T2' ,'3','0' FROM DUAL UNION
SELECT 'T2' ,'4','0' FROM DUAL UNION
SELECT 'T2' ,'5','1' FROM DUAL UNION
SELECT 'T2' ,'6','1' FROM DUAL UNION
SELECT 'T2' ,'7','0' FROM DUAL UNION
SELECT 'T2' ,'8','0' FROM DUAL UNION
SELECT 'T2' ,'9','0' FROM DUAL UNION
SELECT 'T2' ,'10','1' FROM DUAL UNION
SELECT 'T2' ,'11','1' FROM DUAL UNION
SELECT 'T2' ,'12','1' FROM DUAL
)
SELECT * FROM A;
A B C
t1 1 0
t1 2 1
t1 3 1
t1 4 0
t1 5 1
t1 6 0
t1 7 1
t1 8 1
t1 9 1
t1 10 0
t1 11 1
t1 12 0
T2 1 1
T2 2 0
T2 3 0
T2 4 0
T2 5 1
T2 6 1
T2 7 0
T2 8 0
T2 9 0
T2 10 1
T2 11 1
T2 12 1要求的结果表
A B C D
t1 1 0 0
t1 2 1 1
t1 3 1 2
t1 4 0 2
t1 5 1 2
t1 6 0 2
t1 7 1 2
t1 8 1 2
t1 9 1 3
t1 10 0 3
t1 11 1 3
t1 12 0 3
T2 1 1 1
T2 2 0 1
T2 3 0 1
T2 4 0 1
T2 5 1 1
T2 6 1 2
T2 7 0 2
T2 8 0 2
T2 9 0 2
T2 10 1 2
T2 11 1 2
T2 12 1 3表结构及语句如下
WITH A AS (
SELECT 't1' A,'1' B, '0' C FROM DUAL UNION
-- SELECT 't1' ,'1','0' FROM DUAL UNION
SELECT 't1' ,'2','1' FROM DUAL UNION
SELECT 't1' ,'3','1' FROM DUAL UNION
SELECT 't1' ,'4','0' FROM DUAL UNION
SELECT 't1' ,'5','1' FROM DUAL UNION
SELECT 't1' ,'6','0' FROM DUAL UNION
SELECT 't1' ,'7','1' FROM DUAL UNION
SELECT 't1' ,'8','1' FROM DUAL UNION
SELECT 't1' ,'9','1' FROM DUAL UNION
SELECT 't1' ,'10','0' FROM DUAL UNION
SELECT 't1' ,'11','1' FROM DUAL UNION
SELECT 't1' ,'12','0' FROM DUAL UNION
SELECT 'T2' ,'1','1' FROM DUAL UNION
SELECT 'T2' ,'2','0' FROM DUAL UNION
SELECT 'T2' ,'3','0' FROM DUAL UNION
SELECT 'T2' ,'4','0' FROM DUAL UNION
SELECT 'T2' ,'5','1' FROM DUAL UNION
SELECT 'T2' ,'6','1' FROM DUAL UNION
SELECT 'T2' ,'7','0' FROM DUAL UNION
SELECT 'T2' ,'8','0' FROM DUAL UNION
SELECT 'T2' ,'9','0' FROM DUAL UNION
SELECT 'T2' ,'10','1' FROM DUAL UNION
SELECT 'T2' ,'11','1' FROM DUAL UNION
SELECT 'T2' ,'12','1' FROM DUAL
)
SELECT * FROM A;
2 SELECT 't1' A,'1' B, '0' C FROM DUAL UNION ALL
3 SELECT 't1' ,'2','1' FROM DUAL UNION ALL
4 SELECT 't1' ,'3','1' FROM DUAL UNION ALL
5 SELECT 't1' ,'4','0' FROM DUAL UNION ALL
6 SELECT 't1' ,'5','1' FROM DUAL UNION ALL
7 SELECT 't1' ,'6','0' FROM DUAL UNION ALL
8 SELECT 't1' ,'7','1' FROM DUAL UNION ALL
9 SELECT 't1' ,'8','1' FROM DUAL UNION ALL
10 SELECT 't1' ,'9','1' FROM DUAL UNION ALL
11 SELECT 't1' ,'10','0' FROM DUAL UNION ALL
12 SELECT 't1' ,'11','1' FROM DUAL UNION ALL
13 SELECT 't1' ,'12','0' FROM DUAL UNION ALL
14 SELECT 'T2' ,'1','1' FROM DUAL UNION ALL
15 SELECT 'T2' ,'2','0' FROM DUAL UNION ALL
16 SELECT 'T2' ,'3','0' FROM DUAL UNION ALL
17 SELECT 'T2' ,'4','0' FROM DUAL UNION ALL
18 SELECT 'T2' ,'5','1' FROM DUAL UNION ALL
19 SELECT 'T2' ,'6','1' FROM DUAL UNION ALL
20 SELECT 'T2' ,'7','0' FROM DUAL UNION ALL
21 SELECT 'T2' ,'8','0' FROM DUAL UNION ALL
22 SELECT 'T2' ,'9','0' FROM DUAL UNION ALL
23 SELECT 'T2' ,'10','1' FROM DUAL UNION ALL
24 SELECT 'T2' ,'11','1' FROM DUAL UNION ALL
25 SELECT 'T2' ,'12','1' FROM DUAL
26 ),
27 t1 AS (
28 SELECT m.a,
29 SUM(m.c) OVER(PARTITION BY m.a, m.group_num) max_num,
30 MAX(to_number(m.b)) OVER(PARTITION BY m.a, m.group_num) max_month
31 FROM (SELECT t.a,
32 t.b,
33 t.c,
34 DECODE(t.c,
35 '1',
36 t.b - ROW_NUMBER() OVER(PARTITION BY t.a, t.c ORDER BY to_number(t.b)), 37 -1) group_num
38 FROM a t) m
39 )
40 SELECT t.a,
41 t.b,
42 t.c,
43 (SELECT NVL(MAX(t1.max_num),DECODE(t.c, '0', '0', '1'))
44 FROM t1
45 WHERE t1.a = t.a AND t1.max_month <= t.b) d
46 FROM a t;A B C D
---- ---- -- ----------
t1 1 0 0
t1 2 1 1
t1 3 1 2
t1 4 0 2
t1 5 1 2
t1 6 0 2
t1 7 1 2
t1 8 1 2
t1 9 1 3
t1 10 0 3
t1 11 1 3A B C D
---- ---- -- ----------
t1 12 0 3
T2 1 1 1
T2 2 0 1
T2 3 0 1
T2 4 0 1
T2 5 1 1
T2 6 1 2
T2 7 0 2
T2 8 0 2
T2 9 0 2
T2 10 1 2A B C D
---- ---- -- ----------
T2 11 1 2
T2 12 1 3已选择24行。
select a,to_number(b) b,to_number(c) c from
(
SELECT 't1' A,'1' B, '0' C FROM DUAL UNION
-- SELECT 't1' ,'1','0' FROM DUAL UNION
SELECT 't1' ,'2','1' FROM DUAL UNION
SELECT 't1' ,'3','1' FROM DUAL UNION
SELECT 't1' ,'4','0' FROM DUAL UNION
SELECT 't1' ,'5','1' FROM DUAL UNION
SELECT 't1' ,'6','0' FROM DUAL UNION
SELECT 't1' ,'7','1' FROM DUAL UNION
SELECT 't1' ,'8','1' FROM DUAL UNION
SELECT 't1' ,'9','1' FROM DUAL UNION
SELECT 't1' ,'10','0' FROM DUAL UNION
SELECT 't1' ,'11','1' FROM DUAL UNION
SELECT 't1' ,'12','0' FROM DUAL UNION
SELECT 'T2' ,'1','1' FROM DUAL UNION
SELECT 'T2' ,'2','0' FROM DUAL UNION
SELECT 'T2' ,'3','0' FROM DUAL UNION
SELECT 'T2' ,'4','0' FROM DUAL UNION
SELECT 'T2' ,'5','1' FROM DUAL UNION
SELECT 'T2' ,'6','1' FROM DUAL UNION
SELECT 'T2' ,'7','0' FROM DUAL UNION
SELECT 'T2' ,'8','0' FROM DUAL UNION
SELECT 'T2' ,'9','0' FROM DUAL UNION
SELECT 'T2' ,'10','1' FROM DUAL UNION
SELECT 'T2' ,'11','1' FROM DUAL UNION
SELECT 'T2' ,'12','1' FROM DUAL
) a
),
tmp2 as(
select ta.a,ta.b,ta.c,decode(cs,null,0,cs) cs from tmp ta left join
( select a,b,c,count(1) cs from
(
SELECT
a,b,c
FROM tmp
start with c = 1
connect by prior a = a and prior b = b - 1 and prior c = c and c <>0
) an
group by a,b,c
) tb
on ta.a=tb.a and ta.b=tb.b
order by a,to_number(b)
)
select
a,b,c,
case when exists (select * from tmp2 ta where ta.a=tb.a and ta.b<=tb.b and ta.c=1)
then
(select max(cs) from tmp2 ta where ta.a=tb.a and ta.b<=tb.b and ta.c=1)
else 0 end cs
from tmp2 tb