ClickHouse：减少元组数组以计算平均会话时间

Question

ClickHouse：减少元组数组以计算平均会话时间

4

我有一个事件数组。每个事件都表示为元组，并包含session_id和datetime：

[
 ('aa', '2020-11-08 01:00:01'),
 ('aa', '2020-11-08 01:00:03'),
 ('aa', '2020-11-08 01:00:05'),
 ('ab', '2020-11-09 01:00:00'),
 ('ab', '2020-11-09 01:00:05'),
 ('ab', '2020-11-09 01:00:15')
]

我需要使用这个数组来计算平均会话时间。

因此，我需要将这个数组转换为新的数组[(会话ID，最小日期时间，最大日期时间)]

[
 ('aa', '2020-11-08 01:00:01', '2020-11-08 01:00:05'),
 ('ab', '2020-11-09 01:00:00', '2020-11-09 01:00:15')
]

然后计算每个session_id的session_time [(session_id, max(datetime) - min(datetime))]

[
 ('aa', 4),
 ('ab', 15)
]

然后计算平均会话时间 ((4+15)/2) = 9.5

如何最好地做到这一点？

- nikopol

2个回答

1

minMap(data.1, data.2)

select minMap(data.1, data.2) from (
SELECT [('aa', '2020-11-08 01:00:01'), 
        ('aa', '2020-11-08 01:00:03'), 
        ('aa', '2020-11-08 01:00:05'), 
        ('ab', '2020-11-09 01:00:00'), 
        ('ab', '2020-11-09 01:00:05'), 
        ('ab', '2020-11-09 01:00:15')] AS data)

┌─minMap(tupleElement(data, 1), tupleElement(data, 2))────────┐
│ (['aa','ab'],['2020-11-08 01:00:01','2020-11-09 01:00:00']) │
└─────────────────────────────────────────────────────────────┘

select minMap(data.1, data.2).2 as x, maxMap(data.1, data.2).2 as y ,
   arrayMap(i,j -> toDateTime(j)-toDateTime(i), x,y) r,
   arrayReduce('avg', r) z
from (
SELECT [('aa', '2020-11-08 01:00:01'), 
        ('aa', '2020-11-08 01:00:03'), 
        ('aa', '2020-11-08 01:00:05'), 
        ('ab', '2020-11-09 01:00:00'), 
        ('ab', '2020-11-09 01:00:05'), 
        ('ab', '2020-11-09 01:00:15')] AS data)
        
┌─x─────────────────────────────────────────────┬─y─────────────────────────────────────────────┬─r──────┬───z─┐
│ ['2020-11-08 01:00:01','2020-11-09 01:00:00'] │ ['2020-11-08 01:00:05','2020-11-09 01:00:15'] │ [4,15] │ 9.5 │
└───────────────────────────────────────────────┴───────────────────────────────────────────────┴────────┴─────┘

- Denny Crane

谢谢你的回答。Clickhouse非常棒。但是如果没有伟大的社区，没有人会知道它。 - nikopol

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- vladimir · Accepted Answer

为了获得所需的结果，我会使用数据的关系表示而不是数组。 arrayJoin 可以将数组转换为关系。

SELECT avg(duration)
FROM 
(
    SELECT max(time) - min(time) AS duration
    FROM 
    (
        SELECT 
            data.1 AS id,
            toDateTime(data.2) AS time
        FROM 
        (
            SELECT arrayJoin([('aa', '2020-11-08 01:00:01'), ('aa', '2020-11-08 01:00:03'), ('aa', '2020-11-08 01:00:05'), ('ab', '2020-11-09 01:00:00'), ('ab', '2020-11-09 01:00:05'), ('ab', '2020-11-09 01:00:15')]) AS data
        )
    )
    GROUP BY id
)
/*
┌─avg(duration)─┐
│           9.5 │
└───────────────┘
*/

基于数组的决策。请注意，它可能比基于关系的决策慢得多（在选择最佳方法之前，请检查两者）。通过使用arrayReduceInRanges函数可以改进此实现。

SELECT 
    arraySort(x -> (x.1), data) AS sorted_array,
    arraySplit((x, y) -> y, sorted_array, arrayMap((x, i) -> if(i = 1, 1, if((x.1) = ((sorted_array[i - 1]).1), 0, 1)), sorted_array, arrayEnumerate(sorted_array))) AS session_arrays,
    arrayMap(arr -> arrayReduce('min', arrayMap(x -> (x.2), arr)), session_arrays) AS min_session_times,
    arrayMap(arr -> arrayReduce('max', arrayMap(x -> (x.2), arr)), session_arrays) AS max_session_times,
    arrayReduce('avg', arrayMap((x, y) -> (y - x), min_session_times, max_session_times)) AS avg
FROM 
(
    SELECT [('aa', toDateTime('2020-11-08 01:00:01')), ('aa', toDateTime('2020-11-08 01:00:03')), ('aa', toDateTime('2020-11-08 01:00:05')), ('ab', toDateTime('2020-11-09 01:00:00')), ('ab', toDateTime('2020-11-09 01:00:05')), ('ab', toDateTime('2020-11-09 01:00:15'))] AS data
)
/*
Row 1:
──────
sorted_array:      [('aa','2020-11-08 01:00:01'),('aa','2020-11-08 01:00:03'),('aa','2020-11-08 01:00:05'),('ab','2020-11-09 01:00:00'),('ab','2020-11-09 01:00:05'),('ab','2020-11-09 01:00:15')]
session_arrays:    [[('aa','2020-11-08 01:00:01'),('aa','2020-11-08 01:00:03'),('aa','2020-11-08 01:00:05')],[('ab','2020-11-09 01:00:00'),('ab','2020-11-09 01:00:05'),('ab','2020-11-09 01:00:15')]]
min_session_times: ['2020-11-08 01:00:01','2020-11-09 01:00:00']
max_session_times: ['2020-11-08 01:00:05','2020-11-09 01:00:15']
avg:               9.5
*/