# 如何自定义SQL创建标签
## 数据源
![](https://img.kancloud.cn/49/a5/49a505e16f62e58f8e0409c4a98dd5c1_1686x1106.png)
在系统通过SQL创建标签,可以使用的数据源主要有事件行为表、用户属性表以及标签表。其中标签表根据创建的标签数量不同,存在多张表的可能,表名按照smarttag\_开头,后跟数字。有关事件行为和用户属性的详细介绍,请参考《[数据模型](broken-reference)》、《[数据格式](../../../../integration/prepare/data-type.md)》。
| 表名称 | 说明 | 关联字段 |
| --- | --- | --- |
| event\_vd | 存储用户事件行为数据 | xwho,distinct\_id |
| profile\_vd | 存储用户属性数据 | xwho,distinct\_id |
| smarttag\_\* | 存储用户标签数据 | xwho,distinct\_id |
## 查询结果格式
在使用自定义SQL进行标签加工时,需注意遵从如下的格式返回查询数据。
>[info] 查询仅支持返回三列数据,请按照**xwho,distinct\_id和所需返回的标签值**顺序返回查询结果。
> xwho为用户id,在用户未登录的情况下,xwho 会是一个方舟匿名生成的 ID,在已登录的情况下的,xwho 应及时被客户自己业务系统的ID所改写。
> distinct\_id为方舟的唯一ID,与xwho一一对应,在计算时会使用这个ID。
```sql
/*加工字符型标签*/
SELECT xwho,distinct_id,USER_SEX AS VALUE
FROM profile_vd
WHERE ds=20200501
```
**USER\_SEX**即为查询返回的字符型标签
```sql
/*
加工数值型标签
统计3月份用户发生某个事件的次数作为标签
*/
SELECT xwho,distinct_id,COUNT(*) AS VALUE
FROM event_vd
WHERE xwhat='事件名称'
AND ds between 20200301 and 20200331
GROUP BY
xwho,distinct_id
```
```sql
/*
加工集合类型标签
用户购买次数最多的3个商品
*/
SELECT xwho,distinct_id, array_agg(productdel) AS value
FROM
(SELECT xwho,distinct_id, product,RANK()
OVER (PARTITION BY distinct_id ORDER BY ct DESC) AS rank
FROM
(SELECT xwho,distinct_id,product,COUNT(*) AS ct
FROM event_vd
WHERE
xwhat='buy' /*事件名称*/
GROUP BY xwho,distinct_id,product /*事件属性-商品名称*/
)a
)b
WHERE
rank <=3 /*指定取次数最多的多少个商品作为标签*/
GROUP BY xwho,distinct_id
```
```sql
/*
加工时间类型标签
用户最后购买时间
*/
SELECT xwho,distinct_id,max(xwhen)/*xwhen为方舟事件数据模型的预置字段,保存事件发生时间*/
AS VALUE
FROM event_vd
WHERE xwhat='buy'
AND ds BETWEEN 20200301 AND 20200330
GROUP BY
xwho,distinct_id
```
## 动态时间范围
在之前的介绍中我们介绍到可以配置标签的更新周期,每天、每周、每月定期执行标签加工。在实际的加工过程中,会遇到需要每次加工计算前多少日、上月、当月等等动态时间范围的标签,这里将主要介绍如何在SQL中定义动态时间。
```sql
/*
使用动态时间范围创建标签
最近30天用户启动次数
*/
SELECT xwho,distinct_id,count(*) as value
FROM event_vd
WHERE xwhat='$startup'
AND from_unixtime(xwhen/1000) /*事件发生时间,xwhen为时间戳*/
BETWEEN now()-interval '30' day /*统计事件的时间范围,当前时间到30天前*/
AND now()/*当前时间*/
GROUP BY xwho,distinct_id
```
标签的默认加工时间在每日或指定周期的0点开始,因此now()代表了执行当日的时间,但由于是在0时开始执行,所以基本不会包含当日的数据,可以认为实际统计的日期在执行的上一日到前30日的时间范围。通过指定interval后的数值,可以计算如:
近1日: BETWEEN now()- interval '1' day and now()
近7日: BETWEEN now()- interval '7' day and now()
近30日:BETWEEN now()- interval '30' day and now()
易观方舟使用的查询引擎是presto ,关于presto的Date和Time类型的函数可以参考presto的官方文档:[https://prestodb.github.io/docs/0.201/functions/datetime.html](https://prestodb.github.io/docs/0.201/functions/datetime.html)
## SQL标签示例
### 1 将用户属性表属性作为标签
加工标签时,有时需要将上传至用户属性表的属性纳入标签体系,这时候通过SQL可以直接查询属性表,指定需要保存为标签的属性进行加工。
```sql
SELECT xwho,distinct_id,user_level AS VALUE
FROM profile_vd /*用户属性表*/
/*user_level为属性表中的用户等级字段*/
```
>[info] 将用户属性作为标签保存的好处在于,标签表会保存标签的历史数据,如以上的user\_level,在用户表中我们只能保存用户当前的user\_level状态,如果我们想要去查询用户一个月前的用户等级通过属性表是无法回溯的,而在标签中可以回溯,甚至我们对比历史和当前的状态,选择用户级别发生明显变化的用户群出来。
### 2 首次访问距当前天数
```sql
SELECT xwho,distinct_id,
DATE_DIFF('day',from_unixtime("$first_visit_time"/1000),now())
AS VALUE
FFRFROM
FROM profile_vd
/*
$first_visit_time为用户属性表首次访问时间
DATE_DIFF函数计算起始时间的间隔天数
*/
```
### 3 最后购买距当前天数
```sql
SELECT xwho,distinct_id,
DATE_DIFF('day',from_unixtime(lastbuy/1000),now())
AS VALUE
FROM
(
SELECT xwho,distinct_id,MAX(xwhen) as lastbuy
FROM event_vd
WHERE xwhat='buy'
GROUP BY xwho,distinct_id
) a
/*
xwhat指定购买行为,取购买行为的最大日期作为最后购买时间
通过DATE_DIFF函数计算最后购买时间到当前的间隔天数作为标签
*/
```
### 4 RFM用户价值标签
在常用的用户分层标签中,RFM价值模型应用最为广泛,该模型通过三个基本的指标(最后购买时间距当前天数、购买次数、购买总金额)对用户进行分段。
```sql
SELECT xwho,distinct_id,
CASE
/*4. 按照每个用户rfm三项的得分给用户做分层*/
WHEN r=1 and f=1 and m=1 THEN '重要价值客户'
WHEN r=0 and f=1 and m=1 THEN '重要保持客户'
WHEN r=0 and f=0 and m=1 THEN '重要挽留客户'
WHEN r=1 and f=0 and m=1 THEN '重要发展客户'
WHEN r=1 and f=1 and m=0 THEN '一般价值客户'
WHEN r=1 and f=0 and m=0 THEN '一般发展客户'
WHEN r=0 and f=1 and m=0 THEN '一般保持客户'
WHEN r=0 and f=0 and m=0 THEN '一般挽留客户'
END AS VALUE
FROM
(
SELECT xwho,distinct_id,
/*3. 每个用户的得分比上平均分,大于平均则为1,否则为0*/
CASE WHEN r/avg_r>1 THEN 1 ELSE 0 END AS r,
CASE WHEN f/avg_f>1 THEN 1 ELSE 0 END AS f,
CASE WHEN m/avg_m>1 THEN 1 ELSE 0 END AS m
FROM
(
Ss select a.xwho as xwho,a.distinct_id as distinct_id,
a.r as r,a.f as f,a.m as m,
/*2. 求分段后每个指标的平均分数*/
avg(a.r) over() as avg_r,
avg(a.f) over() as avg_f,
avg(a.m) over() as avg_m
FROM
(
SELECT xwho,distinct_id,tag_152,tag_148,tag_149,
Ceiling(percent_rank() over (order by tag_152 desc)/0.2) as r,
/*1. 此处0.2为按照20%的比例将指标切分为5段,并赋值1-5*/
Ceiling(percent_rank() over (order by tag_148)/0.2) as f,
ceiling(percent_rank() over (order by tag_149)/0.2) as m
FROM smarttag_1
where ds=20200604
)a
)b
)c
/*
计算此标签可预先将rfm三项分别保存为标签再进行计算,
此处tag_152,tag_148,tag_149分别对应了最后购买距今天数、购买次数和购买总金额
*/
```
### 5 首末次行为
将用户首次或最后一次发生某个行为的事件属性作为标签,如首次购买金额、最后访问平台等
```sql
/*最后访问平台*/
SELECT a.xwho,a.distinct_id,a.platform AS VALUE
FROM event_vd
a
INNER JOIN
(
SELECT xwho,distinct_id,max(xwhen) as t
FROM event_vd
WHERE xwhat='$startup'
GROUP BY xwho,distinct_id
) b
ON a.xwho=b.xwho and a.distinct_id=b.distinct_id and a.xwhen=b.t
/*可通过调整max为min计算首次访问的平台名称作为标签*/
```
### 6 最近30天访问天数
```sql
SELECT xwho, distinct_id,
COUNT(DISTINCT format_datetime(from_unixtime(xwhen/1000),'yyyyMMdd')) AS VALUE
FROM event_vd
WHERE
xwhat='$startup' and
from_unixtime(xwhen/1000) between now()-interval '30' day and now()
GROUP BY xwho,distinct_id
/*
可替换xwhat指定需要统计的事件名称
可替换指定需要统计的时间周期
可调整时间格式,统计访问的月数、周数、小时数等
*/
```
- 产品简介
- 快速上手
- Step 1 安装部署
- Step 2 激活系统创建项目
- Step 3 开启您的分析旅程
- 1. 集成 SDK
- 2. 可视化埋点
- 3. 创建分析模型
- 附:埋点方案设计
- 附:数据分析思路
- 产品更新日志
- V5.5 新增LTV分析功能等
- V5.3 UI 升级、分布分析重构、维度表动态更新、细节优化等
- V5.2 新增归因分析、消息中心、重构埋点方案、优化看数据体验……
- V5.1.0317 体验优化& Bug修复
- V5.1 升级可视化埋点、增强权限控制……
- Part I 产品功能说明
- 名词解释
- 指标说明
- 看板
- 5.3.3 看板 UI 重构
- 分析
- 事件分析
- 渠道分析
- 渠道相关名词解释
- 来源识别规则
- 搜索引擎
- 社交媒体
- 小程序场景值
- Session 分析
- Session 规则
- 实时分析
- 留存分析
- 转化漏斗
- 智能路径
- 归因分析
- 热图分析
- Web/H5 热图
- APP 热图
- 分布分析
- 间隔分析
- 属性分析
- LTV 分析
- 多主体分析
- 自定义查询
- 用户
- 用户分群
- 用户探查
- 用户标签
- 标签体系应用概览
- 标签体系
- 标签生命周期管理
- 标签加工
- 如何自定义SQL创建标签
- 单用户档案
- 运营
- 广告跟踪
- 微信小程序渠道追踪
- 预置广告媒介和渠道
- App 推广监测(Beta版本)
- 电子邮件(即将下线)
- 短信(即将下线)
- 消息通知(即将下线)
- 项目管理
- 项目概览
- 项目角色管理
- 项目成员管理
- 数据接入管理
- 埋点方案
- 可视化埋点
- 集成SDK接入数据
- 数据验证
- 用户数据导入
- 微信小程序全埋点事件定义
- 元数据管理
- 元事件
- 虚拟事件
- 事件属性
- 用户属性
- Session 管理
- 页面组管理
- 维度表
- 服务集成配置
- 监控告警
- 智能监控
- 自定义监控
- 平台管理
- 企业概览
- 项目管理
- 成员管理
- 安全设置
- 企业设置
- 日志管理
- 帐号设置
- Part II 技术文档
- 技术接入准备工作
- 部署环境检测工具
- 数据模型
- 数据格式
- 预置事件和属性
- App预置事件/属性
- JS 预置事件/属性
- 如何准确识别用户
- 如何设计埋点方案
- 分平台上报数据 vs 跨平台打通
- SDK 指南
- Android SDK
- 快速集成
- 全埋点模块
- 消息推送模块
- Android Hybrid模式
- SDK Gradle集成方式
- 多渠道打包
- 易观小工具
- 合规相关
- iOS SDK
- 快速集成
- 全埋点介绍
- iOS Hybrid模式
- 消息推送模块
- JS SDK
- 快速集成
- JS SDK基础版
- JS SDK插件
- uni-app SDK
- 快速集成
- 打包原生APP
- 开启移动端全埋点
- uni-app SDK标准版
- 微信小程序 SDK
- 快速集成
- 微信小程序标准版
- 微信小程序插件版
- 微信小程序通用框架版
- 支付宝小程序 SDK
- 支付宝小程序标准版
- 支付宝小程序通用框架版
- 字节跳动小程序 SDK
- 字节跳动小程序标准版
- 字节跳动小程序通用框架版
- 百度小程序 SDK
- 百度小程序标准版
- 百度小程序通用框架版
- 钉钉小程序 SDK
- 钉钉小程序标准版
- 钉钉小程序通用框架版
- QQ小程序 SDK
- QQ小程序标准版
- QQ小程序通用框架版
- 快应用 SDK
- 华为WeCode小程序
- WeCode SDK 标准版
- WeCode SDK插件
- PhoneGap SDK
- mPaaS SDK
- ReactNative SDK
- Flutter SDK
- Java SDK
- Python SDK
- PHP SDK
- C++ SDK
- C# SDK
- Node JS SDK
- Lua SDK
- Golang SDK
- SDK FAQ
- identify与alias的区别
- 爬虫数据如何识别?
- 页面停留如何获取时间?
- 如果获取SDK及更新日志
- 代码埋点和无埋点有什么区别
- Web页面中发现丢失某一个事件
- 自研 SDK 注意事项
- 页面时长统计功能
- 飞书小程序 SDK
- 飞书小程序标准版
- 飞书小程序通用框架版
- Unreal Engine SDK
- 数据验证
- 客户端埋点验证
- Debug 数据验证
- 数据入库验证
- 数据导入
- 接口导入
- JAVA工具包
- 标准json文件导入
- csv格式导入
- 数据导入FAQ
- 数据导出
- JAVA工具包
- 事件数据导出
- 用户数据导出
- 直接从Kafka中消费数据
- 使用程序访问数据库
- 脚本工具
- 隐私声明
- API
- 分析API
- 事件分析
- 留存分析
- 自定义查询
- 转化漏斗
- 属性分析
- Session分析
- 渠道分析
- 分布分析
- 用户API
- 分群查询
- 用户档案
- 分群管理
- 管理API
- 权限管理
- 元数据管理
- 埋点方案管理
- 维度表管理
- 运营API
- 广告跟踪
- APP推广监测
- 平台管理API
- 项目管理
- 成员管理
- 第三方登录
- OAuth2.0登录
- LDAP登录
- GDPR 合规
- Part III 常见问题
- License 许可
- 产品试用及采购
- 参与贡献