sql - 为每一行比较 SQL 中的不同列

Question

经过一些转换后，我得到了一个交叉连接(来自表 a 和 b)的结果，我想对其进行一些分析。这个表看起来像这样:

+-----+------+------+------+------+-----+------+------+------+------+
| id  | 10_1 | 10_2 | 11_1 | 11_2 | id  | 10_1 | 10_2 | 11_1 | 11_2 |
+-----+------+------+------+------+-----+------+------+------+------+
| 111 |    1 |    0 |    1 |    0 | 222 |    1 |    0 |    1 |    0 |
| 111 |    1 |    0 |    1 |    0 | 333 |    0 |    0 |    0 |    0 |
| 111 |    1 |    0 |    1 |    0 | 444 |    1 |    0 |    1 |    1 |
| 112 |    0 |    1 |    1 |    0 | 222 |    1 |    0 |    1 |    0 |
+-----+------+------+------+------+-----+------+------+------+------+

第一列的id和第六列的id不同。在一行中总是有两个相互匹配的不同 ID。其他列的值始终为 0 或 1。

我现在试图找出两个 ID 平均有多少个值(意思是在 10_1、10_2 等中都有“1”)，但我真的不知道该怎么做。

我正在尝试这样的开始:

SELECT SUM(CASE WHEN a.10_1 = 1 AND b.10_1 = 1 then 1 end)

但这显然只会计算两个 id 的共同点为 10_1 的频率。例如，我可以为不同的列做这样的事情:

SELECT SUM(CASE WHEN (a.10_1 = 1 AND b.10_1 = 1) 
OR (a.10_2 = 1 AND b.10_1 = 1) OR [...] then 1 end)

一般计算两个 ID 有一个共同点的频率，但如果他们有两个或更多个共同点，这当然也算在内。另外，我还想知道两个 IDS 有两个、三个共同点等的频率。

在我的案例中，还有一个“问题”是我有大约 30 列我想查看，所以我几乎无法为每个案例写下所有可能的组合。

有谁知道我怎样才能更好地解决我的问题？提前致谢。

编辑:可能的结果如下所示:

+-----------+---------+
| in_common |  count  |
+-----------+---------+
|         0 |     100 |
|         1 |     500 |
|         2 |    1500 |
|         3 |    5000 |
|         4 |    3000 |
+-----------+---------+

Answer 1

将代码作为列名，您将不得不编写一些明确引用每个列名的代码。为了将其保持在最低限度，您可以将这些引用写在一个标准化数据的联合语句中，例如:

select id, '10_1' where "10_1" = 1
union
select id, '10_2' where "10_2" = 1
union
select id, '11_1' where "11_1" = 1
union
select id, '11_2' where "11_2" = 1;

这需要修改以包含链接不同 ID 所需的任何其他列。为了便于说明，我假设以下数据模型

create table p (
    id integer not null primary key,
    sex character(1) not null,
    age integer not null
    );

create table t1 (
    id integer not null,
    code character varying(4) not null,
    constraint pk_t1 primary key (id, code)
    );

虽然您的数据目前显然与此结构不相似，但将您的数据规范化为这样的形式将使您能够应用以下解决方案以所需的形式汇总您的数据。

select
    in_common,
    count(*) as count
from (
    select
        count(*) as in_common
    from (
        select
        a.id as a_id, a.code,
        b.id as b_id, b.code
        from
        (select p.*, t1.code
            from p left join t1 on p.id=t1.id
            ) as a
        inner join (select p.*, t1.code
            from p left join t1 on p.id=t1.id
            ) as b on b.sex <> a.sex and b.age between a.age-10 and a.age+10
        where
        a.id < b.id
        and a.code = b.code
        ) as c
    group by
        a_id, b_id
    ) as summ
group by
    in_common;