自定义查询

最后更新于:2018-12-14 18:27:18

本文档所描述的内容属于神策分析的高级使用功能,涉及较多技术细节,适用于对相关功能有经验的用户参考。如果对文档内容有疑惑,请咨询您的数据咨询顾问获取一对一的协助。

对于使用现有的 UI 功能暂时无法满足的高级数据需求,我们提供了更加自由的自定义查询功能。该功能支持使用标准 SQL 来对神策分析的所有数据进行查询,同时也包含对查询结果的简单可视化。

注: 当前版本的自定义查询工具基于 HUE 项目构建。

1. 数据表

目前,神策分析的所有数据映射到 事件用户 这两张数据表,在 SQL 里使用这两张数据表即可完成所有查询。同时支持将客户创建的所有 session 映射成 sessions_${session_name} 命名的表。未来还会支持导入客户自定义的其它辅助数据表。

1.1 事件表 (events)

事件表包含了所有事件的详细信息(不包括虚拟事件),该表的每一行代表一个 track 的 Event。事件表的字段分为特殊字段和 Event 本身的 Property 两大类。其中特殊字段如下:

字段 说明 示例
event 事件的名称 BuyGold
user_id 神策分析为该用户分配的内部 ID,与 user 表的 id 字段相关联 1234
distinct_id 用户的原始 ID,track 时传入,可能是一个匿名 ID 或 登录 ID wahaha
date 事件发生的日期 2015-09-21
time 事件发生的具体时间 2015-09-21 11:11:11

需要特别注意的是,事件表的 user_id 字段并不是 track 时传入的 distinct_id,而是由神策分析为该用户分配的内部 ID,具体的机制见如何准确的标识用户

1.2 用户表 (users)

用户表的每一行代表一个 User,类似于事件表,用户表的字段也分为特殊字段和 User 的其它 Profile 两大类,其中特殊字段的说明如下:

字段 说明 示例
id 神策分析为该用户分配的内部 ID,与 events 表的 user_id 相关联 1234567
first_id 该用户的匿名 ID,与 events 表登录前行为的 distinct_id 相关联。需要特别注意,如果某个用户 first_id 的值等于 second_id,说明该用户没有成功关联到匿名 ID,相当于未知 0c476090a0b2940a
second_id 该用户的登录 ID,与 events 表登录后行为的 distinct_id 相关联 wahaha

1.3 session表 (sessions_${session_name})

session 表是对 events 表做了扩展,除了包含 events 表包含的字段,还包含 session 属性和 session 相关的特殊字段,session 属性的命名规则是原始的属性名加上后缀 $session,表示 session 中初始事件的属性。其中特殊字段说明如下:

字段 说明 示例
$session_id 标示一个 session 的唯一 id 2036149433405577601
$session_position 标示一个 session 中事件的索引,从 0 开始,session 中最后一个事件的索引是-1,如果 session 中只有1个事件,则索引值是-2 0
$session_event_duration session 内事件时长,表示session相邻两个事件发生的时间间隔,单位是秒,最后一个事件的事件时长是 null 354
$session_duration session 内最后一个事件触发的时间减去 session 内第一个事件触发的时间,单位是秒 234
$session_depth session 深度,表示 session 内触发事件的次数 4
$event_id$session Session 内第一次触发的事件 Signup

因为 session 表的计算量较大,所以必须加上时间注解进行使用,比如:

select * from sessions_default/*SESSION_TABLE_DATE_RANGE=[2018-01-01,2018-01-05]*/

2. 数据类型

出于查询效率的考虑,自定义查询功能对不同的数据类型有不同处理,同时某些数据类型有一些使用上的限制,具体说明如下:

  • Number

    数值类型,不区分浮点数与整数,输出的时候会根据是否有小数位自动转换输出格式。

  • String

    字符串类型。

  • Date

    注意:time 字段特殊,不需要经过转换即可直接使用。

    日期类型,在自定义查询中表现为 毫秒级的 Timestamp,例如:1442937600000。

    如果有需要,可以可以使用 TO_TIMESTAMP 函数转换为 Timestamp 类型,例如:

    SELECT TO_TIMESTAMP($signup_time / 1000) FROM users LIMIT 100;
    

    用于过滤条件的例子如下:

    SELECT COUNT(*) AS cnt FROM users WHERE TO_TIMESTAMP($signup_time / 1000) > '2017-01-01';
    
  • Datetime

    日期时间类型,和 Date 类型一样,也使用毫秒级的 Timestamp表示,例如:1442592138000。 同样也可以使用 TO_TIMESTAMP 类型进行类型转换。

  • Bool

    布尔类型,使用 0/1 表示 False/True。

  • List

    列表类型,暂时只支持在 Where 条件里使用 CONTAINS 函数或者 LIKE 函数来进行过滤操作。例如:

    SELECT FavoriteFruits  from users where  CONTAINS('橘子', FavoriteFruits);
    
    SELECT FavoriteFruits  from users where  FavoriteFruits LIKE '%橘子%';
    

3. 功能使用

3.1 基本功能

在输入框中输入要查询的 SQL,例如查询每天的事件总数:

SELECT date, COUNT(*) from events GROUP BY 1 ORDER BY 1

然后点击查询即可看到表格展现的结果,同时还有下方还有简单的图表展示,也可以使用 CSV 格式把结果下载下来进行进一步的分析。

出于性能的考虑,前端展示的结果最大只有 1k 条,而 CSV 下载的结果最大是 100w 条,如果需要下载更多数据请使用查询 API

3.2 日期过滤

date 字段表示事件发生时的日期,精确到天,可以用于快速过滤数据。需要特别注意,任何时候都应当尽量使用 date 字段进行过滤,而不是 time 字段。

由于 date 字段的特殊性,对 SQL 操作和函数的支持有一些限制,目前支持使用的函数和表达式有:

  • CURRENT_DATE() 函数,返回当天,例如 2016-08-23。
  • CURRENT_WEEK() 函数,返回当周的周一,例如 2016-08-22。
  • CURRENT_MONTH() 函数,返回当月的一号,例如 2016-08-01。
  • INTERVAL 表达式,例如 CURRENT_DATE() - INTERVAL '1' DAY 表示昨天。

以下是一些具体的例子:

  • 精确过滤某一天的数据:
SELECT COUNT(*) FROM events WHERE date = '2016-01-01'
  • 查询当天的数据
SELECT COUNT(*) FROM events WHERE date = CURRENT_DATE()
  • 查询最近 3 天的数据
SELECT COUNT(*) FROM events WHERE date BETWEEN CURRENT_DATE() - INTERVAL '2' DAY AND CURRENT_DATE()
  • 查询上个自然月的数据
SELECT COUNT(*) FROM events WHERE date BETWEEN CURRENT_MONTH() - INTERVAL '1' MONTH AND CURRENT_MONTH() - INTERVAL '1' DAY

由于 date 是专门为快速的数据过滤设计的特殊字段,不支持绝大多数的时间函数。因此,如果希望使用其它时间函数,请使用 time 字段代替,例如:

SELECT datediff(now(), trunc(time, 'DD')), COUNT(*) FROM events WHERE date >= CURRENT_DATE() - INTERVAL '100' day GROUP BY 1
  • 按照月份聚合 2018-09-01 之后的事件数
SELECT date_sub(date,dayofmonth(date)-1) the_month,count(*) event_qty 
FROM events WHERE date>'2018-09-01'
GROUP BY the_month ORDER BY the_month;
  • 按照星期聚合 2018-09-01 之后的事件数
SELECT date_sub(date,mod(dayofweek(date)+5,7)) the_week,count(*) event_qty 
FROM events WHERE date>'2018-09-01'
GROUP BY the_week ORDER BY the_week;

3.3 常用函数说明

使用自定义查询经常能用到如下几种函数:

  • 时间日期函数
  • 字符串函数
  • 数学函数

其他更多Impala函数,请参考:

Impala函数参考文档

3.3.1 时间日期函数

自定义查询中和时间日期函数相关的字段分为以下三种:

  1. events 表中的 time 字段

    time 是毫秒级的 Timestamp 类型,可以直接使用所有的时间日期函数。

  2. events 表中的 date 字段

    date 是天级别的 Timestamp 类型,如果不需要时分秒的信息,使用这个字段效率会更高。date 同时也是索引字段,所以应该尽量使用此字段进行日期范围的过滤,具体请参考 "日期过滤" 中的说明。

    注:1.10 版本之前,date 字段不支持使用自定义函数,可以使用 time 替代。

  3. 其它自定义的 Date/Datetime 类型的属性

    这类属性在自定义查询中表现为毫秒级的 Unix 时间戳, 使用时间日期函数时需要先使用 TO_TIMESTAMP 函数转换为 Timestamp 类型,请参考 "数据类型" 中的说明。

  • adddate(timestamp startdate, int days), adddate(timestamp startdate, bigint days)

    用途:在一个TIMESTAMP(时间戳)值上加一个给定的天数

    参数

    • startdate:timestamp类型的开始时间戳
    • days:需要加上的天数,正数表示几天之后,负数表示几天之前

    返回值:加上天数之后的时间戳,timestamp类型

  • datediff(timestamp enddate, timestamp startdate)

    用途:返回两个时间戳间隔天数,例如:

    参数

    • enddate:结束时间
    • startdate:开始时间

    返回值:结束时间减去开始时间的天数,int类型。如果第一个参数时间的日期晚于第二个参数时间的日期,返回正数;相反,如果第一个参数时间的日期早于第二个参数时间的日期,返回负数

  • extract(unit FROM timestamp), extract(timestamp, string unit)

    用途:从TIMESTAMP值中截取数值型的时间域,例如年度,月份,日期,小时,分钟,秒/微秒

    参数

    • unit:时间单位unit字符串可取的值有:year,month,day,hour,minute,second,millisecond。

    返回值:时间域的整型值

    例如:目前为止所有的支付订单次数按照年度和月份查询

     SELECT extract(Year from time) AS Year, extract(Month from time) AS Month, COUNT(*) FROM events 
     WHERE event = 'payOrder'
     GROUP BY Year, Month
     ORDER BY Year, Month
    
  • trunc(timestamp, string unit)

    用途:从给定的timestamp时间戳截取时间域

    参数

    • unit:时间单位
      • SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y:年度
      • Q:季度
      • MONTH, MON, MM, RM: 月份
      • WW, W: 相应周第一天的日期
      • DDD, DD, J: 日期
      • DAY, DY, D: 相应周第一天的日期
      • HH, HH12, HH24: 小时
      • MI: 分钟

    返回值:截取时间域之后的日期

    例如:最近100天内每天发生的事件数和事件发生时间与当前日期的间隔天数

     SELECT datediff(now(), trunc(time, 'DD')), COUNT(*) FROM events 
     WHERE date >= CURRENT_DATE() - INTERVAL '100' day 
     GROUP BY 1
    

3.3.2 字符串函数

  • concat(string a, string b…)

    用途:把所有string类型的参数连接成一个string类型

    参数:

    • string(不限个数):要连接的字符串

    返回值:一个整体的字符串

    例如:查询00后用户地址,地址为省份和地区拼接

     SELECT concat($province, $city) As Address 
     FROM users 
     WHERE yearofbirth > 2000
    
  • regexp_like(string source, string pattern[, string options])

    用途:判断source字符串中是否包含以pattern为正则表达式的内容

    参数

    • source:要检查的字符串
    • pattern:正则表达式
    • option(选填):选项
      • c:区分大小写
      • i:不区分大小写
      • m:匹配多行,^和$操作符对于每一行都会匹配,而不是对多行为整体的开头和结束。
      • n:新行匹配,点(.)操作符会匹配新行。重复操作符如 . 可以匹配source字符串中的多行(可以通过. 跳过几行)

    返回值:匹配与否,boolean类型

    例如:使用QQ邮箱为邮件的用户数

     SELECT COUNT(*) FROM users
     WHERE regexp_like(email, '@qq.com$')
    
  • parse_url(string urlString, string partToExtract [, string keyToExtract])

    用途:通过指定URL中的特定部分返回截取值

    参数

    • urlString:URL
    • partToExtract:要截取的部分。可指定的值为'PROTOCOL', 'HOST', 'PATH', 'REF', 'AUTHORITY', 'FILE', ‘USERINFO', ‘QUERY'
      • PROTOCOL:协议,如HTTP,HTTPS,FTP等
      • HOST:主机名
      • PATH:路径
      • REF:锚点(“又称引用”),即URL中#后面的字符串
      • AUTHORITY:授权
      • FILE:文件名
      • USERINFO:用户信息
      • QUERY:查询参数,即URL中?后面的字符串
    • keyToExtract(选填):当partToExtract为’QUERY’时,可以指定query键值对中的key,获取指定参数值

    返回值:URL中指定部分的截取值

    例如:当天页面浏览事件中各个路径的访问分布情况

     SELECT parse_url(url, 'PATH'), COUNT(*) FROM events
     WHERE date = CURRENT_DATE() AND event = '$pageview'
     GROUP BY 1
    

3.3.3 数学函数

数学函数用于一些数值的操作。 特别的,在做去幂运算时,请使用pow()函数取代幂运算符 ‘**’。

  • pow(double a, double p), power(double a, double p), dpow(double a, double p), fpow(double a, double p)

    用途:取幂,例如:

    参数

    • a:底数
    • b:指数

    返回值:a的b次幂

    例如:查询理财产品到期后本息总额超过10万的用户数

     SELECT count(distinct(user_id)) FROM events
     WHERE event = 'buyProduct' AND (capital + capital * pow(rateofinterest,duration)) > 100000
    
  • round(double a), round(double a, int d), round(decimal a, int_type d), dround(double a), dround(double a, int d)

    用途:返回四舍五入值,例如:

    参数

    • a:要四舍五入的数值
    • d(可选):小数保留位数,若无此参数,保留到整数部分

    返回值:四舍五入值

    例如:查询理财产品收益率超过0.45百分点的用户数

     SELECT count(distinct(user_id)) FROM events
     WHERE event = 'buyProduct' AND round((income/capital),4) * 100 > 0.45
    
  • truncate(double_or_decimal a[, digits_to_leave]), dtrunc(double_or_decimal a[, digits_to_leave])

    用途:去除小数部分的数值,例如:

    参数

    • a:被截取的数值
    • digits_to_leave(可选):小数点保留位数,若无此参数,保留到整数部分

    返回值:被截取的值

3.4 高级选项

  • 开启快速 Distinct 算法,可以大大加速类似 COUNT(DISTINCT user_id) 的计算,并且支持多个 COUNT(DISTINCT) 表达式,缺点是会得到不完全精确的结果。例如:

SELECT COUNT(DISTINCT user_id) FROM events
WHERE date = CURRENT_DATE() /*ENABLE_APPROX_DISTINCT*/
  • 使用 Hive 作为查询引擎,可以支持某些复杂的表达式,但是查询到的数据是非实时的,大概有一个小时左右的延迟。例如:
SELECT COUNT(DISTINCT user_id) FROM events
WHERE date = CURRENT_DATE() /*USE_SQL_ENGINE_HIVE*/
  • 如果 SQL 是查询某个指定 Distinct Id 的数据,可以用此选项来进行查询查询。例如:
SELECT event, time FROM events
WHERE date = CURRENT_DATE() AND distinct_id='abcdef' /*DISTINCT_ID_FILTER=abcdef*/
  • SQL 默认在执行 10 分钟之后会被系统强制杀死,如果希望增大超时时间可以使用如下方式:
SELECT * FROM events WHERE date = CURRENT_DATE() LIMIT 1000 /*MAX_QUERY_EXECUTION_TIME=1800*/
  • 对于 JOIN 类查询,可以使用 Join Hint 来指定 Join 的执行方式,可以是 SHUFFLE 或者 BROADCAST。尤其是在执行过程中如果遇到内存不足的错误,可以考虑强制指定为 SHUFFLE 模式:
SELECT COUNT(*) AS cnt FROM events
JOIN /* +SHUFFLE */ users ON events.user_id = users.id
WHERE date = CURRENT_DATE()

4. 常见案例

4.1 根据用户的 distinct_id 查询某个用户在某天的具体行为

直接使用 distinct_id 查询即可:

SELECT * FROM events WHERE distinct_id = 'wahaha' AND date = '2015-09-10' LIMIT 100

4.2 查询每天上午 10 点至 11 点的下单用户数

使用标准的 SQL 日期函数 EXTRACT 来取出小时信息。

SELECT date, COUNT(*) FROM events 
WHERE EXTRACT(HOUR FROM time) IN (10, 11) AND event = 'SubmitOrder'
GROUP BY 1

4.3 查询一段时间内的用户下单次数分布情况

首先计算每个用户的下单次数,然后使用 CASE..WHEN 语法来分组。

SELECT 
    CASE
        WHEN c < 10 THEN '<10'
        WHEN c < 20 THEN '<20'
        WHEN c < 100 THEN '<100'
        ELSE '>100'
    END,
    COUNT(*)
FROM (
    SELECT user_id, COUNT(*) AS c FROM events
    WHERE date BETWEEN '2015-09-01' AND '2015-09-20' AND event = 'SubmitOrder'
    GROUP BY 1
)a 
GROUP BY 1

4.4 查询做了行为 A 而没有做行为 B 的用户数

使用 LEFT OUTER JOIN 计算差集。

SELECT a.user_id FROM (
  SELECT DISTINCT user_id FROM events WHERE date='2015-10-1' AND event = 'BuyGold'
) a
LEFT OUTER JOIN (
SELECT DISTINCT user_id FROM events WHERE date='2015-10-1' AND event = 'SaleGold'
) b
ON a.user_id = b.user_id
WHERE b.user_id IS NULL

4.5 计算用户的使用时长

使用分析函数,根据每个用户相邻的两个事件的间隔估算累计使用时长,如果两次使用间隔超出10分钟则不计算。

SELECT
user_id,
SUM(
  CASE WHEN
   end_time - begin_time < 600
  THEN
   end_time - begin_time
  ELSE
   0
  END
) FROM (
    SELECT
    user_id,
    EXTRACT(EPOCH FROM time) AS end_time,
    LAG(EXTRACT(EPOCH FROM time), 1, NULL) OVER (PARTITION BY user_id ORDER BY time asc) AS begin_time
    FROM events
    WHERE date='2015-5-1'
) a
GROUP BY 1

4.6 查询漏斗第 1 步的流失用户的使用时长

SELECT
AVG(
  CASE WHEN
   end_time - begin_time < 600
  THEN
   end_time - begin_time
  ELSE
   0
  END
) FROM (
    SELECT
    user_id,
    EXTRACT(EPOCH FROM time) AS end_time,
    LAG(EXTRACT(EPOCH FROM time), 1, NULL) OVER (PARTITION BY user_id ORDER BY time asc) AS begin_time
    FROM events
    WHERE date='2015-5-1' and user_id in (funnel_user(12, '2015-05-01', '2015-05-01', '1.8', true, 0))
) a