PostgreSQL – 按数组排序

我有2个表 – 课程包含课程的id和名称以及包含每门课程标签的tagCourse.

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

我想编写一个函数,通过给定的标签数组搜索课程,并按照匹配标签的数量对它们进行排序.但是我不知道如何以有效的方式正确地编写它.请帮帮我.

即.

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()从输入数组生成表,如already demonstrated by @Clodoaldo.
>你不需要plpgsql.使用普通的sql 函数更简单.
>我使用unexst($1)(带位置参数)而不是unexst(标签),因为后者仅对sql 函数中的Postgresql 9.2有效(与plpgsql不同). I quote the manual here：

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开头.
>使用USING(equi-joins)简化语法的完美场合：USING(tag)而不是ON tc.tag = c.tag.
>首次聚合通常会更快,然后加入另一个表.减少所需的连接操作.
根据@Clodoaldo in the comments的问题,这是一个SQL Fiddle,以证明其中的差异.
> OTOH,如果在连接后聚合,则不需要子查询.更短,但可能更慢：

SELECT c.id_course,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 – 按数组排序

猜你在找的Postgre SQL相关文章