# 自定义查询
> 最后更新时间:2022-3-3 17:16:48
*****
>[info] #### 本文档中描述的内容,主要针对熟悉SQL的数据分析师或技术人员,如果对文档内容有疑惑,请咨询我们。
如果使用方舟现有的分析功能无法满足您的分析需求,或者您想通过SQL来进行更加灵活的数据分析并导出结果,您可以使用我们提供的自定义查询工具。该工具还可以针对查询结果生成图表完成可视化分析。
>[info] 该工具从方舟4.2.2版本开始提供,如果您的版本还没有该工具,您可以升级方舟到4.2.2版本来进行体验。
## 1\. 工具入口
进入方舟后在当前项目下 选择【分析 - 自定义查询】即可打开方舟自定义查询工具。
>[info] 易观方舟自定义查询工具只能查询您当前正在使用的项目。如果您在方舟中切换了项目,可以按上述步骤重新点击“自定义查询”打开新的自定义查询工具窗口。
## 2\. 数据表
在自定义查询工具中只能看到您当前在方舟中访问的项目的表,目前有**event\_vd(事件表)和profile\_vd(用户表)**,未来还会支持导入客户自定义的其它辅助数据表。
### 2.1 查看表结构
然后进入PrestoSQL,即可看到当前项目的库名、表名和字段信息。查看步骤如下:

### 2.2 字段说明
#### **2.2.1 event\_vd (事件表)**
事件表包含了所有事件的详细信息(不包括虚拟事件),该表中存储了所有用户的行为数据,表中的每一行都是一个行为记录。该表中的字段主要分为两类:事件本身的特殊字段和普通的属性字段。
事件特殊字段如下:
| 字段 | 说明 | 示例 |
| --- | --- | --- |
| distinct\_id | 易观方舟为该用户分配的内部 ID,与 profile\_vd 表的 distinct\_id 字段相关联 | 599898980 |
| xwho | 用户行为发生的原始ID,可以为注册用户ID或临时ID | lurenjian |
| xwhen | 用户行为发生的时间,精确到毫秒 | 1552220964474 |
| xwhat | 用户行为名称 | $pageview |
| ds | 用户行为发生的日期,精确到天 | 20190210 |
>[info] distinct\_id并不是真正的用户ID,而是易观方舟基于用户真实ID生成的用于唯一标识一个用户的ID,详细说明请参见《[如何准确识别用户](../../integration/prepare/user-identify.md)》
#### **2.2.2 profile\_vd(用户表)**
用户表的每一行代表一个 用户,类似于事件表,用户表的字段也分为特殊字段和用户的其它 属性两大类,其中特殊字段的说明如下:
| 字段 | 说明 | 示例 |
| --- | --- | --- |
| distinct\_id | 易观方舟为该用户分配的内部 ID,与 profile\_vd 表的 distinct\_id 字段相关联 | 599898980 |
| xwho | 用户行为发生的原始ID,可以为注册用户ID或临时ID | lurenjian |
## 3\. 功能使用
### 3.1 基本功能使用
直接在SQL输入框中写SQL即可,注意一次只能执行一条SQL,而且**SQL的未尾不能加分号。**
下图示例查询每天的事件量:

默认情况下以表格的方式展示结果,为了优化性能,页面最多展示1000条数据,如果您需要查更多的数据,可以将结果下载下来,最多只能下载100w数据。如果您想下载更多数据,请使用数据导出的方式通过api来下载。
#### **3.1.1 图表展示**

如上图,选择以图表的方式展示数据,支持多种图表。
#### **3.1.2 下载数据**
自定义查询工具支持将查询结果下载,当查询结果数据量大于1000时,页面为了性能考虑只展示1000条,但下载结果时最多可下载100w数据。

结果下载支持菜单栏中的CSV和Excel指下载的文件格式,如果结果数据量大,建议您尽量下载CSV格式,CSV格式在相同数据量的情况下文件更小,下载更快。
Clipboard指将下载结果复制到粘贴板,您可以在其它文件编辑器里“粘贴”操作,即可将结果粘贴到其它编辑器中。
### \*\*3.2 \*\*常用函数
#### 日期函数
ds字段是事件发生的日期,也是事件表的分区键。ds可以用来快速过滤数据,任何情况下都建议尽量使用ds来过滤数据,而不是使用xwhen
以下是一些ds字段的使用例子
* 查询7月份每天的事件总量以及用户数
```
SELECT
ds,
count(1) AS pv,
count(DISTINCT distinct_id) AS uv
FROM
event_vd
where
ds between 20200701 and 20200731
GROUP BY
1
ORDER BY
1
```
* 查询某1天的数据
```
select count(1) from event_vd where ds=20200101
```
* 查询连续一段时间的数据
```
select count(1) from event_vd where ds between 20200701 and 20200707
```
xwhen字段是事件发生的时间,记录的是Unix 时间戳,精度到毫秒。其他日期类型的字段也都是以UNIXTIME的形式存储。例如:1577870326123。 这类字段在使用中,往往可以先用from\_unixtime()函数转换为timestamp类型
通过xwhen查询不同时间粒度的数据
* 按不同时间粒度统计,返回日期类型。按周/月/季时,返回的是该周期第一天.
```
select
date_trunc('week',from_unixitme(xwhen/1000)) as cycle,
count(1) as pv
from
event_vd
where
ds between 20200101 and 20200630
group by
1
order by
1
```
* unit:时间单位unit字符串可取的值有:year,quarter,month,week,day,hour,minute,second,millisecond。
通过date\_format 处理日期字段,查询不同粒度周期范围的数据
* 查询每天8点的数据
```
select
*
from
event_vd
where
ds between 20200101 and 20200630
and date_format(from_unixtime(xwhen/1000),‘%H’) = ‘08’
```
* 常用标记符
| 标记符 | 说明 |
| --- | --- |
| %Y | 年份,4位数字 |
| %y | 年份,2位数字 |
| %m | 月份(01-12) |
| %c | 月份(1-12) |
| %d | 日(00-31) |
| %e | 日(0-31) |
| %H | 小时(00-23) |
| %k | 小时(0-23) |
| %i | 分钟(00-59) |
| %s | 秒(00-50) |
| %T | 时分秒(等同于%H:%i:%s) |
| %w | 星期几(0-6,周日为一周第一天) |
也支持通过extract函数直接从日期类型属性中提取数据
* 按年,月查看历史以来订单次数
```
select
extract(year from from_unixtime(xwhen/1000)) as year,
extract(month from from_unixtime(xwhen/1000)) as month,
count(1)
from
event_vd
where
xwhat='%order%'
group by
1,2
order by
1,2
```
* unit:时间单位unit字符串可取的值有:year,quarter,month,week,day,day\_of\_month,day\_of\_week,hour 等等。
### **3.3 常见场景**
#### **通过用户ID查看单个用户的行为序列**
用户上报的行为有可能包含不同的登陆状态数据,因此建议通过profile表的查询distinct\_id后再通过distinct\_id查询行为序列
```
SELECT
EVENT .*
FROM
event_vd event,
profile_vd PROFILE
WHERE
EVENT .distinct_id = PROFILE .distinct_id
AND EVENT .ds = 20200701
AND PROFILE .xwho = '001'
ORDER BY
EVENT .xwhen
```
#### 查询7月份活跃天数大于5天的用户
查询活跃天数,相当于查询用户活跃日期的去重数。活跃时段数,活跃周数等借助日期函数的使用都能通过该方法计算。
```
SELECT
distinct_id
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
GROUP BY
distinct_id
HAVING
count(DISTINCT ds) > 5
```
#### 查询7月用户下单次数分布
通过单个用户的1个或者多个行为统计指标,对用户分布进行分析。都统一先把相应指标计算出来,然后通过case...when 语法进行分布。结合不同维度组合分析,可以查看不同维度下的用户分布情况。
```
SELECT
CASE
WHEN order_cnts < 5 THEN
'[0-5)'
WHEN order_cnts < 10 THEN
'[5-10)'
WHEN order_cnts < 20 THEN
'[10-20)'
WHEN order_cnts < 50 THEN
'[20-50)'
ELSE
'[50-∞)'
END,
count(1)
FROM
(
SELECT
distinct_id,
count(1) AS order_cnts
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
AND xwhat = 'order'
GROUP BY
1
)
GROUP BY
1
ORDER BY
1
```
#### 查询做过A事件但是未做过B事件的用户
通过left join计算2个事件活跃用户的差集
```
SELECT
a.distinct_id
FROM
(
SELECT DISTINCT
distinct_id
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
AND xwhat = 'A'
) a
LEFT JOIN (
SELECT DISTINCT
distinct_id
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
AND xwhat = 'B'
) b ON a.distinct_id = b.distinct_id
WHERE
b.distinct_is IS NULL
```
通过计算不同事件的活跃次数,再进行过滤
```
SELECT
distinct_id
FROM
(
SELECT
distinct_id,
COUNT_if (xwhat = 'A') AS A_CNTS,
COUNT_if (xwhat = 'B') AS B_CNTS
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
AND xwhat IN ('A', 'B')
GROUP BY
1
)
WHERE
a_cnts >= 1
AND b_cnts = 0
```
#### 计算用户使用时长
计算会话内相邻2个事件的间隔时长作为每个事件的使用时长,以此统计累积总时长。会话的结束事件,不统计时长。
```
SELECT
distinct_id,
sum(next_xwhen - xwhen)
FROM
(
SELECT
distinct_id,
xwhen,
lead (xwhen) over (
PARTITION BY distinct_id,
"$session_id"
ORDER BY
xwhen
) AS next_xwhen
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
AND "$session_id" IS NOT NULL
)
GROUP BY
1
```
一些场景下,没有采集会话ID,通过相邻2个事件的间隔时长作为每个事件的使用时长,需要设定一个阈值,比如30分钟,间隔时间超过这个时间则不计算。
```
SELECT
distinct_id,
sum(
CASE
WHEN next_xwhen - xwhen > 1800000 THEN
0
ELSE
next_xwhen - xwhen
END
)
FROM
(
SELECT
distinct_id,
xwhen,
lead (xwhen) over (
PARTITION BY distinct_id
ORDER BY
xwhen
) AS next_xwhen
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
AND "$session_id" IS NOT NULL
)
GROUP BY
1
```
计算特定2个事件之间的时长。通常可以用来统计某个事件的响应时间,比如 点击按钮A,间隔多久事件B相应。先统计时长,再筛选出当前事件为A,且下一事件为B的记录。同样需要根据业务设定一个阈值,比如1分钟。
```
SELECT
distinct_id,
xwhen,
next_xwhen - xwhen
FROM
(
SELECT
distinct_id,
xwhen,
xwhat,
lead (xwhen) over (
PARTITION BY distinct_id
ORDER BY
xwhen
) AS next_xwhen,
lead (xwhat) over (
PARTITION BY distinct_id
ORDER BY
xwhen
) AS next_xwhat
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
AND xwhat IN ('A', 'B')
AND "$session_id" IS NOT NULL
)
WHERE
xwhat = 'A'
AND next_xwhat = 'B'
AND next_xwhen - xwhen <= 60000
```
#### 通过地理位置计算距离
计算2个地理位置的距离,比如,计算(112.879325,28.244123) 与 (112.887949,28.234258)的距离,单位为米。
```
SELECT
ST_Distance (
to_spherical_geography (
ST_point (112.879325, 28.244123)
),
to_spherical_geography (
ST_point (112.887949, 28.234258)
)
);
```
###
### 3.4 使用优化
ds字段是日期字段,您可以使用ds字段过滤数据。为了提高查询性能,请您尽量在SQL中添加ds字段过滤,只查询需要的数据。
易观方舟使用的查询引擎是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)
- 产品简介
- 快速上手
- 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 许可
- 产品试用及采购
- 参与贡献