Skip to content

Add aggregate function combinator -Resample#5590

Merged
alesapin merged 10 commits intoClickHouse:masterfrom
hczhcz:patch-6
Jun 25, 2019
Merged

Add aggregate function combinator -Resample#5590
alesapin merged 10 commits intoClickHouse:masterfrom
hczhcz:patch-6

Conversation

@hczhcz
Copy link
Contributor

@hczhcz hczhcz commented Jun 12, 2019 •
edited by filimonov
Loading

I hereby agree to the terms of the CLA available at: https://yandex.ru/legal/cla/?lang=en

For changelog. Remove if this is non-significant change.

Category (leave one):

  • New Feature

Short description (up to few sentences):

-Resample(begin,end,step)(..., key) can be appended to an aggregate function.The aggregate function will have an extra key argument, and it will run separately for rows which have keys in each slot [begin, begin + step), [begin + step, begin + 2 * step), ....

Detailed description (optional):

Example:

|uid   |age   |
|'A'   |16    |
|'B'   |30    |
|'C'   |35    |
|'D'   |48    |
|'E'   |62    |

SELECT groupArrayResample(0, 80, 10)(uid, age)
    = [[], ['A'], [], ['B', 'C'], ['D'], [], ['E'], []];

hczhcz
hczhcz commented Jun 12, 2019
View reviewed changes
@hczhcz
Copy link
Contributor Author

hczhcz commented Jun 18, 2019

TODO: Fix param handling

@alesapin
Copy link
Member

alesapin commented Jun 24, 2019

select arrayReduce('sumResample(1, 1, 0)', [10, 11, 12, 13, 14, 15, 16, 17, 18, 19], [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]); leads to Floating point exception and server crash https://pastebin.com/3UzexYKW.

alesapin
alesapin reviewed Jun 24, 2019
View reviewed changes
@alesapin
Copy link
Member

alesapin commented Jun 24, 2019

What is the main idea of the negative step and intervals? I don't understand how they work:

SELECT arrayReduce('sumResample(0, -1, -1)', [10], [0])

┌─arrayReduce('sumResample(0, -1, -1)', [10], [0])─┐
│ []                                               │
└──────────────────────────────────────────────────┘
SELECT arrayReduce('sumResample(0, -1, -1)', [10], [1])

┌─arrayReduce('sumResample(0, -1, -1)', [10], [1])─┐
│ []                                               │
└──────────────────────────────────────────────────┘
SELECT arrayReduce('sumResample(9, -1, -1)', [10], [0])                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                       
┌─arrayReduce('sumResample(9, -1, -1)', [10], [0])─┐                                                                                                                                                                                                                                                   
│ [0,0,0,0,0,0,0,0,0,10]                           │                                                                                                                                                                                                                                                   
└──────────────────────────────────────────────────┘   
SELECT arrayReduce('sumResample(0, -1, -1)', [10], [-0])                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                       
┌─arrayReduce('sumResample(0, -1, -1)', [10], [-0])─┐                                                                                                                                                                                                                                                   
│ [10]                                             │                                                                                                                                                                                                                                                   
└──────────────────────────────────────────────────┘

alesapin
alesapin requested changes Jun 24, 2019
View reviewed changes
Copy link
Member

@alesapin alesapin left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

.

@alesapin
Copy link
Member

alesapin commented Jun 24, 2019

Also error messages are unclear:

SELECT arrayReduce('sumResample(11, 10, 1)', [10], [0])

Received exception from server (version 19.9.1):
Code: 36. DB::Exception: Received from localhost:9000, ::1. DB::Exception: The range given in function sumResample contains too many elements.

@alesapin alesapin merged commit eff547c into ClickHouse:master Jun 25, 2019
@hczhcz hczhcz deleted the patch-6 branch June 26, 2019 03:49
@BayoNet BayoNet mentioned this pull request Jul 11, 2019
Merged
@BayoNet BayoNet mentioned this pull request Sep 20, 2019
Merged
@YoannBuch
Copy link

YoannBuch commented Nov 25, 2020 •
edited
Loading

Hello @hczhcz, very nice addition :)

How would one use -Resample in combination of quantileTimingWeighted?

This works (no defined level):

quantileTimingWeightedResample(1, 10, 1)(duration, count, t)

But how do we specify the level, e.g. 0.5 or 0.9?

Edit: this way, e.g. p90:

quantileTimingWeightedResample(0.9, 1, 10, 1)(duration, count, t)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@alesapin alesapin alesapin requested changes

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

4 participants

@hczhcz @alesapin @YoannBuch @alexey-milovidov