ClickHouse row_number() over (partition by)的几种实现方法
hive中有row_number() over (partition by)函数，可以一句SQL实现想要的排序，在ClickHouse中有很多种实现方式，本篇就介绍一下几种方法。
目录
1.row_number排序
2.row_number排序后取出rank=1的结果
3.特殊场景
1.row_number排序
HIVE中写法：
select number,
row_number() over (partition by number order by time desc) as rank
from table a
GROUP BY number
ClickHouse写法：
select number,
groupArray(time) AS arr_val,
arrayEnumerate(arr_val) as row_number
from (select distinct orderid as number,
toDate(operatetime) as time
from table
order by time desc
) a
GROUP BY number
2.row_number排序后取出rank=1的结果
hive写法：
select orderid
from (select orderid,
row_number() over(partition by orderid order by datachange_lasttime desc) as row_num
from table
where d = '${CurrentDate}'
) a
where row_num = 1;
ClickHouse写法：
方法1：利用groupArray
select orderid,
groupArray(1)(datachange_lasttime) as dates
from (select orderid,
datachange_lasttime
from table
ORDER BY orderid, datachange_lasttime desc
) a
group by orderid
方法2：利用max函数实现倒序，如果正序使用min函数即可
select orderid,
max(datachange_lasttime) as datachange_lasttime
from table
group by orderid
方法3：利用rowNumberInAllBlocks函数
select orderid, status
from (select orderid, status, rowNumberInAllBlocks() as rank
from (select orderid, status, datachange_lasttime
from table
order by orderid, datachange_lasttime desc
) a
) b LIMIT 1 BY orderid
方法4：利用arrayEnumerate函数
select orderid
from (select orderid,
groupArray(datachange_lasttime) AS arr_val,
arrayEnumerate(arr_val) as row_number
from (select orderid, datachange_lasttime
from table
order by datachange_lasttime desc
) a
GROUP BY number
) b
where row_number = 1
3.特殊场景
要求：
对于以下场景，需要按照orderid分组，按照日期倒序，取最新一条，若日期一致，则随机取一条作为结果即可
hive写法：
select orderid from (select orderid, status, row_number() over(partition by orderid order by datachange_lasttime desc) as row_num from table where d = '${CurrentDate}' ) as b where row_num = 1
ClickHouse写法：
通过上面的案例，我们很容易想到，把上面的结果作为一个子表，与原表进行关联，只是这样关联，随便举一个关联的写法：
select a.orderid as orderid_a, a.status as status from olap_htlmaindb.tmp_ord_orders_status_s_pre a inner join (select orderid, groupArray(1)(datachange_lasttime) as dates from (select orderid, datachange_lasttime from table ORDER BY orderid, datachange_lasttime desc ) a group by orderid) b on a.orderid = b.orderid and cast(a.datachange_lasttime as String) = cast(b.dates [ 1 ] as String)
这里我们是先把符合要求的orderid和时间取出来，再回去关联，取出需要的列，因为这些函数都有一个缺点是只能有partition by的字段和排序字段，不能有其他字段，所以要返回关联，所以上面四种方法，ininer join原表，都不能解决上面案例的问题。
这里就想到了LIMIT 1 BY这个方法，这个方法其实是最有效的，如下：
select orderid, status, datachange_lasttime from table order by orderid, datachange_lasttime desc LIMIT 1 BY orderid