QuickBI如何进行交叉分析

产品名称

QuickBI

产品模块

数据集

概述

本文通过介绍SQL构建怎样的数据模型,实现交叉分析

问题描述

现有数据:用户id、来源渠道(一个用户id对应多行不同的来源渠道)。想查看同时存在于任意两个来源渠道的用户数。

channel_name user_id
A 111
B 111
C 111
A 222
B 222
C 222
A 333
B 333
C 333

解决方案

您可以通过SQL构建数据集实现该场景,可参考如下SQL:

select a.channel_name_A,b.channel_name_B,a.user_id
from(
select user_id,channel_name as channel_name_A
from user_cross_analytic
group by  user_id,channel_name 
)a
left join(
select user_id,channel_name as channel_name_B
from user_cross_analytic
group by  user_id,channel_name 
)b on a.user_id=b.user_id and a.channel_name_A<>b.channel_name_B

通过上述SQL把一个channel_name字段,拆成了两个channel_name。效果如下

channel_name_A channel_name_B user_id
A B 111
A C 111
B A 111
B C 111
C A 111
C B 111
A B 222
A C 222
B A 222
B C 222
C A 222
C B 222
A B 333
A C 333
B A 333
B C 333
C A 333
C B 333

然后仪表板中,可以把这两个字段通过交叉表展示出来,分别放到中,如下图所示:

备注:

  1. 如果您在使用该方法做分析时感觉查询速度明显变慢,可以在数据库中提前将该表结构计算好,然后建数据集时直接从已计算好的表中创建即可。
  2. 如果您的分析维度较多,全部维度值展示出来查看不方便,想通过查询条件筛选出任意的维度值后查看相应数据(比如:想通过查询条件查看任意几个“来源渠道”的交叉用户数)。可以通过参数SQL实现(当前表结构也可以支持该场景,但是使用参数SQL创建数据集,效率会更高一些),可参考如下SQL:
select a.channel_name_A,b.channel_name_B,a.user_id
from(
select user_id,channel_name as channel_name_A
from user_cross_analytic
where ${channel_name:channel_name_A}

group by  user_id,channel_name 
)a
left join(
select user_id,channel_name as channel_name_B
from user_cross_analytic
where ${channel_name:channel_name_B}
group by  user_id,channel_name 
)b on a.user_id=b.user_id and a.channel_name_A<>b.channel_name_B

 

更多信息

相关文档

如果您不了解参数SQL如何运用,以及如何书写规范的参数SQL,可以参考文档:https://help.aliyun.com/knowledge_detail/273069.html