SQL自定义查询（私有化）

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/

5.1 如何查询用户属性？

查询2020年8月1号后的活跃用户的国家id：

select user_profiles.loc_country_id from users
where last_active_date > '2020-08-01'
limit 200;

用户属性列名的格式为user_profiles.{user_profile_name}。

5.2 如何查询事件属性？

事件属性列名的格式为event_params.{event_name}.{event_param_name} 。样例如下：

select user_profiles.os_name, event_params.app_launch.session_duration, network_type, device_brand, event_time
from events
and event_date >= '20200812' and event_date <= '20200819'
limit 20;

注意：尽管样例SQL中的WHERE条件内，没有含有event = 'app_launch'，但是根据

event_params.app_launch.session_duration的字段语义，仍然只会查询app_launch事件下的session_duration属性值。

如果您需求查询不同事件下的同一个属性名的属性值，请联系客服。

5.3 如何查询事件与事件属性的关系？

在 DataRangers 的“数据管理”功能中可以查看。
在数据表中搜索"event_params"，可以查看所有的事件与事件属性的对应关系。

5.4 如何查询业务对象属性？

业务对象属性列名格式为item_profiles.{item_name}.{item_param_name}。样例如下：

select item_profiles.book.name, item_profiles.book.authors, item_profiles.phone.price from items limit 30;

查询业务对象为book的名称和作者、查询业务对象为phone的价格。与查询事件属性类似，样例SQL中并没有指定item_name in ('book', 'phone')，但是根据item_profiles.book.authors等字段的语义，自定义查询功能会自动推导item_name，因此样例SQL等价于如下SQL：

select item_profiles.book.name, item_profiles.book.authors, item_profiles.phone.price from items where item_name in ('book', 'phone') limit 30;

5.5 如何查询事件与用户之间的关系？

可通过hash_uid进行事件表与用户表的JOIN。样例如下：

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;

注意点：推荐使用hash_uid进行JOIN，通过其他字段JOIN可能导致查询超时。

5.6 如何查询事件与业务对象之间的关系？

可以通过event_params对应事件的xxx_hash_item_id与items表对应业务对象的hash_item_id做JOIN查询。样例如下：

select event_params.buy.book_hash_item_id as book_item_id, 
       event_params.buy.browser as buy_browser,
       item_table.book_name,
from events event_table
any inner join 
    (select hash_item_id, item_profiles.book.name as book_name,
    from items 
    where item_name = 'book') item_table 
on arrayJoin(if(isNotNull(event_params.buy.book_hash_item_id), assumeNotNull(event_params.buy.book_hash_item_id), [NULL])) 
   = item_table.hash_item_id
where event_date > '2020-12-10'
limit 20;

上述样例的作用为：查询buy事件关联的业务对象属性为book时，浏览器的值以及书的名字。
注意点：

1. JOIN时采用

arrayJoin(if(isNotNull(event_params.buy.book_hash_item_id), assumeNotNull(event_params.buy.book_hash_item_id), [NULL]))

将Array类型的book_hash_item_id转换为标量，方便JOIN。

2. event_params、user_profiles、item_profiles为前缀的变量并不符合SQL规范，是自定义查询功能为简化字段语义，降低查询门槛而抽象出的字段，因此在JOIN、子查询等多表查询时，强烈建议对上述字段取一个别名。

5.7 如何查询事件分析中的$is_first_day属性？

$is_first_day属性为虚拟事件属性，Finder查询侧会根据用户事件发生时间和用户首次时间发生时间做比较来计算$is_first_day的属性值。如果您需要查询，可以参考如下SQL：

1. 查询browse事件的$is_first_day属性值。

select (toStartOfDay(toDateTime(event_time, 'Asia/Shanghai')) ==
       toStartOfDay(toDateTime(ut.first_event_time, 'Asia/Shanghai'))) as `$is_first_day`
from events et global any
         inner join (
    select hash_uid, user_profiles.first_event_time as first_event_time from users
    ) ut on et.hash_uid == ut.hash_uid
where event = 'browse'
  and event_date >= '2020-12-22'
  and event_date <= '2020-12-28';

2. 查询12月22日至12与28日中，首日发生browse事件的次数及对应的天数。

select toUInt32((event_time - toUnixTimestamp('2020-12-22', 'Asia/Shanghai')) / 86400) as t, count(1) as cnt
from events et global any
         inner join (
    select hash_uid, user_profiles.first_event_time as first_event_time from users
    ) ut on et.hash_uid == ut.hash_uid
where event = 'browse'
  and event_date >= '2020-12-22'
  and event_date <= '2020-12-28'
  and (toStartOfDay(toDateTime(event_time, 'Asia/Shanghai')) ==
      toStartOfDay(toDateTime(ut.first_event_time, 'Asia/Shanghai')))
group by t
order by t ;

5.8 为什么同一个uuid，在行为细查和在自定义查询出来的行为不一致？

因为行为细查中，会将uuid转化为ssid查询，因此既能查出实名的，也能查出匿名的行为。但是在自定义查询中，sql中如果给定了user_id=xxx，那就只会查询实名行为，不会查询匿名行为。

5.9 为什么有些在事件分析能用的属性，但是自定义查询却不支持？

自定义查询目前不支持“新老用户”、虚拟属性、虚拟事件和圈选事件。
替代方案有：

• 新老用户：见 5.4 如何将新老用户作为条件；
• 虚拟属性：可以直接使用虚拟属性的 sql ；
• 虚拟事件：使用虚拟事件相同的条件来进行查询；
• 圈选事件：暂无替代方案；
  若您非常希望直接在自定义查询中使用以上数据，可以通过工单或客户成功经理来告诉我们。

5.10 如何查询事件公共属性？

事件公共属性通常以如下形式出现：

event_params.any_event.公共属性名
event_params.any_active_event.公共属性名
event_params.事件名.公共属性名
event_params.公共属性名

当查询下述sql时，查询的范围为所有事件。

select event_params.any_event.common_param_a from events;
或者
select event_params.common_param_a from events;

当查询下述sql时，查询的范围为所有主动事件。

select event_params.any_active_event.common_param_a from events;

当查询下述sql时，查询的范围为事件event_1。

select event_params.event_1.common_param_a from events;

上述sql等价为——

select event_params.any_event.common_param_a from events where event = 'event_1';

如果一条sql中包含event_params.any_event.xxx、event_params.any_active_event.xxx、event_params.具体事件名.xxx中的多个，那么查询的事件限制规则如下：

规则之间是or的关系，举例说明：

5.11 查询不同事件下的同一个属性名的属性值

使用event_param.事件属性名，这样自定义查询将不会自动拼装事件限制，需要您在where条件中指定事件，比如：

select event_params.url as url from events where event in ('event_1', 'event_2') and event_date = today();

注意：如果包含该属性的事件较多，可能导致SQL解析耗时增多。