如何将多列事件共现表转换为成对事件共现加计数的三列结构

以下提供两种不同适配场景的SQL实现方案，均适配你10个事件的业务场景，调整参数后即可直接使用：

方案1：高阶函数简化版（适配Spark SQL 3.0+、Presto、Trino等支持combinations函数的引擎） #

逻辑为先筛选每行发生的事件生成事件数组，再直接调用内置函数生成两两不重复组合，最后炸开数组得到结果：

WITH get_event_list AS (
    SELECT
        *,
        -- 下方数组内的事件名、CASE判断分支替换为你实际的10个事件列名即可
        FILTER(ARRAY('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J'),
            event -> CASE event
                WHEN 'A' THEN A = 1
                WHEN 'B' THEN B = 1
                WHEN 'C' THEN C = 1
                -- 按上述格式补全剩余7个事件的判断逻辑
            END
        ) AS event_list,
        n
    FROM 你的原表名称
),
gen_pairs AS (
    SELECT
        *,
        -- 单事件场景单独处理生成自身配对，多事件场景直接生成2元素不重复组合
        IF(SIZE(event_list) = 1, ARRAY(ARRAY(event_list[0], event_list[0])), COMBINATIONS(event_list, 2)) AS event_pairs
    FROM get_event_list
)
-- 炸开配对数组得到最终三列结果
SELECT
    pair[0] AS Event1,
    pair[1] AS Event2,
    n
FROM gen_pairs
LATERAL VIEW EXPLODE(event_pairs) tmp AS pair

方案2：通用兼容版（适配所有支持EXPLODE/LATERAL VIEW的SQL引擎，包括低版本Hive、Impala等） #

逻辑为先把原表从宽格式转成长格式，再通过同行自连接生成不重复的两两配对：

WITH long_format AS (
    SELECT
        row_id,
        event_name,
        n
    FROM (
        SELECT
            -- 生成唯一行标识，无唯一行键的场景可替换为CONCAT_WS('_', A,B,C,D,E,F,G,H,I,J)
            UUID() AS row_id,
            n,
            CASE WHEN A = 1 THEN 'A' END AS A,
            CASE WHEN B = 1 THEN 'B' END AS B,
            CASE WHEN C = 1 THEN 'C' END AS C,
            -- 按上述格式补全剩余7个事件的判断逻辑
        FROM 你的原表名称
    ) t
    LATERAL VIEW EXPLODE(ARRAY(A,B,C,D,E,F,G,H,I,J)) tmp AS event_name
    WHERE event_name IS NOT NULL
)
-- 同行自连接生成不重复配对，a.event_name <= b.event_name保证不会出现(A,B)、(B,A)这类重复对
SELECT
    a.event_name AS Event1,
    b.event_name AS Event2,
    a.n
FROM long_format a
INNER JOIN long_format b
    ON a.row_id = b.row_id
    AND a.event_name <= b.event_name

注意事项 #

• 两种方案的输出完全符合你的转换规则，单事件会生成自身配对，多事件仅生成不重复的两两组合
• 10个事件的场景下，单条数据最多生成45条配对结果，数据膨胀可控，性能无问题

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