clickhouse 之 arrayJoin 函数、with子句以及 limit n by exp 的用法

clickhouse窗口函数

arrayJoin 函数

先把官方的文档贴上来。

这个函数是一个非常有用的函数。我们知道一般的函数不会去更改结果集的行数，只是计算每行的值。

对于聚合函数而言，他们将多行函数压缩到一行（fold 或者 reduce），而 arrayJoin 函数是将一行的结果展开到多行（unfold）。

需要说明的是：arrayJoin 函数中的参数是数组类型。

我们来看个例子：

SELECT arrayJoin([1,2,3] AS src) AS dst, "Hello", src;

分析，该结果有三列，第一列是将数组[1,2,3]展开到3行，第二列是Hello，第三列是src这个数组。所以最终查询到的结果是：

┌─dst─┬─\'Hello\'─┬─src─────┐
│   1 │ Hello     │ [1,2,3] │
│   2 │ Hello     │ [1,2,3] │
│   3 │ Hello     │ [1,2,3] │
└─────┴───────────┴─────────┘

再来看一个例子，下面的这个例子经常用于计算留存率。

SELECT  DISTINCT d,
        arrayJoin(array(d, d - 1, d - 3, d - 7)) dd,
        uid as uid2
FROM dws.dw_dau AS dau
prewhere d = yesterday() - 2;

分析：第二列的取值其实是给同一个d，同一个uid，分别取值 d, d-1, d-3 和 d-7。

结果：

'2020-11-10'| '2020-11-10'| 174411427
'2020-11-10'| '2020-11-09'| 174411427
'2020-11-10'| '2020-11-07'| 174411427
'2020-11-10'| '2020-11-03'| 174411427
'2020-11-10'| '2020-11-10'| 174411683
'2020-11-10'| '2020-11-09'| 174411683
'2020-11-10'| '2020-11-07'| 174411683
'2020-11-10'| '2020-11-03'| 174411683
'2020-11-10'| '2020-11-10'| 174413454
'2020-11-10'| '2020-11-09'| 174413454
'2020-11-10'| '2020-11-07'| 174413454
'2020-11-10'| '2020-11-03'| 174413454 

with 子句

with的功能还是很强大的，官方文档在这里。

with子句提供对公共表达式的支持，with子句的结果可以在select查询中被使用。

我们来看看它的用法。

• with子句的结果可以是常量表达式

WITH '2019-08-01 15:23:00' as ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) 
AND
    EventTime <= ts_upper_bound

• with子句的结果可以是 select子句中分组计算的结果。

WITH sum(bytes) as s
SELECT
    formatReadableSize(s),
    table
FROM system.parts
GROUP BY table
ORDER BY s

• with子句的结果可以是标量自查询。

WITH
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10      

limit by 函数

官方文档先贴上来。

limit n by expression 表示选择第一个n每个不同值的行expression。就是按expression这个字段分组取每个分组里的前n个值。

举个简单的例子先直观的看一看：

假设有这样一个表 limit_by：

id | val
1 | 10
1 | 11
1 | 12
2 | 20
2 | 21

我们做如下的查询：

SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id

分析：对表先按照 id 排序，再按照 val 排序。然后取不同的id的前两行记录。得到的结果如下：

┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
│  2 │  20 │
│  2 │  21 │
└────┴─────┘

再做一个查询：

SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id

分析：这个查询语句和上面不同的地方在于limit 1，2 是从第2个记录开始取，取2个。得到的结果是：

┌─id─┬─val─┐
│  1 │  11 │
│  1 │  12 │
│  2 │  21 │
└────┴─────┘

我们看一个实际业务中遇到的例子：

该业务需求是：过去7天，每个用户的待处理请求的情况。ch代码如下：

SELECT uid2, uid
FROM
    (
     SELECT uid2, uid, id, accepted
     FROM mysqldump.contact_request
     WHERE toDate(uptime) >= yesterday()-7
     ORDER BY uptime DESC
     LIMIT 1 BY id
    )
WHERE accepted = 0

该ch代码中用到了 order by ... limit n by exp 先按时间降序排序，然后取时间最新的id对应的数据。

确保拿到的用户状态是最新的。