SQL每日一题(20201029)在不同日期有个不同的金额，统计连续增长天数和连续负增长天数

SQL每日一题(20210420)
SQL每日一题(20210804)
SQL每日一题(20211122)
SQL每日一题(20220311)

/*
1.首先根据金额差值是否大于0来判断正负
2.然后使用row_number 根据增长方向 代码 分组 日期进行排序 t1 根据代码进行排序t2
3.用t2-t1来判断是不是连续增长的日期
4.最后使用row_number 根据代码 增长方向 是否是连续的月进行分组 日期进行排序
*/

SELECT c.日期,
       c.代码,
       c.金额,
       c.金额差异值,
       c.增长方向,
       row_number() over (partition by c.代码,c.增长方向,c.P3 order by c.日期) as 连续增长天数
from (SELECT *,
             ( cast(b.P1 as signed)-  cast(b.P2 as signed)) as P3
      from (select *,
                   row_number() over (partition by a.增长方向,a.代码 order by a.日期 ) as P1,
                   row_number() over (partition by a.代码 order by a.日期 )        as P2
            from (SELECT *, case when 金额差异值 >= 0 then '正' else '负' end as 增长方向 from T1029) a ORDER by a.代码,a.日期) b) c
order by c.代码, c.日期

with  v0112 AS
(
SELECT *,
       CASE WHEN 金额差异值 >= 0 THEN '正' ELSE '负' END      AS 增长方向,
       ROW_NUMBER() OVER (PARTITION BY 代码 ORDER BY 日期) AS rn
FROM T0112)

SELECT a.*,IFNULL((SELECT b.rn
                      FROM v0112 b
                      WHERE b.代码 = a.代码 AND b.rn < a.rn AND b.增长方向 <> a.增长方向
                      ORDER BY b.rn DESC
                      LIMIT 1), 0) as result,
       a.rn - IFNULL((SELECT b.rn
                      FROM v0112 b
                      WHERE b.代码 = a.代码 AND b.rn < a.rn AND b.增长方向 <> a.增长方向
                      ORDER BY b.rn DESC
                      LIMIT 1), 0) AS 连续天数
FROM v0112 a;