Intereting Posts
Ищете генератор тестовых наборов SQL, идеально с открытым исходным кодом Как передать переменную в оператор SqlCommand и вставить в таблицу базы данных java.sql.SQLException: превышено время ожидания блокировки; попробуйте перезапустить исключение транзакции в MYSQL Понимание Oracle Apex_Application.G_Fnn и как его использовать Android MediaStore имеет различные папки музыкальных файлов Параметрированные запросы с помощью RODBC Как узнать, когда хранимая процедура была в последний раз модифицирована или скомпилирована в Oracle? Команда SQL не закончилась должным образом? отображать пользовательский текст sql из результата столбца таблицы Как использовать INSERT … SELECT с автоматическим приращением конкретного столбца, начиная с 1? SQLite: полное внешнее соединение с четырьмя таблицами с 30 + столбцами Как написать запрос, используя самосоединение в таблице с огромными данными? Разделение поля, разделенного запятыми, в Postgresql и выполнение UNION ALL на всех результирующих таблицах SQL – объединить две таблицы с разным значением даты Выберите из другой таблицы, если значение существует

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() для создания таблицы из вашего входного массива, как это уже продемонстрировано @Clodoaldo .

    • Для этого вам не нужен plpgsql. Упрощен с простой функцией SQL.

    • Я использую unnest($1) (с позиционным параметром) вместо unnest(tags) , поскольку более поздняя версия действительна только для PostgreSQL 9.2+ в SQL-функциях (в отличие от plpgsql). Я цитирую здесь инструкцию :

    В более раннем цифровом подходе аргументы ссылаются с использованием синтаксиса $n : $1 – это первый входной аргумент, второй – второй и т. Д. Это будет работать независимо от того, был ли объявлен конкретный аргумент с именем.

    • count() возвращает bigint . Вам нужно передать его в int для соответствия объявленному типу возврата или объявить возвращаемый столбец как bigint для начала.

    • Прекрасная возможность упростить синтаксис с помощью USING (equi-join): USING (tag) вместо ON tc.tag = c.tag .

    • Регулярно быстрее сначала агрегировать, а затем присоединяться к другой таблице. Уменьшает необходимые операции соединения.
      По вопросу @Clodoaldo в комментариях , вот SQL Fiddle, чтобы продемонстрировать разницу.

    • 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? 
     create or replace function searchByTags(tags varchar[]) returns table (id_course integer, name text, quantitiy integer) as $$ select * from ( select c.id_course, c.name, count(*) quantity from course c inner join tagcourse tc on c.id_course = tc.id_course inner join unnest(tags) s(tag) on s.tag = tc.tag group by c.id_course, c.name ) s order by quantity desc, name ; $$ language sql;