本文件所描述的內容屬於神策分析的高級使用功能，涉及較多技術細節，適用於對相關功能有經驗的用戶參考。如果對文件內容有疑惑，請諮詢您的數據諮詢顧問獲取一對一的協助。

對於使用現有的 UI 功能暫時無法滿足的高級數據需求，我們提供了更加自由的自定義查詢功能。該功能支援使用標準 SQL 來對神策分析的所有數據進行查詢，同時也包含對查詢結果的簡單可視化。

注: 當前版本的自定義查詢工具基於 HUE 項目構建。

1. 數據表

目前，神策分析的所有數據映射到 事件 和 用戶 這兩張數據表，在 SQL 裡使用這兩張數據表即可完成所有查詢。同時支援將客戶創建的所有 session 映射成 sessions_${session_name} 命名的表。以下列舉欄位都為特殊欄位，其他未列舉且帶 "$" 的屬性都為神策預設屬性，具體含義可參考文件 預設事件與預設屬性，不帶 "$" 的屬性都為自定義屬性，具體含義需跟對應埋點人員確認。

1.1. 事件表 (events) 

事件表包含了所有事件的詳細訊息（不包括虛擬事件），該表的每一行代表一個 track 的 Event。事件表的欄位分為特殊欄位和 Event 本身的 Property 兩大類。其中特殊欄位如下：

需要特別注意的是，事件表的 user_id 欄位並不是 track 時傳入的 distinct_id，而是由神策分析為該用戶分配的內部 ID，具體的機制見如何準確的標識用戶。

1.2. 用戶表 (users)

用戶表的每一行代表一個 User，類似於事件表，用戶表的欄位也分為特殊欄位和 User 的其它 Profile 兩大類，其中特殊欄位的說明如下：

1.3. 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/*SESSION_TABLE_DATE_RANGE=[2018-01-01,2018-01-05]*/

注意：由於 SESSION 表查詢比較耗時，為了提升查詢效率，1.14 及之後的神策分析版本不支援使用 select * 查詢 SESSION 表，需要選擇具體的欄位名查詢。

1.4. 用戶分群/標籤表

這些表為系統中分群/標籤結果的儲存表，表中儲存的用戶為此分群/標籤篩選出來的用戶。不同版本的表命名規則有所不同，見下表：  

關於表中具體欄位的說明如下:  

其中 base_time 是以毫秒形式進行的儲存，所以在查詢的時候，用戶可以透過 unix_timestamp_ms 函數將日期轉化成毫秒數進行查詢，例子如下：

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

1.5. Items 表

2. 數據型別

出於查詢效率的考慮，自定義查詢功能對不同的數據型別有不同處理，同時某些數據型別有一些使用上的限制，具體說明如下：

2.1.1. Number

數值型別，不區分浮點數與整數，輸出的時候會根據是否有小數位自動轉換輸出格式。

2.1.2. String

字串型別。

2.1.3. Date

注意：time 欄位特殊，不需要經過轉換即可直接使用。

日期型別，在自定義查詢中表現為 毫秒級的 Timestamp，例如：1442937600000。

如果有需要，可以使用 EPOCH_TO_TIMESTAMP 函數轉換為 Timestamp 型別，例如：

SELECT EPOCH_TO_TIMESTAMP($signup_time / 1000) FROM users LIMIT 100;

用於條件過濾的例子如下：

SELECT COUNT(*) AS cnt FROM users WHERE EPOCH_TO_TIMESTAMP($signup_time / 1000) > '2017-01-01';

2.1.4. Datetime

日期時間型別，和 Date 型別一樣，也使用毫秒級的 Timestamp表示，例如：1442592138000。 同樣也可以使用 EPOCH_TO_TIMESTAMP 型別進行型別轉換。

2.1.5. Bool

布林型別，使用 0/1 表示 False/True。

2.1.6. List

列表型別，支援在 Where 條件裡使用 CONTAINS 函數或者 LIKE 函數來進行過濾操作。例如：

SELECT FavoriteFruits  from users where  CONTAINS('橘子', FavoriteFruits);

同樣也可以使用 /*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. 時間日期函數

自定義查詢中和時間日期函數相關的欄位分為以下三種：

一、events 表中的 time 欄位

time 是毫秒級的 Timestamp 型別，可以直接使用所有的時間日期函數。

二、events 表中的 date 欄位

date 是天級別的 Timestamp 型別，如果不需要時分秒的訊息，使用這個欄位效率會更高。date 同時也是索引欄位，所以應該儘量使用此欄位進行日期範圍的過濾，具體請參考 "日期過濾" 中的說明。

注：1.10 版本之前，date 欄位不支援使用自定義函數，可以使用 time 替代。

三、其它自定義的 Date/Datetime 型別的屬性

這類屬性在自定義查詢中表現為毫秒級的 Unix 時間戳， 使用時間日期函數時需要先使用 EPOCH_TO_TIMESTAMP 函數轉換為 Timestamp 型別，請參考 "數據型別" 中的說明。

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

用途：在一個TIMESTAMP（時間戳）值上加一個給定的天數

參數：

• startdate：timestamp型別的開始時間戳
• days：需要加上的天數，正數表示幾天之後，負數表示幾天之前

返回值：加上天數之後的時間戳，timestamp型別

3.3.1.2. datediff(timestamp enddate, timestamp startdate)

用途：返回兩個時間戳間隔天數,例如：

參數：

• enddate：結束時間
• startdate：開始時間

返回值：結束時間減去開始時間的天數，int型別。如果第一個參數時間的日期晚於第二個參數時間的日期，返回正數；相反，如果第一個參數時間的日期早於第二個參數時間的日期，返回負數

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

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

3.3.2. 字串函數

3.3.2.1. concat(string a, string b…)

用途：把所有string型別的參數連接成一個string型別

參數：

• string(不限個數)：要連接的字串

返回值：一個整體的字串

例如：查詢00後用戶地址，地址為省份和地區拼接

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

3.3.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$')

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

3.3.3. 數學函數

數學函數用於一些數值的操作。 特別的，在做去冪運算時，請使用pow()函數取代冪運算符 ‘**’。

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

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

3.3.3.3. 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.1.1. 查詢每天上午 10 點至 11 點的下單用戶數

使用標準的 SQL 日期函數 EXTRACT 來取出小時訊息。

SELECT date, COUNT(*) FROM events 
WHERE EXTRACT(HOUR FROM time) IN (10, 11) AND event = 'SubmitOrder'
GROUP BY 1

4.1.2. 查詢一段時間內的用戶下單次數分佈情況

首先計算每個用戶的下單次數，然後使用 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.1.3. 查詢做了行為 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.1.4. 計算用戶的使用時長

使用分析函數，根據每個用戶相鄰的兩個事件的間隔估算累計使用時長，如果兩次使用間隔超出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.1.5. 獲取用戶的首次行為屬性

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

5. 版本變更

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;

• 新增了一系列的保留欄位，這些欄位是不能直接用作標識符的。如果需要將其用作標識符，則必須用反引號引起來，例如：

/* 支援的語法 */
SELECT `position` FROM events
 
/* 不再支援的語法 */
SELECT position FROM events

• 新增的保留欄位包括：

Impala 保留欄位參考文件