sql - PostgreSQL - 按数组排序

Question

我有 2 个表 - course 包含类(class)的 id 和名称，tagCourse 包含每门类(class)的标签。

course                    tagcourse
------------            ----------------
PK id_course            PK tag
   name                 PK, FK id_course

我想编写一个函数，按给定的标签数组搜索类(class)，并返回按匹配标签数量排序的类(class)。但是我不知道如何正确有效地编写它。请帮助我。

即。

CREATE OR REPLACE FUNCTION searchByTags(tags varchar[])
RETURNS SETOF.....
  RETURN QUERY SELECT * FROM course c INNER JOIN tagcourse tc ON c.id_course = tc.id_course
  WHERE ???  ORDER BY ???

END....

Answer 1

CREATE OR REPLACE FUNCTION search_by_tags(tags varchar[])
  RETURNS TABLE (id_course integer, name text, tag_ct integer)
  LANGUAGE sql AS
$func$
   SELECT id_course, c.name, ct.tag_ct
   FROM  (
      SELECT tc.id_course, count(*)::int AS tag_ct
      FROM   unnest($1) x(tag)
      JOIN   tagcourse tc USING (tag)
      GROUP  BY 1                      -- first aggregate ..
      ) AS ct
   JOIN   course c USING (id_course)   -- .. then join
   ORDER  BY ct.tag_ct DESC            --  more columns to break ties?
$func$;

使用unnest()从您的输入数组生成一个表，例如 already demonstrated by @Clodoaldo .

为此您不需要 plpgsql。使用普通 SQL 函数更简单。

我使用 unnest($1)(带有位置参数)而不是 unnest(tags)，因为后者仅对 SQL 函数中的 PostgreSQL 9.2+ 有效(不同于plpgsql)。 The manual:

In the older numeric approach, arguments are referenced using thesyntax $n: $1 refers to the first input argument, $2 to the second,and so on. This will work whether or not the particular argument wasdeclared with a name.

count() 返回 bigint .您需要将其转换为 int 以匹配声明的返回类型或将返回的列声明为 bigint 以开始。

用 USING 稍微简化语法的完美时机(等值连接):USING (tag) 而不是 ON tc.tag = c.tag。

先聚合，然后连接到另一个表通常会更快。减少所需的连接操作。
地址@Clodoaldo's comments ，这是一个展示差异的 fiddle :

db<> fiddle here
<子>旧sqlfiddle

OTOH，如果您在连接后进行聚合，则不需要子查询。更短，但可能更慢:

SELECT c.id_course, c.name, count(*)::int AS tag_ct
FROM   unnest($1) x(tag)
JOIN   tagcourse tc USING (tag)
JOIN   course     c USING (id_course)
GROUP  BY 1
ORDER  BY 3 DESC;  --  more columns to break ties?