Дальнейшие сведения, практика в SQL

Как загрузить дополнительные данные в разделённых табуляторами таблицах

Здравствуйте и вновь добро пожаловать на занятия по теме «SQL для маркетологов».

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

Если вы уже загрузили данные до того, как была создана эта лекция, зайдите в папку sql_class и в командной строке наберите команду

git pull

В этом случае вы получите последние версии файлов.

Два нужных нам файла называются ordered_items.tsv и customers.tsv. Почему расширения файлов TSV? Тогда как расширение CSV указывает на значения, разделённые запятыми, расширение TSV указывает на значения, разделённые табуляторами. Такой стандарт тоже очень распространён в сфере обработки данных.

Как и в случае с CSV-файлами, мы должны в SQLite воспользоваться соответствующим режимом с помощью команды

.mode tabs

После использования оператора создания таблицы импортируем данные обычным способом:

.import <файл данных> <название таблицы>

Кстати, вот вам и задачка: каков должен быть оператор создания таблицы? Вы должны уже уметь написать его самостоятельно.

Рассмотрим файл ordered_items.tsv. Заголовками в нём являются: customerid (ID пользователя), в качестве типа которого следует указать INTEGER; order_date (дата заказа), имеет тип DATA; item (предмет) – тип TEXT; quantity (количество) – тип INTEGER; и price (цена), имеет тип REAL.

Заголовки файла customer.tsv следующие: customerid (ID пользователя), тип данных – целые числа; firstname (имя), тип данных – текст; lastname (фамилия) – текстовый тип; city (город) – текстовый тип; state (область) – также текстовый тип.

Поставьте видео на паузу и самостоятельно напишите ответ.

Вот моё решение по поводу оператора создания таблицы заказанных предметов:

CREATE TABLE ordered_items(

  customerid INTEGER,

  order_date DATE,

  item TEXT,

  quantity INTEGER,

  price REAL

);

Обратите внимание, что customerid не обязан иметь уникальные значения, поскольку один и тот же клиент может заказать несколько предметов. И не забывайте про точку с запятой!

Следующий ответ – оператор создания таблицы клиентов:

CREATE TABLE customers(

  customerid INTEGER PRIMARY KEY AUTOINCREMENT,

  firstname TEXT,

  lastname TEXT,

  city TEXT,

  state TEXT

);

В данном случае поле customerid является первичным ключом и должно иметь уникальные значения.

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

Итак, первым делом мы должны проверить наличие заголовков, что можно сделать командами

head ordered_items.tsv

head customers.tsv

Далее запускаем SQLite. Используем для нашей базы данных прежнее название.

sqlite3 the_database.db

Включим режим табулирования.

.mode tabs

И создадим таблицы.

create table ordered_items(customerid integer, order_date date, item text,  quantity integer, price real);

create table customers(customerid integer primary key autoincrement, firstname text, lastname text, city text, state text);

Теперь импортируем данные.

.import ordered_items.tsv ordered_items

.import customer.tsv customers

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

Обратите внимание, что если мы проверим таблицы с помощью команд

select * from customers;

select * from ordered_items;

то увидим, что таблица customers не содержит первой строки, состоящей из заголовков, а вот таблица ordered_items – содержит, что неправильно. Странно, что SQLite включило её, поскольку текст customerid не относится к типу INTEGER, но по какой-то причине она прошла. Поэтому просто удалим эту строку:

delete from ordered_items where customerid = ‘customerid’;

Проверим, исчезла ли эта строка. Строка исчезла.

 

Служебное слово IN

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

Допустим, нам нужно охватить всех клиентов с именем Джина, всех клиентов с именем Алиса и всех клиентов с именем Кэрол. Для этого мы можем использовать знак равенства и объединить их с помощью служебного слова OR. OR означает, что всё выражение является истинным, если хотя бы один из вариантов является истинным:

SELECT * from user_actions

WHERE name = ‘Gina’ OR name = ‘Carol’ OR name = ‘Alice’;

Такое решение совершенно неприемлемо, если у нас есть такой список из, скажем, 100 или 1 000 таких имён. В программировании выход состоит в том, что длинный список объектов хранится в качестве единичного объекта, называемого массивом. В SQL тоже есть подобная вещь. В действительности если SQL взаимодействует с языком программирования, мы часто можем переносить массивы из языка программирования прямо в SQL.

В SQL используется служебное слово IN, после которого перечисляются все нужные имена, разделённые запятыми и окружённые скобками:

SELECT * from user_actions

WHERE name IN (‘Gina’, ‘Carol’, ‘Alice’);

Теперь нам не нужно постоянно повторять выражение «OR имя =».

Обратите внимание, что существуют также ключевые слова NOT IN для обратной операции – исключения, а не включения объектов:

SELECT * from user_actions

WHERE name NOT IN (‘Gina’, ‘Carol’, ‘Alice’);

Теперь испытаем этот приём в нашей базе данных.

Сначала выберем все строки, используя метод «имя =»:

select * from user_actions where name = ‘Gina’ or name = ‘Carol’ or name = ‘Alice’;

Получив результат, попробуем метод массивов:

select * from user_actions where name in (‘Gina’, ‘Carol’, ‘Alice’);

Заметьте, насколько короче стала команда.

 

Служебное слово BETWEEN

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

Синтаксис использования слова BETWEEN следующий. Пусть нам необходимо сделать выборку, где стоимость больше 100, но меньше 200. Другими словами, стоимость должна находиться в диапазоне между 100 и 200. Вот два способа написать такой запрос:

SELECT *

FROM ordered_items

WHERE price > 100 AND price < 200;

SELECT *

FROM ordered_items

WHERE price BETWEEN 100 AND 200;

Как и в случае со словом IN, мы можем использовать NOT BETWEEN, чтобы исключить определённый диапазон. Так, если мы хотим узнать стоимость, которая находится вне диапазона между 100 и 200, мы можем записать:

SELECT *

FROM ordered_items

WHERE price NOT BETWEEN 100 AND 200;

Теперь попробуем проделать это в командной строке. Сначала просмотрим позиции, находящиеся в диапазоне между 100 и 200:

select * from ordered_items where price between 100 and 200;

Мы видим все суммы, находящиеся в пределах от 100 до 200.

Теперь используем NOT BETWEEN:

select * from ordered_items where price not between 100 and 200;

Это даёт нам все суммы, находящиеся вне диапазона между 100 и 200.

Удостоверимся в правильности ответа, дав запрос первоначальным образом:

select * from ordered_items where price > 100 and price < 200;

И получим тот же ответ.

Понравилась статья? Поделить с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: