自定义分析

为了降低运营人员的使用门槛，提升产品易用性，目前用户运营平台主要针对日常运营中常用或重要的指标及流程进行了可视化的功能呈现，针对现有用户运营平台其他功能模块不能分析出来的复杂数据需求，我们提供了SQL查询的自定义分析模块，满足更高阶的分析场景。

点击自定义分析的“新建分析”功能，进入SQL查询页面

通过SQl查询数据

在新建分析页面通过SQL查询想要的数据，可在页面左侧查看可用的数据表和字段信息，书写好SQL语句后，点击执行，查询目标数据，目前仅支持select类型的SQL语句，单次可查询10000条数据，列表视图中仅展示前100条数据，查询出的所有数据可导出。

保存SQL模板

高阶的数分人员或数据研发人员编辑SQL内容后，可将SQL另存为SQL模板，供以后复用，或提供给运营人员使用。

结果数据可视化

SQL查询数据正常展示后，可通过图表可视化功能将数据配置为易于分析的图表，点击“图表视图”，点击页面中的“立即编辑”，打开【可视化图表编辑】弹框，可在该弹框中通过托拉拽的方式配置图表。当前支持的图标样式有：折线图、饼图、柱状图三种。

注意：图表配置部分更改内容后，需点击“更新图表”刷新最新的图表效果。

点击“确定”后编辑的图

表会在图表视图中展示

SQL查询相关细节

数据表

数据表有历史事件表和用户信息表，通过这两张数据表能实现数据查询功能，两表均有默认存在字段及用户扩列字段、扩列字段均无对应的字段类型，均为text类型，如要对其进行使用需要进行类型的转化。

1、历史事件表(event_history)

历史事件表包含了所有事件的信息，每一行代表埋点上报的事件记录，默认字段如下：

2、用户信息表(user_info)

用户信息表包含了用户属性相关的信息，每一行为埋点上报的用户属性信息，默认字段如下:

自定义时间筛选

为满足自定义看板时间筛选动态切换数据查询时间的功能，自定义分析支持基于占位符的时间筛选替换逻辑，满足用户对添加至自定义看板/解决方案的自定义分析图表实现动态时间切换的场景诉求。
注: 占位符支持设置默认值(默认值支持函数)，用于没有筛选时间的情况下的兜底处理逻辑。
占位符
开始时间: ${startDate}
结束时间: ${endDate}
示例:

## 占位符设置固定时间默认值
select * from event_history where create_date >= ${startDate:'2024-01-01'} and create_date <= ${endDate:'2024-02-01'};

## 占位符设置动态时间默认值
select * from event_history where create_date >= ${startDate:today() - 30} and create_date <= ${endDate:today() - 1};

数据类型

出于查询效率的考虑，自定义查询功能对不同的数据类型有不同处理，同时某些数据类型有一些使用上的限制。

类型转换

bigint: 数值类型，长整数，常用于存储日期时间戳

string: 字符串类型，对于用户自行上传的数据均为string类型，如需使用需要对其进行相对应的转化。

date: 日期类型

datetime: 时间类型

timestamp: 时间戳类型

常用函数说明

使用自定义分析常用的函数：

1）时间日期函数

## toDateTime：string转datetime格式
select toDateTime('2023-05-25 00:00:00');

## formatDateTime：datetime或者date类型转string
select formatDateTime(create_time, '%Y-%m-%d');

## hour：截取datetime/date类型字段的小时数
select hour(create_time);

## day：截取datetime/date类型字段的天数
select day(create_time);

## month：获取datetime/date类型字段的月份
select month(create_time);

## year：获取datetime/date类型字段的年份
select year(create_time); -- create_time是datetime或者date类型

## now：获取当前时间，yyyy-MM-dd HH:mm:ss格式
select now();

## today：获取当天时间，yyyy-MM-dd格式
select today();

## dateDiff
## 获取两个datetime/date类型数据相差的月份数
select dateDiff('month', create_time1,create_time2);
## 获取两个datetime/date类型数据相差的周数
select dateDiff('week', create_time1,create_time2);
## 获取两个datetime/date类型数据相差的天数
select dateDiff('day', create_time1,create_time2);

## dateAdd：date/datetime类型数据增加指定时间
select dateAdd((now()),INTERVAL 1 day);

## dateSub：date/datetime类型数据减少指定时间
select dateSub((now()),INTERVAL 1 day);

2）字符串函数

## lower：string字符串转小写
select lower('ABc');

## upper：string字符串转大写
select upper('ABc');

## ltrim：string字符串去除左边空格
select ltrim('  Abc  ');

## rtrim：string字符串去除有边空格
select rtrim('  Abc  ');

## substring：string字符串分割
SELECT substring('hello world', 1, 5);

## replace：string字符串替换
select replace('  Abc  ', ' ', '');

## reverse：string字符串反转
select reverse('ABc');

3）数字函数

## avg：获取数值类型数据的平均值
select event_id,avg(cast(剩余额度 as float)) from event_history where 剩余额度 != '' group by event_id;

## count：获取数量
select count(1) from event_history where create_date between '2022-01-25' and '2022-02-11';

## max：获取数值类型数据的最大值
select max(cast(剩余额度 as float)) from event_history;

## min：获取数值类型数据的最小值
select min(cast(剩余额度 as float)) from event_history;

## sum：获取数值类型数据的总和
select sum(cast(剩余额度 as float)) from event_history;

常见问题

1）字段名加单引号查询不出数据

clickhouse '' 包裹的认为是字符串，并不能查询出对应的所需字段

-- 不可用

select 'create_time' from user_info;

-- 正确查询字段方式

select create_time from user_info;
或
select `create_time` from user_info;
或
select "create_time" from user_info;

2） 在SQL中转化字段数据类型可视化分析时会存在错误

由于指标/Y轴均为数字在运行时会做强制化转化，如果对于预制字段并不做对应的转化，可能将潜在的问题带到可视化，建议在自定义查询时就将字段转化完毕。

常见案例

1）查询指定时间点的用户行为数据

查询 VIP、非VIP用户在近一周晚上7-11点的启动APP的启动人数

select "是否会员",count(0) as open_count from (

select gtcid,

case when "是否为企业客户" = 'true' then '会员' else '非会员' end as "是否会员"

from

user_info

where

cast(sys_$active_time as datetime) >= now() - interval '1 week'

and extract(hour from cast(sys_$active_time as datetime)) >= 19

and extract(hour from cast(sys_$active_time as datetime)) <= 21

) as t group by "是否会员";

2）计算最后一次/第一次行为的行为属性

查询近90天内未启动APP用户的最后一次访问页面的页面名称

select

pay_count ,regist_count ,pay_count / regist_count as "注册7日内的付费率"

from

(

select

(

select

count(regist_gtcid)

from

(

select

 distinct gtcid as regist_gtcid,

 cast(sys_$firstvisittime as datetime) as start_date,

 cast(sys_$firstvisittime as datetime) + interval '7 day' as end_date

from

 user_info

where

 cast(sys_$firstvisittime as datetime) >= cast('2021-10-20 11:36:45' as datetime) - interval '30 day'

) t1

left join event_history as e on

e.gtcid = t1.regist_gtcid

and e.event_id = '付费'

where  e.create_date >t1.start_date

and e.create_date <= t1.end_date

) as pay_count,

(

select

count(0)

from

(

select

 distinct gtcid as regist_gtcid

from

 user_info

where

 cast(sys_$firstvisittime as datetime) >= cast('2021-10-20 11:36:45' as datetime) - interval '30 day'

) as a1) as regist_count

) as b;

3）根据业务需求进行活跃用户数去重：

查询播放歌曲的月活用户数

select count(distinct gtcid) from event_history where event_id = '播放音乐' and create_date between '2022-03-01' and '2022-03-31';