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

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等。

• 在"元数据"标签下，可以查看所有的分群名、分群id以及分群人数。
• 当前暂不支持查询分群历史版本，因此目前通过分群id查询的是 最近一次成功刷新的分群，和其他高级分析场景一致。

1.3 查询范围

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

1.4 日期过滤

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

• 支持函数： subtractMonths,subtractWeeks,subtractDaysaddMonths, addWeeks, addDaystoStartOfQuarter,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_dateevent_date表示事件发生的日期，精确到天，可用于加快查询速度，缺省时默认为过去7天(包含今天)。目前仅支持“YYYY-MM-dd”的格式，例如：event_date='2020-08-10'。

• event_timeevent_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 与 近似中位数 medianquantile(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、multiMatchAnyIndexmatch(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