arrays - 按数组中的匹配数排序

Question

我正在尝试获取最匹配项目的列表，给出带有以下数据的标签列表:

DROP TABLE IF EXISTS testing_items;
CREATE TEMP TABLE testing_items(
    id bigserial primary key,
    tags text[]
);
CREATE INDEX ON testing_items using gin (tags);

INSERT INTO testing_items (tags) VALUES ('{123,456, abc}');
INSERT INTO testing_items (tags) VALUES ('{222,333}');
INSERT INTO testing_items (tags) VALUES ('{222,555}');
INSERT INTO testing_items (tags) VALUES ('{222,123}');
INSERT INTO testing_items (tags) VALUES ('{222,123,555,666}');

我有标签 222 、 555 和 666 。我怎样才能得到这样的列表？

必须使用gin索引，因为会有大量的记录。

<表类="s-表"><头>id匹配<正文>53322141

id 1 不应该在列表中，因为它不匹配任何标签:

<表类="s-表"><头>id匹配<正文>10

Answer 1

取消嵌套标签，过滤未嵌套的元素并聚合剩余的元素:

select id, count(distinct u) as matches
from (
    select id, u
    from testing_items,
    lateral unnest(tags) u
    where u in ('222', '555', '666')
    ) s
group by 1
order by 2 desc

 id | matches 
----+---------
  5 |       3
  3 |       2
  2 |       1
  4 |       1
(4 rows)

---

考虑到所有的答案，这个查询似乎结合了每个答案的优点:

select id, count(*) 
from testing_items,
unnest(array['11','5','8']) u
where tags @> array[u] 
group by id 
order by 2 desc, 1;

它在 Eduardo 的测试中表现最好。