自定义查询

Number

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

String

字符串类型。

注意：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 类型进行类型转换。
布尔类型，使用 0/1 表示 False/True。
列表类型，暂时只支持在 Where 条件里使用 CONTAINS 函数来进行过滤操作。例如：
SELECT CONTAINS('Apple', FavoriteFruits);
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
3.3 常用函数说明

使用自定义查询经常能用到如下几种函数：
时间日期函数
字符串函数
其他更多Impala函数，请参考：
Impala函数参考文档
3.3.1 时间日期函数

自定义查询中和时间日期函数相关的字段分为以下三种：
events 表中的 time 字段
time 是毫秒级的 Timestamp 类型，可以直接使用所有的时间日期函数。
events 表中的 date 字段
 date 是天级别的 Timestamp 类型，如果不需要时分秒的信息，使用这个字段效率会更高。date 同时也是索引字段，所以应该尽量使用此字段进行日期范围的过滤，具体请参考 "日期过滤" 中的说明。
 注：1.10 版本之前，date 字段不支持使用自定义函数，可以使用 time 替代。
其它自定义的 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：年度
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次幂
 例如：查询理财产品到期后本息总额超过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*/
如果 SQL 里明确指定了只查询一个或者多个特定事件，可以用此选项来加速查询。注意不适用于使用了 event NOT IN 或者 event != 类型查询：
SELECT COUNT(*) AS c FROM events
WHERE date = CURRENT_DATE() AND event = 'PayOrder' /*EXPLICIT_EVENT_FILTER*/
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 
        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
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'
LEFT OUTER JOIN (
SELECT DISTINCT user_id FROM events WHERE date='2015-10-1' AND event = 'SaleGold'
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
   end_time - begin_time
) 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'
GROUP BY 1
4.6 查询漏斗第 1 步的流失用户的使用时长

SELECT
AVG(
  CASE WHEN
   end_time - begin_time < 600
   end_time - begin_time
) 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))