SQL查询

诸葛io能够帮助企业采集和分析海量的用户行为数据,我们提供了非常灵活的可视化分析功能,能够很好的满足产品团队、市场团队、运营团队和管理决策层的日常分析需求。

但是,很多企业中会有专门的数据分析师协助业务和管理团队进行数据分析工作。针对不同的业务需求,数据分析师经常需要对用户行为数据展开更为复杂的、更为深入的查询、分析和洞察,这时,诸葛io的可视化分析功能对他们来说是不够灵活和强大的。

为此,我们提供了SQL查询功能,以方便企业中的数据分析师和有需求的高级用户使用 SQL表达式查询和分析用户行为数据。

一、用户行为数据模型简介

诸葛io的数据来源于用户在产品上所发生的互动行为,用户在互动的过程中会发生一次或多次的会话,并且每个会话都会产生一个或多个事件。

在诸葛io数据分析平台中,使用「事件模型」来描述用户在产品上的各种行为,模型数据映射到用户、用户属性、事件、事件属性、设备这五张数据表中,SQL查询平台的使用者可以通过这五张表查询用户的行为数据,下图为各表之间的关系:

二、功能使用

1. 进入SQL查询

登录诸葛后,在「分析模块」点击导航菜单「SQL」进入SQL查询页。

SQL查询页面: blob.png

2. 编写SQL查询语句

进入后,您可以看到,在SQL查询的编辑器中已经有一条示例SQL语句,这条语句的作用是统计您产品一段时间内每天的新增用户数。您可以直接点击「执行」来查看结果,也可以根据需要修改或重新撰写SQL语句(注:撰写查询SQL语句时,最好不要查询全表,以免影响查询结果)。

blob.png

注:诸葛io使用Redshift存储用户行为数据,Redhsift是AWS提供的海量数据仓库,其支持的SQL语法可以参考这里:SQL语法参考

3. 查看查询结果

SQL成功执行后,您会在编辑器下方看到查询结果,结果默认会以数据表的方式呈现。

blob.png

4. 对查询结果进行可视化分析

执行结果默认为列表,可以切换图的方式,进行简单的可视化分析。目前支持图的类型包括柱状图、饼图、折线图、面积图、散点图,下图为折线图。

blob.png

在可视化分析时,通常会通过不同维度和度量进行分析,点击「settings」,您可以看到所有结果字段,可以拖拽任意的字段到key、value、group区域,设置您想要的可视化图。

blob.png

如果上面分析的结果还不能满足业务方提出的需求,您可以调整SQL语句并重复上面的步骤进行多次分析,直到得到可用的结果。

5. 保存查询结果

有些情况下,您需要将查询结果进行的处理,或和其他数据(如业务数据库中的数据)进行整合,以便产出进一步的分析结果或报告;诸葛io的SQL查询提供了查询结果的下载功能,点击切换图表功能栏“下载文件”按钮,下拉选择CSV,下载CSV文件到本地,可以做进一步的分析工作。

blob.png

6. SQL查询的高级功能

诸葛io的SQL查询是基于Zeppelin开发的; Zeppelin是一个Apache的孵化项目. 一个基于web的笔记本(Notebook),支持交互式数据分析。如果您之前用过Jupyter Notebook的话,应该能够很轻松的掌握Zeppelin 的用法。

SQL查询除了可以自己做更深入的探索分析外,可以对不同的业务方提供不同的分析,比如产品、市场、运营,诸葛ioSQL查询平台可以创建多个Note,每个Note里可以创建多个查询,在Notebook下拉框中点击「+ Create newnote」创建新的Note页。

图片.png

同时,根据业务方的不同的分析指标,一个Note页中可以创建多个SQL查询进行查询分析,在每个查询编辑器的设置中,点击「Insert New」或者点击编辑器下方的「+」即可在当前编辑器下方增加一个新的查询编辑器。

图片.png

另外,Note还提供以报表的形式展示Note页的查询结果,在功能栏中点击Note显示样式切换下拉菜单,选择“report”,即可查看。

blob.png

以上为SQL查询一些常见的功能使用,SQL查询提供的功能不仅仅如此,您可以在使用SQL查询过程中去体验,体验SQL查询一些看似微小但却很强大的功能。

三、常见查询案例

在功能介绍中,仅以常见的查询案例中的一种为例进行查询,下面是几个常见的查询(以下查询案例均以京北商城为例)。

1. 查询注册用户和实名用户

通过user_id查实名用户,user_id为接入数据后,埋点自定义的user_id;查最后一次的user_id不为null则为实名用户,为null则为匿名用户(指没有调用identify方法)。

实名用户查询示例:

%jdbc
select 
    * 
from
    (
      select 
          zg_id,
          max(user_id) as user_id 
      from 
          b_user_35510 
      group by 
          zg_id
      )
 where 
     user_id is not null

匿名用户查询示例:

%jdbc
select 
    * 
from
    (
      select 
          zg_id,
          max(user_id) as user_id 
      from 
          b_user_35510 
      group by 
          zg_id
      )
 where 
     user_id is null

2. 统计每日的活跃用户数

查询事件表,以最近7天为例,查询每天的活跃用户数,通过聚合函数count()计算zg_id列非 NULL 值的行的数量,并通过日期进行分组和排序;另使用trunc()截断时间戳并返回日期。

%jdbc.sql     
select       
    trunc(begin_date) as act_date,        
    count(distinct zg_id)     
 from        
    b_user_event_all_35510     
 where        
    begin_date between current_date-7 and current_date     
 group by       
    act_date
 order by       
    act_date

3. 统计单个事件的触发次数(如「加入购物车」的人数)

查询事件表,以最近7天和事件名称event_name(加入购物车)为条件,通过聚合函数count()计算distinct zg_id。

%jdbc
select    
      trunc(begin_date) as date,     
      count(distinct zg_id) as user_count,  
from 
       b_user_event_all_35510
where 
       begin_date between current_date-7 and current_date  
       and event_name = '加入购物车'
group by 
       date
order by
       date

4. 统计单个事件的触发次数(如「加入购物车」的次数)

查询事件表,以最近7天和事件名称event_name(加入购物车)为条件,通过聚合函数count()计算event_id。

%jdbc
select    
      trunc(begin_date) as date,     
      count(event_id) as event_count,  
from 
       b_user_event_all_35510
where 
        begin_date between current_date-7 and current_date  
       and event_name = '加入购物车'
group by 
       date
order by
       date

附录:诸葛io用户行为数据表详述

1. 事件总表(b_user_event_all_appid)

表名b_user_event_all_appid,appid为应用id;字段、字段类型以及字段说明如下:

字段 类型 字段说明
zg_id int8 诸葛id,诸葛为用户分配的唯一标识,一个zg_id为一个用户
device_id int4 设备id
user_id int4 记录每一位用户的唯一id,可以是用户id,email等唯一值作为用户在诸葛io的user_id
session_id int8 会话id
event_name varchar 事件名称
event_id int4 事件类型id,事件类型id包含两个特定事件的值:-1为会话开始、-2为会话结束
begin_date timestamp 开始时间
begin_day_id int4 日期,开始时间的整数格式(yyyyMMdd)
platform int2 平台,1 Android、2 iOS、3 JS
network int2 网络,0为移动网络、1为2G、2为3G、3为4G、4为WIFI
mccmnc int4 int4 运营商,46002、46007为中国移动,46003、46005、46011为中国电信,46001、46006为中国联通,46020为中国铁通
useragent varchar 用户代理
website varchar 来源网站,标识来用户自哪个网站,只限JS平台
current_url varchar 当前URL,只限JS平台
referrer_url varchar 来源URL,标识用户来自哪个网站的URL,只限JS平台
channel varchar 渠道
app_version varchar 版本,只限Android、iOS平台
ip int8 用户IP
country varchar 国家,基于用户IP
area varchar 地区,基于用户IP
city varchar 城市,基于用户IP
os varchar 操作系统,只限Android、iOS平台
ov int4 操作系统版本,只限Android、iOS平台
bs varchar 浏览器,只限JS平台
bv int4 浏览器版本,只限JS平台
utm_source varchar 广告来源,标识来自哪个渠道,只限JS平台
utm_medium varchar 广告媒介,标识来自哪种媒介,只限JS平台
utm_campaign varchar 广告名称,标识推广的主题,只限JS平台
utm_content varchar 广告内容,标识同一推广主题下的不同版本或不同内容,只限JS平台
utm_term varchar 广告关键词,标识推广所使用的关键字,只限JS平台
duration int8 持续时间
utc_date timestamp UTC时间

2. 事件属性表(b_user_event_attr_appid_eventid)

表名b_user_event_attr_appid_eventid,appid为应用id,eventid为事件类型id;字段、字段类型以及字段说明如下:

字段 类型 字段说明
zg_id int8 诸葛id,诸葛为用户分配的唯一标识,一个zg_id为一个用户
device_id int4 设备id
user_id int4 记录每一位用户的唯一id,可以是用户id,email等唯一值作为用户在诸葛io的user_id
session_id int8 会话id
event_id int4 事件类型id,事件类型id包含两个特定事件的值:-1为会话开始、-2为会话结束
event_name varchar 事件名称
attr_id int4 事件属性id
attr_name varchar 事件属性名称
attr_data_type varchar 事件属性数据类型
attr_value varchar 事件属性值
begin_date timestamp 开始时间
begin_day_id int4 日期,开始时间的整数形式(yyyyMMdd)
platform int2 平台,1 Android、2 iOS、3 JS
utc_date timestamp UTC时间

3. 用户表(b_user_appid)

表名b_user_appid,appid为应用id;字段、字段类型以及字段说明如下:

字段 类型 字段说明
device_id int4 设备id
user_id int4 记录每一位用户的唯一id,可以是用户id,email等唯一值作为用户在诸葛io的user_id
zg_id int8 诸葛id,诸葛为用户分配的唯一标识,一个zg_id为一个用户
begin_date timestamp 生成时间
platform int2 平台,1 Android、2 iOS、3 JS

4. 用户属性表(b_user_property_appid)

表名b_user_property_appid,appid为应用id;字段、字段类型以及字段说明如下:

字段 类型 字段说明
zg_id int8 诸葛id,诸葛为用户分配的唯一标识,一个zg_id为一个用户
user_id int4 注册用户的id,诸葛默认生成,不是identity中上传的id
property_id int4 用户属性id
property_name varchar 用户属性名称
property_data_type varchar 用户属性数据类型
property_value varchar 用户属性值
platform int2 平台,1 Android、2 iOS、3 JS

注:property_id为-1的字段为用户ID属性,相对应的property_value值为用户id值(上传的用户唯一标识)。

5. 设备表(b_device_appid)

表名b_device_appid,appid为应用id;字段、字段类型以及字段说明如下:

字段 类型 字段说明
device_id int4 设备id
device_md5 varchar md5
platform int2 平台,1 Android、2 iOS、3 JS
device_type varchar 设备类型
l int4 水平像素
h int4 垂直像素
device_brand varchar 设备商标
device_model varchar 设备型号
resolution varchar 分辨率
imei varchar 移动设备标识,由15位数字组成
mac varchar mac地址
is_prison_break int2 是否越狱
is_crack int2 是否破解
language varchar 语言
timezone varchar 时区

文档由诸葛io材料组编写,如您对文档的阅读体验有任何意见和建议,可与我们取得联系:support@zhugeio.com

©zhugeio.com 京ICP备15049545号            文档更新时间 2024-01-12 08:31:26

results matching ""

    No results matching ""