Выбор последних N с группировкой и нумерация в select

[Алексей Белоусов]

Здравствуйте.

Кажется, у меня небольшие проблемы с SQL. Подскажите пожалуйста, где я не прав.

Есть таблица:

CREATE TABLE last5table (tp UInt16, value UInt16, d Date) ENGINE = MergeTree(d, (tp), 8126);

Данные:

SELECT *

FROM last5table 

┌─tp─┬─value─┬─────d─┐

│  1    │     4        │ 2000-04-05 │

│  1.   │     1.       │ 2000-02-01 │

│  1.   │     2.       │ 2000-02-04 │

│  1.   │     3.       │ 2000-02-05 │

│  2.   │     4.       │ 2000-04-05 │

│  2.   │     1.       │ 2000-02-01 │

│  2.   │     2.       │ 2000-02-04 │

│  2.   │     3.       │ 2000-02-05 │

│  3.   │     4        │ 2000-04-05 │

│  3.   │     2        │ 2000-02-04 │

│  4.   │     1.       │ 2000-02-01 │

│  4.   │     3        │ 2000-02-05 │

Хочется выбрать, допустим, последние N элементов для каждого tp.

Пишу такое:

select tp, count(), groupArray(value), groupArray(d) FROM last5table GROUP BY tp LIMIT 2 BY tp;

Получаю, такое:

┌─tp─┬─count()─┬─groupArray(value)─┬─groupArray(d)─────────────────────────────────────────┐

│  1 │       4 │ [1,2,3,4]         │ ['2000-02-01','2000-02-04','2000-02-05','2000-04-05'] │

│  2 │       4 │ [1,2,3,4]         │ ['2000-02-01','2000-02-04','2000-02-05','2000-04-05'] │

│  3 │       2 │ [2,4]             │ ['2000-02-04','2000-04-05']                           │

│  4 │       2 │ [1,3]             │ ['2000-02-01','2000-02-05']                           │

Хочется получить такое:

┌─tp─┬─count()─┬─groupArray(value)─┬─groupArray(d)─────────────────────────────────────────┐

│  1 │       2 │ [1,2]             │ ['2000-02-01','2000-02-04']

│  2 │       2 │ [1,2]             │ ['2000-02-01','2000-02-04']

│  3 │       2 │ [2,4]             │ ['2000-02-04','2000-04-05']                           │

│  4 │       2 │ [1,3]             │ ['2000-02-01','2000-02-05']                           │

Еще, когда пытаюсь отсортировать по d, то получаю ошибку, что выбираемый столбец не под агрегацией:

select tp, count(), groupArray(value), groupArray(d) FROM last5table GROUP BY tp ORDER BY d LIMIT 2 BY tp;

Code: 215. DB::Exception: Received from clickhouse-server:9000, 172.17.0.4. DB::Exception: Column d is not under aggregate function and not in GROUP BY..

Не понимаю, что я делаю не так :(

Второй вопрос. Можно ли сделать материализованное представление и пронумеровать все строки согласно сортировки?

Т.е. для данных приведенных выше и запроса:

SELECT 

    tp, 

    argMax(value, d)

FROM last5table 

WHERE tp != 1

GROUP BY tp

ORDER BY tp ASC

┌─tp─┬─argMax(value, d)─┐

│  2 │                4 │

│  3 │                4 │

│  4 │                3 │

└────┴──────────────────┘

Хочется видеть дополнительный столбец number:

┌-number-─tp─┬─argMax(value, d)─┐

│  1    | 2  │                4 │

│  2    | 3  │                4 │

│  3    | 4  │                3 │

└───---─┴────-----──────────────┘

И если появится данные с tp = 0, чтобы материализованное представление перестроилось и нумерация осталась валидна (для tp=0 number=1, для tp=2 number=2 и т.п.).

Подскажите куда копать :)

[Denis Zhuravlev]

1. последние по какому критерию? на картинке вроде первые? LIMIT 2 BY tp -- это для строк, не для массивов.

select tp, count(), arraySlice(groupArray(value),1,2), arraySlice(groupArray(d),1,2) FROM last5table GROUP BY tp
arraySort добавить по вкусу.
или
select tp, (value), (d) FROM last5table order by tp,d  LIMIT 2 BY tp;

второй вопрос я не читал (не стоит мешать в кучу несколько вопросов, запутывает слабые мозги).

[Алексей Белоусов]

Спасибо! 

select tp, (value), (d) FROM last5table order by tp,d  LIMIT 2 BY tp; - то что мне нужно было :)

пятница, 11 мая 2018 г., 16:04:21 UTC+3 пользователь Denis Zhuravlev написал:

[Denis Zhuravlev]

>

Второй вопрос. Можно ли сделать материализованное представление и пронумеровать все строки согласно сортировки?

Вы очень долго шли по лесу, заблудились, вышли на полянку и спросили у ежика как пройти в библиотеку.
На полянке ежику очень тяжело ответить на этот вопрос. Я как ежик советую прочитать про engine replacingmergetree.

On Friday, 11 May 2018 08:59:53 UTC-3, Алексей Белоусов wrote: