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

本文介绍了ClickHouse数据库中的arrayJoin函数,它用于将数组展开为多行;with子句用于重用复杂的表达式,并可用于常量表达式、分组计算和标量查询;limit by函数则用于按特定字段分组并选取每个组的前n个记录。文中通过实例展示了这些功能的使用场景,包括留存率计算、数据分组和获取最新用户状态等。

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─┐
│  110 │
│  111 │
│  220 │
│  221 │
└────┴─────┘

再做一个查询:

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

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

┌─id─┬─val─┐
│  111 │
│  112 │
│  221 │
└────┴─────┘

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

该业务需求是:过去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对应的数据。

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

实验目的 熟悉MySQL环境的使用,掌握在MySQL中创建数据库和表的方法,理解MySQL支持的数据类型、数据完整性在MySQL下的表现形式,练习MySQL数据库服务器的使用,练习CREATE TABLE,SHOW TABLES,DESCRIBE TABLE,ALTER TABLE,DROP TABLE语句的操作方法。 实验内容: 【实验1-1】MySQL的安装与配置。 参见4.1节内容,完成MySQL数据库的安装与配置。 【实验1-2】创建“汽车用品网上商城系统”数据库。 用CREATE DATABASE语句创建Shopping数据库,或者通过MySQL Workbench图形化工具创建Shopping数据库。 【实验1-3】在Shopping数据库下,参见3.5节,创建表3-4~表3-11的八个表。 可以使用CREATE TABLE语句,也可以用MySQL Workbench创建表。 【实验1-4】使用SHOW、DESCRIBE语句查看表。 【实验1-5】使用ALTER TABLE、RENAME TABLE语句管理表。 【实验1-6】使用DROP TABLE语句删除表,也可以使用MySQL Workbench删除表。 (注意:删除前最好对已经创建的表进行复制。) 【实验1-7】连接、断开MySQL服务器,启动、停止MySQL服务器。 【实验1-8】使用SHOW DATABASE、USE DATABASE、DROP DATABASE语句管理“网上商城系统” Shopping数据库。 实验训练2:数据查询操作 请到电脑端查看 实验目的: 基于实验1创建的汽车用品网上商城数据库Shopping,理解MySQL运算符、函数、谓词,练习Select语句的操作方法。 实验内容: 1.单表查询 【实验2.1】字段查询 (1)查询商品名称为“挡风玻璃”的商品信息。 分析:商品信息存在于商品表,而且商品表中包含商品名称此被查询信息,因此这是只需要涉及一个表就可以完成简单单表查询。 (2)查询ID为1的订单。 分析:所有的订单信息存在于订单表中,而且订单用户ID也存在于此表中,因此这是只需要查询订单表就可以完成的查询。 【实验2.2】多条件查询 查询所有促销的价格小于1000的商品信息。 分析:此查询过程包含两个条件,第一个是是否促销,第二个是价格,在商品表中均有此信息,因此这是一个多重条件的查询。 【实验2.3】DISTINCT (1)查询所有对商品ID为1的商品发表过评论的用户ID。 分析:条件和查询对象存在于评论表中,对此商品发表过评论的用户不止一个,而且一个用户可以对此商品发表多个评论,因此,结果需要进行去重,这里使用DISTINCT实现。 (2)查询此汽车用品网上商城会员的创建时间段,1年为一段。 分析:通过用户表可以完成查询,每年可能包含多个会员,如果把此表中的创建年份都列出来会有重复,因此使用DISTINCT去重。 【实验2.4】ORDER BY (1)查询类别ID为1的所有商品,结果按照商品ID降序排列。 分析:从商品表中可以查询出所有类别ID为1的商品信息,结果按照商品ID的降序排列,因此使用ORDER BY语句,降序使用DESC关键字。 (2)查询今年新增的所有会员,结果按照用户名字排序。 分析:在用户表中可以完成查询,创建日期条件设置为今年,此处使用语句ORDER BY。 【实验2.5】GROUP BY (1)查询每个用户的消费总金额(所有订单)。 分析:订单表中包含每个订单的订单总价和用户ID。现在需要将每个用户的所有订单提取出来分为一类,通过SUM()函数取得总金额。此处使用GROUP BY语句和SUM()函数
<think>我们注意到用户这次提供的SQL查询语句为: ```sql SELECT m.id, ml.`code`, ml.`name` FROM member m LEFT JOIN member_level ml ON (m.`exp` >= ml.min_exp ORDER BY ml.min_exp DESC LIMIT 1); ``` 这个查询语句在`ON`子句中使用了`ORDER BY`和`LIMIT`,这是不正确的。在SQL中,`ON`子句用于指定连接条件,它只能包含条件表达式(返回布尔值的表达式),而不能包含`ORDER BY`和`LIMIT`子句。因此,这个查询会报语法错误。 ### 报错原因 1. **语法错误**:在`ON`子句中使用`ORDER BY`和`LIMIT`是无效的,因为`ON`子句只接受条件表达式。 2. **逻辑意图**:根据用户之前的查询历史,我们推测用户想要为每个会员(member)匹配一个会员等级(member_level),条件是会员的经验值(exp)大于等于等级所需的最低经验值(min_exp),并且希望每个会员只匹配一个等级(可能是最高等级,即min_exp最大的那个等级)。 ### 解决方案 我们需要重构查询,以达到用户的目的。我们可以使用子查询或窗口函数来为每个会员匹配一个等级(取满足条件的最高等级,即min_exp最大的那个)。 #### 方法1:使用子查询和MAX函数 思路:先找到每个会员可以匹配的最大min_exp(即满足m.exp>=ml.min_exp的最大min_exp),然后再关联到member_level表获取该等级的信息。 ```sql SELECT m.id, ml.`code`, ml.`name` FROM member m LEFT JOIN ( SELECT m_inner.id, MAX(ml_inner.min_exp) AS min_exp -- 找到每个会员满足条件的最大min_exp FROM member m_inner LEFT JOIN member_level ml_inner ON m_inner.`exp` >= ml_inner.min_exp GROUP BY m_inner.id ) t ON m.id = t.id LEFT JOIN member_level ml ON t.min_exp = ml.min_exp; -- 再通过最大min_exp关联获取等级信息 ``` 注意:这种方法在同一个经验值可能有多个等级定义(即多个等级有相同的min_exp)时,可能会返回多行。如果min_exp是唯一的(例如是主键或唯一约束),则没问题。否则,我们需要进一步处理。 #### 方法2:使用窗口函数(ROW_NUMBER) 思路:在连接后,为每个会员的匹配等级按min_exp降序排序(即最大的min_exp排第一),然后取每个会员的第一条记录。 ```sql SELECT id, `code`, `name` FROM ( SELECT m.id, ml.`code`, ml.`name`, ROW_NUMBER() OVER (PARTITION BY m.id ORDER BY ml.min_exp DESC) AS rn FROM member m LEFT JOIN member_level ml ON m.`exp` >= ml.min_exp ) t WHERE rn = 1; ``` 这个查询会为每个会员(m.id)匹配所有满足条件的会员等级,然后按min_exp降序排列(即最高等级排在最前面),然后取第一条(rn=1)。这样,每个会员只会返回一行,且是满足条件的最高等级。 ### 两种方法的比较 - 方法1(子查询)在MySQL5.6及以下版本中可以使用,而窗口函数(方法2)需要MySQL8.0+或支持窗口函数的数据库。 - 方法2通常更直观,性能也可能更好(尤其是当匹配的等级记录很多时)。 ### 特别注意 如果会员的经验值(exp)没有满足任何等级(即没有min_exp小于等于该经验值),那么LEFT JOIN将返回NULL。以上两种方法都会返回该会员,且等级信息为NULL。 ### 相关问题 1. 在SQL中,如何为每个分组选择最大值对应的行? 2. 窗口函数ROW_NUMBER()、RANK()和DENSE_RANK()有什么区别? 3. 在MySQL中,如何兼容低版本实现窗口函数的功能? </think>### SQL 查询报错原因分析 您的查询语句: ```sql SELECT m.id,ml.`code`,ml.`name` FROM member m LEFT JOIN member_level ml ON (m.`exp` >= ml.min_exp ORDER BY ml.min_exp DESC LIMIT 1); ``` 报错核心原因是 **`ON` 子句中不允许使用 `ORDER BY` 和 `LIMIT` 子句**。SQL 语法规定: 1. `ON` 子句只能包含连接条件表达式(布尔表达式) 2. `ORDER BY` 和 `LIMIT` 属于结果集处理子句,只能在主查询中使用 ### 解决方案(三种实现方式) #### 方法一:使用子查询 + 窗口函数(推荐) ```sql SELECT id, `code`, `name` FROM ( SELECT m.id, ml.`code`, ml.`name`, ROW_NUMBER() OVER ( PARTITION BY m.id ORDER BY ml.min_exp DESC ) AS rn FROM member m LEFT JOIN member_level ml ON m.`exp` >= ml.min_exp ) t WHERE rn = 1 ``` **原理**:通过 `ROW_NUMBER()` 为每个会员的匹配等级排序,取最高等级(`min_exp DESC` 最大值)[^2] #### 方法二:关联子查询 ```sql SELECT m.id, (SELECT ml.`code` FROM member_level ml WHERE m.`exp` >= ml.min_exp ORDER BY ml.min_exp DESC LIMIT 1) AS `code`, (SELECT ml.`name` FROM member_level ml WHERE m.`exp` >= ml.min_exp ORDER BY ml.min_exp DESC LIMIT 1) AS `name` FROM member m ``` **适用场景**:MySQL 5.7 等不支持窗口函数的版本 #### 方法三:LEFT JOIN + 聚合函数 ```sql SELECT m.id, MAX(ml.`code`) AS `code`, -- 假设code与min_exp正相关 MAX(ml.`name`) AS `name` FROM member m LEFT JOIN member_level ml ON m.`exp` >= ml.min_exp GROUP BY m.id ``` **注意**:此方法要求 `code` 和 `name` 与 `min_exp` 排序一致 ### 关键问题说明 1. **为什么原始写法错误?** SQL 语法规定 `ON` 子句只能是布尔表达式,不能包含排序和限制操作 2. **如何实现"取最高等级"逻辑?** 本质是 **分组取极值问题**(每组会员取最大 `min_exp` 的等级记录)。推荐使用窗口函数方案解决[^2] 3. **空值处理建议**: 添加 `COALESCE` 处理无等级匹配的情况: ```sql COALESCE(ml.`code`, 'N/A') AS `code` ``` ### 性能优化建议 1. 为 `member_level.min_exp` 创建索引 2. 为 `member.exp` 创建索引 3. 大数据量时优先使用方法一(窗口函数
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值