Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как выделить в Excel числа, кратные определенному числу?

Курс молодого бойца PostgreSQL

Вот несколько советов и рекомендаций по работе с PostgreSQL (другие СУБД могут иметь похожие возможности, но у них может отличаться синтаксис)

Я постараюсь охватить множество тем и приемов, которые помогут в работе с данными, стараясь не углубляться в подробное описание той или иной функциональности. Мне нравились подобные статьи, когда я был самоучкой. Позвольте мне отдать дань бесплатному самообразованию в Интернете, написав свою собственную статью.

Этот материал будет полезен тем, кто хорошо знаком с основами SQL и хочет узнать больше. Я предлагаю запустить и поэкспериментировать с примерами в pgAdmin, я сделал все SQL-запросы исполняемыми без развертывания дампов.

1. Использование временных таблиц

При решении сложных задач трудно уместить решение в один запрос (хотя многие пытаются). Если для хранения промежуточных данных необходима временная таблица, удобно поместить ее туда.

Такие таблицы создаются как обычные таблицы, но с ключевым словом TEMP, и автоматически удаляются при завершении сеанса.

Ключ ON COMMIT DROP автоматически удаляет таблицу (и все связанные с ней объекты) после завершения транзакции.

2. Часто используемый сокращенный синтаксис Postgres

  • Конвертирование типов данных.

Может быть написан менее громоздким способом:

  • Сокращенная запись конструкции (I)LIKE ‘%text%’

* (две тильды со звездочкой)

Поиск регулярными выражениями (имеет отличный от LIKE синтаксис)
оператор

* (одна тильда и звездочка) регистронезависимая версия

3. Общие табличные выражения (CTE). Конструкция WITH

Очень полезная конструкция, которая позволяет поместить результат запроса во временную таблицу и использовать его немедленно.

В качестве примера я буду использовать примитивные техники, чтобы уловить суть.

A) ВЫБОР чаши

Таким образом, вы можете «обернуть» любой запрос (включая UPDATE, DELETE и INSERT, о которых мы поговорим позже) и использовать результаты позже.

Читайте так же:
Как вставить последовательные номера страниц на листы при печати?

(b) Можно создать несколько таблиц, перечисленных ниже

(c) Вы даже можете включить вышеуказанную конструкцию в другую (и еще одну) конструкцию с

Если учитывать производительность, то не следует помещать в секцию WITH данные, которые будут сильно фильтроваться последующими внешними условиями (вне скобок запроса), поскольку оптимизатор не сможет построить эффективный запрос. Это удобнее, чем помещать в CTE результаты, к которым нужно обращаться многократно.

4. Функция array_agg(MyColumn).

Значения в реляционной базе хранятся разрозненно (атрибуты по одному объекту могут быть представлены в нескольких строках). Для передачи данных какому-либо приложению часто возникает необходимость собрать данные в одну строку (ячейку) или массив.
В PostgreSQL для этого существует функция array_agg(), она позволяет собрать в массив данные всего столбца (если выборка из одного столбца).
При использовании GROUP BY в массив попадут данные какого-либо столбца относительно каждой группы.

Сразу опишу еще одну функцию и перейдем к примеру.
array_to_string(array[], ‘;’) позволяет преобразовать массив в строку: первым параметром указывается массив, вторым — удобный нам разделитель в одинарных кавычках (апострофах). В качестве разделителя можно использовать

Выдаст результат:

Давайте сделаем обратное. Разложим массив на строки с помощью функции UNNEST и продемонстрируем SELECT columns INTO table_name. Я помещу это в спойлер, чтобы статья не получилась слишком раздутой.

Результат:

5. Ключевое слово RETURNIG *

указанное после запросов INSERT, UPDATE или DELETE позволяет увидеть строки, которых коснулась модификация (обычно сервер сообщает лишь количество модифицированных строк).
Удобно в связке с BEGIN посмотреть на что именно повлияет запрос, в случае неуверенности в результате или для передачи каких либо id на следующий шаг.

Его можно использовать в сочетании с ETC, организовав сумасшедший пример.

Читайте так же:
Как добавить конечные пробелы к тексту в Excel?

Удалив данные, следующий шаг сможет передать удаленные значения. Воображение и цели являются ключевыми. Перед использованием сложных конструкций изучите документацию к вашей версии СУБД! (существуют тонкости при параллельном комбинировании INSERT, UPDATE или DELETE)

6. Сохранение результата запроса в файл

Команда COPY имеет много различных параметров и способов использования.

7. Выполнение запроса на другой базе

Не так давно я узнал, что можно адресовать запрос к другой базе данных, для этого существует функция dblink (все подробности в руководстве).

Если возникла ошибка:

Вам необходимо установить расширение с помощью следующей команды:

8. Функция similarity

Функция для определения схожести одного значения с другим.

Использовал для сопоставления текстовых данных, которые были похожи, но не равны друг другу (имелись опечатки). Сэкономил уйму времени и нервов, сведя к минимуму ручную привязку.
similarity(a, b) выдает дробное число от 0 до 1, чем ближе к 1, тем точнее совпадение.
Перейдем к примеру. С помощью WITH организуем временную таблицу с вымышленными данными (и специально исковерканными для демонстрации функции), и будем сравнивать каждую строку с нашим текстом. В примере ниже будем искать то, что больше похоже на ООО «РОМАШКА» (подставим во второй параметр функции).

Получим следующий результат:

Если возникает ошибка

Необходимо установить расширение с помощью следующей команды:

Получим такой результат:

Сортировка по сходству DESC. 1 — полное сходство приводит к наиболее похожим строкам.

Необязательно выводить значение similarity в SELECT, можно просто использовать его в условии WHERE similarity(c_name, ‘ООО «РОМАШКА»’) >0.7
и самим задавать устраивающий нас параметр.

П.С. Я буду признателен, если вы предложите другие способы сопоставления текстовых данных. Я пробовал удалять все, кроме букв/цифр, используя регулярные выражения и сопоставление по сходству, но это не работает для орфографических ошибок.

Читайте так же:
Как вставить кнопку макроса для запуска макроса в Excel?

9. Оконные функции OVER() (PARTITION BY __ ORDER BY __ )

Я обнаружил, что подобная высоко оцененная статья на эту тему уже существует (с грустью и радостью). Не вижу смысла дублировать информацию, поэтому однозначно рекомендую всем, кто не знает, как пользоваться оконными функциями SQL, прочитать эту статью (ссылка — habrahabr.ru/post/268983/, автору большой поклон).

10. Множественный шаблон для LIKE

Задача. Фильтр списка пользователей, имена которых должны соответствовать определенным шаблонам.

Еще раз приведу очень простой пример:

У нас есть запрос, который выполняет свою функцию, но становится громоздким при большом числе фильтров.

Я покажу, как сделать ее более компактной:

Можно проделать интересные трюки, используя подобный подход.
Напишите в комментариях, если есть мысли, как еще можно переписать исходный запрос.

11. Несколько полезных функций

NULLIF(a,b)
Возникают ситуации, когда определенное значение нужно трактовать как NULL.
Например, строки нулевой длины ( » — пустые строки) или ноль(0).
Можно написать CASE, но лаконичнее использовать функцию NULLIF, которая имеет 2 параметра, при равенстве которых возвращается NULL, иначе выводит исходное значение.

COALESCE выбирается первое ненулевое значение

GREATEST выбирает из списков наибольшее значение.

LEAST выбирает наименьшее значение из списка.

P G_TYPEOF показывает тип данных колонки

PG_CANCEL_BACKEND прекращает нежелательные процессы в базе данных.

12. Экранирование символов

Начну с основ.
В SQL строковые значения обрамляются апострофом (одинарной кавычкой).
Числовые значения можно не обрамлять апострофами, а для разделения дробной части нужно использовать точку, т.к. запятая будет воспринята как разделитель

результат:

Все хорошо, до тех пор пока не требуется выводить сам знак апострофа
Для этого существуют два способа экранирования (известных мне)

результат одинаковый:

В PostgreSQL экранирование символов не требуется для использования данных. С помощью обрамления строки двумя знаками доллара $ можно набрать практически все, что угодно.

Читайте так же:
Как заменить тильду и подстановочные знаки в Excel?

получаю данные в первозданном виде:

Если этого недостаточно и вы хотите использовать два последовательных символа $, Postgres позволяет вам определить собственный «разделитель». Просто введите свой собственный текст между двумя $, например:

Увидим наш текст:

Я обнаружил это не так давно, когда начинал изучать написание функций.

Заключение

Я надеюсь, что этот материал поможет новичкам и опытным пользователям узнать много нового. Сам я не программист, а просто любитель SQL, так что вам решать, как использовать описанные приемы.

Желаю успехов в изучении SQL. Жду комментариев и благодарю за прочтение!
UPD. Вышло продолжение

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector