RETENTION

SinceVersion 1.2.0

RETENTION

Description

Syntax

retention(event1, event2, ... , eventN);

The retention function takes as arguments a set of conditions from 1 to 32 arguments of type UInt8 that indicate whether a certain condition was met for the event. Any condition can be specified as an argument.

The conditions, except the first, apply in pairs: the result of the second will be true if the first and second are true, of the third if the first and third are true, etc.

Arguments

event — An expression that returns a UInt8 result (1 or 0).

Returned value

The array of 1 or 0.

1 — Condition was met for the event.

0 — Condition wasn’t met for the event.

example

1. DROP TABLE IF EXISTS retention_test;

3. CREATE TABLE retention_test(
4. `uid` int COMMENT 'user id',
5. `date` datetime COMMENT 'date time'
6. )
7. DUPLICATE KEY(uid)
8. DISTRIBUTED BY HASH(uid) BUCKETS 3
9. PROPERTIES (
10. "replication_num" = "1"
11. );

13. INSERT into retention_test (uid, date) values (0, '2022-10-12'),
14. (0, '2022-10-13'),
15. (0, '2022-10-14'),
16. (1, '2022-10-12'),
17. (1, '2022-10-13'),
18. (2, '2022-10-12');

20. SELECT * from retention_test;

22. +------+---------------------+
23. | uid  | date                |
24. +------+---------------------+
25. |    0 | 2022-10-14 00:00:00 |
26. |    0 | 2022-10-13 00:00:00 |
27. |    0 | 2022-10-12 00:00:00 |
28. |    1 | 2022-10-13 00:00:00 |
29. |    1 | 2022-10-12 00:00:00 |
30. |    2 | 2022-10-12 00:00:00 |
31. +------+---------------------+

33. SELECT
34. uid,
35. retention(date = '2022-10-12')
36. AS r
37. FROM retention_test
38. GROUP BY uid
39. ORDER BY uid ASC;

41. +------+------+
42. | uid  | r    |
43. +------+------+
44. |    0 | [1]  |
45. |    1 | [1]  |
46. |    2 | [1]  |
47. +------+------+

49. SELECT
50. uid,
51. retention(date = '2022-10-12', date = '2022-10-13')
52. AS r
53. FROM retention_test
54. GROUP BY uid
55. ORDER BY uid ASC;

57. +------+--------+
58. | uid  | r      |
59. +------+--------+
60. |    0 | [1, 1] |
61. |    1 | [1, 1] |
62. |    2 | [1, 0] |
63. +------+--------+

65. SELECT
66. uid,
67. retention(date = '2022-10-12', date = '2022-10-13', date = '2022-10-14')
68. AS r
69. FROM retention_test
70. GROUP BY uid
71. ORDER BY uid ASC;

73. +------+-----------+
74. | uid  | r         |
75. +------+-----------+
76. |    0 | [1, 1, 1] |
77. |    1 | [1, 1, 0] |
78. |    2 | [1, 0, 0] |
79. +------+-----------+

keywords

RETENTION