1.1 基本用法

select event, event_time
from events
where event_date >= '2020-08-10'
and event_date <= '2020-08-18'
and event_time > 1597161600
limit 20;
前端展示1000条，下载可下载1000000条。

1.2 字段说明

1.2.1 events表

本表查询范围为：用户SSID/UUID、事件、事件属性、事件用户属性、all_value类型的用户属性与业务对象属性。

• stat_standard_id
  • 统计口径ID，SSID或UUID，取决于app的统计口径
• user_id
  • 用户ID，匿名时为空字符串
• hash_uid
  • 与users表进行join时，建议采用hash_uid以提升join效率，加速查询
• device_id
  • 设备id
• web_id
• event
  • 事件名
• event_date
  • 事件发生日期，任何SQL都建议指定事件发生时间，否则根据event_time进行推导
• event_time
  • 事件发生时间戳，10位
  • 当且仅当使用event_time作为约束条件时，会自动推导event_date；因此不建议使用time和server_time进行时间条件的约束
• time
  • 事件发生时间戳，历史为10位，现为13位
• server_time
  • 服务端接收到事件的时间戳，10位
• content
  • 行为日志的概览，其中包括了事件、事件属性等基本信息
• event_params.xxx.yyy
  • 事件属性，格式为event_params.事件名.事件属性名，此时sql只会查询该事件相关的数据。
  • 事件名可省略，写作event_params.事件属性名，此时sql会查询所有事件。
• user_profiles.xxx
  • 用户属性，格式为user_profiles.用户属性名
• item_profiles.xxx.yyyy
  • 业务对象属性，格式为item_profiles.业务对象名.业务对象属性名
• map列
  • 包含string_params, int_params, float_params, string_array_params等，分别对应string(version)、int(datetime)、float、list类型的事件属性落库后存在的位置。比如，list类型的事件属性a，可以通过mapElemet(string_array_params, 'a')的方式获取，结果与event_params.a一致。
  • 使用map列的典型场景：上报过一些不符合sql规范的事件属性，比如'wechat.uid'，这个属性中存在点，如果用event_params.'wechat.uid'(对于不符合sql规范的属性名，自定义查询会自动加上单引号作为标识)，可能会引发查询错误，但是使用mapElemet(string_params, 'wechat.uid')则可正常查询。
  • 使用map列的弊端：1. 需要sql编辑人员感知事件属性的类型； 2. 如果属性类型发生变更，sql编辑人员较难把握应该在哪个map列中查询。此时可以先查一下content列，确定一下对应属性在什么map列中。
• 其他字段

提示：event_params.xxx.yyy、user_profiles.xxx 、item_profiles.xxx.yyy三种字段建议起别名，否则查询可能存在问题。

1.2.2 users表

本表查询范围为：用户SSID/UUID、all_value/last_value类型的用户属性。

• stat_standard_id
  • 统计口径ID，与events表含义相同
• user_id
  • 用户ID
• ssid
  • ssid
• device_id
• web_id
• last_active_date
  • 表示该用户的最后活跃时间，可以理解为users表该记录的最后刷新时间。目前支持“YYYY-MM-dd”与“YYYYMMdd”的格式，例如：last_active_date='2020-08-10'。
  • events表和users表进行join时，会自动根据events表的event_date限制推导last_active_date；比如查询事件发生事件为2021-08-01，那么last_active_date将会自推导为大于等于2021-08-01（因为如果last_active_date小于2021-08-01，那么该用户在2021-08-01之后必然没有发生过任何事件）。
• user_profiles.xxxx
  • 用户属性，格式为user_profiles.用户属性名
  • all_value和last_value均可，但是users表中保存的值都是最新值；如需查询all_value类型的用户属性的历史值，可以在events表中进行查询。
• string_profiles，int_profiles，float_profiles， string_array_profiles。
  • map列，与事件表中类似。
• 其他字段

1.2.3 items表

本表查询范围为：all_value/last_value类型的业务对象相关信息

• item_name
  • 业务对象名
• last_active_date
  • 表示该业务对象属性最后的刷新时间。
• hash_item_id
  • 用于和事件表join
• item_profiles.xxx.yyyy
  • 业务对象属性，格式为item_profiles.业务对象名.业务对象属性名
• string_profiles，int_profiles， float_profiles，string_array_profiles。
  • map列，与事件表中类似。
• 其他字段

1.2.4 cohorts表

本表查询范围为：分群中包含的用户统计口径id、hash_uid、分群id等

• stat_standard_id
  • 统计口径id
• hash_uid
  • 对统计口径id进行了hash处理，通常用于join和in子查询，查询速度比用stat_standard_id更快。
• cohort_id
  • 分群id

*在"元数据"标签下，可以查看所有的分群名、分群id以及分群人数。

*当前暂不支持查询分群历史版本，因此目前通过分群id查询的是最近一次成功刷新的分群，和其他高级分析场景一致。

1.3 查询范围

• 时间范围：近一年（包含今天）
• 数据范围：事件、事件属性、用户属性与业务对象属性。

1.4 日期过滤

*针对event_date与last_active_date，目前支持以下函数，如对其他函数有需求，可向客服反馈。

支持函数：
subtractMonths,subtractWeeks,subtractDays
addMonths, addWeeks, addDays
toStartOfQuarter,toStartOfMonth,toStartOfISOYear,toMonday,toStartOfWeek
备注：toStartOfWeek函数，mode为偶数时，起始为周一，否则为周日。
错误用法：

select event from events where toMonth(event_date) = 9；

select event from events where subtractDays(event_date, 10) = '2021-09-20'；

上述写法，自定义查询会提示无法推导出event_date。
正确用法:

select event from events where event_date >= subtractDays(today(), 10)；

1.4.1 events表

在任何情况下，都强烈建议采用event_date作为日期过滤条件，如需更加精确的时间区间，可采用event_date+event_time进行限制。

• event_date

event_date表示事件发生的日期，精确到天，可用于加快查询速度，缺省时默认为过去7天(包含今天)。

目前仅支持“YYYY-MM-dd”的格式，例如：event_date='2020-08-10'。

• event_time

event_time表示事件的发生时间，为10位时间戳，精确到秒，可用于提供更精确的时间区间。

• 时区

国内站接入应用的event_date均为UTC+8（东八区）时间，国际站接入应用的event_date均为UTC（零时区）时间。

例如：

event_time = 1597273200，国内站event_date为'2020-08-13'

event_time = 1597273200，国际站event_date为'2020-08-12'
国内站接入应用: 查询北京时间2020年8月10日6：00至2020年8月12日14:00所发生的事件

select event
from events
where
event_time >= 1597010400
and event_time <= 1597212000
and event_date >= '2020-08-10'
and event_date <= '2020-08-12'

国际站接入应用，应用的时区为UTC时间: 查询UTC时间2020年8月10日6：00至2020年8月12日14:00所发生的事件

select event
from events
where
event_time >= 1597039200
and event_time <= 1597240800
and event_date >= '2020-08-10'
and event_date <= '2020-08-12'

国际站接入应用，应用的时区为UTC+7时间: 查询UTC+7时间2020年8月10日6：00至2020年8月12日14:00所发生的事件，相当于查询UTC时间2020年8月9日23:00至2020年8月12日7:00所发生的事件:

select event
from events
where
event_time >= 1597014000
and event_time <= 1597215600
and event_date >= '2020-08-09'
and event_date <= '2020-08-12'

注意：无论何时，都建议您在SQL中指定event_date的起、止时间，以此加速查询速度。如果您未添加，SQL自定义查询功能会自动拼装时间限制，可能会产生与您预计时间段不同的数据。

1.4.2 users、items表

与events表进行join时，last_active_date可以通过event_date进行自动推导，其他情况建议用户指定last_active_date范围。

语义举例：

1. 查询2020年8月10日后的活跃用户的性别。

select user_profiles.gender from users where last_active_date >= '2020-08-10'

2. 查询2020年8月10日前的做过A事件的用户的性别

select gender, hash_uid, user_id
    from events as table_a
            left join
            (   
                select hash_uid, user_profiles.gender as gender from users
                where user_profiles.gender is not null
            ) as table_b 
    on table_a.hash_uid == table_b.hash_uid
   where event_date <= '2020-08-10' and event = 'A'
    limit 500;

该sql未指定事件的起始event_date，因此默认起始event_date为今日的365天前，假设为'2019-10-11'。此时，join子句中，也默认查询last_active_date>='2019-10-11'的用户。

值得注意的是，该案例的典型错误写法为：

select gender, hash_uid, user_id
    from events as table_a
            left join
            (   
                select hash_uid, user_profiles.gender as gender from users
                where user_profiles.gender is not null where last_active_date <= '2020-08-10'
            ) as table_b 
    on table_a.hash_uid == table_b.hash_uid
   where event_date <= '2020-08-10'
    limit 500;

如果用户填写last_active_date <= '2020-08-10'，其join子句的语义就变为——查询2020年8月10号之后就没有事件的那些用户，而不是查询2020年8月10号之前有活动的用户。

1.5 常用函数

1. 近似分位数函数 quantile 与 近似中位数 median

quantile(level)(expr)

参数

• Level —— 分位数层次。可选参数，level的推荐取值范围为[0.01, 0.99]，默认值level=0.5，即为计算中位数。
• expr —— 表达式。 可选数值、日期或时间数据类型

median(expr)相当于是quantile(0.5)(expr)

注意: 该函数采用Reservoir_sampling随机算法，因此结果是近似且非确定的。
举例：查询2020年8月10日的订单事件中，分位数为0.2的金额。

select quantile(0.2)(event_params.order.money) from events where event = 'order' and event_date = '2020-08-10' limit 1;

2. 方差函数 varSamp

计算 Σ((x - x̅)^2) / (n - 1)，这里 n 是样本大小， x̅是x的平均值。
举例: 查询2020年8月10日的订单事件中，金额的方差

select varSamp(event_params.order.money) from events where event = 'order' and event_date = '2020-08-10' limit 1;

3. 条件函数 if

语法：

SELECT if(cond, then, else)

如果条件cond的计算结果为非零值，则返回表达式then的结果，并且跳过表达式else的结果（如果存在）。如果cond为零或NULL，则跳过then表达式的结果，并返回else表达式的结果（如果存在）。、
参数：

• cond: 条件表达式
• then: 条件满足时返回的表达式
• else: 条件不满足时返回的表达式

举例：查询2020年10月6日至12日中，触发A事件比触发B事件次数多的人。

select distinct user_unique_id
from (select user_unique_id as `user_unique_id`,
*if*(event = 'A', *count*(1), 0) as `event1_cnt`,
*if*(event = 'B', *count*(1), 0) as `event2_cnt`
from events
where event in ('A', 'B')
and event_date >= '2020-10-06'
and event_date <= '2020-10-12'
group by user_unique_id, event
limit 1000)
group by user_unique_id
having *sum*(event1_cnt) > *sum*(event2_cnt)

4. 数组函数 arrayEnumerate(arr)

返回与源数组大小相同的数组，其中每个元素表示与其下标对应的原数组元素在原数组中出现的次数。常用用法类似hive中的开窗函数row_number()
参数：

• arr 数组

举例：查询2020年10月25日至11月1日中，不同事件的前三次发生时间

select \*
from (
select event, time\_arr, row\_number
from (
select event,
*groupArray*(time) as time\_arr,
*arrayEnumerate*(time\_arr) as row\_number
from (select distinct event, event\_time as time
from events
where event\_date >= '2020-10-25'
and event\_date <= '2020-11-01'
order by time) a
group by event
)
array join
time\_arr
, row\_number
)
where row\_number <= 3;

结果集：

补充：

该用法类似于HQL的row_number()，在上述例子中的HQL写法为：

select *

from

(

select event,

row_number() over(partition by event order by event_time) as row_number

from events

) a

where a.row_number <= 3;
类似函数：

arrayEnumerateUniq、arrayEnumerateDense

5. match、multiMatchAny、multiMatchAnyIndex

match(haystack, pattern)

• haystack 需要匹配的字段
• pattern 符合RE2语法的正则表达式 re2: https://github.com/google/re2/wiki/Syntax
• 返回值为0表示未匹配，1表示匹配。

举例：

select distinct user_profiles.user_id, multiMatchAny(user_profiles.user_id, ['^[0-9]*$', '^\d{10}$']) as is_match from events where event_date > '2021-03-01' limit 20;

结果集：

multiMatchAny(haystack, [pattern1, pattern2, ...])

• [pattern1, pattern2, ...] 任意符合PCRE语法的正则表达式。
• 返回值为0表示均为匹配，1表示至少存在一个pattern匹配。
• 在匹配子串时，该函数匹配速度更快。
• haystack字符串需要小于2^32字节。

multiMatchAnyIndex(haystack, [pattern1, pattern2, ...])

• 与multiMatchAny类似
• 返回值为与haystack匹配的索引，索引从1开始，无匹配返回0，

举例：

select distinct user_profiles.user_id, multiMatchAnyIndex(user_profiles.user_id, ['^[0-9]*$', '^([a-zA-Z0-9_-]\.)+@[a-zA-Z0-9_-]+(\.[a-zA-Z0-9_-]+)+$', '(\.[a-zA-Z0-9_-]@)+']) as regex_index from events where event_date > '2021-03-01' limit 2;

结果集：

1.6 常见用法举例

根据uuid查询用户在某一天的行为

select event
from events
where user_profiles.user_id = 'datafinder@datarangers.com'
and event_date = '2020-08-10'
limit 20

查询在某一时间段内的用户的某个事件的发生总量

select count(event)
from events
where
event = 'event_name'
and event_time > 1596988800
and event_time < 1597161600
limit 20

查询发生过行为1和行为2，但是没有发生过行为3的用户数

select count(distinct hash_uid)
from events
where
(event = 'event_1' or event = 'event_2' and event != 'event_3')
and event_time > 1596988800
and event_time < 1597161600
limit 20

查询用户的首次行为属性

select user_profiles.user_id, event_params.{event_name}.{event_param_name} from events
where event = 'sogo'
and event_date > '2020-01-01'
order by event_time desc
limit 10;

查询一段时间内用户购买数量分布情况

select
case
when amount > 10 then 'x>10'
when amount >5 and amount <= 10 then '5<x<=10'
else 'x<=5'
end
from
(
select event_params.buy.amount as amount
from events
where event = '`buy`'
and event_date > '2020-06-01' and event_date < '2020-08-02'
limit 200
)

查询事件相关信息时，把分群作为条件

select event from events where hash_uid in (select hash_uid from cohorts where cohort_id = 154) and event_date = yesterday()

查询分群中用户的user_id

select user_id from users where hash_uid in (select hash_uid from cohorts where cohort_id = 154) and last_active_date >= '2021-01-01'

函数详见：https://clickhouse.com/docs/zh/sql-reference/functions/