自定义查询

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

对于使用现有的 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]*/

1.4 segmenter 分群表 (segmenter_${segmenter_name})

segmenter 表为系统中分群结果的存储表，表中存储的用户为此分群筛选出来的用户，表中字段说明如下:

字段	说明	示例
user_id	用户 id	-9220214159525537212
distinct_id	与事件表中的 distinct_id 相关联	3f840957485db9a9
values	分群的值	1
base_time	分群计算的基准时间	1547015611000

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

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

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 '%橘子%';

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

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

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*/

• 开启维度字典映射和维度表关联，默认关闭。例如：

SELECT $model FROM events
WHERE date = CURRENT_DATE() /*ENABLE_DIMENSION_DICT_MAPPING*/

• 如果 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 获取用户的首次行为属性

使用 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