Snapshot #331f3

original_query

select * from (
    select
        days.1 as day,
        arrayPopBack(arrayConcat([0], arrayCumSum(days.2))) _asc,
        arrayReverse(arrayCumSum(arrayReverse(days.2))) _desc
    from (
        select groupArray((day, s)) days, sum(s) total from (
            select toDate(ts) day, sum(amount) s from (
                -- table that generates random dates, numbers
                select now() + number*3600 as ts, number as amount from numbers(1000)
            ) group by day
        )
    )
) array join day, _asc, _desc

7.81KB processed, 1000 rows x 3 columns. This query took 1.04ms

· View chart

day Date	_asc UInt64	_desc UInt64
2021-01-28	0	499500
2021-01-29	45	499455
2021-01-30	561	498939
2021-01-31	1653	497847
2021-02-01	3321	496179
2021-02-02	5565	493935
2021-02-03	8385	491115
2021-02-04	11781	487719
2021-02-05	15753	483747
2021-02-06	20301	479199
2021-02-07	25425	474075
2021-02-08	31125	468375
2021-02-09	37401	462099
2021-02-10	44253	455247
2021-02-11	51681	447819
2021-02-12	59685	439815
2021-02-13	68265	431235
2021-02-14	77421	422079
2021-02-15	87153	412347
2021-02-16	97461	402039
2021-02-17	108345	391155
2021-02-18	119805	379695
2021-02-19	131841	367659
2021-02-20	144453	355047
2021-02-21	157641	341859
2021-02-22	171405	328095
2021-02-23	185745	313755
2021-02-24	200661	298839
2021-02-25	216153	283347
2021-02-26	232221	267279
2021-02-27	248865	250635
2021-02-28	266085	233415
2021-03-01	283881	215619
2021-03-02	302253	197247
2021-03-03	321201	178299
2021-03-04	340725	158775
2021-03-05	360825	138675
2021-03-06	381501	117999
2021-03-07	402753	96747
2021-03-08	424581	74919
2021-03-09	446985	52515
2021-03-10	469965	29535
2021-03-11	493521	5979

grouparray

select groupArray((day, s)) days, sum(s) total from (
    select toDate(ts) day, sum(amount) s from (
        -- table that generates random dates, numbers
        select now() + number*3600 as ts, number as amount from numbers(1000)
    ) group by day)

7.81KB processed, 1000 rows x 2 columns. This query took 0.75ms

· View chart

days Array(Tuple(Date, UInt64))	total UInt64
[['2021-01-28', 45], ['2021-01-29', 516], ['2021-01-30', 1092], ['2021-01-31', 1668], ['2021-02-01', 2244], ['2021-02-02', 2820], ['2021-02-03', 3396], ['2021-02-04', 3972], ['2021-02-05', 4548], ['2021-02-06', 5124], ['2021-02-07', 5700], ['2021-02-08', 6276], ['2021-02-09', 6852], ['2021-02-10', 7428], ['2021-02-11', 8004], ['2021-02-12', 8580], ['2021-02-13', 9156], ['2021-02-14', 9732], ['2021-02-15', 10308], ['2021-02-16', 10884], ['2021-02-17', 11460], ['2021-02-18', 12036], ['2021-02-19', 12612], ['2021-02-20', 13188], ['2021-02-21', 13764], ['2021-02-22', 14340], ['2021-02-23', 14916], ['2021-02-24', 15492], ['2021-02-25', 16068], ['2021-02-26', 16644], ['2021-02-27', 17220], ['2021-02-28', 17796], ['2021-03-01', 18372], ['2021-03-02', 18948], ['2021-03-03', 19524], ['2021-03-04', 20100], ['2021-03-05', 20676], ['2021-03-06', 21252], ['2021-03-07', 21828], ['2021-03-08', 22404], ['2021-03-09', 22980], ['2021-03-10', 23556], ['2021-03-11', 5979]]	499500

days

Array(Tuple(Date, UInt64))

total

UInt64

[['2021-01-28', 45], ['2021-01-29', 516], ['2021-01-30', 1092], ['2021-01-31', 1668], ['2021-02-01', 2244], ['2021-02-02', 2820], ['2021-02-03', 3396], ['2021-02-04', 3972], ['2021-02-05', 4548], ['2021-02-06', 5124], ['2021-02-07', 5700], ['2021-02-08', 6276], ['2021-02-09', 6852], ['2021-02-10', 7428], ['2021-02-11', 8004], ['2021-02-12', 8580], ['2021-02-13', 9156], ['2021-02-14', 9732], ['2021-02-15', 10308], ['2021-02-16', 10884], ['2021-02-17', 11460], ['2021-02-18', 12036], ['2021-02-19', 12612], ['2021-02-20', 13188], ['2021-02-21', 13764], ['2021-02-22', 14340], ['2021-02-23', 14916], ['2021-02-24', 15492], ['2021-02-25', 16068], ['2021-02-26', 16644], ['2021-02-27', 17220], ['2021-02-28', 17796], ['2021-03-01', 18372], ['2021-03-02', 18948], ['2021-03-03', 19524], ['2021-03-04', 20100], ['2021-03-05', 20676], ['2021-03-06', 21252], ['2021-03-07', 21828], ['2021-03-08', 22404], ['2021-03-09', 22980], ['2021-03-10', 23556], ['2021-03-11', 5979]]

499500

arrays

select
        days.1 as day,
        arrayPopBack(arrayConcat([0], arrayCumSum(days.2))) _asc,
        arrayReverse(arrayCumSum(arrayReverse(days.2))) _desc
    from grouparray

7.81KB processed, 1000 rows x 3 columns. This query took 0.79ms

· View chart

day Array(Date)	_asc Array(UInt64)	_desc Array(UInt64)
['2021-01-28', '2021-01-29', '2021-01-30', '2021-01-31', '2021-02-01', '2021-02-02', '2021-02-03', '2021-02-04', '2021-02-05', '2021-02-06', '2021-02-07', '2021-02-08', '2021-02-09', '2021-02-10', '2021-02-11', '2021-02-12', '2021-02-13', '2021-02-14', '2021-02-15', '2021-02-16', '2021-02-17', '2021-02-18', '2021-02-19', '2021-02-20', '2021-02-21', '2021-02-22', '2021-02-23', '2021-02-24', '2021-02-25', '2021-02-26', '2021-02-27', '2021-02-28', '2021-03-01', '2021-03-02', '2021-03-03', '2021-03-04', '2021-03-05', '2021-03-06', '2021-03-07', '2021-03-08', '2021-03-09', '2021-03-10', '2021-03-11']	[0, 45, 561, 1653, 3321, 5565, 8385, 11781, 15753, 20301, 25425, 31125, 37401, 44253, 51681, 59685, 68265, 77421, 87153, 97461, 108345, 119805, 131841, 144453, 157641, 171405, 185745, 200661, 216153, 232221, 248865, 266085, 283881, 302253, 321201, 340725, 360825, 381501, 402753, 424581, 446985, 469965, 493521]	[499500, 499455, 498939, 497847, 496179, 493935, 491115, 487719, 483747, 479199, 474075, 468375, 462099, 455247, 447819, 439815, 431235, 422079, 412347, 402039, 391155, 379695, 367659, 355047, 341859, 328095, 313755, 298839, 283347, 267279, 250635, 233415, 215619, 197247, 178299, 158775, 138675, 117999, 96747, 74919, 52515, 29535, 5979]

end

select * from arrays array join day, _asc, _desc

7.81KB processed, 1000 rows x 3 columns. This query took 0.55ms

· View chart

day Date	_asc UInt64	_desc UInt64
2021-01-28	0	499500
2021-01-29	45	499455
2021-01-30	561	498939
2021-01-31	1653	497847
2021-02-01	3321	496179
2021-02-02	5565	493935
2021-02-03	8385	491115
2021-02-04	11781	487719
2021-02-05	15753	483747
2021-02-06	20301	479199
2021-02-07	25425	474075
2021-02-08	31125	468375
2021-02-09	37401	462099
2021-02-10	44253	455247
2021-02-11	51681	447819
2021-02-12	59685	439815
2021-02-13	68265	431235
2021-02-14	77421	422079
2021-02-15	87153	412347
2021-02-16	97461	402039
2021-02-17	108345	391155
2021-02-18	119805	379695
2021-02-19	131841	367659
2021-02-20	144453	355047
2021-02-21	157641	341859
2021-02-22	171405	328095
2021-02-23	185745	313755
2021-02-24	200661	298839
2021-02-25	216153	283347
2021-02-26	232221	267279
2021-02-27	248865	250635
2021-02-28	266085	233415
2021-03-01	283881	215619
2021-03-02	302253	197247
2021-03-03	321201	178299
2021-03-04	340725	158775
2021-03-05	360825	138675
2021-03-06	381501	117999
2021-03-07	402753	96747
2021-03-08	424581	74919
2021-03-09	446985	52515
2021-03-10	469965	29535
2021-03-11	493521	5979

xoel_at_tinybird / Snapshot #331f3

original_query

grouparray

arrays

end