SQL查询

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

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

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

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

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

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

二、功能使用

1. 进入SQL查询

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

SQL查询页面：

2. 编写SQL查询语句

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

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

3. 查看查询结果

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

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

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

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

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

5. 保存查询结果

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

6. SQL查询的高级功能

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

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

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

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

以上为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；字段、字段类型以及字段说明如下：

2. 事件属性表(b_user_event_attr_appid_eventid)

表名b_user_event_attr_appid_eventid，appid为应用id，eventid为事件类型id；字段、字段类型以及字段说明如下：

3. 用户表(b_user_appid)

表名b_user_appid，appid为应用id；字段、字段类型以及字段说明如下：

4. 用户属性表(b_user_property_appid)

表名b_user_property_appid，appid为应用id；字段、字段类型以及字段说明如下：

注：property_id为-1的字段为用户ID属性，相对应的property_value值为用户id值（上传的用户唯一标识）。

5. 设备表(b_device_appid)

表名b_device_appid，appid为应用id；字段、字段类型以及字段说明如下：