本文档所描述的内容属于神策分析的高级使用功能，涉及较多技术细节，适用于对相关功能有经验的用户参考。如果对文档内容有疑惑，请咨询您的数据咨询顾问获取一对一的协助。
对于使用现有的 UI 功能暂时无法满足的高级数据需求，我们提供了更加自由的自定义查询功能。该功能支持使用标准 SQL 来对神策分析的所有数据进行查询，同时也包含对查询结果的简单可视化。
注: 当前版本的自定义查询工具基于 HUE 项目构建。
数据表
目前，神策分析的所有数据映射到
事件和
用户这两张数据表，在 SQL 里使用这两张数据表即可完成所有查询。同时支持将客户创建的所有 session 映射成 sessions_${session_name} 命名的表。以下列举字段都为特殊字段，其他未列举且带 "$" 的属性都为神策预置属性，具体含义可参考文档 预置事件与预置属性，不带 "$" 的属性都为自定义属性，具体含义需跟对应埋点人员确认。
事件表 (events)
事件表包含了所有事件的详细信息（不包括虚拟事件），该表的每一行代表一个 track 的 Event。事件表的字段分为特殊字段和 Event 本身的 Property 两大类。其中特殊字段如下：
需要特别注意的是，事件表的 user_id 字段并不是 track 时传入的 distinct_id，而是由神策分析为该用户分配的内部 ID，具体的机制见如何准确的标识用户。
用户表 (users)
用户表的每一行代表一个 User，类似于事件表，用户表的字段也分为特殊字段和 User 的其它 Profile 两大类，其中特殊字段的说明如下：
Session 表
每张 Session 表都对应一个 Session 的配置，命名规则为：sessions_${session_name}。
Session 表是对 events 表做了扩展，除了包含 events 表包含的字段，还包含 session 属性和 session 相关的特殊字段，session 属性的命名规则是原始的属性名加上后缀 $session，表示 session 中初始事件的属性。其中特殊字段说明如下：
因为 session 表的计算量较大，所以必须加上时间注解进行使用，比如：
SELECT event, user_id, distinct_id, date FROM sessions_default
SQL
注意：由于 SESSION 表查询比较耗时，为了提升查询效率，目前不支持使用 select * 查询 SESSION 表，需要选择具体的字段名查询。
用户分群/标签表
这些表为系统中分群/标签结果的存储表，表中存储的用户为此分群/标签筛选出来的用户。不同版本的表命名规则有所不同，见下表：
关于表中具体字段的说明如下:
其中 base_time 是以毫秒形式进行的存储，所以在查询的时候，用户可以通过 unix_timestamp_ms 函数将日期转化成毫秒数进行查询，例子如下：
SELECT * FROM user_group_fenqun9 WHERE base_time=unix_timestamp_ms('2019-01-17 00:00:00')
SQL
Items 表
| 字段名称 | 说明 | 示例 |
| $item_type | item 表的类型 | apple |
| $item_id | 表示 item 的 id | 123 |
| $is_valid | 该 item 是否有效，不传入默认为 true | 1 |
| $receive_time | 该 item 到达时间 | 1575604527772 |
| $update_time | 该 item 的更新时间，不传入默认为写入时间 | 1575604527772 |
数据类型
出于查询效率的考虑，自定义查询功能对不同的数据类型有不同处理，同时某些数据类型有一些使用上的限制，具体说明如下：
Number
数值类型，不区分浮点数与整数，输出的时候会根据是否有小数位自动转换输出格式。
String
字符串类型。
Date
日期类型，在自定义查询中表现为
毫秒级的 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
Datetime
日期时间类型，和 Date 类型一样，也使用毫秒级的 Timestamp表示，例如：1442592138000。 同样也可以使用 EPOCH_TO_TIMESTAMP 类型进行类型转换。
Bool
布尔类型，使用 0/1 表示 False/True。
List
列表类型，支持在 Where 条件里使用 CONTAINS 函数或者 LIKE 函数来进行过滤操作。例如：
SELECT FavoriteFruits from users where CONTAINS('橘子', FavoriteFruits);
SQL
同样也可以使用
注解来将 List 类型数据打散成多行 string 类型数据。例如：
SELECT list_property FROM events
SQL
功能使用
基本功能
在输入框中输入要查询的 SQL，例如查询每天的事件总数：
SELECT date, COUNT(*) from events GROUP BY 1 ORDER BY 1
SQL
然后点击查询即可看到表格展现的结果，同时还有下方还有简单的图表展示，也可以使用 CSV 格式把结果下载下来进行进一步的分析。
出于性能的考虑，前端展示的结果最大只有 1k 条，而 CSV 下载的结果最大是 100w 条，如果需要下载更多数据请使用查询 API。
日期过滤
字段表示事件发生时的日期，精确到天，可以用于快速过滤数据。需要特别注意，任何时候都应当尽量使用
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
常用函数说明
使用自定义查询经常能用到如下几种函数：
- 时间日期函数
- 字符串函数
- 数学函数
其他更多Impala函数，请参考：
Impala 函数参考文档
时间日期函数
自定义查询中和时间日期函数相关的字段分为以下三种：
一、events 表中的 time 字段time 是毫秒级的 Timestamp 类型，可以直接使用所有的时间日期函数。
二、events 表中的 date 字段date 是天级别的 Timestamp 类型，如果不需要时分秒的信息，使用这个字段效率会更高。date 同时也是索引字段，所以应该尽量使用此字段进行日期范围的过滤，具体请参考 "日期过滤" 中的说明。
注：1.10 版本之前，date 字段不支持使用自定义函数，可以使用 time 替代。
三、其它自定义的 Date/Datetime 类型的属性这类属性在自定义查询中表现为毫秒级的 Unix 时间戳， 使用时间日期函数时需要先使用 EPOCH_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
SQL
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
字符串函数
concat(string a, string b…)
把所有string类型的参数连接成一个string类型
参数：- string(不限个数)：要连接的字符串
：一个整体的字符串
例如：查询00后用户地址，地址为省份和地区拼接SELECT concat($province, $city) As Address
FROM users
WHERE yearofbirth > 2000
SQL
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
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
数学函数
数学函数用于一些数值的操作。 特别的，在做去幂运算时，请使用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
SQL
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
truncate(double_or_decimal a[, digits_to_leave]), dtrunc(double_or_decimal a[, digits_to_leave])
：去除小数部分的数值，例如：
参数：
- a：被截取的数值
- digits_to_leave（可选）：小数点保留位数，若无此参数，保留到整数部分
：被截取的值
高级选项
- 开启快速 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()
SQL
- 开启维度字典映射和维度表关联，默认关闭。例如：
SELECT $model FROM events
WHERE date = CURRENT_DATE()
SQL
- 如果 SQL 是查询某个指定 Distinct Id 的数据，可以用此选项来进行查询查询。例如：
SELECT event, time FROM events
WHERE date = CURRENT_DATE() AND distinct_id='abcdef'
SQL
- SQL 默认在执行 10 分钟之后会被系统强制杀死，如果希望增大超时时间可以使用如下方式：
SELECT * FROM events WHERE date = CURRENT_DATE() LIMIT 1000
SQL
- 对于 JOIN 类查询，可以使用 Join Hint 来指定 Join 的执行方式，可以是 SHUFFLE 或者 BROADCAST。尤其是在执行过程中如果遇到内存不足的错误，可以考虑强制指定为 SHUFFLE 模式：
SELECT COUNT(*) AS cnt FROM events
JOIN users ON events.user_id = users.id
WHERE date = CURRENT_DATE()
SQL
常见案例
根据用户的 distinct_id 查询某个用户在某天的具体行为
直接使用 distinct_id 查询即可:
SELECT * FROM events WHERE distinct_id = 'wahaha' AND date = '2015-09-10' LIMIT 100
SQL
查询每天上午 10 点至 11 点的下单用户数
使用标准的 SQL 日期函数 EXTRACT 来取出小时信息。
SELECT date, COUNT(*) FROM events
WHERE EXTRACT(HOUR FROM time) IN (10, 11) AND event = 'SubmitOrder'
GROUP BY 1
SQL
查询一段时间内的用户下单次数分布情况
首先计算每个用户的下单次数，然后使用 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
查询做了行为 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
计算用户的使用时长
使用分析函数，根据每个用户相邻的两个事件的间隔估算累计使用时长，如果两次使用间隔超出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
获取用户的首次行为属性
使用 first_time_value(time, 其他属性) 聚合函数来获取第一次发生某行为时的相关属性
SELECT user_id, first_time_value(time, $url) FROM events WHERE event = '$pageview' GROUP BY user_id
SELECT user_id, first_time_value(time, order_amount) first_order_amount FROM events WHERE event = 'payOrder' GROUP BY user_id
SQL
版本变更
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 保留字段参考文档