Poldet1.ru

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

Как ранжировать по двум столбцам в Excel?

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

Я хочу поделиться некоторыми полезными советами по PostgreSQL (в других РСУБД есть похожие функции, но синтаксис может отличаться).

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

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

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

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

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

Благодаря использованию этого ключа при завершении транзакции таблица (и все связанные с ней объекты) автоматически удаляется.

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

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

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

  • Сокращенное обозначение для конструкции (I)LIKE «%текст%».

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

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

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

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

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

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

(a) ПРОСТОТА ВЫБОРА

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

(b) Можно создать несколько таблиц со следующим перечнем

Читайте так же:
Как проверить, существует ли путь к папке в Excel?

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

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

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

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

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

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

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

Результат:

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

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

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

Мы удалим данные и передадим удаленные значения на следующий шаг. Ваше воображение и цели определят ваш успех. Перед использованием сложных конструкций обязательно тщательно изучите документацию вашей системы управления базами данных! (Существуют тонкости при параллельном комбинировании 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
и самим задавать устраивающий нас параметр.

P.S. Подскажите, пожалуйста, какие еще есть способы сопоставления текстовых данных? Я пробовал удалять все, кроме букв/цифр, регулярными выражениями и сопоставлять по равенству, но этот вариант не работает, если есть опечатки.

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

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

Читайте так же:
Как преобразовать серийный номер на сегодняшний день в Excel?

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

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

Как обычно, я приведу простой пример:

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

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

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

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

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

C OALESCE выбирает первое не NULL-значение

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

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

PG_TYPEOF указывает тип данных колонки.

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

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

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

результат:

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

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

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

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

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

Читайте так же:
Как проверить, существует ли значение в другом столбце, а затем суммировать в Excel?

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

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

Заключение

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

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

Рассчитывать, только если не пусто

I F с соответствующей логикой можно использовать для запуска формулы, только если одна или несколько ячеек не пусты. Формула для E5 имеет следующий вид:

Поскольку C7 не имеет значения в приведенном выше скриншоте, формула не показывает результат. На приведенном ниже экране C7 содержит число, и на экране отображается сумма:

Та же формула с расчетом

На этом примере мы будем проверять входные данные перед вычислением результата. Чтобы понять это, нам нужно понять, что любую реальную формулу можно заменить. Функция СУММ используется только в качестве примера. В зависимости от обстоятельств можно применять и другую логику.

В приведенном примере мы используем функцию IF вместе с функцией SCREEN. Критерий — это выражение, основанное на функции RATE, которая учитывает только числовое значение:

Если диапазон включает три числа (т.е. все 3 ячейки не пусты), результат будет TRUE, и IF запустит функцию AMOUNT. Если нет, результат будет FALSE, и IF вернет пустую строку (»). Из-за отсутствия значения для C7 на экране выше, эта формула не имеет результата.

Существует много способов проверки пустых ячеек, некоторые из которых описаны ниже.

С COUNTBLANK

Функция Count Empty Cells подсчитывает пустые ячейки в диапазоне, что позволяет нам написать немного более компактную формулу, например, такую, как эта:

Читайте так же:
Как привязать ячейки формулы (изменить ссылку на ячейку на абсолютную) в Excel?

C OUNTBLANK возвращает любое число, отличное от нуля, в этом случае функция IF оценит TRUE и не вернет ничего (»). Она возвращает сумму, если COUNTBLANK возвращает ноль.

С ISBLANK

В показанном примере все входные ячейки находятся в одной смежной области. Если ячейки не сгруппированы вместе, можно создать формулу следующим образом:

В этом примере используется литеральный подход с помощью функции ISBLANK. Поскольку мы хотим проверить все три ячейки одновременно, нам нужно использовать ISBLANK три раза в функции OR. Это логический тест в рамках IF:

Когда OR возвращает TRUE (хотя бы одна ячейка пуста), IF возвращает пустую строку (»). Когда OR возвращает FALSE (пустых ячеек нет), IF выполняет SUMM (функцию SUMM) и возвращает результат:

С логическими операторами

Функция ISBLANK может быть заменена стандартными операторами логики, такими как

Если мы хотим, мы можем объединить оператор not equal to оператор () с функцией AND, как показано ниже:

Обратите внимание, что функция SUM была перенесена на результат TRUE. Это будет работать только в том случае, если C5, C6 и C5 не пусты.

С COUNTA

Наконец, можно использовать функцию NUMERIC для проверки числовых или текстовых записей:

Пока диапазон C5:C5 содержит три значения (числа или текст), результатом будет TRUE и использование функции AMOUNT. Это не очень подходит для представленного примера (который требует числового ввода), но все же может быть использовано в других ситуациях.

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