Комментарии 59
Про similarity не знал. Могло бы сэкономить кучу времени в некоторых задачах, например, со строковыми адресами.
Как раз решаю сейчас задачи, с которыми similarity справляется на ура. Спасибо, автор! Пошел удалять лишний код.
Думаю, с приведением типов любой разработчик, использующий postgres рано или поздно столкнется, а вот про функции, подобные similarity жду следующий пост.
Думаю, с приведением типов любой разработчик, использующий postgres рано или поздно столкнется, а вот про функции, подобные similarity жду следующий пост.
Рад, что статья популярна не только из-за картинки (хотя, над ней я тоже попотел).
similarity и для меня была 'палочкой выручалочкой', грустно, когда и не предполагаешь, что СУБД может такое 'вытворять'.
Я бы с удовольствием выпустил «Часть 2», знать бы в сторону каких функций и фишек смотреть…
similarity и для меня была 'палочкой выручалочкой', грустно, когда и не предполагаешь, что СУБД может такое 'вытворять'.
Я бы с удовольствием выпустил «Часть 2», знать бы в сторону каких функций и фишек смотреть…
Курсоры — www.postgresql.org/docs/9.6/static/plpgsql-cursors.html
Процедурный язык PL/pgSQL — www.postgresql.org/docs/current/static/plpgsql.html
Я считаю, что на это стоит обратить внимание. Я бы сказал, что без использования этих вещей postgresql и не postgresql вовсе.
Процедурный язык PL/pgSQL — www.postgresql.org/docs/current/static/plpgsql.html
Я считаю, что на это стоит обратить внимание. Я бы сказал, что без использования этих вещей postgresql и не postgresql вовсе.
Откройте для себя товарища Левенштейна https://postgrespro.ru/docs/postgresql/9.4/fuzzystrmatch.html
Самое забавное, что раньше (на postgres 8.*) я использовал самописную функцию similarity в теле которого была функция Левенштейна (на 9.2 она затерлась, и я думал, что разница в результатах появилась из-за новой версии СУБД).
Я вырезал участок своего негодования из статьи, т.к. он даже не совпадал с документацией postgres 8, а перепроверив тело функции, я понял свою ошибку.
В спойлере можно почерпнуть разницу между similarity и levenshtein
P.S. Только жаль, что SOUNDEX не очень подходит для русского языка.
Я вырезал участок своего негодования из статьи, т.к. он даже не совпадал с документацией postgres 8, а перепроверив тело функции, я понял свою ошибку.
В спойлере можно почерпнуть разницу между similarity и levenshtein
Вырезанный участок, имеются ошибочные домыслы
Стоит отметить, что результат зависит от версии БД. Желание накатать подобную обучающую статью у меня возникло еще в январе (активно начал писать, потом благополучно забил, и лишь сейчас (октябрь) решил продолжить). Еще тогда я подготовил пример на Postgre 8.* (теперь то мы знаем, что это был levenshtein), получив нижеприведенный результат, который пришлось переделать.
Изменилось направление сортировки (теперь 1 это полное совпадение) (функция лишь была так описана), перестали учитываться знаки пунктуации и регистр, ушло ограничение в 255 символов. Одно мне не понравилось, что на моей текущей версии Postgres 9.2 выдало 2 строки с похожестью 1, ибо одна из них не является полностью идентичной (может это к лучшему). Возможно, есть дополнительные настройки, но я не углублялся в это т.к. не было необходимости.
Изменилось направление сортировки (теперь 1 это полное совпадение) (функция лишь была так описана), перестали учитываться знаки пунктуации и регистр, ушло ограничение в 255 символов. Одно мне не понравилось, что на моей текущей версии Postgres 9.2 выдало 2 строки с похожестью 1, ибо одна из них не является полностью идентичной (может это к лучшему). Возможно, есть дополнительные настройки, но я не углублялся в это т.к. не было необходимости.
P.S. Только жаль, что SOUNDEX не очень подходит для русского языка.
О, как! Есть готовое. Сам то всегда реализовывал такую задачу методом Q-грамм (Би-грамм если быть точным). Надеюсь, производительность реализации позволит в лоб сопоставить две таблицы хоты бы 10к в каждой.
«в лоб» будет медленно, т.е. надо для каждой строки из первой таблицы найти по всем записям similarity второй таблицы, сортирнуть по ней, и взять с наивысшим соответствием. Вот именно так я вчера и проверял, у меня уходило секунд ~6 на запись (limit 10 всего запроса работал минуту).
Правда у меня объём был 230к, зато реальных данных. И связка очень красиво так получилась! Адреса разной структуры — в одном опущена область, в другом индекс, первый структурированный с разделителем, второй ручной ввод.
Естественно некий процент ошибок будет, но это лучше, чем всё лопатить руками.
Правда у меня объём был 230к, зато реальных данных. И связка очень красиво так получилась! Адреса разной структуры — в одном опущена область, в другом индекс, первый структурированный с разделителем, второй ручной ввод.
Естественно некий процент ошибок будет, но это лучше, чем всё лопатить руками.
А попробуйте создать индекс для столбца по триграммам www.postgresql.org/docs/9.1/static/pgtrgm.html
Ну! 6 сек, на 230к строк … да небось в Unicode, со средней длиной ~50 символов – это круто!
Проверяет соответствие регулярному выражению с учётом регистра
LIKE использует не регулярные выражения, а свой синтаксис шаблонов. Кто это знает, понял, что имелось в виду, а кто не знает, мог подумать, что туда надо писать regexp.
Про временные таблицы есть ограничения, связанные с хранением версий строк: очень интенсивное их использование приводит к распуханию системных таблиц и снижению производительности, аналогично постоянным update обычных таблиц.
Как альтернативу временным таблицам могу предложить переменные типа jsonb или array композитных типов, правда индексы тут уже не применить, в отличие от временных таблиц. К слову, jsonb делает работу с данными в plpgsql гораздо удобнее, рекомендую взять на вооружение.
Как альтернативу временным таблицам могу предложить переменные типа jsonb или array композитных типов, правда индексы тут уже не применить, в отличие от временных таблиц. К слову, jsonb делает работу с данными в plpgsql гораздо удобнее, рекомендую взять на вооружение.
Спасибо, не знал о таком! Моя цель была просто продемонстрировать возможности, и подбить профессионалов на написание подобных статей.
Индексы в jsonb? Легко.
Это индекс по текстовому массиву. Думаю, нет смысла приводить примеры для простых строковых или числовых индексов.
CREATE INDEX station_synonyms_idx
ON station
USING btree
((additional_data #>> '{synonyms}'::text[]) COLLATE pg_catalog."default");Это индекс по текстовому массиву. Думаю, нет смысла приводить примеры для простых строковых или числовых индексов.
По колонке в таблице — легко, а тут вместо temp table предлагаю переменную типа jsonb для промежуточного хранения. Если внутри json-а просто dict, то вытаскивание значения по ключу происходит быстро.
Но если в переменной большой массив dict-ов, то как из него вытащить пару значений по каким то условиям, не перебрав его целиком? Индексы на переменные типа jsonb не повешать.
Но если в переменной большой массив dict-ов, то как из него вытащить пару значений по каким то условиям, не перебрав его целиком? Индексы на переменные типа jsonb не повешать.
dblink — не функция, а расширение. Странно, что всплыло именно оно, а не postgres_fdw: https://www.postgresql.org/docs/9.6/static/postgres-fdw.html
postgres_fdw, если память не изменяет, в 9.3 появился? А в более ранних версиях только dblink.
Если не ошибаюсь, в dblink в плане транзакций довольно странно: транзакциями наверное можно управлять явно на уровне выражений, но документация этот момент не описывает. В fdw управление транзакциями происходит автоматически.
Для удалённого вызова функций удобнее использовать pl/proxy или plexor вместо fdw. В первом, насколько знаю, также нет автоматического управления транзакциями, во втором оно автоматическое.
Для удалённого вызова функций удобнее использовать pl/proxy или plexor вместо fdw. В первом, насколько знаю, также нет автоматического управления транзакциями, во втором оно автоматическое.
Еще надо не забывать, что функции GREATEST и LEAST, в отличии от реализации в Oracle и DB2 LUW, игнорируют NULL.
SELECT GREATEST(1, 2, 3, NULL, 4, 5) -- 5SELECT GREATEST(1, 2, 3, NULL, 4, 5) FROM DUAL; -- NULLSELECT GREATEST(1, 2, 3, NULL, 4, 5) FROM SYSIBM.DUAL; -- NULLВот, кстати, прямо сейчас наткнулся на проблему с GREATEST и NULL. По задаче надо выбрать все положительные числа, вместо отрицательного — ноль. Но с сохранением NULL, а она не сохраняет его.
На скорую руку можно обойтись таким костылём:
Пояснение:
Тип поля — numeric, сначала преобразуется в integer, затем в boolean затем опять в integer. В итоге при любом значении поля, отличном от нуля, получим единицу на выходе. Или NULL, если field IS NULL.
На скорую руку можно обойтись таким костылём:
SELECT GREATEST(field, 0) * (field::integer::boolean::integer)Пояснение:
Тип поля — numeric, сначала преобразуется в integer, затем в boolean затем опять в integer. В итоге при любом значении поля, отличном от нуля, получим единицу на выходе. Или NULL, если field IS NULL.
Крутое решение, хотя я бы все же сделал через старый добрый CASE .. THEN .. ELSE .. END, тогда результат был такой же, однако тому кто потому будет доробатовать это дело не пришлось бы думать, а что вот здесь вот происходит
WITH TEST_DATA as (
SELECT unnest(ARRAY[1,NULL,42,-3,0,2,-15,NULL,55])::int field
)
SELECT
field
, GREATEST(field, 0) * (field::integer::boolean::integer) option_1
, CASE WHEN field < 0 THEN 0 ELSE field END option_2
FROM TEST_DATAЕстественно, если это код будет храниться. Если это разовый наколеночный запрос иногда костыли выручают. Ну и пример ниже придётся довольно сложно раскладывать
Собственно, вопрос в том, нет ли что-то похожего на greatest/least с учетом null? Нагуглить сходу не удалось.
select field1 * (a>b)::integer + field2 * (c>d)::integer + field3 * (e = f)::integer
Собственно, вопрос в том, нет ли что-то похожего на greatest/least с учетом null? Нагуглить сходу не удалось.
Ну… можно написать свои функции, я такие себе собираю, когда нужно быстро мигрировать с ORACLE с сохранением бизнес логики с наименьшими потерями
К примеру в данном случае можно использовать что-то навроде такого
CREATE OR REPLACE FUNCTION f_array_has_null (ANYARRAY)
RETURNS bool LANGUAGE sql IMMUTABLE AS
'SELECT array_position($1, NULL) IS NOT NULL';
CREATE FUNCTION f_least_ora(VARIADIC arr numeric[])
RETURNS numeric LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
IF f_array_has_null($1) THEN
RETURN NULL;
ELSE
RETURN (SELECT min(x) FROM unnest($1) x);
END IF;
END
$$ ;
CREATE FUNCTION f_greatest_ora(VARIADIC arr numeric[])
RETURNS numeric LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
IF f_array_has_null($1) THEN
RETURN NULL;
ELSE
RETURN (SELECT max(x) FROM unnest($1) x);
END IF;
END
$$ ;Ну и как результат
SELECT
least(1, NULL, 42, -3, 0, -0.5, -15, NULL, 55) -- -15
, f_least_ora(1, NULL, 42, -3, 0, -0.5, -15, NULL, 55) -- NULL
, least(1, 42, -3, 0, -0.5, -15, 55) -- -15
, f_least_ora(1, 42, -3, 0, -0.5, -15, 55) -- NULL
, greatest(1, NULL, 42, -3, 0, -0.5, -15, NULL, 55) -- 55
, f_greatest_ora(1, NULL, 42, -3, 0, -0.5, -15, NULL, 55) -- NULL
, greatest(1, 42, -3, 0, -0.5, -15, 55) -- 55
, f_greatest_ora(1, 42, -3, 0, -0.5, -15, 55) -- 55
А вот за абзац про экранирование строки через $$ вам от меня благодарность! Я писал функции и не понимал, что просто описываю тело функции в виде обычного текстового поля в ddl команде create function .... as $$ ... $$. По факту я могу смело писать
do language plpgsql 'begin select 1; end';вместо идущего в примерах
do language plpgsql $$begin select 1; end$$;ведь это одно и то же.
~ — это сокращенная запись LIKE?
Мне казалось, тильда делает поиск по регулярному выражению, а LIKE только учитывает проценты/черточки
Если есть NULLIF, то можно добавить и COALESCE, выбирает первое значение отличное от NULL.
SELECT COALESCE(NULL, NULL, 1, 2, 3, NULL, 4, 5); -- 1Добавил COALESCE и еще парочку
Ну, думаю, COALESCE знаком всем, кто плотно работает с PostgreSQL. Конструкции вида
WHERE COALESCE(field, 0) = 0 и подобные встречаются повсеместно, если тип поля допускает NULL. А на разовых запросах позволяет не вспоминать, что там за поле что оно допускает.Там же «курс молодого бойца», а не опытного ;-)
Кстати, я стараюсь не допускать идентичности NULL и 0 (NULL и ''). Так удобнее, чтобы пусто было пусто, а 0 может что-то значить. В WHERE, соответственно пишу FieldValue IS NULL. А при заполнении полей сначала привожу переменные _variable:=NULLIF(_variable,'') или в триггере причёсываю значения полей.
Кстати, я стараюсь не допускать идентичности NULL и 0 (NULL и ''). Так удобнее, чтобы пусто было пусто, а 0 может что-то значить. В WHERE, соответственно пишу FieldValue IS NULL. А при заполнении полей сначала привожу переменные _variable:=NULLIF(_variable,'') или в триггере причёсываю значения полей.
Кстати, я стараюсь не допускать идентичности NULL и 0
Да все стараются, как вы сказали, «с опытом». NULL разрешается только там, где он логически необходим, а на практике это не так уж и часто.
Но структуры достаются по наследству, либо сам не очень удачно спроектировал и т.д., когда на рефакторинг ресурсов нет, имеем что имеем…
Ещё хотелось бы добавить команду DO — выполнение анонимного блока кода. Бывает полезно когда нужно разово (по-быстрому) выполнить какие-то действия в транзакции, без создания отдельной функции.
DO $$
DECLARE
-- переменные
BEGIN
-- блок кода
-- * транзакция запускается автоматически
-- * для вывода данных удобно использовать RAISE NOTICE 'Data: %', foo;
END$$;
Зачем в 8 примере в запросе используются UNION ALL? С помощью VALUES можно выбрать сразу несколько строк:
SQL
WITH company (id,c_name) AS (
VALUES (1, 'ООО РОМАШка'),
(2, 'ООО "РОМАШКА"'),
(3, 'ООО РаМАШКА'),
(4, 'ОАО "РОМАКША"'),
(5, 'ЗАО РОМАШКА'),
(6, 'ООО РО МАШКА'),
(7, 'ООО РОГА И КОПЫТА'),
(8, 'ZAO РОМАШКА'),
(9, 'Как это сюда попало?'),
(10, 'Ромашка 33'),
(11, 'ИП "РомаШкович"'),
(12, 'ООО "Рома Шкович"'),
(13, 'ИП "Рома Шкович"')
)
SELECT *, similarity(c_name, 'ООО "РОМАШКА"')
,dense_rank() OVER (ORDER BY similarity(c_name, 'ООО "РОМАШКА"') DESC)
AS "Ранжирование результатов" -- оконная функций, о ней будет сказано ниже
FROM company
WHERE similarity(c_name, 'ООО "РОМАШКА"') >0.25 -- значения от 0 до 1, чем ближе к 1, тем точнее совпадение
ORDER BY similarity DESC;
Еще пара полезных штук:
1) Например вы используете составной ключ, и вам надо найти некоторые строки IN может работать с несколькими колонками:
SELECT * FROM product_attribute WHERE (product_id, attribute_id) IN ((1, 11), (2, 12), (2, 13))2) Конструкцию VALUES удобно иногда использовать в FROM и JOINах:
SELECT tmp.dig, tmp.name FROM (VALUES (1, 'one'), (2, 'two'), '3, 'three')) as tmp(dig, name)
Кстати, если в postgtes-е cte и материализуется, то в других СУБД это вовсе не обязательно, т.е. я бы не стал рассматривать cte как что-то типа временной таблицы на время выполнения запроса всегда и везде.
Уважаемые знатоки postgres, есть небольшая задачка, подскажите как правильно решить её с помощью postgres(сейчас использую свою коленнописную фурту на python для такого расчета).
Вообщем задачка:
Есть простая таблица с колонками id и price. В данных колонка записываются результаты примерно в таком порядке:
id price
1 45
2 80
3 75
4 125
5 50
6 70
7 22
8 23
9 47
10 20
Необходимо: отобразить id всех колонок, где (price)значение или сума сложения которых(-ой) будут равны, например, = 125
В приведенном случае это будут колонки: 4, 1+2, 3+5, 3+8+9 и так в порядке усложнения.
Подскажите пожалуйста более правильный вариант.
Заранее благодарен сообществу.
Автору спасибо большое за статью.
Вообщем задачка:
Есть простая таблица с колонками id и price. В данных колонка записываются результаты примерно в таком порядке:
id price
1 45
2 80
3 75
4 125
5 50
6 70
7 22
8 23
9 47
10 20
Необходимо: отобразить id всех колонок, где (price)значение или сума сложения которых(-ой) будут равны, например, = 125
В приведенном случае это будут колонки: 4, 1+2, 3+5, 3+8+9 и так в порядке усложнения.
Подскажите пожалуйста более правильный вариант.
Заранее благодарен сообществу.
Автору спасибо большое за статью.
Что то я сомневаюсь что это задача уровня бд
PS 3+8+9 = 145 :)
Да, немного подошибся:)
Простым перебором как-то так, спасибо habrahabr.ru/post/340460/#comment_10497126
SQL
DROP TABLE IF EXISTS My_Values;
CREATE TABLE My_Values (id,sm) --Создаем таблицу с данными
as (
VALUES (1, 45),
(2, 80),
(3 ,75),
(4, 125),
(5, 50),
(6, 70),
(7, 22),
(8, 23),
(9, 47),
(10, 20)
);
DROP TABLE IF EXISTS need_val;
CREATE TABLE need_val(nv)
AS SELECT 125; --- УКАЗЫВАЕМ НАШЕ ЧИСЛО
--p.s. Большие числа с мелкими составными будут очень долго вычисляться
WITH RECURSIVE t AS (
WITH q as (
SELECT DISTINCT
id
, sm
FROM My_Values
WHERE sm <= (SELECT nv FROM need_val) and sm >0
)
SELECT id,
sm,
sm tsm,
id::text AS path,
sm::text AS vals
FROM q
UNION ALL
SELECT
q.id AS id,
q.sm AS sm,
t.tsm + q.sm AS tsm,
(t.path || ','::text) || q.id AS path,
(t.vals || '+'::text) || q.sm AS vals
FROM t
JOIN q ON q.id < t.id
--where (t.tsm + q.sm) <= (SELECT nv FROM need_val)
)
SELECT t.path, tsm,'='||t.vals
FROM t
WHERE t.tsm = (SELECT nv FROM need_val)
--LIMIT 10000;А в VALUE указать построчное соответствие/скормить таблицу с колонками id и price вместо перечисления нельзя? Каждый раз id и price меняются, количество строк over 1000, переписывать эти параметры врукопашную задача так скажем не из простых.
Большое Вам спасибо за проявленный интерес к моему вопросу.
Большое Вам спасибо за проявленный интерес к моему вопросу.
Повесьте триггер на инсерт и пересчитывайте. Результаты можно в отдельнкю таблицу. Вариантов вобщем то миллион
Решал такое: https://prosql.github.io/summ/
Прикольное решение, с разрешения автора zoroda выкладываю немного доработанный вариант.
Конечно, рекурсивный перебор не эффективный, но результат не так плох как я думал.
Например: нашел все варианты как можно получить число 195 из суммы чисел кратных трем.
Конечно, рекурсивный перебор не эффективный, но результат не так плох как я думал.
Например: нашел все варианты как можно получить число 195 из суммы чисел кратных трем.
SQL побаловаться
DROP TABLE IF EXISTS My_Values;
CREATE TABLE My_Values (id,sm) --Создаем таблицу с данными
AS (SELECT row_number() over(),* FROM generate_series(0,500,3) ORDER BY 2 DESC );
DROP TABLE IF EXISTS need_val;
CREATE TABLE need_val(nv)
AS SELECT 195; --- УКАЗЫВАЕМ НАШЕ ЧИСЛО
--p.s. Большие числа с мелкими составными будут очень долго вычисляться
WITH RECURSIVE t AS (
WITH q as (
SELECT DISTINCT
id
, sm
FROM My_Values
WHERE sm <= (SELECT nv FROM need_val) and sm >0
)
SELECT id,
sm,
sm tsm,
id::text AS path,
sm::text AS vals
FROM q
UNION ALL
SELECT
q.id AS id,
q.sm AS sm,
t.tsm + q.sm AS tsm,
(t.path || ','::text) || q.id AS path,
(t.vals || '+'::text) || q.sm AS vals
FROM t
JOIN q ON q.id < t.id
where (t.tsm + q.sm) <= (SELECT nv FROM need_val)
)
SELECT t.path, tsm,'='||t.vals
FROM t
WHERE t.tsm = (SELECT nv FROM need_val)
--LIMIT 10000;
Ох, еще вспомнил, что с to_date(text, text) надо быть осторожным, так как PostgreSQL спокойно скушает to_date('30.02.2017', 'dd.mm.yyyy') и вернет 2 марта 2017, когда Oracle и DB2 LUW вернет ошибку.
В таком случае, если позволяет задача переключится на другой datestyle и сделать простое приведение типов
sql> set datestyle to DMY
sql> SELECT '30.02.2017'::date
[22008] ERROR: date/time field value out of range: "30.02.2017" Position: 8
Зачем этот неудобный pgadmin, когда есть в 100 раз лучше dbForge for PostgreSQL. Думаю, скоро и студия появится.
array_to_string(array_agg(any_val),';') можно сократить до string_agg(any_val,';')
А еще, планировщик с большей вероятностью использует индекс по t2_id в
А еще, планировщик с большей вероятностью использует индекс по t2_id в
SELECT * FROM table1 WHERE t2_id=ANY((SELECT array_agg(id) FROM table2)::integer[])SELECT * FROM table1 WHERE t2_id IN (SELECT id FROM table2)string_agg() крут, спасибо. Жаль его не было до 9 версии.
Насчет оператора IN. Давно заметил, что его лучше не использовать, если IN (OVER 9000 rows..)
и обычно переписывал на EXISTS или JOIN
Спасибо за: "=ANY((SELECT array_agg(id) FROM table2)::integer[])" — интересная особенность.
Насчет оператора IN. Давно заметил, что его лучше не использовать, если IN (OVER 9000 rows..)
и обычно переписывал на EXISTS или JOIN
SELECT * FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2 where t2.id = t1.t2_id)Спасибо за: "=ANY((SELECT array_agg(id) FROM table2)::integer[])" — интересная особенность.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Курс молодого бойца PostgreSQL