ClickHouse中通过字段分组并获取分组排序后每组的前N条数据

72 阅读 0 评论 48 点赞

我是靠谱客的博主哭泣悟空，最近开发中收集的这篇文章主要介绍ClickHouse中通过字段分组并获取分组排序后每组的前N条数据，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

下面以股票交易数据为例子：

需求为获取每个股票最后五个交易日的交易数据。ClickHouse不提供窗口函数，那么解决思路变为先找到每个股票的最后五个交易日期，然后再通过股票代码和交易日期关联源表获得相应详细交易数据。

怎嘛解决？

思路：1、按照股票分组，并按照股票和时间排序。

2、groupArray函数分组数据合并函数，arrayJoin函数可以将array中的元素展开为行

groupArray函数用法说明：

groupArray(x)和groupArray(max_size)(x)，数组中元素的顺序就是分组数据中指定的顺序，groupArray(max_size)(x)可以指定数组的大小，max_size即为需求中前N条的数值；

具体实现的SQL语句

SELECT symbol,name,date,open,low,high,close,volume,amount
FROM
  (
    SELECT symbol,name,date,open,low,high,close,volume,amount
    FROM stock_daily
  )ALL INNER JOIN(
    SELECT symbol,arrayJoin(dates)AS date
    FROM
      (
        SELECT symbol,groupArray(5)(date)AS dates
        FROM
          (
            SELECT symbol,date
            FROM stock_daily
            ORDER BY symbol,date DESC
          )
        GROUP BY symbol
      )
    WHERE dates[1]< toDate('2018-03-16') -- 过滤2018年3月16日停牌中的股票，ClickHouse中数组元素序号从1开始。
  )USING symbol,date

SQL实现说明：按照需要分组的字段分组，按照字段排序，然后对非分组字段拼接成数组数据，并按顺序截取数据的前N条数据。最后外层套一层查询，将数组字段拆分成行，就实现了该功能。

系统中应用功能：

实现统计一段时间内区域内每个地铁站的上车总人数，并截取分组排序后每组的前100个。

select 
  ts.start_region_code ,
  ts.up_station_name,
  arrayJoin( ts.arr_val) as arr_val,
  arrayJoin( ts.total) as total
from 
(SELECT 
  start_region_code,
  up_station_name,
  groupArray(100)(down_station_name) as arr_val,
  groupArray(100)(total) as total,
  arrayEnumerate(arr_val) as row_number
 from (
  select
   start_region_code,
   up_station_name,
   down_station_name,
   COUNT(*) as total
  from modular.bus_od_base_all boba
  where
   up_time >='2020-11-01 00:00:00'
   and up_time <'2020-11-10 06:00:00'
   and down_time >='2020-11-01 00:00:00'
   and down_time <'2020-11-10 06:00:00'
  GROUP by start_region_code,up_station_name,down_station_name
  order by start_region_code,up_station_name, total DESC
 )
 group by start_region_code,up_station_name
 order by start_region_code,up_station_name) ts
 where 1=1 order  by ts.start_region_code,total DESC

以上内容转载于：https://www.jianshu.com/p/c12f6a56a316