Начните осваивать SQL и создайте свою собственную модель данных

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

  • Язык определения данных SQL
  • Язык манипулирования данными SQL
  • Согласованность с ограничениями и производительность с индексом

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

Итак, если вы готовы, приступим!

Часть I: язык определения данных SQL

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

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

Что касается числовых типов данных в Postgres, у нас есть множество различных числовых типов, таких как SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION, SMALLSERIAL, SERIAL и BIGSERIAL. Теперь вы можете спросить себя, в чем разница между ними и как понять, что лучше всего подходит для вашей ситуации.

Не волнуйтесь, многие из этих типов данных похожи, и сейчас я вам покажу.

Единственная разница между SMALLINT, INTEGER, BIGINT - это заданный диапазон чисел, которые вы можете сохранить. Ниже размера числа, которое вы можете сохранить для каждого типа.

МАЛЕНЬКАЯ ИНФОРМАЦИЯ: от -32 768 до +32 767

ЦЕЛОЕ: от -2 147 483 648 до +2 147 483 647

BIGINT: от -9,223,372,036,854,775,808 до +9,223,372,036,854,775,807

Поэтому, если мне нужно сохранить число больше 32 КБ или меньше -32 КБ, я предпочитаю использовать INTEGER или BIGINT.

Еще одним интересным типом являются следующие три типа:

МАЛЫЙ СЕРИЙНЫЙ НОМЕР: от 1 до 32 767

СЕРИЙНЫЙ НОМЕР: от 1 до 2 147 483 647

BIGSERIAL: от 1 до 9,223,372,036,854,775,807

Как вы можете заметить, все они не имеют отрицательных значений и начинаются с 1. Еще одно важное соображение относительно типов SERIAL заключается в том, что каждый раз, когда вы добавляете новую строку в свою таблицу, Postgres по умолчанию автоматически добавляет следующую последовательность, увеличивая последний.

Предположим, у вас есть эти три таблицы: test_1, test_2 и test_3.

CREATE TABLE test_1 (
"id" SMALLINT,
"Test_Number" SMALLINT,
"First_Name" VARCHAR(30),
"Last_Name" VARCHAR(30));
CREATE TABLE test_2 (
"id" INT,
"Test_Number" INT,
First_Name" VARCHAR(30),
"Last_Name" VARCHAR(30));
CREATE TABLE test_3 (
"id" SERIAL,
"Test_Number" INT,
"First_Name" VARCHAR(30),
"Last_Name" VARCHAR(30));

Теперь я хочу вставить эти значения: 60000, «Мили», «Дэвис». Шаг за шагом мы увидим разные результаты для каждой таблицы. Для первой таблицы:

INSERT INTO test_1 VALUES (1, 60000, ‘Miles’, ‘Davis’);

вне:

ERROR: smallint out of range

Как вы заметили, мы получили сообщение об ошибке, потому что я установил SMALLINT в качестве типа данных для таблицы test_1.

Вместо этого, если мы попробуем еще раз, со второй таблицей test_2 вывод будет в порядке, потому что тип данных для столбца «Test_Number» теперь INT, где диапазон больше, чем раньше.

INSERT INTO test_2 VALUES (1, 60000, ‘Miles’, ‘Davis’);

Предположим, мы уже вставили значения (6000, «Мили» и «Дэвис») в таблицу, а затем мы хотим вставить вторую строку со значениями (70000, «Джон» и «Колтрейн») и получить по умолчанию значение « id », равный 1 и 2. Для достижения этого результата мне нужно использовать таблицу test_3, где тип данных столбца« id »- SERIAL.

INSERT INTO test_3 (“test_number”,”first_name”,”last_name”) 
VALUES (60000, ‘Miles’, ‘Davis’), (70000, 'John', 'Coltrane');

вывод:

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

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

SHOW TIMEZONE;
SET TIMEZONE = "Europe/Rome"

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

SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_DATE;
SELECT CURRENT_TIME;

Иногда мы не хотим УБИРАТЬ таблицу, но нам просто нужно удалить значения из таблицы, чтобы добавить другие значения. Итак, команда TRUNCATE нам может помочь.

TRUNCATE TABLE test_3 RESTART IDENTITY

Как вы могли заметить, я добавил RESTART IDENTITY, потому что я хочу, чтобы столбец «id» моей таблицы test_3 перезапускался с 1, а не с 3.

Добавление комментариев в описание может помочь кому-то другому лучше понять, что указывает на конкретный столбец. Так:

COMMENT ON COLUMN test_3.”test_number” 
IS ‘describe the number of tests in one year’;

Чтобы увидеть комментарий, используйте команду:

\d+ test_3

вне:

Выше в выводе вы можете увидеть мой комментарий слева.

Часть II: язык обработки данных SQL

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

Иногда бывает полезно вставить данные из другой таблицы, чтобы выполнить миграцию данных из одной таблицы в другую. В моем предыдущем примере я создал таблицу test_3, содержащую некоторые значения. Теперь я хочу перенести значения столбцов «first_name» и «last_name» из таблицы test_3 в новую таблицу с именем test_4.

CREATE TABLE test_4 (
first_name VARCHAR(30),
last_name VARCHAR(30));

Итак, эта таблица test_4 пуста, и я хочу вставить значения из test_3. Процесс прост:

INSERT INTO test_4 ("first_name", "last_name) 
SELECT “first_name”, ”last_name” FROM test_3;

вне:

Я хочу добавить столбец в эту новую таблицу. Так:

ALTER TABLE test_4 ADD COLUMN “song” VARCHAR;

Еще один полезный навык - иметь возможность обновлять таблицу в любое время. Для этого я могу использовать ОБНОВЛЕНИЕ.

UPDATE test_4 SET “song”=’Blue in Gree’ WHERE “last_name”=’Davis’;
UPDATE test_4 SET “song”=’In A Sentimental Mood’ 
WHERE “last_name”=’Coltrane’;
TABLE test_4;

вне:

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

DELETE FROM test_4 WHERE “last_name”=’Coltrane’;
INSERT INTO test_4 VALUES (‘Duke', 'Ellington’, ‘In A Sentimental Mood’);
ALTER TABLE test_4 ADD COLUMN "song_date" DATE;
UPDATE test_4 SET "song_date"='1959-03-02' 
WHERE "last_name"='Davis';
UPDATE test_4 SET "song_date"='1935-04-30' 
WHERE "last_name"='Ellington';
INSERT INTO test_4 
VALUES ('Paul', 'Desmond','Take Five', '1959-07-01');

вне:

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

SELECT pg_typeof(CURRENT_TIMESTAMP — “song_date”) FROM test_4;

вне:

SELECT “song”,”song_date”, 
(CURRENT_TIMESTAMP — “song_date”) > INTERVAL ’70 years’ FROM test_4;

вне:

Как видите, второй песне уже более 70 лет.

Часть III: Согласованность с ограничениями и производительность с индексом

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

Первое ограничение - УНИКАЛЬНОЕ. Это ограничение позволяет нам фиксировать уникальные значения в столбце или в наборе столбцов.

Ниже я покажу вам различные способы использования UNIQUE.

--FIRST example--
CREATE TABLE table_name (
"id" SERIAL,
"full_name" VARCHAR(30) UNIQUE);
--SECOND example---
CREATE TABLE table_name ( 
“id” SERIAL,
"full_name" VARCHAR(30),
UNIQUE ("full_name"));
--THIRD example--
ALTER TABLE table_name ADD UNIQUE ("full_name")

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

Особый тип уникального ограничения - PRIMARY KEY. С PRIMARY KEY мы можем гарантировать, что конкретный столбец получит только уникальные, а не нулевые значения.

--FIRST example--
CREATE TABLE table_name_1 (
"id" SERIAL PRIMARY KEY,
"full_name" VARCHAR(30) UNIQUE);
--SECOND example--
CREATE TABLE table_name_1 (
"id" SERIAL, 
"full_name" VARCHAR(30),
PRIMARY KEY ("id"),
UNIQUE ("full_name"));
--THIRD example--
ALTER TABLE table_name_1 ADD PRIMARY KEY ("id")

Другой ключ - FOREIGN KEY, который ограничивает значения в столбце только значениями, которые появляются в другом столбце. Ниже приведены некоторые примеры.

--FIRST example--
CREATE TABLE table_name_2 (
"id_2" INTEGER REFERENCES teble_name_1 ("id"),
"full_name" VARCHAR(30) UNIQUE);
--SECOND example--
CREATE TABLE table_name_2 (
"id_2" INTEGER,
"full_name" VARCHAR(30),
FOREIGN KEY ("id_2") REFERENCES table_name_1 ("id"),
UNIQUE ("full_name"));
--THIRD EXAMPLE--
ALTER TABLE table_name_2 ADD FOREIGN KEY ("id_2") 
REFERENCES table_name_1 ("id");

Наконец, мы можем взглянуть на INDEX и узнать, как он может помочь нам в некоторых случаях сократить время обработки запроса.

Итак, чтобы узнать, сколько времени требуется нашему запросу для выдачи вывода, нам нужно использовать следующую команду:

\timing on

Ниже представлен список различных способов создания индекса:

CREATE INDEX ON table_name (“col_1”);
CREATE INDEX ON table_name (“col_1”, “col_2);
CREATE UNIQUE INDEX ON table_name ("col_1");

Однако вы должны знать, что иногда Postgres предпочитает использовать сканирование последовательности, даже если вы установили ИНДЕКС. Это происходит потому, что Postgres ищет лучшее время обработки, и в некоторых ситуациях, например, когда количество записей не так велико, Postgres может выбрать сканирование последовательности вместо индекса, который вы установили.

Вывод

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

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