Quick BI如何进行交叉分析

概述

本文通过介绍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，可以参考Quick BI的参数SQL应该怎样写。

适用于

• 产品名称：Quick BI
• 产品模块：数据集