clickhouse - How to extract ascending subsets from the sequence?

Question

I have some data:

┌─id--┬─serial┐
│ 1   │     1 │
│ 2   │     2 │
│ 3   │     3 │
│ 4   │     1 │
│ 5   │     3 │
│ 6   │     2 │
│ 7   │     1 │
│ 8   │     2 │
│ 9   │     3 │
│ 10  │     1 │
│ 11  │     2 │
│ 12  │     1 │
│ 13  │     2 │
│ 14  │     3 │
└─────┴───────┘

I want to group by column 'serial' where the group rule is: any ascending subset (like this, 1 -> 2 -> 3) is a group.

I expect result：</p>

┌─id--┬─serial┬─group─┐
│ 1   │     1 │ 1     │
│ 2   │     2 │ 1     │
│ 3   │     3 │ 1     │
│ 4   │     1 │ 2     │
│ 5   │     3 │ 2     │
│ 6   │     2 │ 3     │
│ 7   │     1 │ 4     │
│ 8   │     2 │ 4     │
│ 9   │     3 │ 4     │
│ 10  │     1 │ 5     │
│ 11  │     2 │ 5     │
│ 12  │     1 │ 6     │
│ 13  │     2 │ 6     │
│ 14  │     3 │ 6     │
└─────┴───────┴───────┘

标签： clickhouse

Answer 1

如果我理解正确，您想将集合拆分为具有上升趋势的子集。

SELECT r.1 id, r.2 serial, r.3 AS group, arrayJoin(result) r
FROM (
  SELECT 
    groupArray((id, serial)) sourceArray,
    /* find indexes where the ascending trend is broken */  
    arrayFilter(i -> (i = 1 OR sourceArray[i - 1].2 > sourceArray[i].2), arrayEnumerate(sourceArray)) trendBrokenIndexes,
    /* select all groups with ascending trend and assign them group-id */
    arrayMap(i -> 
      (i, arraySlice(sourceArray, trendBrokenIndexes[i], i < length(trendBrokenIndexes) ? trendBrokenIndexes[i+1] - trendBrokenIndexes[i] : null)), 
      arrayEnumerate(trendBrokenIndexes)) groups,
    /* prepare the result */
    arrayReduce('groupArrayArray', arrayMap(x -> arrayMap(y -> (y.1, y.2, x.1), x.2), groups)) result    
  FROM (
    /* source data */
    SELECT arrayJoin([(1 , 1),(2 , 2),(3 , 3),(4 , 1),(5 , 3),(6 , 2),(7 , 1),(8 , 2),(9 , 3),(10, 1),(11, 2),(12, 1),(13, 2),(14, 3)]) a, a.1 id, a.2 serial
    ORDER BY id))

/* Result
┌─id─┬─serial─┬─group─┬─r────────┐
│  1 │      1 │     1 │ (1,1,1)  │
│  2 │      2 │     1 │ (2,2,1)  │
│  3 │      3 │     1 │ (3,3,1)  │
│  4 │      1 │     2 │ (4,1,2)  │
│  5 │      3 │     2 │ (5,3,2)  │
│  6 │      2 │     3 │ (6,2,3)  │
│  7 │      1 │     4 │ (7,1,4)  │
│  8 │      2 │     4 │ (8,2,4)  │
│  9 │      3 │     4 │ (9,3,4)  │
│ 10 │      1 │     5 │ (10,1,5) │
│ 11 │      2 │     5 │ (11,2,5) │
│ 12 │      1 │     6 │ (12,1,6) │
│ 13 │      2 │     6 │ (13,2,6) │
│ 14 │      3 │     6 │ (14,3,6) │
└────┴────────┴───────┴──────────┘
*/