方舟智能分析
产品功能SDK指南API
  • 产品简介
  • 快速上手
    • Step 1 安装部署
    • Step 2 激活系统创建项目
    • Step 3 开启您的分析旅程
      • 1. 集成 SDK
      • 2. 可视化埋点
      • 3. 创建分析模型
    • 附:埋点方案设计
    • 附:数据分析思路
  • 产品更新日志
    • V5.3.3 UI 升级、分布分析重构、维度表动态更新、细节优化等
    • V5.2.0 新增归因分析、消息中心、重构埋点方案、优化看数据体验……
    • V5.1.0317 体验优化& Bug修复
    • V5.1.0 升级可视化埋点、增强权限控制……
  • 我要反馈
  • 🐱Part I 产品功能说明
    • 名词解释
    • 指标说明
    • 看板
    • 分析
      • 事件分析
      • 渠道分析
        • 渠道相关名词解释
        • 来源识别规则
        • 搜索引擎
        • 社交媒体
        • 小程序场景值
        • Session 规则
      • Session 分析
      • 实时分析
      • 留存分析
      • 转化漏斗
      • 智能路径
      • 归因分析(Beta)
      • 热图分析
        • Web/H5 热图
        • APP 热图
      • 分布分析
      • 间隔分析
      • 属性分析
      • 自定义查询
    • 用户
      • 用户探查
      • 用户分群
      • 用户标签
        • 标签体系
        • 标签加工
          • 如何自定义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插件
      • 微信小程序 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 注意事项
        • 页面时长统计功能
    • 数据验证
      • 客户端埋点验证
      • Debug 数据验证
      • 数据入库验证
    • 数据导入
      • 接口导入
      • JAVA工具包
        • 标准json文件导入
        • csv格式导入
      • 数据导入FAQ
    • 数据导出
      • JAVA工具包
        • 事件数据导出
        • 用户数据导出
      • 直接从Kafka中消费数据
      • 使用程序访问数据库
    • 脚本工具
    • API
      • 分析API
        • 事件分析
        • 留存分析
        • 转化漏斗
        • 属性分析
        • Session分析
        • 渠道分析
        • 分布分析
        • 自定义查询
      • 用户API
        • 分群查询
        • 用户档案
        • 分群管理
      • 管理API
        • 权限管理
        • 元数据管理
        • 埋点方案管理
        • 维度表管理
      • 运营API
        • 广告跟踪
      • 平台管理API
        • 项目管理
        • 成员管理
    • 第三方登录
      • OAuth2.0登录
      • LDAP登录
    • GDPR 合规
  • �� Part III 常见问题
    • License 许可
    • 产品试用及采购
    • 参与贡献
由 GitBook 提供支持
在本页
  • 1. 工具入口
  • 2. 数据表
  • 2.1 查看表结构
  • 2.2 字段说明
  • 3. 功能使用
  • 3.1 基本功能使用
  • 3.2 常用函数
  • 3.3 常见场景
  • 3.4 使用优化

这有帮助吗?

  1. Part I 产品功能说明
  2. 分析

自定义查询

本文档中描述的内容,主要针对熟悉SQL的数据分析师或技术人员,如果对文档内容有疑惑,请咨询我们。

上一页属性分析下一页用户

最后更新于4年前

这有帮助吗?

如果使用方舟现有的分析功能无法满足您的分析需求,或者您想通过SQL来进行更加灵活的数据分析并导出结果,您可以使用我们提供的自定义查询工具。该工具还可以针对查询结果生成图表完成可视化分析。

该工具从方舟4.2.2版本开始提供,如果您的版本还没有该工具,您可以升级方舟到4.2.2版本来进行体验。

1. 工具入口

进入方舟后在当前项目下 选择【分析 - 自定义查询】即可打开方舟自定义查询工具。

自定义查询工具界面如下图:

易观方舟自定义查询工具只能查询您当前正在使用的项目。如果您在方舟中切换了项目,可以按上述步骤重新点击“自定义查询”打开新的自定义查询工具窗口。

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

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字段过滤,只查询需要的数据。

distinct_id并不是真正的用户ID,而是易观方舟基于用户真实ID生成的用于唯一标识一个用户的ID,详细说明请参见《》

易观方舟使用的查询引擎是presto,关于presto的Date和Time类型的函数可以参考presto的官方文档:

以上内容没有解答我的问题? 🚀

🐱
如何准确识别用户
https://prestodb.github.io/docs/0.201/functions/datetime.html
点击我来反馈