如何用SQL、Tableau、Power Query计算组间唯一公共值并生成对称矩阵？

针对你需要生成组间唯一公共值对称矩阵的需求，以下是Power Query、SQL、Tableau的高效实现方案，适配百万级数据集：

SQL 方案 #

先对原表去重减少计算量，再通过笛卡尔积生成组对，统计交集数量，最后转置为矩阵格式：

-- 第一步：提取每个组的唯一值（去重）
WITH unique_group_vals AS (
    SELECT DISTINCT "Group", Value FROM your_dataset
),
-- 第二步：生成所有组的两两组合
group_pairs AS (
    SELECT g1."Group" AS group_a, g2."Group" AS group_b
    FROM (SELECT DISTINCT "Group" FROM unique_group_vals) g1
    CROSS JOIN (SELECT DISTINCT "Group" FROM unique_group_vals) g2
),
-- 第三步：统计每组自身的唯一值总数
group_totals AS (
    SELECT "Group", COUNT(Value) AS total_unique
    FROM unique_group_vals
    GROUP BY "Group"
)
-- 第四步：计算组间公共值数量并转置为矩阵
SELECT
    group_a,
    [A], [B], [C], -- 替换为实际存在的组名，或用动态SQL自动生成
    total_unique
FROM (
    SELECT
        gp.group_a,
        gp.group_b,
        COUNT(ugv.Value) AS common_count,
        gt.total_unique
    FROM group_pairs gp
    LEFT JOIN unique_group_vals ugv ON gp.group_a = ugv."Group"
    LEFT JOIN unique_group_vals ugv2 ON gp.group_b = ugv2."Group" AND ugv.Value = ugv2.Value
    JOIN group_totals gt ON gp.group_a = gt."Group"
    GROUP BY gp.group_a, gp.group_b, gt.total_unique
) src
PIVOT (
    MAX(common_count)
    FOR group_b IN ([A], [B], [C])
) pivot_result
ORDER BY group_a;

注意：如果组数量不固定，可使用动态SQL自动生成PIVOT的列列表，避免手动维护。

Power Query 方案 #

通过自定义函数+交叉表实现，提前去重优化性能：

1. 数据预处理：导入数据后，选择Group和Value列，执行删除重复项操作。
2. 生成组列表：新建查询，输入List.Distinct(Table.Column(去重后的表, "Group"))，命名为allGroups。
3. 创建公共值计数函数：

fnCountSharedValues = (group1 as text, group2 as text) =>
let
    FilterGroup1 = Table.SelectRows(去重后的表, each [Group] = group1),
    FilterGroup2 = Table.SelectRows(去重后的表, each [Group] = group2),
    SharedValues = Table.Join(FilterGroup1, "Value", FilterGroup2, "Value"),
    Count = Table.RowCount(SharedValues)
in
    Count

4. 生成交叉表：
   • 新建表，输入List.CrossJoin(allGroups, allGroups)，转成表后命名列Group1、Group2。
   • 添加自定义列，调用fnCountSharedValues([Group1], [Group2])，命名为CommonCount。
5. 添加总计列：统计每个组的唯一值数量，合并到交叉表后，转置为矩阵格式。

优化建议：对去重后的表执行Table.Buffer()缓存，减少重复读取数据的开销。

Tableau 方案 #

利用数据源提取+LOD表达式高效计算，适配大数据集：

1. 数据提取优化：连接数据源后，点击数据 > 提取数据，勾选移除重复项（基于Group和Value列），提升查询速度。
2. 创建计算字段：
   • 公共值计数：

     {FIXED [Group], [对比组]: COUNTD(IF EXISTS(SELECT [Value] FROM [your_dataset] WHERE [Group] = [对比组] AND [Value] = [your_dataset].[Value]) THEN [Value] END)}
     

   • Total Unique：{FIXED [Group]: COUNTD([Value])}
3. 构建矩阵视图：
   • 将Group拖到行，对比组（可通过参数或LOD生成所有组列表）拖到列。
   • 将公共值计数拖到文本标记，Total Unique拖到列的最右侧作为总计列。
4. 格式调整：隐藏对称重复项（如B-A与A-B），设置矩阵样式。

内容的提问来源于stack exchange，提问作者gaurav burman