-Resample vs -If vs -Map vs Subquery

SELECT sumResample(0, 5, 1)(number, number % 5) AS sum
FROM numbers_mt(1000000000)

┌─sum───────────────────────────────────────────────────────────────────────────────────────────┐
│ [99999999500000000,99999999700000000,99999999900000000,100000000100000000,100000000300000000] │
└───────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 1.010 sec. Processed 1.00 billion rows, 8.00 GB (990.20 million rows/s., 7.92 GB/s.)


SELECT sumMap([number % 5], [number]) AS sum
FROM numbers_mt(1000000000)

┌─sum─────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ ([0,1,2,3,4],[99999999500000000,99999999700000000,99999999900000000,100000000100000000,100000000300000000]) │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 5.730 sec. Processed 1.00 billion rows, 8.00 GB (174.51 million rows/s., 1.40 GB/s.)

SELECT sumMap(map(number % 5, number)) AS sum
FROM numbers_mt(1000000000)

┌─sum─────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {0:99999999500000000,1:99999999700000000,2:99999999900000000,3:100000000100000000,4:100000000300000000} │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 4.169 sec. Processed 1.00 billion rows, 8.00 GB (239.89 million rows/s., 1.92 GB/s.)

SELECT
    sumIf(number, (number % 5) = 0) AS sum_0,
    sumIf(number, (number % 5) = 1) AS sum_1,
    sumIf(number, (number % 5) = 2) AS sum_2,
    sumIf(number, (number % 5) = 3) AS sum_3,
    sumIf(number, (number % 5) = 4) AS sum_4
FROM numbers_mt(1000000000)

┌─────────────sum_0─┬─────────────sum_1─┬─────────────sum_2─┬──────────────sum_3─┬──────────────sum_4─┐
│ 99999999500000000 │ 99999999700000000 │ 99999999900000000 │ 100000000100000000 │ 100000000300000000 │
└───────────────────┴───────────────────┴───────────────────┴────────────────────┴────────────────────┘

1 rows in set. Elapsed: 0.762 sec. Processed 1.00 billion rows, 8.00 GB (1.31 billion rows/s., 10.50 GB/s.)

SELECT sumMap([id], [sum]) AS sum
FROM
(
    SELECT
        number % 5 AS id,
        sum(number) AS sum
    FROM numbers_mt(1000000000)
    GROUP BY id
)

┌─sum─────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ ([0,1,2,3,4],[99999999500000000,99999999700000000,99999999900000000,100000000100000000,100000000300000000]) │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.331 sec. Processed 1.00 billion rows, 8.00 GB (3.02 billion rows/s., 24.15 GB/s.)

SELECT sumResample(0, 20, 1)(number, number % 20) AS sum
FROM numbers_mt(1000000000)

1 rows in set. Elapsed: 1.056 sec. Processed 1.00 billion rows, 8.00 GB (947.28 million rows/s., 7.58 GB/s.)

SELECT sumMap([number % 20], [number]) AS sum
FROM numbers_mt(1000000000)

1 rows in set. Elapsed: 6.410 sec. Processed 1.00 billion rows, 8.00 GB (156.00 million rows/s., 1.25 GB/s.)

SELECT sumMap(map(number % 20, number)) AS sum
FROM numbers_mt(1000000000)

┌─sum────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {0:24999999500000000,1:24999999550000000,2:24999999600000000,3:24999999650000000,4:24999999700000000,5:24999999750000000,6:24999999800000000,7:24999999850000000,8:24999999900000000,9:24999999950000000,10:25000000000000000,11:25000000050000000,12:25000000100000000,13:25000000150000000,14:25000000200000000,15:25000000250000000,16:25000000300000000,17:25000000350000000,18:25000000400000000,19:25000000450000000} │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 4.629 sec. Processed 1.00 billion rows, 8.00 GB (216.04 million rows/s., 1.73 GB/s.)

SELECT
    sumIf(number, (number % 5) = 0) AS sum_0,
    sumIf(number, (number % 5) = 1) AS sum_1,
    sumIf(number, (number % 5) = 2) AS sum_2,
    sumIf(number, (number % 5) = 3) AS sum_3,
    sumIf(number, (number % 5) = 4) AS sum_4,
    sumIf(number, (number % 5) = 5) AS sum_5,
    sumIf(number, (number % 5) = 6) AS sum_6,
    sumIf(number, (number % 5) = 7) AS sum_7,
    sumIf(number, (number % 5) = 8) AS sum_8,
    sumIf(number, (number % 5) = 9) AS sum_9,
    sumIf(number, (number % 5) = 10) AS sum_10,
    sumIf(number, (number % 5) = 11) AS sum_11,
    sumIf(number, (number % 5) = 12) AS sum_12,
    sumIf(number, (number % 5) = 13) AS sum_13,
    sumIf(number, (number % 5) = 14) AS sum_14,
    sumIf(number, (number % 5) = 15) AS sum_15,
    sumIf(number, (number % 5) = 16) AS sum_16,
    sumIf(number, (number % 5) = 17) AS sum_17,
    sumIf(number, (number % 5) = 18) AS sum_18,
    sumIf(number, (number % 5) = 19) AS sum_19
FROM numbers_mt(1000000000)

1 rows in set. Elapsed: 5.282 sec. Processed 1.00 billion rows, 8.00 GB (189.30 million rows/s., 1.51 GB/s.)

SELECT sumMap([id], [sum]) AS sum
FROM
(
    SELECT
        number % 20 AS id,
        sum(number) AS sum
    FROM numbers_mt(1000000000)
    GROUP BY id
)

1 rows in set. Elapsed: 0.362 sec. Processed 1.00 billion rows, 8.00 GB (2.76 billion rows/s., 22.10 GB/s.)

SELECT sumMap(map(id, sum)) AS sum
FROM
(
    SELECT
        number % 20 AS id,
        sum(number) AS sum
    FROM numbers_mt(1000000000)
    GROUP BY id
)

SELECT
    day,
    category_id,
    sales
FROM
(
    SELECT sumMapResample(1, 31, 1)([category_id], [sales], day) AS res
    FROM
    (
        SELECT
            number % 31 AS day,
            100 * (number % 11) AS category_id,
            number AS sales
        FROM numbers(10000)
    )
)
ARRAY JOIN
    res.1 AS category_id,
    res.2 AS sales,
    arrayEnumerate(res.1) AS day

┌─day─┬─category_id──────────────────────────────────┬─sales──────────────────────────────────────────────────────────────────────────┐
│   1 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [143869,148365,142970,147465,142071,146566,151155,145667,150225,144768,149295] │
│   2 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [149325,143898,148395,142999,147494,142100,146595,151185,145696,150255,144797] │
│   3 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [144826,149355,143927,148425,143028,147523,142129,146624,151215,145725,150285] │
│   4 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [150315,144855,149385,143956,148455,143057,147552,142158,146653,151245,145754] │
│   5 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [145783,150345,144884,149415,143985,148485,143086,147581,142187,146682,151275] │
│   6 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [151305,145812,150375,144913,149445,144014,148515,143115,147610,142216,146711] │
│   7 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [146740,151335,145841,150405,144942,149475,144043,148545,143144,147639,142245] │
│   8 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [142274,146769,151365,145870,150435,144971,149505,144072,148575,143173,147668] │
│   9 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [147697,142303,146798,151395,145899,150465,145000,149535,144101,148605,143202] │
│  10 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [143231,147726,142332,146827,151425,145928,150495,145029,149565,144130,148635] │
│  11 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [148665,143260,147755,142361,146856,151455,145957,150525,145058,149595,144159] │
│  12 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [144188,148695,143289,147784,142390,146885,151485,145986,150555,145087,149625] │
│  13 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [149655,144217,148725,143318,147813,142419,146914,151515,146015,150585,145116] │
│  14 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [145145,149685,144246,148755,143347,147842,142448,146943,151545,146044,150615] │
│  15 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [150645,145174,149715,144275,148785,143376,147871,142477,146972,151575,146073] │
│  16 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [146102,150675,145203,149745,144304,148815,143405,147900,142506,147001,151605] │
│  17 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [151635,146131,150705,145232,149775,144333,148845,143434,147929,142535,147030] │
│  18 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [147059,141665,146160,150735,145261,149805,144362,148875,143463,147958,142564] │
│  19 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [142593,147088,141694,146189,150765,145290,149835,144391,148905,143492,147987] │
│  20 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [148016,142622,147117,141723,146218,150795,145319,149865,144420,148935,143521] │
│  21 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [143550,148045,142651,147146,141752,146247,150825,145348,149895,144449,148965] │
│  22 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [148995,143579,148074,142680,147175,141781,146276,150855,145377,149925,144478] │
│  23 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [144507,149025,143608,148103,142709,147204,141810,146305,150885,145406,149955] │
│  24 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [149985,144536,149055,143637,148132,142738,147233,141839,146334,150915,145435] │
│  25 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [145464,150015,144565,149085,143666,148161,142767,147262,141868,146363,150945] │
│  26 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [150975,145493,150045,144594,149115,143695,148190,142796,147291,141897,146392] │
│  27 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [146421,151005,145522,150075,144623,149145,143724,148219,142825,147320,141926] │
│  28 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [141955,146450,151035,145551,150105,144652,149175,143753,148248,142854,147349] │
│  29 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [147378,141984,146479,151065,145580,150135,144681,149205,143782,148277,142883] │
│  30 │ [0,100,200,300,400,500,600,700,800,900,1000] │ [142912,147407,142013,146508,151095,145609,150165,144710,149235,143811,148306] │
└─────┴──────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────────┘