SQL自定义查询（SaaS）

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类型的公共属性与业务对象属性。

1.2.2 users表

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

1.2.3 items表

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

1.2.4 cohorts表

本表查询范围为：分群中包含的用户统计口径id、hash_uid、分群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;
     
     

     结果集：

     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

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;
   
   

   结果集：

   user_profiles.user_id	is_match
   209041	1
   unknown	0

   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;
   
   

   结果集：

   user_profiles.user_id	regex_index
   209041	1
   xxxx.xxx@xxx.com	3
   aaa@aaa.com	2
   unknown	0

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()
  sql
  

• 查询分群中用户的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'
  sql
  

函数使用方法详见：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
  )