Table2_arrays中Table1_ID的计数

I'm working with two tables:

我正在使用两个表：

CREATE TABLE Table1
(
id int,
name varchar
)

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

Table2.link contains values that correspond to Table1.id. I'd like to count how many times each Table1.id appears in an instance of Table2.link. This would be trivial using cell references in Excel, but I can't figure out how to do it with a SQL query.

Table2.link包含与Table1.id对应的值。我想计算每个Table1.id出现在Table2.link实例中的次数。这在Excel中使用单元格引用是微不足道的，但我无法弄清楚如何使用SQL查询来完成它。

1 个解决方案

#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 demo

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