Clickhouse order by with fill

clikhouse_order_by_with_fill.sql

https://clickhouse.github.io/clickhouse-presentations/meetup30/new_features/#10



select number, rand() as val1, 'xxx'||toString(number) as val3, 'zzz' as const_col  from numbers(10) where number%3 = 1;

SELECT 
    number, 
    rand() AS val1, 
    concat('xxx', toString(number)) AS val3, 
    'zzz' AS const_col
FROM numbers(10)
WHERE (number % 3) = 1

┌─number─┬───────val1─┬─val3─┬─const_col─┐
│      1 │ 1388741176 │ xxx1 │ zzz       │
│      4 │ 3361902449 │ xxx4 │ zzz       │
│      7 │ 3345638744 │ xxx7 │ zzz       │
└────────┴────────────┴──────┴───────────┘



eb90336ba886 :) select number, rand() as val1, 'xxx'||toString(number) as val3, 'zzz' as const_col  from numbers(10) where number%3 = 1 order by number with fill

SELECT 
    number, 
    rand() AS val1, 
    concat('xxx', toString(number)) AS val3, 
    'zzz' AS const_col
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY number ASC WITH FILL 

┌─number─┬───────val1─┬─val3─┬─const_col─┐
│      1 │ 2900399857 │ xxx1 │ zzz       │
│      2 │          0 │      │ zzz       │
│      3 │          0 │      │ zzz       │
│      4 │ 1093675396 │ xxx4 │ zzz       │
│      5 │          0 │      │ zzz       │
│      6 │          0 │      │ zzz       │
│      7 │ 1847490795 │ xxx7 │ zzz       │
└────────┴────────────┴──────┴───────────┘

7 rows in set. Elapsed: 0.005 sec. 



eb90336ba886 :) select number, rand() as val1, 'xxx'||toString(number) as val3, 'zzz' as const_col  from numbers(10) where number%3 = 1 order by number with fill FROM 0;

SELECT 
    number, 
    rand() AS val1, 
    concat('xxx', toString(number)) AS val3, 
    'zzz' AS const_col
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY number ASC WITH FILL  FROM 0

┌─number─┬───────val1─┬─val3─┬─const_col─┐
│      0 │          0 │      │ zzz       │
│      1 │ 2145792954 │ xxx1 │ zzz       │
│      2 │          0 │      │ zzz       │
│      3 │          0 │      │ zzz       │
│      4 │ 2553499589 │ xxx4 │ zzz       │
│      5 │          0 │      │ zzz       │
│      6 │          0 │      │ zzz       │
│      7 │ 2116124468 │ xxx7 │ zzz       │
└────────┴────────────┴──────┴───────────┘

8 rows in set. Elapsed: 0.003 sec. 



eb90336ba886 :) select number, rand() as val1, 'xxx'||toString(number) as val3, 'zzz' as const_col  from numbers(10) where number%3 = 1 order by number with fill to 20;

SELECT 
    number, 
    rand() AS val1, 
    concat('xxx', toString(number)) AS val3, 
    'zzz' AS const_col
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY number ASC WITH FILL  TO 20

┌─number─┬───────val1─┬─val3─┬─const_col─┐
│      1 │ 3835916850 │ xxx1 │ zzz       │
│      2 │          0 │      │ zzz       │
│      3 │          0 │      │ zzz       │
│      4 │ 2108236721 │ xxx4 │ zzz       │
│      5 │          0 │      │ zzz       │
│      6 │          0 │      │ zzz       │
│      7 │ 1645938399 │ xxx7 │ zzz       │
└────────┴────────────┴──────┴───────────┘
┌─number─┬─val1─┬─val3─┬─const_col─┐
│      8 │    0 │      │ zzz       │
│      9 │    0 │      │ zzz       │
│     10 │    0 │      │ zzz       │
│     11 │    0 │      │ zzz       │
│     12 │    0 │      │ zzz       │
│     13 │    0 │      │ zzz       │
│     14 │    0 │      │ zzz       │
│     15 │    0 │      │ zzz       │
│     16 │    0 │      │ zzz       │
│     17 │    0 │      │ zzz       │
│     18 │    0 │      │ zzz       │
│     19 │    0 │      │ zzz       │
└────────┴──────┴──────┴───────────┘

19 rows in set. Elapsed: 0.005 sec. 




SELECT 
    number, 
    rand() AS val1, 
    concat('xxx', toString(number)) AS val3, 
    'zzz' AS const_col
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY number ASC WITH FILL  FROM 0 TO 100 STEP 10

┌─number─┬───────val1─┬─val3─┬─const_col─┐
│      0 │          0 │      │ zzz       │
│      1 │ 3232087387 │ xxx1 │ zzz       │
│      4 │  780714599 │ xxx4 │ zzz       │
│      7 │ 3003415626 │ xxx7 │ zzz       │
└────────┴────────────┴──────┴───────────┘
┌─number─┬─val1─┬─val3─┬─const_col─┐
│     10 │    0 │      │ zzz       │
│     20 │    0 │      │ zzz       │
│     30 │    0 │      │ zzz       │
│     40 │    0 │      │ zzz       │
│     50 │    0 │      │ zzz       │
│     60 │    0 │      │ zzz       │
│     70 │    0 │      │ zzz       │
│     80 │    0 │      │ zzz       │
│     90 │    0 │      │ zzz       │
└────────┴──────┴──────┴───────────┘

13 rows in set. Elapsed: 0.005 sec.