Clickhouse SQL问题
发布于 6 个月前 作者 guzhenping 1416 次浏览 来自 实战经验

最近在用Clickhouse的SQL处理之前数仓经常会遇到的场景,发现有点不好用。

第一:缺乏window函数,很多统计不变去做,比如:分组累计,分组总计。虽然提供runningAccumulate()函数,但是做不出来想要的效果。

第二:这些在Hive中常见的高级函数,找不到对应。如:

  • Rank()
  • Dense_rank()
  • Row_number()
  • Percent_rank()
  • Cume_dist()
  • lag()
  • lead()
  • first_value()
  • last_value()

求问各位大佬,可有比较好的解决方案。给点SQL DEMO,不胜感谢!

6 回复

clickhouse本来就主要用于分布式查询,你要做统计的话用其他的吧

请具体说一个你要解决的问题吧。 ClickHouse的函数未必能和Hive的一一对应,但是适当组合ClickHouse的函数,可能和组合一系列Hive的函数达到同样的目的,解决你的问题。

放个案例吧。

-- 建表
CREATE TABLE default.test_happysql ( dt Date,  level1 String,  t_month Int32,  rev_rmb Int32) ENGINE = MergeTree(dt, dt, 8192)

-- 插入模拟的测试数据
insert into test_happysql(dt, level1, t_month, rev_rmb)   
Values ('2018-05-07', '代理商', 1, 3302895),('2018-05-07', '代理商', 2, 5706791),('2018-05-07', '代理商', 3, 12773293),('2018-05-07', '代理商', 4, 11495202),('2018-05-07', '代理商', 5, 12218673),('2018-05-07', '代理商', 6, 15383688),('2018-05-07', '地推', 1, 818915),('2018-05-07', '地推', 2, 1794729),('2018-05-07', '地推', 3, 3318292),('2018-05-07', '地推', 4, 2800776),('2018-05-07', '地推', 5, 2664450),('2018-05-07', '地推', 6, 2931170),('2018-05-07', '电销', 1, 2530506),('2018-05-07', '电销', 2, 3669839),('2018-05-07', '电销', 3, 5799906),('2018-05-07', '电销', 4, 5257448),('2018-05-07', '电销', 5, 5118635);

p1.png

如果想做出这种效果(数据仓库里常见的分组累计的技巧,一条SQL就可以处理出来。): p3.png

@kikanjuu 等诸位大神,这种处理在hive, spark, presto, impala都算好做,使用开窗函数。但是在clickhouse里,好困难啊

你是按照level1分组,还是按照tmonth=1-6分组?

另外为什么按照sum_1升序+sum_3降序来排序后,level1字段也恰好分组了。数据有这么巧合吗?

还是请你给一下用其他系统做出这个统计结果的完整SQL,或者详细描述下生成结果的逻辑。

嗨,@kikanjuu, 我少给一条测试数据,但是不影响这个场景。 解释下:

  • level1:销售渠道

  • t_month: 月份(这里只有半年的销售业绩)

  • rev_rmb: 当月金钱

  • sum_1:rev_rmb按照level1和t_month升序的累计

  • sum_2:所有rev_rmb之和

  • sum_3: rev_rmb按照level1和t_month降序的累计

  • sum_4:rev_rmb按level1分组t_month升序的累计

在Hive/Presto/Impala里,这么写:

select level1, t_month, rev_rmb,
  sum(rev_rmb) over (order by level1, t_month rows between unbounded preceding and current row) as sum1,
  sum(rev_rmb) over(order by level1, t_month rows between unbounded preceding and unbounded following) as sum_2, 
  sum(rev_rmb) over(order by level1, t_month rows between current row and unbounded following) as sum_3, 
  sum(rev_rmb) over(partition by level1 order by t_month rows between unbounded preceding and current row) as sum_4 
from test_happysql;
回到顶部