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. 使用动态时间

对于例行计算的用户标签而言，标签每一次的运算任务都有一个对应的 “基准时间”，即标签详情页面所展示的数据日期。使用 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 计算规则中不要包含基准时间当天的数据。

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 7 DAY AND '[baseTime]' - INTERVAL 1 DAY
    AND event = 'BuyProduct'
GROUP BY 1

/* 其中 count() 表示用户的消费次数，返回值是数值类型，需要创建为数值类型标签 */

SQL

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

3.3. 创建「过去 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

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 7 DAY AND '[baseTime]' - INTERVAL 1 DAY
    AND event = 'PayOrder'
GROUP BY 1

SQL

3.5. 创建「过去 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

3.6. 创建「过去 7 天中进行了登录的用户」

/* bool 类型标签 */

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

SQL