跳到主要内容
版本：2.9

创建 SQL 标签

在使用 SQL 标签前，推荐您阅读数据模型文档。

一、在 客户数据平台 > 用户管理 > 用户标签 中点击 新建用户标签 进入用户标签创建弹窗

二、选择 SQL 标签 并填写标签基本信息

项是否必填说明限制条件
名称是用户标签名称名称唯一，不可重复
最大输入 30 个字符
标识符是用户标签标识符
可用于数据库和 API 查询		名称唯一，不可重复
最大输入 100 个字符
仅允许大小写英文、数字、以及下划线
描述否用户标签的业务意义描述最大输入 150 个字符
所属分类否选择自定义的用户标签分类如不选择则为未分类

三、点击 下一步 开始定义 SQL 规则

控件说明

项是否必填说明限制条件
数值类型是SQL 标签输出结果数值类型支持选择字符串、整数、小数、集合
SQL 规则是SQL 计算规则(见 SQL 校验规则)

注：已保存的 SQL 标签支持在分析工具、群体画像等应用中作为维度过滤和维度拆解使用。为保证标签在应用中平稳使用，标签编辑时禁止修改标签的数值类型。

校验规则

保存校验

  • 库表校验：SQL 标签仅支持使用 event 和 user 表，使用需要指定库名 olap

  • 格式校验：SQL 标签标准返回参数为 gio_id 和 tag_value

  • 数值类型校验：SQL 输出结果(tag_value)和所选数值类型应保持一致

存储校验

  • 用户校验：SQL 标签计算结果存储时会校验 gio_id 合法性，仅会保留系统已识别的 gio_id

格式示例

select
    gio_id          as gio_id
    ,usr_gender     as tag_value
from olap.user
where usr_gender is not null

语法说明

SQL 标签使用 clickhouse SQL 语法

应用场景

例 1：过去 7 天订单支付金额总和​

select
    gio_id
    ,sum( var_payAmount_var ) as tag_value
from olap.event
where event_key = 'payOrderSuccess'
    and dateDiff( 'day' , dt , today () ) between 1 and 7
    and var_payAmount_var is not null
group by gio_id

例 2：过去 30 天浏览次数 Top 3 的商品名称​

--- 方法一(推荐) ---
select
    gio_id
    ,groupArray(3)(var_goodsName_var) as tag_value
from
(
select
    gio_id
    ,var_goodsName_var
    ,count(1) as pv
from olap.event
where event_key = 'goodsDetailPageView'
    and dateDiff( 'day' , dt , today () ) between 1 and 30
group by gio_id
    ,var_goodsName_var
order by gio_id
    ,count(1) desc
    ,var_goodsName_var
)
group by gio_id

--- 方法二 ---
select
    gio_id
    ,groupUniqArray(var_goodsName_var) as tag_value
from
(
select
    gio_id
    ,var_goodsName_var
    ,count(1) as pv
    ,row_number() over ( partition by gio_id order by count(1) desc, var_goodsName_var ) as num
from olap.event
where event_key = 'goodsDetailPageView'
    and dateDiff( 'day' , dt , today () ) between 1 and 30
group by gio_id
    ,var_goodsName_var
)
where num <= 3
group by gio_id

例 3：过去 90 天最后一次订单支付具体日期​

--- 方法一(推荐) ---
select
    gio_id
    ,argMax( dt , dt ) as tav_value
from olap.event
where event_key = 'payOrderSuccess'
    and dateDiff( 'day' , dt , today () ) between 1 and 90
group by gio_id

--- 方法二 ---
select
    gio_id
    ,groupArray(1)(dt)[1] as tag_value
from
(
    select
        gio_id
        ,dt
    from olap.event
    where event_key = 'payOrderSuccess'
    and dateDiff( 'day' , dt , today () ) between 1 and 90
    order by gio_id
        ,dt desc
)
group by gio_id

例 4：过去 90 天最后一次订单支付距今天数​

--- 方法一(推荐) ---
select
    gio_id
    ,dateDiff( 'day' , argMax( dt , dt ) , today() )  as tav_value
from olap.event
where event_key = 'payOrderSuccess'
    and dateDiff( 'day' , dt , today () ) between 1 and 90
group by gio_id

--- 方法二 ---
select
    gio_id
    ,dateDiff( 'day' , groupArray(1)(dt)[1] , today() ) as tag_value
from
(
    select
        gio_id
        ,dt
    from olap.event
    where event_key = 'payOrderSuccess'
        and dateDiff( 'day' , dt , today () ) between 1 and 90
    order by gio_id
        ,dt desc
)
group by gio_id

常用语法

数值类型转化​

toString​

select toString(123)
toString(123)
123

toDate​

select toDate('2021-12-31')
toDate('2021-12-31')
2021-12-31

toFloat64​

select toFloat64('3.1415926')
toFloat64('3.1415926')
3.1415926

日期函数​

dateDiff​

select dateDiff( 'day' , toDate('2021-01-01') , toDate('2021-12-31') )
dateDiff( 'day' , toDate('2021-01-01') , toDate('2021-12-31')
364

聚合函数​

groupArray​

  • 多行转一行
select
    gio_id
    ,groupArray(tag_value)
from
(
    select 1 as gio_id, 'a' as tag_value
    union all
    select 1 as gio_id, 'b' as tag_value
    union all
    select 2 as gio_id, 'c' as tag_value
    union all
    select 2 as gio_id, 'a' as tag_value
)
group by gio_id
gio_idgroupArray(tag_value)
1['a','b']
2['c','a']
  • 多行转一行，每个用户取第一条
select
    gio_id
    ,groupArray(tag_value)[1]
from
(
    select 1 as gio_id, 'a' as tag_value
    union all
    select 1 as gio_id, 'b' as tag_value
    union all
    select 2 as gio_id, 'c' as tag_value
    union all
    select 2 as gio_id, 'a' as tag_value
)
group by gio_id
gio_idgroupArray(tag_value)[1]
1a
2c