Остатки на дату

[Родион Горбачев]

Добрый день.

Имею таблицу изменения мест хранения вещей:

:) CREATE TABLE events

( 

id Int64

, place_id Int32

, dt DateTime

, dt_date Date

, article Int64

) ENGINE = MergeTree(dt_date, (dt, place_id, article), 8192)

В таблице 10 миллиардов записей. Вот часть из них:

id | place_id         | dt | dt_date | article |

---------------------------------------------------------

0 | 2 |1998-01-01 |1998-01-01 | 50 |

1 | 1 |2017-01-01 |2017-01-01 | 100 |

2 | 2 |2017-02-02 |2017-02-02 | 100 |

3 | 3 |2017-03-03 |2017-03-03 | 100 |

Нужно увидеть количество вещей на месте 2 на момент dt=2017-04-04 (должно быть 1):

Указанный ниже запрос возвращает не то что нужно:

 

select

article

, dt

from events

where place_id = 2

and dt <= '2017-04-04'

order by dt desc

limit 1 by article

Видно, что article = 100 на момент времени '2017-04-04' лежит на месте 3.

Прошу помощи - как узнать количество вещей на '2017-04-04' на месте 2 ?

[Николай Кочетов]

Добрый день!

Видмо, проблема в условии dt <= '2017-04-04'. В этом случае строка '2017-04-04' должна преобразоваться в DateTime '2017-04-04 00:00:00', то есть почти весть день 2017-04-04 фильтруется.
Должно сработать так:

select

article

, dt

from events

where place_id = 2

and toDate(dt) <= '2017-04-04'

order by dt desc

limit 1 by article

а интересующий Вас запрос я бы написал так:
select count() from events where toDate(dt) = '2017-04-04' and place_id = 2

Возможно, стоит добавить dt_date в начало первичного ключа и писать условие на него для того, чтобы запрос читал меньше данных.

вторник, 26 сентября 2017 г., 12:14:45 UTC+3 пользователь Родион Горбачев написал:

[Родион Горбачев]

Николай , спасибо за ответ.

Но суть вопроса не в этом.

Тот запрос , который я указал - возвращает 2 строки с id in (0, 2)

А должна возвращаться только одна - c id = 0.

Потому что на дату '2017-04-04 00:00:00' есть только один артикул на месте = 2

вторник, 26 сентября 2017 г., 14:03:18 UTC+3 пользователь Николай Кочетов написал:

[Николай Кочетов]

Прошу прощения, сразу не понял вопроса.

Можно попробовать так:
select argMax(article, dt), argMax(place_id, dt) as place max(dt) where dt <= '2017-04-04' group by id having place = 2

вторник, 26 сентября 2017 г., 14:09:54 UTC+3 пользователь Родион Горбачев написал:

[Konstantin]

Николай, попробую подключится к обсуждению.

Приведенный вами вариант приведет к сканированию таблицы, и результат будет вычисляться очень долго.

А есть ли более оптимальный вариант посмотреть состояние (остатки) на заданную дату?

В данном случае - "сколько объектов article находились в состоянии place=2 на дату 2017-04-04 00:00:00"

[Konstantin]

добавлю пример:

Предмет article=40 поступил на место place=7 в 98г, 

затем в январе 2017 его переместили на место 2

затем в мае 2017 его переместили на место 9

Если посмотреть на приведенную ниже таблицу, то дату-время 2017-04-04 00:00:00 на месте 2 находился два предмета (40, 90)

и предмет 70 выбыл до наступления этой даты

как оптимально, без сканирования таблицы определить, сколько предметов было на месте 2 на заданную дату?

может быть необходимо как-то по другому сформировать витрину с данными.

Причем, хочется посмотреть не просто остатки на дату, а сформировать динамику изменение остатков на местах по дням,

поэтому если расчет одного места на дату будет занимать минуты, динамику строить очень долго.

article |     dt     | place

    40  | 1998-01-01 |   7

    40  | 2017-01-01 |   2

    40  | 2017-05-20 |   9

    90  | 1998-01-01 |   7

    90  | 2017-01-01 |   2

    90  | 2017-05-20 |   9

    70  | 2017-01-01 |   2

    70  | 2017-02-01 |   9

[Николай Кочетов]

Есть несколько идей на этот счет:

1. Если убрать из запроса having, то получится статистика сразу по всем местам, которая будет считаться не сильно дольше статистики без having

2. Можно разделить информацию о том, что предмет переложили с места A на место B на 2 события: предмет убрали с места A и предмет положили на место B. После чего уже можно считать для каждого отдельного места изменение в количестве товара за произвольный промежуток времени (при этом отсечение по времени будет эффективно. если добавить place в первичный ключ, то и по нему будет отсечение. возможно, даже стоит переместить place в начало первичного ключа). Далее, можно периодически пересчитывать, сколько товара лежит на каждом месте в определенный момент времени (скажем, к полуночи каждого дня). Тогда количество товара на месте 2 ко времени 2017-04-04 10:00:00 - это количество товара на месте 2 ко времени 2017-04-04 00:00:00 (предподсчитано) + изменение в количестве в промежуток с 2017-04-04 10:00:00 до 2017-04-04 10:00:00.
Также, CollapsingMergeTree поможет экономить память, если сами события про перекладывание товара можно не запоминать.

четверг, 28 сентября 2017 г., 11:03:01 UTC+3 пользователь Konstantin написал:

[Родион Горбачев]

Да , второй вариант - это хороший вариант.

Плохо в нем одно - в процессе стронирования - придется обращаться к старым записям.

А если они в буфере КХ , то их можно потерять... (final очень медленный)

Есть еще один вариант - у каждой записи обозначить дату окончания "лежания" на месте хранения.

Возможно - в отдельной таблице и с типом ReplacingMergeTree - тоже обращение к старым записям.

Ну или "якорную модель" попытаться использовать...

четверг, 28 сентября 2017 г., 17:05:35 UTC+3 пользователь Николай Кочетов написал:

[Николай Кочетов]

Плохо в нем одно - в процессе стронирования - придется обращаться к старым записям.

То есть, если мы захотим переложить товар задним числом, то могут возникнуть проблемы? Тогде действительно все придется пересчитывать. Хотя, в общем-то, не все, а только сагрегированные записи после "заднего числа".
Есть еще один странный вариант: аггрегировать в AggregatingMergeTree с group by по товару и периоду времени, тогда можно будет дописывать в него как исправления, так и новые данные. Хотя, это может быть как дольше так и сложнее.


четверг, 28 сентября 2017 г., 18:25:19 UTC+3 пользователь Родион Горбачев написал:

[Родион Горбачев]

Нет , нет , Николай. Речь идет не об изменении задним числом.

Например , артикул лежал на месте 10 с 1-го января.

1 февраля - его переместили на место 20.

Очевидно , что для того , чтобы стронировать строку от 1-го января , нужно знать место на 1-е января - а это обращение к старым записям.

Например :

dt | qty | place_id|

------------------------------------------------------

2017-01-01 | 1 | 10 |

2017-02-01 | -1 | 10 |

2017-02-01 | 1 | 20 |

Например, такая таблица , под названием InventTrans присутствует в ERP DAX от Майкрософта - Им будет очень удобно пользоваться КХ. 

У нас же стронирующих записей нет - и придется изобретать нефиговый велосипед : )

Приведенный пример - очень простой. А что будет , если строка от 10 января будет находиться в кэше КХ ?

четверг, 28 сентября 2017 г., 22:55:41 UTC+3 пользователь Николай Кочетов написал:

[Николай Кочетов]

Стронирующая запись - это та, что с -1 в примере?
2017-02-01 | -1 | 10 |

У нас же стронирующих записей нет - и придется изобретать нефиговый велосипед : )

Кажется, наконец-то понял проблему. Если не знаем, с какого места перекладываем, то понять проблематично.
Пока единственная идея в том, чтобы кешировать инсерты, вставлять в целевую таблицу из одного места и делать селекты старых данных перед вставкой (в любом случае, вставлять нужно пачками). Что звучит не очень хорошо.
Возможно, проще научиться получать исходное место откуда-то извне :)

пятница, 29 сентября 2017 г., 10:08:39 UTC+3 пользователь Родион Горбачев написал:

[Родион Горбачев]

Да , именно она )

пятница, 29 сентября 2017 г., 13:56:24 UTC+3 пользователь Николай Кочетов написал:

[Konstantin]

Николай, а у вас считается время проведенное клиентом на сайте?

Если я все правильно понимаю, то у этих данных тоже есть начало посещения сайта и время закрытия страницы.

Вы как-то считаете динамику изменения посетителей на сайте?

пятница, 29 сентября 2017 г., 13:56:24 UTC+3 пользователь Николай Кочетов написал:

[Николай Кочетов]

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

Поискал по чату CH в телеграмме:

Alexey Milovidov, [01.09.17 23:08]
[In reply to Andrew 2002]
Визиты в Метрике вычисляются не из данных в ClickHouse, а с использованием отдельной структуры данных (базы), которая для этого сделана. В ClickHouse пишется результат - лог изменений визитов.

Это всё потому что считать визиты сложно.

И еще заодно нашел нечто похожее:

Andrey Pochtov, [30.08.17 09:45]
Всем привет. Помогите решить задачу в рамках СН.  Есть таблица по покупкам. В ней есть колонки order_id, order_datetime, client_id.  Необходимо для каждой строки покупки посчитать количество совершенных покупок клиентом до момента текущей покупки. Раньше мы в MySQL имели агрегационную таблицу, где для кажой покупки клиента,  подсчитывалось количество покупок ранее сделанное им же.  Не хочется делать какие-то агрегации в СН, ведь как раз от них мы и хотели отказаться с переходом аналитики на СН.  Помогите, пожалуйста

Alexey Milovidov, [30.08.17 17:44]
[In reply to Andrey Pochtov]
Полностью в рамках ClickHouse решить это сложно. Идеальный вариант - если при каждой покупке, вы уже знаете предыдущее количество покупок (из отдельной базы, например), и затем пишете в лог в ClickHouse это общее количество отдельным столбцом. Тогда у вас в таблице будут строчки на каждую покупку, которые также содержат информацию об общем количестве предыдущих покупок.

В Яндекс.Метрике так сделано для истории посетителей. В таблице с визитами, для каждого визита есть ещё столбцы, содержащие информацию о накопленной истории.

пятница, 29 сентября 2017 г., 15:56:11 UTC+3 пользователь Konstantin написал:

[Родион Горбачев]

В общем , нам удалось за приемлемое время получить результат. (Костя , спасибо !) 

Около 1 сек на 5 млрд - дохлого железа (9Гб+XeonCPU E7-4880 v2)

Для решения пришлось создать 2 материализованных представления.

Исходные данные:

id | place_id         | dt | dt_date | article |

----------------------------------------------------------------------------------------------

0 | 2 |1998-01-01 |1998-01-01 | 50 |

1 | 1 |2017-01-01 |2017-01-01 | 100 |

2 | 2 |2017-02-02 |2017-02-02 | 100 |

3 | 3 |2017-03-03 |2017-03-03 | 100 |

CREATE MATERIALIZED VIEW default.mv_events_place_dt 

(id Int64,  article Int32,  dt DateTime,  dt_date Date,  place_id Int64) 

ENGINE = MergeTree(dt_date, (place_id, dt), 8192) as 

select 

id

, article

, dt

, dt_date

, place_id 

from default.events 

CREATE MATERIALIZED VIEW default.events_dt_article 

( id Int64,  article Int32,  dt DateTime,  dt_date Date,  place_id Int64) 

ENGINE = MergeTree(dt_date, (dt, article), 8192) as 

select 

id

, article

, dt

, dt_date

, place_id

from default.events 

select 

dt

, article

, place_id

from 

(

select 

dt

, article

, place_id

from events_dt_article

prewhere 

dt between 

(

select 

min(mdt) 

from 

(

select 

max(dt) mdt

, article 

from mv_events_place_dt 

where place_id = 'my_place_id' 

and dt <= 'my_dt'

group by article

)

) 

and 'my_dt'

and article in 

(

select article 

from mv_events_place_dt 

where place_id = 'my_place_id' 

and dt <= 'my_dt' 

order by dt desc

limit 1 by article

)

order by dt desc 

limit 1 by article

)

where place_id = 'my_place_id'

пятница, 29 сентября 2017 г., 13:56:24 UTC+3 пользователь Николай Кочетов написал: