Analog of ROW_NUMBER() OVER(PARTITION BY)

[kot...@gmail.com]

Hi,

I'm trying to make query selecting popular events chains.

Does ClickHouse have something like Transact-SQL function ROW_NUMBER() OVER(PARTITION BY)?

Also, that kind of queries could be done if ClikHouse JOIN supports not only equal conditions in USING section.

I've tried to use rowNumberInAllBlocks() and rowNumberInBlock() functions but blocks seems to can't be able to defined manually.

Maybe there are some specific tricks that can be used to calculate most popular events chains?

[Alex Zatelepin]

Greetings!

There are no general-purpose window functions in ClickHouse at the moment. However there is a clause in SELECT statement that might help in your case: LIMIT n BY column - select first n rows for each value of column. Unfortunately it is undocumented at the moment, but here is a github issue with some comments and examples: https://github.com/yandex/ClickHouse/issues/111

Is LIMIT BY enough for your problem?

[kot...@gmail.com]

Thanks.

Actually, it isn't enough, but it seems to be very useful.

[voros...@phystech.edu]

Hello!

Are there any updates on window functions in ClickHouse? SUM() OVER(PARTITION BY) would be very useful 

вторник, 28 февраля 2017 г., 16:41:28 UTC+3 пользователь Alex Zatelepin написал:

[Denis Zhuravlev]

>Are there any updates on window functions in ClickHouse?

it depends, but generally no updates.

>SUM() OVER(PARTITION BY) would be very useful

it's the same as  sum() group by

[inv2004]

Denis,

Please find that the result is not the same, because OVER do not GROUP-BY result, but adds calculated value (calculated over group-by).

Regards,