SQL 的規範

需嚴格返回 3 列數據，每列分別表示：神策id，使用者id，標籤值
範例：

/* 需修改語句中的 event 為自己的業務欄位 */

SELECT DISTINCT user_id AS id, distinct_id AS distinct_id, 1 AS value
FROM events
WHERE date BETWEEN '[baseTime]' AND '[baseTime]'
    AND event = 'login'

/* 其中 id 表示"神策id"， distinct_id 表示"設備id"或者"登錄id"，value 表示"標籤值" */

SQL

SQL 語句語法說明

使用動態時間

對於例行計算的用戶標籤而言，標籤每一次的運算任務都有一個對應的「基準時間」，即標籤詳情頁面所展示的數據日期。使用 SQL 建立使用者標籤可以以「基準時間」為基礎，實現按動態時間選取數據。基準時間均以 "天" 為單位，在 SQL 中以 "[baseTime]" 作為佔位符。

範例：

/* 需修改語句中的 event 為自己的業務欄位 */

SELECT DISTINCT user_id AS id, distinct_id AS distinct_id, '1' AS value
FROM events
WHERE date BETWEEN '[baseTime]' AND '[baseTime]'
    AND event = 'login'

/* 最近 7 天: WHERE date BETWEEN '[baseTime]' - INTERVAL '6' DAY AND '[baseTime]' */
/* 過去 7 天: WHERE date BETWEEN '[baseTime]' - INTERVAL '7' DAY AND '[baseTime]' - INTERVAL '1' DAY */

SQL

在以上範例中，假設使用者標籤計算任務的基準時間為 2019-06-19，即數據日期為 2019-06-19，則 SQL 中 "\[baseTime\]" 部分就會被替換為 "2019-06-19"，也就是說參與計算的是 2019-06-19 全天的數據。

需要注意的是，使用者標籤計算任務的執行時間與基準時間相互獨立，但計算任務的執行時間會影響數據的完整性。默認情況下，計算任務會在基準時間當天 0 點開始進入調度，而基準時間當天的 event 數據還未入庫，因此若計算規則中包含了基準時間當天的數據，則在計算執行時不會被計入在內。例如，基準時間為 2019-06-19 的計算任務，會在 2019-06-19 0：00 開始進入調度周期，可能會在 2019-06-19 3：00 計算完成，而此時 2019-06-19 的 event 數據還未發生，數據不完備。由此我們建議，SQL 計算規則中不要包含基準時間當天的數據。

其他查詢語法

參考神策分析官網文檔《自定義查詢》
SQL 建立標籤與神策分析自訂查詢的描述能力相同
點擊查看

SQL 建立標籤範例

建立「過去 7 天消費次數」

/* 假設當前基準時間為 2019-06-19 */
/* 數值類型標籤 */

SELECT user_id AS id, MAX(distinct_id) AS distinct_id, COUNT(*) AS value
FROM events
WHERE date BETWEEN '[baseTime]' - INTERVAL '7' DAY AND '[baseTime]' - INTERVAL '1' DAY
    AND event = 'BuyProduct'
GROUP BY 1

/* 其中 count（） 表示使用者的消費次數，返回值是數值類型，需要創建為數值類型標籤*/

SQL

建立「過去 7 天瀏覽偏好的商品類型（前 3）」

/* 假設當前基準時間為 2019-06-19 */
/* 集合類型標籤*/ 

SELECT id, MAX(distinct_id) AS distinct_id,
    GROUP_CONCAT(product_type, '\n') AS value
FROM (
    SELECT id, distinct_id, product_type, 
        RANK() OVER (PARTITION BY id ORDER BY cnt DESC) AS rank_num
    FROM (
        SELECT user_id AS id, product_type,
            MAX(distinct_id) AS distinct_id, COUNT(*) AS cnt
        FROM events
        WHERE date BETWEEN '[baseTime]' - INTERVAL '7' DAY AND '[baseTime]' - INTERVAL '1'DAY
            AND event = 'ProductDetails'
        GROUP BY 1, 2
    ) a
) b
WHERE rank_num <= 3
GROUP BY 1

/* 其中 group_concat（product_type， '\n'） 表示使用者前三的商品類型。 */
/* 傳回值是 list 類型，需要創建為集合類型的標籤 */

SQL

建立「過去 7 天中使用者最近一次存取距今時間（天）」

/* 假設當前基準時間為 2019-06-19 */
/* 數值類型標籤 */

SELECT id, distinct_id, DATEDIFF(now(), time) AS value
FROM (
    SELECT user_id AS id, MAX(distinct_id) AS distinct_id, MAX(time) AS time
    FROM events
    WHERE date BETWEEN '[baseTime]' - INTERVAL '7' DAY AND '[baseTime]' - INTERVAL '1' DAY
        AND event = 'View'
    GROUP BY 1
) a

/* 其中View 為使用者訪事件，datediff（now（）， time） as value 表示事件發生的距今天數 */

SQL

建立「過去 7 天中使用者最近一次支付事件發生的時間」

/* 時間類型標籤 */

SELECT user_id AS id, MAX(distinct_id) AS distinct_id, 
    UNIX_TIMESTAMP(MAX(time)) * 1000 AS value
FROM events
WHERE date BETWEEN '[baseTime]' - INTERVAL '7' DAY AND '[baseTime]' - INTERVAL '1' DAY
    AND event = 'PayOrder'
GROUP BY 1

SQL

建立「過去 7 天流覽最多的商品類型」

/* 字串類型標籤 */

SELECT id, distinct_id, product_type AS value
FROM (
    SELECT id, distinct_id, product_type, 
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY cnt DESC) AS row_num
    FROM (
        SELECT user_id AS id, product_type, MAX(distinct_id) AS distinct_id, COUNT(*) AS cnt
        FROM events
        WHERE date BETWEEN '[baseTime]' - INTERVAL '7' DAY AND '[baseTime]' - INTERVAL '1' DAY
            AND event = 'ProductDetails'
        GROUP BY 1, 2
    ) a
) b
WHERE row_num <= 1

SQL

建立「過去 7 天中進行了登錄的使用者」

/* bool 類型標籤 */

SELECT DISTINCT user_id AS id, distinct_id AS distinct_id, 1 AS value
FROM events
WHERE date BETWEEN '[baseTime]' - INTERVAL '7' DAY AND '[baseTime]' - INTERVAL '1' DAY
    AND event = 'login'

SQL