使用 SQL 建立用戶標籤
1. 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
2. SQL 語句語法說明
2.1. 使用動態時間
標籤的每次計算都存在一個計算的“基準時間”。
若您的數據範圍是動態變化的，例如：每天標籤更新時，都會使用相對時間是“昨日”的數據進行計算；這時就需要使用動態時間來表示數據的時間範圍。
範例：
/* 需修改語句中的 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'
/* 假設當前日期為 2019-06-20，那麼標籤規則的基準時間為 2019-06-19，於是 [baseTime] 代表昨日 2019-06-19， */
/* 每次標籤數據更新時，都會使用相對時間是昨日的數據進行計算 */
CODE
注意：SQL 建立標籤時，暫時不支援使用當前日期作為 baseTime。
'[baseTime]' 實際上表示的為“昨日”
'[baseTime]' - INTERVAL '6' DAY 表示為“過去第 7 天”
2.2. 其他查詢語法
參考神策分析官網文件《自定義查詢》
SQL 建立標籤 與神策分析自定義查詢的描述能力相同
3. SQL 建立標籤範例
3.1. 建立「最近 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 '6' DAY AND '[baseTime]'
AND event = 'BuyProduct'
GROUP BY 1
/* 其中 count() 表示用戶的消費次數，返回值是數值型別，需要建立為數值型別標籤 */
CODE
3.2. 建立「最近 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 '6' DAY AND '[baseTime]'
AND event = 'productdetails'
GROUP BY 1, 2
) a
) b
WHERE rank_num <= 3
GROUP BY 1
/* 其中 group_concat(product_type, '\n') 表示用戶前三的商品型別。 */
/* 返回值是 list 型別，需要建立為集合型別的標籤 */
SQL
3.3. 建立「用戶最近一次訪問距今時間（天）」
/* 假設當前基準時間為 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 '6' DAY AND '[baseTime]'
AND event = 'View'
GROUP BY 1
) a
/* 其中 View 為用戶訪事件，datediff(now(), time) as value 表示事件發生的距今天數 */
SQL
3.4. 建立「最近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 '6' DAY AND '[baseTime]'
AND event = 'View'
GROUP BY 1
SQL
3.5. 建立「最近7天瀏覽最多的商品類型」
/* 字串型別標籤 */
SELECT id, distinct_id, platform
FROM (
SELECT id, distinct_id, platform,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY cnt DESC) AS row_num
FROM (
SELECT user_id AS id, platform, MAX(distinct_id) AS distinct_id, COUNT(*) AS cnt
FROM events
WHERE date BETWEEN '[baseTime]' - INTERVAL '6' DAY AND '[baseTime]'
AND event = 'match'
GROUP BY 1, 2
) a
) b
WHERE row_num <= 1
SQL
3.6. 建立「昨日進行了登入的用戶」
/* bool 型別標籤 */
SELECT DISTINCT user_id AS id, distinct_id AS distinct_id, 1 AS value
FROM events
WHERE date BETWEEN '[baseTime]' - INTERVAL '6' DAY AND '[baseTime]'
AND event = 'match'
SQL
