ClickHouse

ClickHouse

• 易点天下基于 StarRocks 构建实时离线一体的数仓方案

retention 函数分析用户留存

select user,
       retention(
           toDate(addDays(ts, 2)) = '2023-04-12',
           toDate(addDays(ts, 1)) = '2023-04-12',
           toDate(ts) = '2023-04-12'
       )
from action_event_message
where ts > '2023-04-01 00:00:00'
group by user;

连表查 - 传统方法

with
  dateDiff('second', toDateTime(aem.ts), toDateTime(ju.ts)) as diff
select
    *,
    diff
from
    (
        select
        user,
        ts
        from
            action_event_message
        where
            ts between '2023-04-11 00:00:00' and '2023-04-12 00:00:00'
          and action = 'joinRoom'
    ) as ju
        left join action_event_message aem on ju.user = aem.user
        and aem.action = 'publish'
where
    aem.ts between '2023-04-11 00:00:00'
        and '2023-04-12 00:00:00'
  and diff between 0
    and 10

windowFunnel 窗口函数

select
    room,
    user,
    min(ts),
    any(trace_id),
    windowFunnel(60)(
        toDateTime(ts),
        action = 'signalChannelConnect',
        action = 'joinRoom',
        action = 'leaveRoom'
    ) as events
from
    action_event_message
where
    ts between '2023-04-11 00:00:00'
  and '2023-04-12 00:00:00'
group by
    room,
    user;

• 使用毫秒

select
    room,
    user,
    min(ts),
    any(trace_id),
    windowFunnel(60000)(
    toUInt64(toUnixTimestamp64Milli(ts)),
    action = 'joinRoom',
    action = 'publish',
    action = 'leaveRoom'
    ) as events
from
    action_event_message
where
    user = '12310'
  and ts between '2023-04-11 00:00:00'
  and '2023-04-12 00:00:00'
group by
    room,
    user
order by
    events;