本客服平台由提供产品服务

SQL查询

诸葛君发表于:2017年02月20日 17:39:37更新于:2017年03月17日 15:43:20

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

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

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


1.用户行为数据模型简介

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

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



2.功能使用

2.1进入SQL查询

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

SQL查询入口:

blob.png

进入后的SQL查询页:

blob.png

2.2编写SQL查询语句

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

blob.png

注:诸葛io使用Redshift存储用户行为数据,Redhsift是AWS提供的海量数据仓库,其支持的SQL语法可以参考这里:http://docs.aws.amazon.com/zh_cn/redshift/latest/dg/r_SELECT_synopsis.html

2.3查看查询结果

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

blob.png

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

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

blob.png

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

blob.png

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

2.5保存查询结果

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

blob.png

2.6SQL查询的高级功能

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

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

图片.png

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

图片.png

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


blob.png

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

3.常见查询案例

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

3.1查询注册用户和实名用户

通过user_id查实名用户,user_id为接入数据后,埋点自定义的user_id;查最后一次的user_id不为null则为实名用户,为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 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

3.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.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

3.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_idint8诸葛id,诸葛为用户分配的唯一标识,一个zg_id为一个用户
device_idint4设备id
user_idint4记录每一位用户的唯一id,可以是用户id,email等唯一值作为用户在诸葛io的user_id
session_idint8会话id
event_namevarchar事件名称
event_idint4事件类型id,事件类型id包含两个特定事件的值:-1为会话开始、-2为会话结束
begin_datetimestamp开始时间
begin_day_idint4日期,开始时间的整数格式(yyyyMMdd)
platformint2平台,1 Android、2 iOS、3 JS
networkint2

网络,46002、46007为中国移动,46003、46005、46011为中国电信,46001、46006为中国联通,46020为中国铁通

mccmncint4运营商,1为 2G、2 为3G、3为4G、4为WIFI
useragentvarchar用户代理
websitevarchar来源网站,标识来用户自哪个网站,只限JS平台
current_urlvarchar当前URL,只限JS平台
referrer_urlvarchar来源URL,标识用户来自哪个网站的URL,只限JS平台
channelvarchar渠道
app_versionvarchar版本,只限Android、iOS平台
ipint8用户IP
countryvarchar国家,基于用户IP
areavarchar地区,基于用户IP
cityvarchar城市,基于用户IP
osvarchar操作系统,只限Android、iOS平台
ov
int4操作系统版本,只限Android、iOS平台
bsvarchar

浏览器,只限JS平台

bvint4

浏览器版本,只限JS平台

utm_sourcevarchar

广告来源,标识来自哪个渠道,只限JS平台

utm_mediumvarchar

广告媒介,标识来自哪种媒介,只限JS平台

utm_campaignvarchar

广告名称,标识推广的主题,只限JS平台

utm_contentvarchar

广告内容,标识同一推广主题下的不同版本或不同内容,只限JS平台

utm_termvarchar

广告关键词,标识推广所使用的关键字,只限JS平台

durationint8

持续时间

utc_datetimestamp

UTC时间

2.事件属性表(b_user_event_attr_appid_eventid)

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

字段类型字段说明
zg_idint8

诸葛id,诸葛为用户分配的唯一标识,一个zg_id为一个用户

device_idint4

设备id

user_idint4

记录每一位用户的唯一id,可以是用户id,email等唯一值作为用户在诸葛io的user_id

session_idint8

会话id

event_idint4

事件类型id,事件类型id包含两个特定事件的值:-1为会话开始、-2为会话结束

event_namevarchar

事件名称

attr_idint4

事件属性id

attr_namevarchar

事件属性名称

attr_data_typevarchar

事件属性数据类型

attr_valuevarchar

事件属性值

begin_datetimestamp

开始时间

begin_day_idint4

日期,开始时间的整数形式(yyyyMMdd)

platformint2

平台,1 Android、2 iOS、3 JS

utc_datetimestamp

UTC时间

3.用户表(b_user_appid)

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

字段类型字段说明
device_idint4设备id
user_idint4

记录每一位用户的唯一id,可以是用户id,email等唯一值作为用户在诸葛io的user_id

zg_idint8

诸葛id,诸葛为用户分配的唯一标识,一个zg_id为一个用户

begin_datetimestamp

生成时间

platformint2

平台,1 Android、2 iOS、3 JS

4.用户属性表(b_user_property_appid)

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

字段类型字段说明
zg_idint8诸葛id,诸葛为用户分配的唯一标识,一个zg_id为一个用户
user_idint4

记录每一位用户的唯一id,可以是用户id,email等唯一值作为用户在诸葛io的user_id

property_idint4

用户属性id

property_namevarchar

用户属性名称

property_data_typevarchar

用户属性数据类型

property_valuevarchar

用户属性值

platformint2

平台,1 Android、2 iOS、3 JS

5.设备表(b_device_appid)

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

字段类型字段说明
device_idint4

设备id

device_md5varcharmd5
platformint2

平台,1 Android、2 iOS、3 JS

device_typevarchar

设备类型

lint4

水平像素

hint4垂直像素
device_brandvarchar

设备商标

device_modelvarchar

设备型号

resolutionvarchar分辨率
imeivarchar移动设备标识,由15位数字组成
macvarchar

mac地址

is_prison_breakint2

是否越狱

is_crackint2是否破解
languagevarchar

语言

timezonevarchar

时区

 

    您需要登录后才可以回复