php - 矩阵表的 Postgres 查询

Question

我想在 MySQL 或 postgres 中进行查询，该查询将从 4 个表生成。

请参阅下表。

我想要 postgres 或 sql 查询下面定义的矩阵表。

如何使用 SQL 实现此目的？

非常感谢您。

表:目标

+----+-------------+
| id | name        |
+----+-------------+
|  1 | 9999999991  |
|  2 | 9999999992  |
|  3 | 9999999993  |
|  4 | 9999999994  |
|  5 | 9999999995  |
|  6 | 9999999996  |
|  7 | 9999999997  |
|  8 | 9999999998  |
+----+-------------+

表:Target_groups

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Group 1     |
|  2 | Group 2     |
|  3 | Group 3     |
|  4 | Group 4     |
+----+-------------+

表:Target_groups_map

+----+-----------+--------------+
| id |targets    | target_groups|
+----+-----------+--------------+
|  1 | 9999999991|    1         |
|  2 | 9999999992|    1         |
|  3 | 9999999993|    2         |
|  4 | 9999999994|    2         |
|  5 | 9999999995|    3         |
|  6 | 9999999996|    3         |
|  6 | 9999999997|    4         |
|  6 | 9999999998|    4         |
+----+-----------+--------------+

表:Call_details

+----+-----------+--------------+
| id | caller    | called       |
+----+-----------+--------------+
|  1 | 9999999995| 9999999996   |
|  2 | 9999999992| 9999999998   |
|  3 | 9999999993| 9999999998   |
|  4 | 9999999994| 9999999991   |
|  5 | 9999999995| 9999999998   |
|  6 | 9999999996| 9999999992   |
|  6 | 9999999991| 9999999993   |
|  6 | 9999999992| 9999999998   |
+----+-----------+--------------+

我想要的

矩阵表

+--------+--------+--------+--------+--------+
|        | Group 1| Group 2| Group 3| Group 4|
+--------+--------+--------+--------+--------+
| Group 1|     -  |     1  |     -  |     2  |
| Group 2|     1  |     -  |     -  |     1  |
| Group 3|     1  |     -  |     1  |     1  | 
| Group 4|     -  |     -  |     -  |     -  |
+--------+--------+--------+--------+--------+

Answer 1

在 Postgres 中你需要扩展 tablefunc生成数据透视表:

create extension if not exists tablefunc;

使用crosstab() 的查询:

select * from crosstab($$
    select t1.name caller_name, t2.name called_name, count
    from target_groups t1
    cross join target_groups t2
    left join (
        select c1, c2, count(*)::int
        from (
            select g1.target_groups c1, g2.target_groups c2
            from call_details c
            join target_groups_map g1 on c.caller = g1.targets
            join target_groups_map g2 on c.called = g2.targets
            ) c
        group by 1, 2
        order by 1, 2
        ) c
    on t1.id = c1 and t2.id = c2
$$) 
as ct (" " text, "Group 1" int, "Group 2" int, "Group 3" int, "Group 4" int)

         | Group 1 | Group 2 | Group 3 | Group 4 
---------+---------+---------+---------+---------
 Group 1 |         |       1 |         |       2
 Group 2 |       1 |         |         |       1
 Group 3 |       1 |         |       1 |       1
 Group 4 |         |         |         |        
(4 rows)

与聚合函数相同的查询 string_agg()而不是 crosstab():

select caller_name as " ", string_agg(coalesce(count::text, '-'), ', ') matrix
from (
    select t1.name caller_name, t2.name called_name, count
    from target_groups t1
    cross join target_groups t2
    left join (
        select c1, c2, count(*)::int
        from (
            select g1.target_groups c1, g2.target_groups c2
            from call_details c
            join target_groups_map g1 on c.caller = g1.targets
            join target_groups_map g2 on c.called = g2.targets
            ) c
        group by 1, 2
        order by 1, 2
        ) c
    on t1.id = c1 and t2.id = c2
    ) sub
group by 1
order by 1;

         |   matrix   
---------+------------
 Group 1 | -, 1, -, 2
 Group 2 | 1, -, -, 1
 Group 3 | 1, -, 1, 1
 Group 4 | -, -, -, -
(4 rows)