Count of Table1_IDs in Table2_arrays

Question

我正在使用两个表：

CREATE TABLE Table1
(
id int,
name varchar
)
CREATE TABLE Table2
(
id int,
name varchar,
link array<int>
)

Table2.link 包含与 Table1.id 对应的值。我想计算每个 Table1.id 在 Table2.link 实例中出现的次数。在Excel中使用单元格引用是微不足道的，但是我不知道如何使用SQL查询来做到这一点。

Answer 1

Presto

select    *
from     (select    l.id           
                   ,count(*)    as cnt
          from      Table2 cross join unnest (link) as l(id)
          group by  l.id     
          ) t2
where     t2.id in (select id from Table1)
order by  id

---

presto:default> select    *
             -> from     (select    l.id
             ->                    ,count(*)    as cnt
             ->           from      Table2 cross join unnest (link) as l(id)
             ->           group by  l.id
             ->           ) t2
             -> where     t2.id in (select id from Table1)
             -> order by  id;
 id | cnt
----+-----
  1 |   7
  2 |   5
  3 |   4
(3 rows)

PostgreSQL 演示

create table Table1 (id int);
create table Table2 (arr int[]);
insert into Table1 values 
    (1),(2),(3)
;
insert into Table2 values 
    (array[1,5]),(array[1,3]),(array[1,2,3]),(array[2,3])
   ,(array[1,2,4]),(array[1,2]),(array[1,3,5]),(array[1,2,4])
;

---

select    *
from     (select    unnest(arr) as id             
                   ,count(*)    as cnt
          from      Table2
          group by  id     
          ) t2
where     t2.id in (select id from Table1)
order by  id

---

+----+-----+
| id | cnt |
+----+-----+
| 1  | 7   |
+----+-----+
| 2  | 5   |
+----+-----+
| 3  | 4   |
+----+-----+