PostgreSQL-按数组排序 [英] PostgreSQL - order by an array

问题描述

我有2个表-包含课程ID和课程名称的课程以及包含每个课程标签的tagCourse。

I have 2 tables - course that contains id and name of the courses and tagCourse that contains tags for each course.

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

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

I'd like to write a function that searches courses by given array of tags and returns them ordered by quantity of matching tags. However I don't know how to write it correctly and in an efficient way. Please help me.

即。

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

推荐答案

CREATE OR REPLACE FUNCTION search_by_tags(tags varchar[])
  RETURNS TABLE (id_course integer, name text, tag_ct integer) 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$  LANGUAGE sql;

• 使用 unnest（） 从您的输入数组生成一个表，例如已由@Clodoaldo演示。

  您不需要plpgsql。使用简单的SQL函数更简单。

  You don't need plpgsql for this. Simpler with a plain SQL function.

  我改用 unnest（$ 1）（带有位置参数）的 unnest（tags），因为后者仅对SQL函数中的PostgreSQL 9.2+有效（与plpgsql不同）。 在此处引用该手册：

  I use unnest($1) (with positional parameter) instead of unnest(tags), since the later is only valid for PostgreSQL 9.2+ in SQL functions (unlike plpgsql). I quote the manual here:

  
  

  在较旧的数字方法中，使用$ b $引用参数b语法 $ n ： $ 1 引用第一个输入参数 $ 2 到第二个，
  ，依此类推。不管特定的参数是否是用名称声明的
  都可以使用。

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

  
  
  

  
  

  • count（）返回 bigint 。您需要将其强制转换为 int 以匹配声明的返回类型，或者将返回的列声明为 bigint 开头

    • count() returns bigint. You need to cast it to int to match the declared return type or declare the the returned column as bigint to begin with.

      使用 使用中 （等价加入）：使用中（标记） 而不是 ON tc.tag = c.tag 。

      它是定期更快地 first 进行汇总，然后 then 联接到另一个表。减少所需的联接操作。
      
      根据 @的问题注释中的Clodoaldo ，这是 SQL Fiddle > 演示差异。

      It's regularly faster to first aggregate, then join to another table. Reduces the needed join operations.
      As per question of @Clodoaldo in the comments, here is an SQL Fiddle to demonstrate the difference.

      OTOH，如果在连接后进行汇总，则不需要子查询。较短，但可能较慢：

      OTOH, if you aggregate after the join, you don't need a subquery. Shorter, but probably slower:

      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?
      

      这篇关于PostgreSQL-按数组排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！