无需在 Tableau 中加入即可将两个数据源连接在一起 [英] Connect two data sources together without Join in Tableau

问题描述

我在 tableau 中有两个数据源(A 和 B).关系为 1:n.带有列 ID(主键)、字段 1、字段 2... 的表 A(主表).带有列 ID(非 PK)、字段 X、字段 Y 的表 B.

I have two data sources in tableau (A and B). The relationship is 1:n. Table A (main Table) with columns ID (Primary Key), Field 1, Field 2... . Table B with columns ID (not PK), Field X, Field Y.

我想使用表 B 按字段 X 和字段 Y 进行过滤，然后在表 A 中的相关工作表绘图数据中使用 SQL 中等效于 WHERE A.ID IN (SELECT B.ID FROM TableBB) 其中表 B 已经被字段 X 和字段 Y 的值过滤了.

I want to use table B for filtering by Field X and Field Y and then in a related sheet plot data from table A with the filter that in SQL would be equivalent to WHERE A.ID IN (SELECT B.ID FROM TableB B) where the Table B would already be filtered by the values of Field X and Field Y.

经过一些研究，我发现了两个不能说服我的选项:

After some research I have found two options that do not convince me:

选项 1:在两个表之间使用公共内连接，然后在表 A 的度量上使用 AVG 和 COUNT DISTINCT 等聚合函数以避免重复.

Option 1: Use a common inner join between the two tables and then use aggregation functions like AVG and COUNT DISTINCT on the measures of table A to avoid duplication.

选项 2:根据从 Tableau 发送的参数对表 A 使用自定义 SQL，并在数据库级别过滤表 A.

Option 2: Use custom SQL on table A based on a parameter that is sent from Tableau and filter Table A at a database level.

最好的选择是在以下结构中的源之间进行连接

The best option would be to have a join between sources in the following structure

SELECT A.* FROM tableA A INNER JOIN (SELECT DISTINCT ID FROM TABLE B WHERE Field X IN (Dynamic selection from Tableau) AND Field Y IN (Dynamic selection from Tableau) ) B ON A.ID = B.ID

SELECT A.* FROM tableA A INNER JOIN (SELECT DISTINCT ID FROM TABLE B WHERE Field X IN (Dynamic selection from Tableau) AND Field Y IN (Dynamic selection from Tableau) ) B ON A.ID = B.ID

这种事情有可能吗?

谢谢！！

推荐答案

还有第三种选择，根据您的表的大小，它可能已经足够好了.使用 Tableau 数据混合.使用 Id 字段在 2 个表(数据 -> 编辑关系)之间创建链接.

There is a third option, that can be good enough depending on the sizes of your tables. Use Tableau Data Blending. Create a link between the 2 tables (Data -> Edit Relationships), using the Id field.

现在将您想要的信息从表 A 拖到工作表，然后将字段 X 和字段 Y 拖到过滤器.瞧.

Now drag the info you want from table A to the worksheet, then drag field X and field Y to Filter. Voilà.

如果表太大(尤其是表B)，您可能需要提前加入.但这可能会导致重复条目问题(如果 A 中的每个条目在 B 上有多个对应关系).左连接更可取(并且没有过滤器)

If the tables are too big (especially table B), you may want to join beforehand. But that could cause the duplicated entries problem (if there are more than one correspondence of each entry in A on B). A Left join is more advisable (and no filters)

这篇关于无需在 Tableau 中加入即可将两个数据源连接在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！