Create Traits with SQL

SQL Specification

Strictly return 2 columns of data, each representing: Sensors ID, Tag Value.

Example:

/* 需修改语句中的 event 为自己的业务字段 */ SELECT DISTINCT user_id AS id, 1 AS value FROM events WHERE date BETWEEN '[baseTime]' AND '[baseTime]' AND event = 'login' /* 其中 id 表示"神策 id"，value 表示"标签值" */

SQL Statement Syntax Description

Using Dynamic Time

For tags calculated routinely, each computation task of the tag has a corresponding reference time, which is the data date displayed on the tag detail page. When creating tags using SQL, you can select data based on dynamic time using the reference time. The reference time is measured in days, and represented by "[baseTime]" in SQL.

Example:

/* 需修改语句中的 event 为自己的业务字段 */ SELECT DISTINCT user_id AS 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 */

In the above example, assuming the reference time for the tag computation task is 2019-06-19, i.e., the data date is 2019-06-19, the "[baseTime]" in SQL will be replaced with "2019-06-19", meaning that the data for the entire day of 2019-06-19 will be used for computation.

It should be noted that the execution time of the tag computation task is independent of the reference time, but the execution time can affect the completeness of the data. By default, the computation task will start scheduling at 0:00 on the reference time day, while the event data for that day has not yet been stored. Therefore, if the computation rule includes data from the reference time day, it will not be included in the computation. For example, a computation task with the reference time of 2019-06-19 will start scheduling at 0:00 on 2019-06-19 and may complete around 3:00 on 2019-06-19, at which point the event data for 2019-06-19 would not be complete. Hence, we suggest not including the reference time day's data in the SQL computation rule.

Other Query Syntax

Refer to the Sensortech Analysis official documentation "Custom Queries"

Creating Tags with SQL has the same descriptive capability as Sensortech Analysis custom queries

SQL Trait Creation Examples

Create "Number of Consumptions in Past 7 Days"

/* 假设当前基准时间为 2019-06-19 */ /* 数值类型标签 */ SELECT user_id AS 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() 表示用户的消费次数，返回值是数值类型，需要创建为数值类型标签 */

Create "Preferred Product Types in Past 7 Days (Top 3)"

/* 假设当前基准时间为 2019-06-19 */ /* 集合类型标签*/ SELECT id,     GROUP_CONCAT(product_type, '\n') AS value FROM ( SELECT id, product_type, RANK() OVER (PARTITION BY id ORDER BY cnt DESC) AS rank_num FROM ( SELECT user_id AS id, product_type, 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 类型，需要创建为集合类型的标签 */

Create "Time Elapsed Since Last User Visit in Past 7 Days"

/* 假设当前基准时间为 2019-06-19 */ /* 数值类型标签 */ SELECT id, DATEDIFF(now(), time) AS value FROM ( SELECT user_id AS 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 表示事件发生的距今天数 */

Create "Time of Last Payment Event in Past 7 Days"

/* 时间类型标签 */ SELECT user_id AS 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

Create "Most Viewed Product Types in Past 7 Days"

/* 字符串类型标签 */ SELECT id, product_type AS value FROM ( SELECT id, product_type, ROW_NUMBER() OVER (PARTITION BY id ORDER BY cnt DESC) AS row_num FROM ( SELECT user_id AS id, product_type 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

Create "Users who have logged in in Past 7 Days"

/* bool 类型标签 */ SELECT DISTINCT user_id AS id, CAST(1 AS boolean) AS value FROM events WHERE date BETWEEN '[baseTime]' - INTERVAL 7 DAY AND '[baseTime]' - INTERVAL 1 DAY AND event = 'login'