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

程序员文章站 2022-03-20 23:51:15

...

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，业务数据分行：

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

查询结果如下：

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

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

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

查询结果如下：

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