dhruv jadia dhruv jadia - 2 months ago 17
MySQL Question

Postgres query for matrix table

I want to make a query in MySQL or postgres that will be generated from 4 tables.

Please see the following tables.

I want postgres or sql query for matrix table that is defined below.

How can I achieve this using SQL?

Thank you a lot in advance.

table: Targets

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


table: Target_groups

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


table: 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 |
+----+-----------+--------------+


table: 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 |
+----+-----------+--------------+


Matrix table that I want

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

Answer

In Postgres you need the extension tablefunc to generate pivot table:

create extension if not exists tablefunc;

The query with 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)

The same query with the aggregate function string_agg() instead of 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)
Comments