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

最新推荐文章于 2024-05-28 09:55:27 发布

wppwpp1

最新推荐文章于 2024-05-28 09:55:27 发布

阅读量3.5k

点赞数

分类专栏： clickhouse

本文链接：https://blog.csdn.net/wppwpp1/article/details/106930934

版权

clickhouse 专栏收录该内容

12 篇文章 3 订阅

订阅专栏

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

查询结果如下：

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

查询结果如下：