ClickHouse支持row_number函数，相关的demo如下

109 阅读 0 评论 72 点赞

我是靠谱客的博主完美芝麻，这篇文章主要介绍ClickHouse支持row_number函数，相关的demo如下，现在分享给大家，希望可以做个参考。

1，ck对窗口函数支持不是太好，只是有限的支持。

表结构如下：

CREATE TABLE warehouse.c_click_common_16_cls (`id` String, `app_version` Nullable(String), `guid` Nullable(String), `imei` Nullable(String), `mac` Nullable(String), `channel_id` Nullable(String), `tel_opr` Nullable(String), `phone_brand` Nullable(String), `phone_type` Nullable(String), `osversion` Nullable(String), `platform` Int32, `lang` Nullable(String), `session_id` Nullable(String), `client_time` Int64, `user_id` Nullable(String), `event_id` Nullable(String), `client_ip` Nullable(String), `net_type` Int32, `body_title` Nullable(String), `body_id` Nullable(String), `body_type` Nullable(String), `body_car_id` Nullable(String), `body_city_name` Nullable(String), `app_date` Int32, `create_time` Date, `body_index` Nullable(String)) ENGINE = Distributed(crm_4shards_1replicas, warehouse, c_click_common_16, rand())

2，业务数据分行：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select  versiontype,platform,date,
arrayJoin(arr_val)
--arrayJoin(row_number)
from (
select versiontype,platform,date,
groupArray(4)(uv) AS arr_val,
arrayEnumerate(4)(arr_val) as row_number
from (
    select 
    case when app_version>='3.0.0' then 'new'
          else 'old' end as versiontype,
          platform,
          toDate(client_time/1000) date,
          body_title, 
     count(distinct imei) uv
    from warehouse.c_click_common_16_cls
    where toDate(client_time/1000)='2020-06-22'
    and body_title not in('xxxx')
    group by versiontype,
          platform,
          toDate(client_time/1000),
          body_title
    order by uv desc) m
group by versiontype,platform,date
) c

查询结果如下：

3，针对结果数据进行分行

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select  versiontype,platform,date,
arrayJoin(arr_val)
--arrayJoin(row_number)
from (
select versiontype,platform,date,
groupArray(4)(uv) AS arr_val,
arrayEnumerate(4)(arr_val) as row_number
from (
    select 
    case when app_version>='3.0.0' then 'new'
          else 'old' end as versiontype,
          platform,
          toDate(client_time/1000) date,
          body_title, 
     count(distinct imei) uv
    from warehouse.c_click_common_16_cls
    where toDate(client_time/1000)='2020-06-22'
    and body_title not in('xxxx')
    group by versiontype,
          platform,
          toDate(client_time/1000),
          body_title
    order by uv desc) m
group by versiontype,platform,date
) c