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

对于使用现有的 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 或 登录 IDwahaha
date事件发生的日期,属于特殊字段,上传数据时无需上传 date 字段2015-09-21
time事件发生的具体时间2015-09-21 11:11:11
$receive_time服务器接收到事件时的具体时间戳。该字段可以在自定义查询中显示,在前端的分析模块中,所有事件都无法使用该字段分析数据,因为 $receive_time 默认不会与任何事件绑定。1570230586048

需要特别注意的是,事件表的 user_id 字段并不是 track 时传入的 distinct_id,而是由神策分析为该用户分配的内部 ID,具体的机制见2021-10-19_10-11-23_.标识用户 v1.17

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
$update_time该用户最近一次更新用户表信息的时间戳1570230586048
$device_id_list开启多对一关联机制时,会记录与登录 ID 关联的匿名 ID 列表,以及关联时的时间戳1570230586048:0c476090a0b2940a;1570230591000:65A71299-7139-4B4C-9B71-23A0AC9AAF7D

1.3. Session 表

每张 Session 表都对应一个 Session 的配置,命名规则为:Sessions_${Session_name}。

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

字段说明示例
$Session_id标示一个 Session 的唯一 id2036149433405577601
$Session_position标示一个 Session 中事件的索引,从 0 开始,依次递增。1.14 及之前版本 Session 中最后一个事件的索引是-1,如果 Session 中只有1个事件,则索引值是-2 。1.15 及之后版本,不再有特殊的 -1、-2 索引值。0
$Session_event_durationSession 内事件时长,表示Session相邻两个事件发生的时间间隔,单位是秒,最后一个事件的事件时长是 null354
$Session_durationSession 内最后一个事件触发的时间减去 Session 内第一个事件触发的时间,单位是秒234
$Session_depthSession 深度,表示 Session 内触发事件的次数4
$event_id$SessionSession 内第一次触发的事件Signup

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

SELECT event, user_id, distinct_id, date FROM Sessions_default/*SESSION_TABLE_DATE_RANGE=[2018-01-01,2018-01-05]*/
SQL

注意:由于 Session 表查询比较耗时,为了提升查询效率,目前不支持使用 select * 查询 表,需要选择具体的字段名查询。

1.4. 用户分群/标签表

这些表为系统中分群/标签结果的存储表,表中存储的用户为此分群/标签筛选出来的用户。不同版本的表命名规则有所不同,见下表:

系统版本类型表名规则示例
<=1.13分群segmenter_${segmenter_name}segmenter_abc
>=1.14分群user_group_${user_group_name}user_group_abc
>=1.14标签user_tag_${user_tag_name}user_tag_abc

关于表中具体字段的说明如下:

字段说明示例
user_id用户 id-9220214159525537212
distinct_id与事件表中的 distinct_id 相关联3f840957485db9a9
values用户分群/标签值1
base_time用户分群/标签计算的基准时间,1.14 及之后版本之后新增1547015611000

其中 base_time 是以毫秒形式进行的存储,所以在查询的时候,用户可以通过 unix_timestamp_ms 函数将日期转化成毫秒数进行查询,例子如下:

SELECT * FROM user_group_fenqun9 WHERE base_time=unix_timestamp_ms('2019-01-17 00:00:00')
SQL

1.5. Items 表

字段名称说明示例
$item_typeitem 表的类型apple
$item_id表示 item 的 id123
$is_valid该 item 是否有效,不传入默认为 true1
$receive_time该 item 到达时间1575604527772
$update_time该 item 的更新时间,不传入默认为写入时间1575604527772

2. 数据类型

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

2.1. Number

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

2.2. String

字符串类型。

2.3. Date

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

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

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

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

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

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

2.4. Datetime

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

2.5. Bool

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

2.6. List

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

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

同样也可以使用 /*EXPLODE_LIST_COLUMN=${table.columnName}*/ 注解来将 List 类型数据打散成多行 string 类型数据。例如:

SELECT list_property FROM events /*EXPLODE_LIST_COLUMN=events.list_property*/ 
SQL

3. 功能使用

3.1. 基本功能

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

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

然后点击查询即可看到表格展现的结果,同时还有下方还有简单的图表展示,也可以使用 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'
SQL
  • 查询当天的数据
SELECT COUNT(*) FROM events WHERE date = CURRENT_DATE()
SQL
  • 查询最近 3 天的数据
SELECT COUNT(*) FROM events WHERE date BETWEEN CURRENT_DATE() - INTERVAL '2' DAY AND CURRENT_DATE()
SQL
  • 查询上个自然月的数据
SELECT COUNT(*) FROM events WHERE date BETWEEN CURRENT_MONTH() - INTERVAL '1' MONTH AND CURRENT_MONTH() - INTERVAL '1' DAY
SQL

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

SELECT datediff(now(), trunc(time, 'DD')), COUNT(*) FROM events WHERE date >= CURRENT_DATE() - INTERVAL '100' day GROUP BY 1
SQL
  • 按照月份聚合 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;
SQL
  • 按照星期聚合 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;
SQL

3.3. 常用函数说明

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

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

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

Impala 函数参考文档

3.3.1. 时间日期函数

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

一、events 表中的 time 字段

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

二、events 表中的 date 字段

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

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

三、其它自定义的 Date/Datetime 类型的属性

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

3.3.1.1. adddate(timestamp startdate, int days), adddate(timestamp startdate, bigint days)

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

参数

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

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

3.3.1.2. datediff(timestamp enddate, timestamp startdate)

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

参数

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

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

3.3.1.3. extract(unit FROM timestamp), extract(timestamp, string unit)

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

参数

  • 时间单位 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
SQL

3.3.1.4. 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
SQL

3.3.2. 字符串函数

3.3.2.1. concat(string a, string b…)

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

参数:

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

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

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

 SELECT concat($province, $city) As Address 
 FROM users 
 WHERE yearofbirth > 2000
SQL

3.3.2.2. 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$')
SQL

3.3.2.3. 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
SQL

3.3.3. 数学函数

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

3.3.3.1. 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
SQL

3.3.3.2. 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
SQL

3.3.3.3. 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) 表达式(1.17+ 版本,不加此注释,也可以支持多个 COUNT(DISTINCT) ,但是 1.16 及之前版本,必须加此注释才能支持多个 COUNT(DISTINCT) ),缺点是会得到不完全精确的结果。例如:
SELECT COUNT(DISTINCT user_id) FROM events
WHERE date = CURRENT_DATE() /*ENABLE_APPROX_DISTINCT*/
SQL
  • 开启维度字典映射和维度表关联,默认关闭。例如:
SELECT $model FROM events
WHERE date = CURRENT_DATE() /*ENABLE_DIMENSION_DICT_MAPPING*/
SQL
  • 如果 SQL 是查询某个指定 distinct_id 的数据,可以用此选项来进行查询查询。例如:
SELECT event, time FROM events
WHERE date = CURRENT_DATE() AND distinct_id='abcdef' /*DISTINCT_ID_FILTER=abcdef*/
SQL
  • SQL 默认在执行 10 分钟之后会被系统强制杀死,如果希望增大超时时间可以使用如下方式:
SELECT * FROM events WHERE date = CURRENT_DATE() LIMIT 1000 /*MAX_QUERY_EXECUTION_TIME=1800*/
SQL
  • 对于 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()
SQL

4. 常见案例

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

直接使用 distinct_id 查询即可:

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

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
SQL

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
SQL

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
SQL

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
SQL

4.6. 获取用户的首次行为属性

使用 first_time_value(time, 其他属性) 聚合函数来获取第一次发生某行为时的相关属性

-- 示例1:获取用户第一次发生页面浏览行为时,所在的页面 URL
SELECT user_id, first_time_value(time, $url) FROM events WHERE event = '$pageview' GROUP BY user_id

-- 示例2:获取用户第一次购买时,所购买的金额
SELECT user_id, first_time_value(time, order_amount) first_order_amount FROM events WHERE event = 'payOrder' GROUP BY user_id
SQL

5. 版本变更

Impala 从 2.12 版本升级到 3.2,有少量的不兼容语法变化。

  • GROUP BY、HAVING、ORDER BY 中的别名替换逻辑与标准 SQL 行为更一致,即别名仅在顶级表达式有效,而在子表达式中无效。例如:
/* 支持的语法 */
SELECT NOT bool_col AS nb
FROM t
GROUP BY nb
HAVING nb;
 
/* 不再支持的语法 */
SELECT int_col / 2 AS x
FROM t
GROUP BY x
HAVING x > 3;
SQL
  • 新增了一系列的保留字段,这些字段是不能直接用作标识符的。如果需要将其用作标识符,则必须用反引号引起来,例如:
/* 支持的语法 */
SELECT `position` FROM events
 
/* 不再支持的语法 */
SELECT position FROM events
SQL
  • 新增的保留字段包括:
allocate、any、api_version、are、array_agg、array_max_cardinality、asensitive、asymmetric、at、atomic、authorization、begin_frame、begin_partition、blob、block_size、both、called、cardinality、cascaded、character、clob、close_fn、collate、collect、commit、condition、connect、constraint、contains、convert、copy、corr、corresponding、covar_pop、covar_samp、cube、current_date、current_default_transform_group、current_path、current_role、current_row、current_schema、current_time、current_transform_group_for_type、cursor、cycle、deallocate、dec、decfloat、declare、define、deref、deterministic、disconnect、dynamic、each、element、empty、end-exec、end_frame、end_partition、equals、escape、every、except、exec、execute、fetch、filter、finalize_fn、foreign、frame_row、free、fusion、get、global、grouping、groups、hold、indicator、init_fn、initial、inout、insensitive、intersect、intersection、json_array、json_arrayagg、jso、n_exists、json_object、json_objectagg、json_query、json_table、json_table_primitive、json_value、large、lateral、leading、like_regex、listagg、local、localtimestamp、log10、match、match_number、match_recognize、matches、merge、merge_fn、method、modifies、multiset、national、natural、nchar、nclob、no、none、normalize、nth_value、nth_value、occurrences_regex、octet_length、of、off、omit、one、only、out、overlaps、overlay、pattern、per、percent、percentile_cont、percentile_disc、portion、position、position_regex、precedes、prepare、prepare_fn、procedure、ptf、reads、recursive、ref、references、regr_avgx、regr_avgy、regr_count、regr_intercept、regr_r2、regr_slope、regr_sxx、regr_sxy、regr_syy、release、rollback、rollup、running、savepoint、scope、scroll、search、seek、serialize_fn、similar、skip、some、specific、specifictype、sqlexception、sqlexception、sqlwarning、static、straight_join、submultiset、subset、substring_regex、succeeds、symmetric、system_time、system_user、timezone_hour、timezone_minute、trailing、translate_regex、translation、treat、trigger、trim_array、uescape、unique、unnest、update_fn、value_of、varbinary、varying、versioning、whenever、width_bucket、window、within、without

Impala 保留字段参考文档