State machine user defined function support

[igor-suhorukov]

Use case
User has huge dataset and process description in form of state machine with transition between states activated by SQL logical expression. He wants mark each row in this dataset with state and/or its description.

Source dataset:

# \copy life from 'state_machine_example/src/main/resources/life.txt';

# select * from life;              
 name | age | desire_to_learn |             exams              
------+-----+-----------------+--------------------------------
 Вася |   1 | t               | 
 Вася |   2 | t               | 
 Вася |   3 | t               | 
 Вася |   4 | t               | 
 Вася |   5 | t               | 
 Вася |   6 | t               | 
 Вася |   7 | t               | 
 Вася |   8 | t               | 
 Вася |   9 | t               | 
 Вася |  10 | t               | 
 Вася |  11 | t               | 
 Вася |  12 | t               | 
 Вася |  13 | t               | 
 Вася |  14 | t               | 
 Вася |  15 | t               | выпускные экзамены в 9 классе
 Вася |  16 | t               | 
 Вася |  17 | t               | выпускные экзамены в 11 классе
 Вася |  18 | t               | вступительные экзамены в ВУЗ
 Вася |  19 | t               | 
 Вася |  20 | t               | 
 Вася |  21 | t               | 
 Вася |  22 | t               | защита диплома
 Вася |  23 | t               | 
 Вася |  24 | t               | 
 Вася |  25 | t               | 
 Вася |  26 | t               | 
 Вася |  27 | f               | 
 Вася |  28 | f               | 
 Вася |  29 | f               | 
 Вася |  30 | f               | 

State-transition table as input:

0	0	появился на свет
0	1	ходит в детсадик	age>=3 and desire_to_learn
1	2	ученик начальной школы	age>=7 and exams is null and desire_to_learn
2	3	ученик средней школы	age>=11 and exams is null and desire_to_learn
3	4	основное общее образование	age>=15 and exams='выпускные экзамены в 9 классе'
4	5	ученик старших классов	age>=15 and desire_to_learn
5	6	среднее общее образование	age>=17 and exams='выпускные экзамены в 11 классе'
4	7	учащийся техникума	age>=16 and exams='вступительные экзамены в техникум' and desire_to_learn
7	8	среднее профессиональное образование	age>=18 and exams='защита диплома'
6	9	учащийся института	age>=18 and exams='вступительные экзамены в ВУЗ' and desire_to_learn
8	9	учащийся института	age>=18 and exams='вступительные экзамены в ВУЗ' and desire_to_learn
9	10	оконченное высшее образование	age>=22 and exams='защита диплома' and desire_to_learn
10	11	аспирант	age>=22  and exams='экзамены в аспирантуру' and desire_to_learn
11	12	претендент на ученую степень	age>=24 and exams='кандидатский минимум' and desire_to_learn
12	13	кандидат наук	age>=25 and exams='защита диссертации' and desire_to_learn
4	14	больше не обучается	not(desire_to_learn)
6	14	больше не обучается	not(desire_to_learn)
8	14	больше не обучается	not(desire_to_learn)
10	14	больше не обучается	not(desire_to_learn)
13	14	больше не обучается	not(desire_to_learn)

And expected dataset:

 name | age | desire_to_learn |             exams              |             state             
------+-----+-----------------+--------------------------------+-------------------------------
 Вася |   1 | t               |                                | появился на свет
 Вася |   2 | t               |                                | появился на свет
 Вася |   3 | t               |                                | ходит в детсадик
 Вася |   4 | t               |                                | ходит в детсадик
 Вася |   5 | t               |                                | ходит в детсадик
 Вася |   6 | t               |                                | ходит в детсадик
 Вася |   7 | t               |                                | ученик начальной школы
 Вася |   8 | t               |                                | ученик начальной школы
 Вася |   9 | t               |                                | ученик начальной школы
 Вася |  10 | t               |                                | ученик начальной школы
 Вася |  11 | t               |                                | ученик средней школы
 Вася |  12 | t               |                                | ученик средней школы
 Вася |  13 | t               |                                | ученик средней школы
 Вася |  14 | t               |                                | ученик средней школы
 Вася |  15 | t               | выпускные экзамены в 9 классе  | основное общее образование
 Вася |  16 | t               |                                | ученик старших классов
 Вася |  17 | t               | выпускные экзамены в 11 классе | среднее общее образование
 Вася |  18 | t               | вступительные экзамены в ВУЗ   | учащийся института
 Вася |  19 | t               |                                | учащийся института
 Вася |  20 | t               |                                | учащийся института
 Вася |  21 | t               |                                | учащийся института
 Вася |  22 | t               | защита диплома                 | оконченное высшее образование
 Вася |  23 | t               |                                | оконченное высшее образование
 Вася |  24 | t               |                                | оконченное высшее образование
 Вася |  25 | t               |                                | оконченное высшее образование
 Вася |  26 | t               |                                | оконченное высшее образование
 Вася |  27 | f               |                                | больше не обучается
 Вася |  28 | f               |                                | больше не обучается
 Вася |  29 | f               |                                | больше не обучается
 Вася |  30 | f               |                                | больше не обучается

Describe the solution you'd like

After use this function source dataset marked with "state" value from requested function.

Describe alternatives you've considered

Enrich raw data with states in Apache Spark/PySpark but it is one more additional component in analytical flow.

Additional context

This approach described in this post for PostgreSQL and implemented by using user defined aggregate function.
https://github.com/igor-suhorukov/state_machine_example

[igor-suhorukov]

@alexey-milovidov @blinkov is there any workaround in ClickHouse for this?

[UnamedRus]

windowfunnel, but it's linear

[igor-suhorukov]

@UnamedRus thanks, but windowfunnel is not solve this task...

[alexey-milovidov]

The workaround can be - using executable table functions to iterate over a table:
https://clickhouse.com/docs/en/engines/table-functions/executable

[igor-suhorukov]

@alexey-milovidov thank you, I know this approach as I mentioned in my post. This table function involve expensive serialization/deserialization of all column in query, also it required installation of user script by ClickHouse admin team