1.1 基本用法

• 在页面下方查看查询表的相关字段名称后，手动输入查询SQL并查询。支持的数据表及字段详情请参见下文的字段说明章节，以下为一个最简单的查询示例。

  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条查询结果数据。

• 支持在页面下方直接勾选数据表中关联的事件属性，单击页面右上角的“解析”按钮，自动生成查询语句。
  

  说明

  • 当前仅SaaS-云原生环境支持此种展示方式和自动解析功能，SaaS-非云原生环境暂不支持展示关联的事件属性和解析功能。
  • event表支持关联展示的事件为埋点事件，虚拟事件、圈选事件不支持。
  • event表支持关联展示的数据为埋点属性，虚拟属性、Item属性不支持。
  • 当前最多支持选择30个属性。

1.2 字段说明

您可以在SQL自定义查询页面下方的数据表页面中，选择希望查询的表，当前支持events、users、items、cohorts表，并支持在右上角的搜索框中通过关键词快速搜索查看对应表中的字段信息。

以下为您介绍各表中的关键字段，更多其他字段可在界面中查看。

1.2.1 events表

• 表数据范围：用户SSID/UUID、事件、事件属性、事件公共属性、all_value类型的公共属性与业务对象属性。

• 表关键字段：

  字段	说明
  stat_standard_id	统计口径ID，SSID或UUID，取决于app的统计口径。 列名曾为user_unique_id，现已废弃。
  user_profiles.user_id	用户ID，匿名时通过device_id/web_id填充。 列名曾为user_profiles.uuid，现已废弃。
  hash_uid	与users表进行join时，建议采用hash_uid以提升join效率，加速查询。
  bddid	可以理解为处理后的device_id。该字段只支持in、not in、=、!=这四种运算符，不支持like、字符串函数等。 注意 仅SaaS-非云原生支持bddid字段，SaaS-云原生可直接使用device_id字段，无需使用bddid字段。
  event	事件名
  event_date	事件发生日期，任何SQL都建议指定事件发生时间，否则根据event_time进行推导。
  event_time	事件发生时间戳，10位。 当且仅当使用event_time作为约束条件时，会自动推导event_date；因此不建议使用time和server_time进行时间条件的约束。
  time	事件发生时间戳，历史为10位，现为13位。
  server_time	服务端接收到事件的时间戳，10位。
  event_params.xxx.yyy	事件属性，格式为 event_params.事件名.事件属性名 ，此时sql只会查询该事件相关的数据。 事件名可省略，写作 event_params.事件属性名 ，此时sql会查询所有事件。
  user_profiles.xxx	公共属性，格式为 user_profiles.公共属性名。user_profiles.user_id 对应产品中的user_unique_id。
  item_profiles.xxx.yyyy	业务对象属性，格式为 item_profiles.业务对象名.业务对象属性名。 查出来的值均为array类型，使用方法可见FAQ。

• 查询示例：
  

  注意

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

1.2.2 users表

• 表查询范围：用户SSID/UUID、all_value/last_value类型的公共属性与业务对象属性。

• 表关键字段：

  字段	说明
  ssid	ssid
  stat_standard_id	统计口径ID，与events表含义相同。
  user_id	用户ID。
  bddid	可以理解为处理后的device_id。该字段只支持in、not in、=、!=这四种运算符，不支持like、字符串函数等。 注意 仅SaaS-非云原生支持bddid字段，SaaS-云原生可直接使用device_id字段，无需使用bddid字段。
  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表时，需使用last_active_date进行where过滤，否则查询可能会报错。
  user_profiles.xxxx	公共属性，格式为 user_profiles.公共属性名。 all_value和last_value均可，但是users表中保存的值都是最新值；如需查询all_value类型的公共属性的历史值，可以在events表中进行查询。

• 查询示例
  

  注意

  • user_profiles.xxx 字段建议起别名，否则查询可能存在问题。
  • 查询时需使用last_active_date字段进行where过滤，否则可能会查询报错。

1.2.3 items表

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

• 关键字段：

  字段	说明
  item_name	业务对象名。
  last_active_date	表示该业务对象属性最后的刷新时间。
  item_profiles.xxx.yyyy	业务对象属性，格式为 item_profiles.业务对象名.业务对象属性名。

• 查询示例
  

  注意

  • user_profiles.xxx 字段建议起别名，否则查询可能存在问题。
  • 查询时需使用last_active_date字段进行where过滤，否则可能会查询报错。

1.2.4 cohorts表

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

• 关键字段：

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

• 查询示例
  

  注意

  • 查询时，建议通过cohort_id = x 或 cohort_id in (x, y, ...)指定分群，否则查询可能会报错。
  • 使用cohort_id字段进行where过滤来指定用户分群时，您可以在元数据页面中查看对应的分群的分群ID。

  

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

1.3 查询范围限制

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

1.4 日期过滤

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

1.4.1 日期过滤方式

时间动态参数过滤

在SQL代码框下方添加时间动态参数后，即可使用动态参数的方式进行时间过滤。

select stat_standard_id from events  where ${event_date:date1} limit 50;
--其中 ${event_date:date1}为添加的时间动态参数

函数过滤

• 支持函数：
  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.2 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'

1.4.3 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 常用函数

近似分位数函数 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;

方差函数 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;

条件函数 if

语法：

SELECT if(cond, 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)

数组函数 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;

结果集：

| event | time_arr | row_number |
| --- | --- | --- |
| wechat_unsubscribe | 1603967567 | 1 |
| wechat_unsubscribe | 1603968269 | 2 |
| wechat_unsubscribe | 1603968275 | 3 |
| goods_detail_view | 1603469824 | 1 |
| goods_detail_view | 1603473124 | 2 |
| goods_detail_view | 1603479424 | 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

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 如何查询用户属性或业务对象属性？

用户属性列名的格式为user_profiles.{user_profile_name}，业务对象属性列名的格式为item_profiles.{item_name}.{item_param_name}，样例如下：

select user_profiles.loc_country_id, item_profiles.jersey.team 
from events 
where event_date > '2020-08-01' 
and event_date < '202-08-10' 
limit 200;

5.2 如何查询事件属性？

事件属性列名的格式为event_params.{event_name}.{event_param_name} 。查询事件属性时，需要在WHERE条件中指定事件属性对应的事件名，样例如下：

select user_profiles.os_name, event_params.app_launch.session_duration, network_type, device_brand, event_time
from events
where event_date >= '2020-08-12' and event_date <= '2020-08-19'
limit 20;

注意：尽管样例SQL中的WHERE条件内，没有含有event = 'app_launch'，但是根据
event_params.app_launch.session_duration的字段语义，仍然只会查询app_launch事件下的session_duration属性值。

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

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

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

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

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

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

5.5 如何将新老用户作为条件

1. 查询当天新用户发生的事件种类。

   select count(distinct event) 
   from events 
   where (toDate(toDateTime(user_register_ts),'Asia/Shanghai') = toDate(toDateTime(event_time),'Asia/Shanghai')) 
   limit 1;
   

2. 将2020年9月1日前的用户当做老用户，查询他们网络种类的使用情况。

   select network_type, count(network_type) 
   from events 
   where (toDate(toDateTime(user_register_ts),'Asia/Shanghai') < toDate(toDateTime('2020-09-01 00:00:00'))) 
   and event_date >= '2019-10-01' 
   and event_date <= '2020-10-01' 
   group by network_type;
   

5.6 对于user_profiles.user_id这种，如何join？

如果写作如下形式，则会导致查询报错" 查询存在错误: unknown_identifier "。这是因为Join的主句和子句都采用了user_profiles.user_id。

select user_profiles.user_id
from events
         left join (select user_profiles.user_id from events) as tbb on user_profiles.user_id == tbb.user_profiles.user_id
limit 100;

建议改写成如下形式：

select user_profiles.user_id
from events
         left join (select user_profiles.user_id as uuid from events) as tbb on user_profiles.user_id == tbb.uuid
limit 100;

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

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

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

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

select event_params.any_event.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.8 如何将事件和last_value类型的业务对象关联起来

select item_profiles.phone.id from events et
 any inner join 
(select item_id from items) it
on arrayJoin(if(isNotNull(item_profiles.phone.id), assumeNotNull(item_profiles.phone.id), [NULL])) = it.item_id
where event_date >= '2021-04-07' and event_date <= '2021-04-13' and event = 'phone_event';

5.9 如何将事件和all_value类型的业务对象关联起来

select item_profiles.phone.color 
from events 
where 
arrayExists(x->x in ('black'), assumeNotNull(item_profiles.phone.color))
and event = 'phone_event' and event_date >= '2021-04-07' and event_date <= '2021-04-13'

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

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

• 新老用户：见 5.4 如何将新老用户作为条件；
• 虚拟属性：可以直接使用虚拟属性的 sql ；
• 虚拟事件：使用虚拟事件相同的条件来进行查询；- 圈选事件：暂无替代方案；

若您非常希望直接在自定义查询中使用以上数据，可以通过工单或客户成功经理来告诉我们。

5.11 如何查询任意主动事件

事件分析中的any_active_event本质上是一个虚拟事件，翻译成自定义查询的sql语段如下：

event not in ('rangers_push_send','rangers_push_workflow') and ifNull(event_params.$inactive, 'null') != 'true'

5.12 如何查询app_platform这个用户属性

app_platform在实际查询时，并不是某一个单一的属性，而是一个sql片段，翻译成自定义查询的sql语段如下：

• app_platfrom = 'app'

  (bddid not in ('null', '', '-1', '0') and os_name in ('ios','android'))
  

• app_platform = 'web'

  (bddid in ('null', '', '-1', '0') and user_profiles.platform in ('wap','web'))
  

• app_platform = 'web'

  (
  bddid in ('null','','-1','0') 
  and user_profiles.platform not in ('wap','web')
  and user_profiles.custom_mp_platform is not null
  )
  

5.13 自定义SQL是否支持动态参数

当前自定义SQL查询不支持使用动态参数的方式进行SQL查询，例如：

select event, event_time from events where event = ‘app_launch’ and event_date = ${event_date} limit 20;
--其中 event = ${event_name}这种动态参数由后续动态传入，此类动态参数形式的SQL查询，当前不支持