SQL 创建用户标签

最后更新于:2019-11-08 14:43:01

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 表示"标签值" */

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, */
/* 每次标签数据更新时,都会使用相对时间是昨日的数据进行计算 */

注意: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() 表示用户的消费次数,返回值是数值类型,需要创建为数值类型标签 */

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 类型,需要创建为集合类型的标签 */

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 表示事件发生的距今天数 */

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

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

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'