--统计连续列重复个数

DROP SPECIFIC FUNCTION FRPT @
CREATE FUNCTION FRPT(
    A1 INTEGER,
    A2 INTEGER,
    A3 INTEGER,
    A4 INTEGER,
    A5 INTEGER,
    A6 INTEGER,
    A7 INTEGER,
    A8 INTEGER
)
RETURNS INTEGER
SPECIFIC FRPT
READS SQL DATA
RETURN
  SELECT MAX(T.NUMBERS)
    FROM (
          SELECT T.REPEAT,
                 COUNT(1) NUMBERS
            FROM (
                  SELECT C1 - ROWNUMBER() OVER(PARTITION BY C2 ORDER BY C1) REPEAT,
                         MIN(C1) OVER(PARTITION BY C2) -1 BEGIN
                    FROM TABLE(
                               VALUES (1, A1),
                                      (2, A2),
                                      (3, A3),
                                      (4, A4),
                                      (5, A5),
                                      (6, A6),
                                      (7, A7),
                                      (8, A8)
                              ) T(C1, C2)
                 ) T
           WHERE T.REPEAT = T.BEGIN
           GROUP BY T.REPEAT        
         ) T
@

--测试语句一
SELECT '连续重复'
     ||RTRIM(CHAR(REPEAT))
     ||'列的: '
     ||RTRIM(CHAR(COUNTS)) TXT
  FROM (
        SELECT REPEAT,
               COUNT(1) COUNTS
          FROM (
                SELECT FRPT(A1, A2, A3, A4, A5, A6, A7, A8) REPEAT
                  FROM TABLE(
                             VALUES (1, 1, 2, 2, 3, 3, 4, 4),
                                    (1, 1, 2, 5, 6, 3, 4, 9),
                                    (1, 1, 1, 3, 5, 7 ,2 ,6),
                                    (1, 1, 1, 3, 3, 3 ,8 ,8)
                            ) T(A1, A2, A3, A4, A5, A6, A7, A8)
               ) T
         WHERE REPEAT > 1
           AND REPEAT < 8
         GROUP BY REPEAT
       ) T
      
--测试语句二
SELECT '连续重复'
     ||RTRIM(CHAR(REPEAT))
     ||'列的: '
     ||RTRIM(CHAR(COUNTS)) TXT
  FROM (
        SELECT REPEAT,
               COUNT(1) COUNTS
          FROM (
                SELECT FRPT(A1, A2, A3, A4, A5, A6, A7, A8) REPEAT
                  FROM TABLE(
                             VALUES (11,11,11,11,22,22,33,44),
                                    (11,11,11,11,22,22,33,44),
                                    (11,11,11,11,22,22,33,44),

                                    (11,11,33,33,22,22,33,44),
                                    (11,11,33,33,22,22,33,44),
                                    (11,11,66,11,22,11,33,44),

                                    (11,11,11,66,22,11,33,44)
                            ) T(A1, A2, A3, A4, A5, A6, A7, A8)
               ) T
         WHERE REPEAT > 1
           AND REPEAT < 8
         GROUP BY REPEAT
       ) T