1. 常用函数说明

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

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

1.1. 时间日期函数

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

一、events 表中的 time 字段

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

二、events 表中的 date 字段

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

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

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

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

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

用途：在一个TIMESTAMP（时间戳）值上加一个给定的天数

参数：

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

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

1.1.2. datediff(timestamp enddate, timestamp startdate)

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

参数：

enddate：结束时间
startdate：开始时间

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

1.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

1.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

1.2. 字符串函数

1.2.1. concat(string a, string b…)

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

参数：

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

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

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

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

SQL

1.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

1.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

1.3. 数学函数

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

1.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

1.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

1.3.3. truncate(double_or_decimal a[, digits_to_leave]), dtrunc(double_or_decimal a[, digits_to_leave])

用途：去除小数部分的数值，例如：

参数：

a：被截取的数值
digits_to_leave（可选）：小数点保留位数，若无此参数，保留到整数部分

返回值：被截取的值

2. impala 函数

请参考：Impala 函数参考文档

3. 神策自定义 impala 函数

3.1.1. percentile_count函数

 SELECT day,percentile_count(perf_metric_ic_m_copy, 0.3) FROM events 
group by 1

SQL