SQL для бизнеса и финансов

МЕНЮ


Искусственный интеллект
Поиск
Регистрация на сайте
Помощь проекту

ТЕМЫ


Новости ИИРазработка ИИВнедрение ИИРабота разума и сознаниеМодель мозгаРобототехника, БПЛАТрансгуманизмОбработка текстаТеория эволюцииДополненная реальностьЖелезоКиберугрозыНаучный мирИТ индустрияРазработка ПОТеория информацииМатематикаЦифровая экономика

Авторизация



RSS


RSS новости


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

JavaScript и SQL уверенно держатся на верхних строчках и не собираются уступать свои лидерские позиции.

Популярность JavaScript нисколько не шокирующий факт. JS предлагает широкий круг возможностей, начиная с простых функций: создания галерей изображений, меняющийся макетов и отклика мыши, и заканчивая написанием сложных 3D график, компьютерных игр, приложений, основанных на базах данных и так далее.

SQL уже «наступает на пятки» JavaScript и, предположительно, тенденция не изменится еще многие годы. Об этом языке мы поговорим поподробнее.

SQL (или Structured Query Language) — это структурированный язык запросов, который используют для работы с реляционными базами данных.

Реляционная база данных – это БД, данные в которой связаны между собой определенными отношениями. Информация хранится в таблицах, состоящих из столбцов (атрибутов) и строк. У атрибутов есть уникальные названия и тип данных, которыми заполняются ячейки в этом столбце. По строкам записываются значения атрибутов, относящихся к одному объекту. В таблице не может быть одинаковых сток, поэтому каждая должна быть отмечена уникальным номером, который называется Primary Key (первичный ключ), а в свою очередь таблицы связаны друг с другом с помощью Foreign Key (внешний ключ). Благодаря такой связи можно различными способами получать любые необходимые для нужд бизнеса данные из БД, не переделывая ее.

Реляционная СУБД (или РСУБД) – система управления реляционными БД. Обычные пользователи не имеют доступа к СУБД.

Посмотрим структуру таблицы в БД на примере таблицы товаров.

В качестве атрибутов выступают столбцы «id», «Название», «Производитель», «Цена», «Категория». В строках находятся данные, относящиеся к определенному товару. В качестве уникального идентификатора выступает id. Он же может быть и внешним ключом для других таблиц.

Именно SQL позволяет нам «вытягивать» необходимые нам данные из таблиц, например, товары, которые стоят больше 200 рублей; группировать товары по категориям; находить продукты, которые хуже продаются и делать другие запросы.

История

Познакомимся немного с историей языка.

Первая версия языка SQL была презентована еще в 70-х годах прошлого века. Разработкой занимались Дональд Чэмбэрлин и Рэй Бойс для компании IBM Research.

Изначально SQL создавался как средство извлечения и управления данными, которые находились в реляционных базах данных. Язык всегда был прост и для изучения, и для пользования. Перед создателями стояла цель – придумать удобный инструмент, который был бы понятен тем, кто не разбирается в языках программирования. Это удалось реализовать, ведь языком пользовались и бухгалтеры, и финансисты, даже кладовщики и другие специалисты. То есть язык предназначался для конечного пользователя-обывателя. Более поздние версии SQL стали сложнее и превратились уже в инструмент разработки.

Но все же операторы SQL только описывают действия, а СУБД уже решает, как их выполнять, и реализует введенный запрос. В этом и заключается простота языка: пользователь не обязан разбираться во внутреннем устройстве СУБД, ему достаточно научиться создавать запросы.

Тем не менее, разработчикам, которые используют БД в своих приложениях и программах, для эффективного использования возможностей SQL нужно иметь представление о том, что «происходит в голове у СУБД»: как она анализирует каждый оператор и создает план его выполнения.

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

В Data Science без SQL никуда

Можете себе представить, что каждый день появляется огромное количество информации – примерно 2,5 * 1018 байт (такое число даже представить себе трудно). Ценных данных из этого многообразия, конечно, много меньше, но все же это невероятно большое количество. И информацию сегодня можно считать новым видом валюты: компании, у которых много данных и которые умеют качественно ее обрабатывать, строить прогнозы и тенденции, разбираются в клиентских потребностях, могут увеличивать свои доходы с помощью этих данных и уверенно себя чувствовать на рынке.

Поэтому требование знания SQL встречается во многих вакансиях Data Scientists и аналитиков, в целом. Многие ученые по данным отмечают, что создание SQL-запроса часто бывает самым первым (и зачастую непростым) шагом в любой обработке данных. Для более мощного и гибкого анализа они объединяют несколько разноплановых языков, например, Python, R и SQL.

Базы данных используются во всех сферах: бизнес, экономика, финансы, наука, общество и так далее. Но сама по себе БД не несет особой ценности – это просто хранилище информации. А вот язык, с помощью которого можно управлять базой данных – другое дело.

Предлагаем познакомиться с основами языка SQL, который точно Вам пригодится, не зависимо от того, кем вы работаете: финансистом, бухгалтером, бизнес-аналитиком или Data Scientist`ом.

Основные операторы языка SQL

Существует много систем управления базами данных (СУБД), например, Oracle Database, Interbase, Firebird, Microsoft SQL Server, PostgreSQL и другие. В каждой применяется язык SQL, но с некоторыми модификациями. Разница между языками есть: в синтаксисе, обработке запросов, но общая канва схожа. Поэтому для специалиста, знающего SQL, любые вариации (например, PL/SQL, PSQL, PL/pgSQL) будет достаточно легко освоить.

Мы познакомимся с SQL, который применяется в PostgreSQL. Это мощная система объектно-реляционных баз данных с открытым исходным кодом. Данная СУБД заслужила хорошую репутацию благодаря надежности и высокой производительности. Еще одним важным плюсом системы является ее расширяемость – возможность создавать новые типы данных, соединяться с любыми внешними источниками данных.

Для работы с PostgreSQL используется psql - интерактивный терминал PostgreSQL, который позволяет вводить запросы, передавать их СУБД и видеть результаты. Программа psql очень похожа на консоль, в которой пишут команды.

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

Чаще всего используются следующие команды:

h [команда] Справка по SQL: список доступных команд или синтаксис конкретной команды.

с [имяБД] Подключение к базе данных.

l Показать список баз данных.

dt Показать список таблиц.

d [имя] Показать подробную информацию по конкретному объекту (например, таблице или индексу).

q Выход из psql.

Это основные команды, которые могут понадобиться новичкам на этапе обучения.

Теперь мы знаем всю необходимую информацию для выполнения основного задания: Создание БД, в которой будут храниться две следующие таблицы:

Goods

Bill

Для создания БД используют оператор CREATE DATABASE [имя_базы]. Мы создадим базу данных, которая будет называться, например, «Shop». Команды языка SQL заканчиваются точкой с запятой (;). Без этого знака команда не будет обработана.

  • CREATE DATABASE Shop;

После ввода команды система ответить Вам.

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

Теперь нужно подключиться к базе данных Магазин. Используем команду сonnect.

  • connect shop

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

CREATE TABLE goods(
id integer PRIMARY KEY,
name text,
maker text,
price float4,
quantity integer,
category text);

Мы создали первую таблицу, указав в качестве Primary Key – id товара. Теперь заполним таблицу. Это действие выполняет команда INSERT INTO.

INSERT INTO goods
VALUES (001, 'Мыло', 'ООО «Сирень» ', 45, 6, 'косметика'),
(002, 'Шампунь', 'ОАО «Красота и здоровье» ', 260, 15, 'косметика'),
(003, 'Порошок', 'ООО «Чистота» ', 190, 4, 'бытовая химия'),
(004, 'Крем для рук', 'ОАО «Красота и здоровье» ', 93, 12, 'косметика'),
(005, 'Средство для мытья посуды', ' ООО «Чистота» ', 110, 36, 'бытовая химия');

Теперь в нашей таблице 5 строк. Чтобы проверить, что находится в таблице товары можем воспользоваться командой SELECT:

SELECT * FROM goods;

Это запрос, который выводит все (значок *) из таблицы «goods». Нетрудно заметить, что формулировка запросов очень проста и понятна, если знать английский язык даже на базовом уровне. Чтобы выбрать какие-то определенные атрибуты из таблицы, вместо * в скобочках нужно указать названия столбцов. Например, выведем на экран только название и стоимость товаров:

SELECT name, price FROM goods;

Более сложные запросы мы рассмотрим далее.

А пока аналогично предыдущим процедурам, создадим вторую таблицу:

CREATE TABLE bill(
id text,
num_string integer,
date date,
goods integer REFERENCES goods(id),
quantity integer);

Эта таблица связана с таблицей «goods» по внешнему ключу – id. Чтобы указать на Foreign Key, нужно применить оператор REFERENCES (ссылка) и написать название таблицы и в скобках атрибут, по которому таблицы связаны.

Добавим строки в таблицу «bill»:

INSERT INTO bill
VALUES ('100/1', 1, '2019-10-25',002,2),
('100/1', 2, '2019-10-25',001,5),
('101/3', 1, '2019-10-26',002 ,4),
('101/2', 1, '2019-10-26', 003, 1),
('101/2', 2, '2019-10-26', 001, 3),
('101/2', 3, '2019-10-26', 005, 1);

Дата в SQL указывается в обратном порядке: 'гггг-мм-дд' (в одинарных кавычках).

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

Самый простой способ мы уже знаем: SELECT * FROM [название таблицы].

Но очень редко специалисту нужно выводить всю таблицу. Для более узкого запроса есть полезный оператор – WHERE. Вы уже, наверное, догадались, для чего он нужен? Он накладывает ограничения на выводимые значения.

Например, SELECT name FROM goods WHERE price>200;

Результатом запроса будет одна строка: «Шампунь».

Или SELECT goods FROM bill WHERE date='2019-10-25';

В ответ получим 2 и 1.

Условий выбора может быть много, их соединяют оператором AND, например,

SELECT name FROM goods WHERE (price<200) AND (category= 'бытовая химия');

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

SELECT DISTINCT category FROM goods;

Получим только две строки: косметика и бытовая химия.

Еще в начале мы отметили, что SQL – язык управления базами данных. С его помощью можно не только создавать (CREATE), удалять (DROP) таблицы, но и объединять их.

Самый простой способ объединения – объединить все:

SELECT * FROM goods, bill;

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

Например, нам нужно посмотреть, товары и выручку по ним. Для этого выведем столбцы с названием товара, ценой и проданным количеством (из таблицы bill), объединив таблицы по столбцу id из таблицы goods и атрибуту goods из таблицы bill:

SELECT name, price, bill.quantity FROM goods, bill WHERE goods.id=bill.goods;

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

Но эта таблица тоже будет неудобной, так как мы получим не суммарное проданное количество, а разбитое по чекам («Шампунь» и «Мыло» встречаются два раза).

Для того, чтобы получить нужный нам результат, сгруппируем данные (GROUP BY) по названию товара и найдем суммарное проданное количество каждого товара (SUM).

SELECT name, AVG(price) AS price_per_one, SUM(bill.quantity) AS quantity FROM goods, bill WHERE goods.id=bill.goods GROUP BY name;

Мы получили то, что нам было нужно: сколько каких товаров было продано и по какой цене. Теперь можно найти выручку по каждому товару, добавив еще один столбец

GROUP BY всегда ставится в конце запроса. В сгруппированном запросе должны выводиться либо атрибуты, по которым происходит группировка, либо агрегатные функции: такие, как SUM, COUNT, MAX, MIN, AVG (среднее значение), которые выполняются уже для сгруппированных данных.

Именно поэтому в примере вместо обычной цены мы вывели среднюю.

Мы рассмотрели еще один оператор: GROUP BY. Благодаря ему можно группировать данные по любому атрибуту, что упрощает визуальное восприятие и дальнейший анализ.

Еще обратим внимание на оператор AS. С помощью него можно именовать столбцы и новые таблицы, что мы и сделали в примере выше.

Вернемся к соединению таблиц (JOIN). Это очень важный вопрос при работе с базами данных. Объединение делится на внутреннее (INNER) и внешнее (OUTER). Внешнее в свою очередь делится на левое (LEFT), правое (RIGHT) и полное (FULL).

По умолчанию при указании [имя таблицы] JOIN [имя таблицы] ON [условие соединения] подразумевается внутреннее соединение. То есть будут выведены те соединения строк, которые удовлетворяют заданному условию.

Например,

SELECT name, AVG(price) AS price_per_one, SUM(bill.quantity) AS quantity FROM goods JOIN bill ON goods.id=bill.goods GROUP BY name;

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

Если мы воспользуемся левым соединением:

SELECT name, AVG(price) AS price_per_one, SUM(bill.quantity) AS quantity FROM goods LEFT JOIN bill ON goods.id=bill.goods GROUP BY name;

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

Правое соединение обратно левому. Результатом запроса

SELECT name, AVG(price) AS price_per_one, SUM(bill.quantity) AS quantity FROM goods RIGHT JOIN bill ON goods.id=bill.goods GROUP BY name;

Будут соединение строк из второй и первой таблицы, удовлетворяющие условию, и остальные строки из второй таблицы.

Но в нашем случае результат запроса будет идентичен запросу с внешним соединением (INNER JOIN), так как таблицы связаны по id товара. А это значит, что в таблице bill могут присутствовать только те id товара, которые есть в таблице goods.

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

Сортировка

И еще одна из базовых команд SQL – ORDER BY, которая, как вы уже догадались, упорядочивает строки. По умолчанию применяется сортировка по возрастанию: ASC, чтобы ранжировать по убыванию, нужно добавить DESC. Посмотрим на примере:

SELECT * FROM goods ORDER BY price;

Получили сортировку товаров по возрастанию стоимости.

Или SELECT * FROM goods ORDER BY quantity DESC;

Поиск совпадений

Познакомимся еще с одной полезной функцией – LIKE или (~). С помощью нее можно находить слова в строках, даты, численные значения. Например, найдем среди производителей общества с ограниченной ответственностью (ООО):

SELECT maker FROM goods WHERE maker LIKE 'ООО%';

Если то, что мы ищем может стоять в середине строки, то его окружают знаком % со всех сторон. В нашем случае «ООО» должно встречаться в начале названия, поэтому знак процента стоит после искомого выражения.

Найдем товары, проданные только в первую смену:

SELECT name, date,bill.id FROM goods, bill WHERE goods.id=bill.goods AND bill.id LIKE '%1';

Есть еще очень много полезных функций SQL, которые могут облегчить поиск информации. Например,

  • BETWEEN [от] AND [до] – выбирает значения только в заданном интервале
  • IN [множество значений] – выбирает значения из указанного множества
  • NOT IN [множество значений] - выбирает значения не совпадающие со значениями, указанными в множестве
  • IS NULL – находит пустые ячейки

Все это только вершина айсберга, но этого вполне достаточно для начального погружения в SQL. Дальше на основе таких простых и интуитивно понятных команд можно строить совсем не тривиальные запросы и выводить удобную для анализа информацию. Следующий шаг – научиться создавать сложные, многоуровневые запросы и составлять подзапросы. Чем больше у Вас будет практики, чем сложнее конструкции Вы будете строить, тем легче будет в будущем работать с базами данных. Этот навык 100% пригодится, где бы Вы не работали.

Для более углубленного изучения можете пройти наш курс по языку SQL, где профессионалы подскажут Вам все тонкости и хитрости языка, знание которых сделает Вас высококлассным специалистом!

Data Science: clck.ru/JkPnj


Источник: m.vk.com

Комментарии: