本文檔所描述的内容屬于神策分析的進階使用功能，涉及較多技術細節，适用于對相關功能有經驗的使用者參考。如果對文檔内容有疑惑，請咨詢您的資料咨詢顧問擷取一對一的協助。
對于使用現有的 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 保留字段參考文檔