-Resample vs -If vs -Map vs Subquery

5 categories

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
    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.)

20 categories

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
    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.)

sumMapResample

It’s also possible to combine them.

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] 
└─────┴──────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────────┘