sql - 在同一行中的两个 array_agg 列上相交

Question

我有一个简单的 Postgres 数据集，如下所示:

INSERT INTO mytable (day, person)
values
('Monday', 'A'),
('Monday', 'B'),
('Tuesday', 'A'),
('Thursday', 'B');

然后我运行一个查询，生成两个 array_aggs，如下所示:

SELECT *
FROM (select day as d1,
             array_agg(distinct person) as agg1
      from mytable
      group by day) AS AA
   cross join
     (select day as d2,
             array_agg(distinct person) as agg2
      from mytable
      group by day) AS BB

产生这个数据集:

Monday, {A,B}, Monday, {A,B}
Monday, {A,B}, Thursday, {B}
Monday, {A,B}, Tuesday, {A}
Thursday, {B}, Monday, {A,B}
Thursday, {B}, Thursday, {B}
Thursday, {B}, Tuesday, {A}
Tuesday, {A}, Monday, {A,B}
Tuesday, {A}, Thursday, {B}
Tuesday, {A}, Tuesday, {A}

我想在此查询中添加第五列，用于标识每一行中 agg1 和 agg2 中的重复条目数。

因此，例如，第一行为 2，第二行为 1。我希望按如下方式进行，但这给了我一个不明确的语法错误:

SELECT *, count(select unnest(agg1) intersect select unnest(agg2))
FROM (select day as d1,
             array_agg(distinct person) as agg1
      from mytable
      group by day) AS AA
   cross join
     (select day as d2,
             array_agg(distinct person) as agg2
      from mytable
      group by day) AS BB

Answer 1

Postgresql 有 LATERAL。

可用于在记录级别对字段内容执行某些操作。

create table mytable (day varchar(30), person varchar(1));

INSERT INTO mytable (day, person)
values
('Monday', 'A'),
('Monday', 'B'),
('Tuesday', 'A'),
('Thursday', 'B');

SELECT *
FROM (
  select day as d1,
             array_agg(distinct person) as agg1
      from mytable
      group by day) AS AA
   cross join
     (select day as d2,
             array_agg(distinct person) as agg2
      from mytable
      group by day
) AS BB
CROSS JOIN LATERAL 
(
   SELECT COUNT(*) AS MatchingPersons
   FROM
   (
     SELECT unnest(agg1) person
     INTERSECT
     SELECT unnest(agg2)
   ) q
) lat 

d1       | agg1  | d2       | agg2  | matchingpersons:------- | :---- | :------- | :---- | --------------:Monday   | {A,B} | Monday   | {A,B} |               2Thursday | {B}   | Monday   | {A,B} |               1Tuesday  | {A}   | Monday   | {A,B} |               1Monday   | {A,B} | Thursday | {B}   |               1Thursday | {B}   | Thursday | {B}   |               1Tuesday  | {A}   | Thursday | {B}   |               0Monday   | {A,B} | Tuesday  | {A}   |               1Thursday | {B}   | Tuesday  | {A}   |               0Tuesday  | {A}   | Tuesday  | {A}   |               1

db<> fiddle here