计算Oracle中的连续发生

Question

我有一个如下的桌子：

预期结果如下：

这里的T1是上个月，T2是上个月的最后一个，T3从现在开始3个月，依此类推。

total_inactivity =金额为0

max_consectee_inactivity =连续数月的最大数量为零的月数=零最近

_inactivity =最近从最近一个月开始的连续数月的最大数量，即t1 t1 t1 the the金额为0。这意味着如果T1表示T1除零外，有任何值，最近的inActivity将为零。例如，自t1_amount，t2_amount，t3_amount和t4_amount以来，Person_ID 5555的近期_ Inactivity = 4，全部为0。nb

：我有大约1000万个记录，并寻找优化的解决方案

Answer 1

您可以使用案例表达式：

SELECT t.*,
       CASE t8_amount WHEN 0 THEN 1 ELSE 0 END
       + CASE t7_amount WHEN 0 THEN 1 ELSE 0 END
       + CASE t6_amount WHEN 0 THEN 1 ELSE 0 END
       + CASE t5_amount WHEN 0 THEN 1 ELSE 0 END
       + CASE t4_amount WHEN 0 THEN 1 ELSE 0 END
       + CASE t3_amount WHEN 0 THEN 1 ELSE 0 END
       + CASE t2_amount WHEN 0 THEN 1 ELSE 0 END
       + CASE t1_amount WHEN 0 THEN 1 ELSE 0 END AS total_inactivity,
       CASE
       WHEN (0,0,0,0,0,0,0,0) IN (
         (t1_amount, t2_amount, t3_amount, t4_amount, t5_amount, t6_amount, t7_amount, t8_amount)
       )
       THEN 8
       WHEN (0,0,0,0,0,0,0) IN (
         (t1_amount, t2_amount, t3_amount, t4_amount, t5_amount, t6_amount, t7_amount),
         (t2_amount, t3_amount, t4_amount, t5_amount, t6_amount, t7_amount, t8_amount)
       )
       THEN 7
       WHEN (0,0,0,0,0,0) IN (
         (t1_amount, t2_amount, t3_amount, t4_amount, t5_amount, t6_amount),
         (t2_amount, t3_amount, t4_amount, t5_amount, t6_amount, t7_amount),
         (t3_amount, t4_amount, t5_amount, t6_amount, t7_amount, t8_amount)
       )
       THEN 6
       WHEN (0,0,0,0,0) IN (
         (t1_amount, t2_amount, t3_amount, t4_amount, t5_amount),
         (t2_amount, t3_amount, t4_amount, t5_amount, t6_amount),
         (t3_amount, t4_amount, t5_amount, t6_amount, t7_amount),
         (t4_amount, t5_amount, t6_amount, t7_amount, t8_amount)
       )
       THEN 5
       WHEN (0,0,0,0) IN (
         (t1_amount, t2_amount, t3_amount, t4_amount),
         (t2_amount, t3_amount, t4_amount, t5_amount),
         (t3_amount, t4_amount, t5_amount, t6_amount),
         (t4_amount, t5_amount, t6_amount, t7_amount),
         (t5_amount, t6_amount, t7_amount, t8_amount)
       )
       THEN 4
       WHEN (0,0,0) IN (
         (t1_amount, t2_amount, t3_amount),
         (t2_amount, t3_amount, t4_amount),
         (t3_amount, t4_amount, t5_amount),
         (t4_amount, t5_amount, t6_amount),
         (t5_amount, t6_amount, t7_amount),
         (t6_amount, t7_amount, t8_amount)
       )
       THEN 3
       WHEN (0,0) IN (
         (t1_amount, t2_amount),
         (t2_amount, t3_amount),
         (t3_amount, t4_amount),
         (t4_amount, t5_amount),
         (t5_amount, t6_amount),
         (t6_amount, t7_amount),
         (t7_amount, t8_amount)
       )
       THEN 2
       WHEN (0) IN (
         (t1_amount), (t2_amount), (t3_amount), (t4_amount),
         (t5_amount), (t6_amount), (t7_amount), (t8_amount)
       )
       THEN 1
       ELSE 0
       END AS max_consecutive_inactivity,
       CASE t1_amount WHEN 0 THEN
         1 + CASE t2_amount WHEN 0 THEN
           1 + CASE t3_amount WHEN 0 THEN
             1 + CASE t4_amount WHEN 0 THEN
               1 + CASE t5_amount WHEN 0 THEN
                 1 + CASE t6_amount WHEN 0 THEN
                   1 + CASE t7_amount WHEN 0 THEN
                     1 + CASE t8_amount WHEN 0 THEN
                       1 
                       ELSE 0
                       END
                     ELSE 0
                     END
                   ELSE 0
                   END
                 ELSE 0
                 END
               ELSE 0
               END
             ELSE 0
             END 
           ELSE 0
           END
       ELSE 0
       END AS recent_inactivity
FROM   Tran_rec t;

对于示例数据，输出：

person_id	t8_amount	t7_amount	t6_amount	t5_amount	t4_amount	t3_amount	t2_amount	t1_amount	total_inactivity	max_consecnate_inactivity	最近的_inactivity
1111	0	0	0	20	34	0	89	33	4	3	0
2222	100	120	0	200	340	110	89	0	2	1	1
3333	0	0	0	0	0	0	0	110	7	7	0
4444	80	0	0	0	0	0	0	60	6	6	0
5555	80	70	60	50	0	0	0	0	4	4	4
6666	80	0	0	50	0	40	0	50	4	2	0

db＆lt;＆gt;＆gt; fiddle here