Category: Базы данных

10
Ноя
2022

🗄️⚠️ Как намеренно расставленные ошибки помогают сделать SQL-код легко поддерживаемым

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

Данная статья является переводом. Ссылка на оригинал.

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

Простая платежная система

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

        db=# CREATE TABLE payment (
    id      INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    method  TEXT NOT NULL
        CONSTRAINT payment_method_check CHECK (method IN ('credit_card', 'cash')),
    amount  INT NOT NULL
);
CREATE TABLE
    

Вы предоставляете своим пользователям два варианта оплаты: наличными или кредитной картой:

        db=# INSERT INTO payment (method, amount) VALUES
    ('cash', 10000),
    ('credit_card', 12000),
    ('credit_card', 5000);
INSERT 0 3

db=# SELECT * FROM payment;
 id │   method    │ amount
────┼─────────────┼────────
  1 │ cash        │  10000
  2 │ credit_card │  12000
  3 │ credit_card │   5000
(3 rows)
    

Расчет комиссии

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

        -- calculate_commission.sql
SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
        END
    ) AS commission
FROM
    payment;
    

При оплате наличными вы взимаете фиксированную комиссию в размере 1 доллара США (100 центов), а при оплате кредитной картой вы взимаете фиксированную плату в размере 30 центов плюс 2% от взимаемой суммы.

Это комиссия за первые 3 платежных процесса:

        db=# \i calculate_commission.sql
 payments  │ commission
───────────┼────────────
        3  │     500.00
(1 row)
    

Поздравляю! Вы только что заработали свои первые 5$.

Добавление нового способа оплаты

Время идет, и ваша платежная система становится настоящим хитом! Спрос на ваши услуги стремительно растет, и ваши клиенты просят больше способов оплаты. Вы хорошенько все обдумываете и решаете ввести новый способ оплаты — банковский перевод:

        db=# ALTER TABLE payment DROP CONSTRAINT payment_method_check;
ALTER TABLE

db=# ALTER TABLE payment ADD CONSTRAINT payment_method_check
    CHECK (method IN ('credit_card', 'cash', 'bank_transfer'));
ALTER TABLE
    

Прошло еще несколько месяцев, и новый способ оплаты оказался настоящим хитом:

        INSERT INTO payment (method, amount) VALUES
    ('bank_transfer', 9000),
    ('bank_transfer', 15000),
    ('bank_transfer', 30000);
INSERT 0 3
    

Вы обрабатываете больше платежей, чем могли себе представить, но что-то не так:

        db=# \i calculate_commission.sql
 payments │ commission
──────────┼────────────
        6 │     500.00
(1 row)
    

Вы обрабатываете все эти платежи, но ваш доход остается прежним, почему?

Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

SQL-код, который не потеряет актуальности

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

Этот тип сценария довольно распространен. SQL обычно не проверяется статически, поэтому, если у вас нет автоматических тестов для этого конкретного запроса, он может легко остаться незамеченным!

Совершаем ошибки намеренно

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

Напомним запрос для расчета комиссии:

        SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
        END
    ) AS commission
FROM
    payment;
    

В запросе используется CASE-выражение для расчета комиссии для каждого способа оплаты. Выражение не определяет, что должно произойти, если метод не соответствует ни одному из WHEN-выражений, поэтому выражение неявно оценивается как NULL, а агрегатная функция игнорирует его.

Что, если вместо неявной оценки NULL мы получим ошибку?

Assert never в SQL

Чтобы вызвать ошибку в PostgreSQL, мы можем использовать простую функцию:

        CREATE OR REPLACE FUNCTION assert_never(v anyelement)
RETURNS anyelement
LANGUAGE plpgsql AS
$$
BEGIN
    RAISE EXCEPTION 'Unhandled value "%"', v;
END;
$$;
    

Функция принимает аргумент любого типа и вызывает исключение:

        db=# SELECT assert_never(1);
ERROR:  Unhandled value "1"
CONTEXT:  PL/pgSQL function assert_never(anyelement) line 3 at RAISE
    

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

        db=# SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
            ELSE assert_never(method)::int
        END
    ) AS commission
FROM
    payment;

ERROR:  Unhandled value "bank_transfer"
CONTEXT:  PL/pgSQL function assert_never(anyelement) line 3 at RAISE
    

Это круто! Запрос обнаружил необработанный способ оплаты bank_transfer и завершился ошибкой. К ошибке также относятся значения, которые мы забыли учесть, что делает его особенно полезным для отладки.

Ошибка заставляет разработчика предпринять следующие действия при обработке исключения:

Явно исключить необработанное значение:

        SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
            ELSE assert_never(method)::int
        END
    ) AS commission
FROM
    payment
WHERE
    method IN ('cash', 'credit_card');

 payments │ commission
──────────┼────────────
        3 │     500.00
    

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

Обработать новое значение:

        SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
            WHEN 'bank_transfer' THEN 50
            ELSE assert_never(method)::int
        END
    ) AS commission
FROM
    payment;

 payments │ commission
──────────┼────────────
        6 │     650.00
    

Разработчик заметил ошибку и добавил в запрос комиссию за необработанный способ оплаты. Ошибка предотвращена!

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

Assert never

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

К счастью, после публикации статьи функция assert_never была встроена в модуль ввода в Python 3.11, и ее можно использовать для выполнения исчерпывающей проверки:

        from typing import assert_never, Literal

def calculate_commission(
    method: Literal['cash', 'credit_card', 'bank_transfer'],
    amount: int,
) -> float:
    if method == 'cash':
        return 100
    elif method == 'credit_card':
        return 30 + 0.02 * amount
    else:
        assert_never(method)
    

Запуская код в Mypy, программа проверки опциональных статических типов для Python выдаст следующую ошибку:

        error: Argument 1 to "assert_never" has incompatible type "Literal['bank_transfer']";
expected "NoReturn"
    

Как и функция assert_never в SQL, ошибка предупреждает о необработанном значении bank_transfer. В отличие от функции в SQL, это произойдет не во время выполнения, а во время статического анализа.

Ошибка без функции

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

Злоупотребление делением на ноль

Самый простой способ вызвать ошибки в любом языке программирования — это разделить некоторое число на ноль:

        SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
            ELSE 1/0 -- intentional
        END
    ) AS commission
FROM
    payment;

ERROR:  division by zero
    

Вместо возврата NULL, когда метод не обрабатывается, мы делим 1 на 0, чтобы вызвать ошибку деления на ноль. Запрос не удался, как мы и хотели, но это не работает так, как мы могли бы ожидать.

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

        SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
            WHEN 'bank_transfer' THEN 50
            ELSE 1/0 -- fail on purpose
        END
    ) AS commission
FROM
    payment;

ERROR:  division by zero
    

Этот запрос обрабатывал все возможные способы оплаты, но все равно возникли ошибки. Если посмотреть документацию про CASE, то становится понятно почему:

Существуют различные ситуации, в которых подвыражения выражения оцениваются в разное время, поэтому принцип «CASE оценивает только необходимые подвыражения» не является фундаментальным. Например, постоянное подвыражение 1/0 обычно приводит к ошибке деления на ноль во время планирования, даже если оно находится в той части CASE, которая никогда не будет введена во время выполнения.

В документации можно найти объяснение этому. Хотя CASE обычно оценивает только необходимые выражения, бывают случаи, когда выражения, использующие только константы, такие как 1/0, оцениваются во время планирования. Вот почему запрос завершился ошибкой, хотя базе данных не нужно было оценивать выражение в ELSE-условии.

Злоупотребление приведением типов

Еще один популярный вид ошибок — ошибки приведения. Попробуем вызвать ошибку, приведя значение к несовместимому типу:

        SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
            ELSE method::int
        END
    ) AS commission
FROM
    payment;

ERROR:  invalid input syntax for type integer: "bank_transfer"

    

Мы пытались преобразовать текстовое значение в столбце method в целое число, но запрос не был выполнен. В качестве бонуса сообщение об ошибке предоставляет нам значение bank_transfer, что позволяет легко идентифицировать необработанное значение.

Давайте также проверим, что запрос не завершается ошибкой при обработке всех методов:

        SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
            WHEN 'bank_transfer' THEN 50
            ELSE method::int
        END
    ) AS commission
FROM
    payment;

 payments │ commission
──────────┼────────────
        6 │     650.00
    

Когда запрос обрабатывает все возможные значения для method, он не завершается ошибкой!

Злоупотребление приведением типов для нетекстовых типов

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

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

        SELECT
    CASE n
        WHEN 1 THEN 'one'
        WHEN 2 THEN 'two'
        ELSE ('Unhandled value ' || n)::int::text
    END as v
FROM (VALUES
    (1),
    (2),
    (3)
) AS t(n);

ERROR:  invalid input syntax for type integer: "Unhandled value 3"
    

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

***

Материалы по теме

12
Окт
2022

🐘🗂️ Гибкая ORM для Node.js – Sequelize

Когда начинаешь делать очередной проект, появляется желание упростить себе жизнь и лишний раз не писать SQL-запросы. В таком случае было бы неплохо познакомиться с технологией ORM.

Что такое ORM

ORM – (с англ. ​​Object-Relational Mapping, объектно-реляционное отображение) технология в программировании, которая связывает ваши объекты с базой данных, тем самым создавая виртуальную базу данных. К виртуальной базе данных можно обращаться, извлекая или записывая информацию без написания SQL-запросов.

Что такое Sequelize

Sequelize – это Node.js ORM на базе промисов, которая может работать в связке Postgres, MySQL, MariaDB, SQLite, Microsoft SQL Server, Amazon Redshift.

Sequelize может помочь закрыть 90% нужных задач без написания SQL-запросов. Внутри есть поддержка создания, обновления, удаления сущностей. Есть поддержка вложенных сортировок, сложных условий, LEFT JOIN, лимитов, подзапросов, кастомных запросов, а также есть защита от SQL-инъекций и отмена транзакций.

Установка и настройка

Как базу данных мы будем использовать PostgreSQL, поэтому пример интеграции Sequelize в проект будем показывать на ней. С вас готовый Node.js-сервер (можно с express) и развернутая база данных.

Для начала, установим Sequelize командой:

        npm install sequelize
    

После этого устанавливаем «драйверы» для ORM:

        npm install pg pg-hstore
    

Если вы пожелаете использовать MySQL вместо Postgres, то вам надо установить другие пакеты:

        npm install --save mysql2
    

Подробней про это можно почитать тут.

Подключайте базу данных в основном файле проекта (это может быть app.js).

app.js
        const db = require('./db.js')
db.authenticate()
  .catch(error => console.error(error))
    
db.js
        const Sequilize = require('sequelize')

module.exports = new Sequilize('proglib', 'postgres', 'secret', {
  host: 'localhost',
  dialect: 'postgres',
  operatorsAliases: 0,
  pool: {
    max: 5,
    min: 0,
    acquire: 3000,
    idle: 10000
  }
})
    

После запуска вы должны увидеть в консоли SELECT 1+1 AS result. Это значит, что подключение прошло успешно:


Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека фронтендера»

Методы

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

Создание таблицы в базе и ORM класса в проекте

Для начала давайте создадим таблицу users в базе данных, а после ORM класс в Node.js для взаимодействия с ней:


Теперь нам нужно создать нужный класс модели в нашем проекте. Для этого создайте папку models и добавьте там файл users.js. Добавьте в файл этот код:

        // Db
const { DataTypes } = require('sequelize')
const db = require('../db.js')

const Users = db.define('users',
  // Описание таблиц
  {
    user_id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      allowNull: false
    },
    firstname: {
      type: DataTypes.STRING,
      allowNull: false
    },
    lastname: {
      type: DataTypes.STRING,
      allowNull: false
    },
    comment: {
      type: DataTypes.TEXT,
      allowNull: true
    },
    order_by: {
      type: DataTypes.INTEGER,
      allowNull: false
    },
    file_id: {
      type: DataTypes.INTEGER,
      allowNull: true
    }
  },
  // Опции
  {
    timestamps: false
  }
)

module.exports = Users
    

Теперь импортируйте в нужное место и используйте по назначению.

Для примера.
Для примера.

Создание элемента

        const Users = require('./models/users.js')

await Users.create({
  firstname: 'Иван',
  lastname: 'Иванов',
  comment: 'Классный парень',
  order_by: 10
})
    

Если какие-то поля в описании модели имеют allowNull: false, и вы попытаетесь создать сущность без них, то фреймворк выдаст ошибку.

Обновление элемента

        const Users = require('./models/users.js')

await Users.update({
  firstname: 'Сергей'
}, {
  where: {
    user_id: 1
  }
})
    

Удаление элемента

        const Users = require('./models/users.js')

await Users.destroy({
  where: {
    user_id: 1
  }
})
    

Найти один элемент

        const Users = require('./models/users.js')

const user = await Users.findOne({
  where: {
    user_id: 1
  }
})
    

Найти много элементов

        const Users = require('./models/users.js')

const user = await Users.findAll({
  where: {
    order_by: 10
  }
})
    

Если хотите указать лимит, то можно добавить атрибуты offset и limit к аргументам объекта:

        const Users = require('./models/users.js')

const user = await Users.findAll({
  where: {
    order_by: 10
  },
  offset: 0,
  limit: 10
})
    

А если хотите получить какие-то конкретные поля (а не все), то достаточно указать аргумент attributes и передать туда массив с нужными полями:

        const Users = require('./models/users.js')

const user = await Users.findAll({
  attributes: ['firstname', 'lastname', 'order_by'],
  where: {
    order_by: 10
  },
  offset: 0,
  limit: 10
})
    

Если хотите все отсортировать, достаточно указать атрибут order и указать, какую сортировку будем делать и по какому полю:

        const Users = require('./models/users.js')

const user = await Users.findAll({
  attributes: ['firstname', 'lastname', 'order_by'],
  where: {
    order_by: 10
  },
  offset: 0,
  limit: 10,
  order: [
    ['order_by', 'ASC']
  ]
})
    

Кроме ASC (по возрастанию) можно указать DESC (по убыванию).

Сложные условия

Для сложных условий существует оператор Op. Он поддерживает множество конструкций, например: and, or, not in, in, like, between, not between, регулярные выражения. Давайте продемонстрируем парочку примеров.

Или order_by равно 10 или user_id равно 1:

        const { Op } = require('sequelize')

const Users = require('./models/users.js')

const user = await Users.findAll({
  where: {
    [Op.or]: {
      order_by: 10,
      user_id: 1
    }
  }
})
    

Все. Но лишь бы не user_id под номером 1:

        const { Op } = require('sequelize')

const Users = require('./models/users.js')

const user = await Users.findAll({
  where: {
    user_id: {
      [Op.notIn]: [1]
    }
  }
})
    

Поиск через iLike:

        const { Op } = require('sequelize')

const Users = require('./models/users.js')

const user = await Users.findAll({
  where: {
    name: {
      [Op.iLike]: `%Иван%`
    }
  }
})
    

В MySQL нет оператора iLike, надо использовать like. Разница лишь в поиске с учетом регистра и без.

Инкремент и декремент

Прибавить 1 к полю order_by:

        const Users = require('./models/users.js')

await Users.increment('order_by', {
  by: 1,
  where: {
    user_id: 1
  }
})
    

Убавить 1 от поля order_by:

        const Users = require('./models/users.js')

await Users.decrement('order_by', {
  by: 1,
  where: {
    user_id: 1
  }
})
    

Кастомные запросы

        const db = require('./db.js')

await db.query('SELECT * FROM users')
    

Можно связать с моделью, сделав свой собственный метод и вызывать его через model.myMethod(). Для этого нужно просто добавить метод в модель таким способом:


Связи

Чтобы делать LEFT JOIN и тянуть данные из связанных таблиц полезно сделать связь. Для этого давайте создадим таблицу files в базе данных с полями file_id, path. И забьем ее данными:

Таблица files в базе данных.
Таблица files в базе данных.

И не забудем добавить file_id к нужным пользователям в таблице users:


Теперь надо добавить связь в ORM класс:


        Users.hasOne(Files, { foreignKey: 'file_id', sourceKey: 'file_id', as: 'file_info' })
Files.belongsTo(Users, { foreignKey: 'file_id', targetKey: 'file_id', as: 'file_info' })
    

Главное – выше не забудьте внутрь одной модели импортировать другую модель.

После этого в нужном месте делаете так:

        const Users = require('./models/users.js')
const Files = require('./models/files.js')

const result = await Users.findOne({
  include: [
    {
      model: Files,
      as: 'file_info'
    }
  ],
  where: {
    user_id: 1
  }
})
    

Результат вас должен приятно удивить:

У автора стоит плагин для Google Chrome JSON Viewer
У автора стоит плагин для Google Chrome JSON Viewer

Можно делать include с «обратной» стороны, если вы сделали belongsTo. Это значит, что можно делать include не только из основного класса, но и дополнительного (с кем связались через belongsTo). В нашем случае из класса Files. Также, кроме hasOne есть еще hasMany для «подгрузки» множества элементов.

Отмена транзакции

        const Users = require('./models/users.js')

const transaction = await Users.sequelize.transaction()

const result = await Users.create({
  firstname: 'Иван',
  lastname: 'Иванов',
  comment: 'Классный парень',
  order_by: 10
}, {
  transaction
})

if (result.user_id > 25) {
  await transaction.rollback()
} else {
  await transaction.commit()
}
    

Показанным способом вы можете отменять транзакции в базе данных. Главное – не забывайте использовать commit() для подтверждения транзакции и rollback() для ее отмены.

***

В этой статье мы рассмотрели потрясающую ORM для Node.js – Sequelize. Мы научились:

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

Материалы по теме

07
Окт
2022

🐘 Функции и хранимые процедуры в SQL: зачем нужны и как применять в реальных примерах

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

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

  • что такое функции и хранимые процедуры;
  • разница между функциями и хранимыми процедурами;
  • создание функций и хранимых процедур;
  • применение функций и хранимых процедур в реальных примерах.

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

Между функциями и хранимыми процедурами в PostgreSQL есть несколько различий. Они показаны в таблице ниже.

Функции Хранимые процедуры
Функция имеет возвращаемый тип и возвращает значение Хранимая процедура не имеет возвращаемого типа, но имеет выходные аргументы
Использование DML (insert, update, delete) запросов внутри функции невозможно. В функциях разрешены только SELECT-запросы Использование DML-запросов (insert, update, delete) возможно в хранимой процедуре.
Функция не имеет выходных аргументов Хранимая процедура имеет и входные, и выходные аргументы
Вызов хранимой процедуры из функции невозможно Использование или же управление транзакциями возможно в хранимой процедуре
Вызов функции внутри SELECT запросов возможен Вызов хранимой процедуры из SELECT запросов невозможно

Давайте рассмотрим создание функции в PostgreSQL. Следующий блок кода иллюстрирует, как создавать функцию.

        CREATE [or REPLACE] FUNCTION function_name(param_list)
   RETURNS return_type 
   LANGUAGE plpgsql
  as
$$
DECLATE 
-- variable declaration
BEGIN
 -- logic
END;
$$
    

Оператор:

  • create [or replace] function имя_функции — создает или заменяет функцию, если она существует, с заданным именем и параметрами;
  • returns return_type — тип данных, который возвращает функция;
  • язык plpgsql — указывает на процедурное расширение PostgreSQL;
  • внутри знака $ является телом функции;
  • declare — показывает, как объявляются или инициализируются переменные;
  • блок кода [begin — end] — содержит всю логику функции;
  • begin — указывает на начало запросов;
  • end — указывает конец функции.

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

        CREATE OR REPLACE FUNCTION findMostExpensivePurchase(customer_id int)
    RETURNS numeric(10, 2)
    LANGUAGE plpgsql
AS
$$
DECLARE
    itemCost numeric(10, 2);
begin
    SELECT MAX(cost)
    INTO itemCost
    FROM purchases
    WHERE user_id = customer_id;
    RETURN itemCost;
end;
$$;

    

Важно отметить:

  • Declare itemCost integer — объявляем локальную переменную;
  • SELECT max(cost) INTO itemCost — как мы инициализируем переменную itemCost;
  • Возвращает itemCost — возвращает значение функции.

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

        CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)
LANGUAGE language_name
AS $
    stored_procedure_body;
$;
    

В приведенном ниже блоке кода показано создание процедуры — transfer(), которая принимает три параметра. Сразу после имени процедуры передаются аргументы с соответствующими типами данных — sourceAccountId, destinationAccountId, сумма. Процедура вычитает переданную сумму из одного account и добавляет ее к другому account.

        CREATE OR REPLACE PROCEDURE transfer(sourceAccountId bigInt, destinationAccountId bigInt, amount Integer)
language plpgsql
as $$
begin
    update accounts
    set balance = accounts.balance - amount
    where id = sourceAccountId;

    update accounts
    set balance = balance + amount
    where id = destinationAccountId;

    commit;
end;
$$;
    

Наконец, давайте применим все это на реальных примерах. Чтобы запустить весь код, который будет показан ниже, потребуется установить PostgreSQL (версия 13.2) на локальный компьютер или запустить PostgreSQL с помощью Docker-контейнера. Чтобы запустить PostgreSQL в Docker контейнере, необходимо запустить файл docker-compose.yaml, указанный ниже.

        version: "3.8"

services:

  postgresqldb:
    container_name: database
    image: postgres:13.2-alpine
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_DB=customer-service
      - POSTGRES_USER=customer-dev
      - POSTGRES_PASSWORD=1awer321!qwQ
    volumes:
    - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:
    
        > docker-compose up
    

Давайте создадим таблицы users и purchases и заполним их. Для простоты в таблице users есть три столбца — id, name и profession; таблица profession состоит из четырех столбцов — id, name, cost и user_id.

        CREATE table users
(
    id         serial primary key,
    name       varchar(255),
    profession varchar(255)
);

insert into users(name, profession)
values
('Bob', 'QA'),
('Camilo', 'Front End developer'),
('Billy', 'Backend Developer'),
('Alice', 'Mobile Developer'),
('Kate', 'QA'),
('Wayne', 'DevOps'),
('Tim', 'Mobile Developer'),
('Amigos', 'QA');

CREATE TABLE purchases
(
    id      serial primary key ,
    name    varchar(255),
    cost    numeric(10, 2),
    user_id int,
    foreign key (user_id)
        references users (id)
);

insert into purchases(name, cost, user_id)
values
('M1 MacBook Air', 1300.99, 1),
('Iphone 14', 1200.00 , 2),
('Iphon 10', 700.00, 3),
('Iphone 13', 800.00, 1),
('Intel Core i5', 500.00, 4),
('M1 MacBook Pro', 1500, 5),
('IMAC',2500 , 7),
('ASUS VIVOBOOK', 899.99, 6),
('Lenovo', 1232.99, 1),
('Galaxy S21', 999.99, 2),
('XIAMI REDMIBOOK 14', 742.99, 4),
('M1 MacBook Air', 1299.99 , 8),
('ACER', 799.99, 7);
    

После выполнения запросов мы можем проверить нашу базу данных

Получение записей из таблицы <code class="inline-code">users</code>
Получение записей из таблицы users
Получение записей из таблицы purchases
Получение записей из таблицы purchases

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

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

        CREATE OR REPLACE FUNCTION findMostExpensivePurchase(customer_id int)
    RETURNS numeric(10, 2)
    LANGUAGE plpgsql
AS
$$
DECLARE
    itemCost numeric(10, 2);
begin
    SELECT MAX(cost)
    INTO itemCost
    FROM purchases
    WHERE user_id = customer_id;
    RETURN itemCost;
end;
$$;
    

Чтобы вызвать функцию — выполните следующую команду:

        SELECT findMostExpensivePurchase(1) as mostExpensivePurchase;
    
Получение самой дорогой покупки пользователя по id = 1
Получение самой дорогой покупки пользователя по id = 1

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

Получение списка имён пользователей и их самых дорогих покупок
Получение списка имён пользователей и их самых дорогих покупок

Как мы обсуждали ранее, хранимые процедуры немного отличаются от функций. Функции позволяют выполнять только Select-запросы, а хранимые процедуры позволяют выполнять Insert, Update, Delete операции. Хранимые процедуры очень удобны при работе со случаями, когда необходимы операции insert, update или delete.

Рассмотрим банковскую операцию — перевод. При выполнении какой-либо банковской операции деньги переводятся с одного счета на другой. Чтобы реализовать эту хранимую процедуру — transfer(), давайте создадим таблицу accounts и заполним ее.

        create table accounts
(
    id      SERIAL primary key,
    balance  BIGINT,
    user_id INT unique ,
    FOREIGN KEY (user_id)
        references users (id)
);

INSERT INTO accounts(balance, user_id)
values
(1500, 1),
(1100, 2),
(2300, 3),
(7500, 5),
(6500, 4);
    

После выполнения приведенных выше запросов мы получим следующий вывод.

Получение записей из таблицы accounts
Получение записей из таблицы accounts

Для вызова хранимой процедуры используется — call procedure_name().

Сделаем условно перевод денег – 500 единиц с account.id = 3 на account.id = 4 и проверим результат.

Вызов функции transfer() и с последующим выводом записей из таблицы accounts
Вызов функции transfer() и с последующим выводом записей из таблицы accounts

Заключение

В этой статье мы рассмотрели:

  • что такое функции и хранимые процедуры, а также их разницу;
  • создание функции и процедуры;
  • применение функций и процедур на реальных примерах;
***

Материалы по теме

Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

07
Окт
2022

🐘 Функции и хранимые процедуры в SQL: зачем нужны и как применять в реальных примерах

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

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

  • что такое функции и хранимые процедуры;
  • разница между функциями и хранимыми процедурами;
  • создание функций и хранимых процедур;
  • применение функций и хранимых процедур в реальных примерах.

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

Между функциями и хранимыми процедурами в PostgreSQL есть несколько различий. Они показаны в таблице ниже.

Функции Хранимые процедуры
Функция имеет возвращаемый тип и возвращает значение Хранимая процедура не имеет возвращаемого типа, но имеет выходные аргументы
Использование DML (insert, update, delete) запросов внутри функции невозможно. В функциях разрешены только SELECT-запросы Использование DML-запросов (insert, update, delete) возможно в хранимой процедуре.
Функция не имеет выходных аргументов Хранимая процедура имеет и входные, и выходные аргументы
Вызов хранимой процедуры из функции невозможно Использование или же управление транзакциями возможно в хранимой процедуре
Вызов функции внутри SELECT запросов возможен Вызов хранимой процедуры из SELECT запросов невозможно

Давайте рассмотрим создание функции в PostgreSQL. Следующий блок кода иллюстрирует, как создавать функцию.

        CREATE [or REPLACE] FUNCTION function_name(param_list)
   RETURNS return_type 
   LANGUAGE plpgsql
  as
$$
DECLATE 
-- variable declaration
BEGIN
 -- logic
END;
$$
    

Оператор:

  • create [or replace] function имя_функции — создает или заменяет функцию, если она существует, с заданным именем и параметрами;
  • returns return_type — тип данных, который возвращает функция;
  • язык plpgsql — указывает на процедурное расширение PostgreSQL;
  • внутри знака $ является телом функции;
  • declare — показывает, как объявляются или инициализируются переменные;
  • блок кода [begin — end] — содержит всю логику функции;
  • begin — указывает на начало запросов;
  • end — указывает конец функции.

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

        CREATE OR REPLACE FUNCTION findMostExpensivePurchase(customer_id int)
    RETURNS numeric(10, 2)
    LANGUAGE plpgsql
AS
$$
DECLARE
    itemCost numeric(10, 2);
begin
    SELECT MAX(cost)
    INTO itemCost
    FROM purchases
    WHERE user_id = customer_id;
    RETURN itemCost;
end;
$$;

    

Важно отметить:

  • Declare itemCost integer — объявляем локальную переменную;
  • SELECT max(cost) INTO itemCost — как мы инициализируем переменную itemCost;
  • Возвращает itemCost — возвращает значение функции.

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

        CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)
LANGUAGE language_name
AS $
    stored_procedure_body;
$;
    

В приведенном ниже блоке кода показано создание процедуры — transfer(), которая принимает три параметра. Сразу после имени процедуры передаются аргументы с соответствующими типами данных — sourceAccountId, destinationAccountId, сумма. Процедура вычитает переданную сумму из одного account и добавляет ее к другому account.

        CREATE OR REPLACE PROCEDURE transfer(sourceAccountId bigInt, destinationAccountId bigInt, amount Integer)
language plpgsql
as $$
begin
    update accounts
    set balance = accounts.balance - amount
    where id = sourceAccountId;

    update accounts
    set balance = balance + amount
    where id = destinationAccountId;

    commit;
end;
$$;
    

Наконец, давайте применим все это на реальных примерах. Чтобы запустить весь код, который будет показан ниже, потребуется установить PostgreSQL (версия 13.2) на локальный компьютер или запустить PostgreSQL с помощью Docker-контейнера. Чтобы запустить PostgreSQL в Docker контейнере, необходимо запустить файл docker-compose.yaml, указанный ниже.

        version: "3.8"

services:

  postgresqldb:
    container_name: database
    image: postgres:13.2-alpine
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_DB=customer-service
      - POSTGRES_USER=customer-dev
      - POSTGRES_PASSWORD=1awer321!qwQ
    volumes:
    - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:
    
        > docker-compose up
    

Давайте создадим таблицы users и purchases и заполним их. Для простоты в таблице users есть три столбца — id, name и profession; таблица profession состоит из четырех столбцов — id, name, cost и user_id.

        CREATE table users
(
    id         serial primary key,
    name       varchar(255),
    profession varchar(255)
);

insert into users(name, profession)
values
('Bob', 'QA'),
('Camilo', 'Front End developer'),
('Billy', 'Backend Developer'),
('Alice', 'Mobile Developer'),
('Kate', 'QA'),
('Wayne', 'DevOps'),
('Tim', 'Mobile Developer'),
('Amigos', 'QA');

CREATE TABLE purchases
(
    id      serial primary key ,
    name    varchar(255),
    cost    numeric(10, 2),
    user_id int,
    foreign key (user_id)
        references users (id)
);

insert into purchases(name, cost, user_id)
values
('M1 MacBook Air', 1300.99, 1),
('Iphone 14', 1200.00 , 2),
('Iphon 10', 700.00, 3),
('Iphone 13', 800.00, 1),
('Intel Core i5', 500.00, 4),
('M1 MacBook Pro', 1500, 5),
('IMAC',2500 , 7),
('ASUS VIVOBOOK', 899.99, 6),
('Lenovo', 1232.99, 1),
('Galaxy S21', 999.99, 2),
('XIAMI REDMIBOOK 14', 742.99, 4),
('M1 MacBook Air', 1299.99 , 8),
('ACER', 799.99, 7);
    

После выполнения запросов мы можем проверить нашу базу данных

Получение записей из таблицы <code class="inline-code">users</code>
Получение записей из таблицы users
Получение записей из таблицы purchases
Получение записей из таблицы purchases

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

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

        CREATE OR REPLACE FUNCTION findMostExpensivePurchase(customer_id int)
    RETURNS numeric(10, 2)
    LANGUAGE plpgsql
AS
$$
DECLARE
    itemCost numeric(10, 2);
begin
    SELECT MAX(cost)
    INTO itemCost
    FROM purchases
    WHERE user_id = customer_id;
    RETURN itemCost;
end;
$$;
    

Чтобы вызвать функцию — выполните следующую команду:

        SELECT findMostExpensivePurchase(1) as mostExpensivePurchase;
    
Получение самой дорогой покупки пользователя по id = 1
Получение самой дорогой покупки пользователя по id = 1

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

Получение списка имён пользователей и их самых дорогих покупок
Получение списка имён пользователей и их самых дорогих покупок

Как мы обсуждали ранее, хранимые процедуры немного отличаются от функций. Функции позволяют выполнять только Select-запросы, а хранимые процедуры позволяют выполнять Insert, Update, Delete операции. Хранимые процедуры очень удобны при работе со случаями, когда необходимы операции insert, update или delete.

Рассмотрим банковскую операцию — перевод. При выполнении какой-либо банковской операции деньги переводятся с одного счета на другой. Чтобы реализовать эту хранимую процедуру — transfer(), давайте создадим таблицу accounts и заполним ее.

        create table accounts
(
    id      SERIAL primary key,
    balance  BIGINT,
    user_id INT unique ,
    FOREIGN KEY (user_id)
        references users (id)
);

INSERT INTO accounts(balance, user_id)
values
(1500, 1),
(1100, 2),
(2300, 3),
(7500, 5),
(6500, 4);
    

После выполнения приведенных выше запросов мы получим следующий вывод.

Получение записей из таблицы accounts
Получение записей из таблицы accounts

Для вызова хранимой процедуры используется — call procedure_name().

Сделаем условно перевод денег – 500 единиц с account.id = 3 на account.id = 4 и проверим результат.

Вызов функции transfer() и с последующим выводом записей из таблицы accounts
Вызов функции transfer() и с последующим выводом записей из таблицы accounts

Заключение

В этой статье мы рассмотрели:

  • что такое функции и хранимые процедуры, а также их разницу;
  • создание функции и процедуры;
  • применение функций и процедур на реальных примерах;
***

Материалы по теме

Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

30
Авг
2022

Обзор Prisma ORM — инструмента для работы с Node.js и TypeScript

Рассказываем, как работать с Prisma ORM. Это инструмент, который позволяет работать с базами данных SQL-типа на языках JS и TS.
— Читать дальше «Обзор Prisma ORM — инструмента для работы с Node.js и TypeScript»

15
Авг
2022

🗄️ Лучшие стратегии по работе с РСУБД: индексы, транзакции и уровни изоляции

В какой-то «идеальной» базе данных реализованы почти все хорошие стратегии, которые вы когда-либо могли придумать. В этой статье делимся лучшими практиками по работе с РСУБД.

Данная статья является переводом. Ссылка на оригинал.

Часто бывает неожиданным, как мало мы знаем о том, как работают базы данных, учитывая, что они хранят почти все состояния наших приложений. Тем не менее они являются фундаментом успеха большинства систем. Итак, сегодня я объясню две наиболее важные темы при работе с индексами и транзакциями РСУБД.

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

<i>Что нужно знать о базах данных</i>
Что нужно знать о базах данных

Что такое РСУБД?

Реляционная база данных – это цифровая база данных, основанная на модели реляционных данных, предложенной Э.Ф. Коддом в 1970 году. Для обслуживания реляционных баз данных используется система управления реляционными базами данных (СУБД). Многие системы реляционных баз данных имеют возможность использовать SQL (Язык структурированных запросов) для запроса и сопровождения базы данных. Примерами могут служить MySQL и PostgreSQL.

Что такое индекс?

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

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

Зачем нужны индексы?

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

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

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

Нам нужны индексы, чтобы помочь получить как можно быстрее релевантные данные, которые нам нужны.

Как работают индексы?

<i>Производительность чтения (Read performance) увеличивается по мере индексации данных, но это происходит за счет производительности записи (write performance), поскольку вам необходимо поддерживать индекс в актуальном состоянии</i>
Производительность чтения (Read performance) увеличивается по мере индексации данных, но это происходит за счет производительности записи (write performance), поскольку вам необходимо поддерживать индекс в актуальном состоянии

Таким образом, ответ на вопрос, который часто задается выше, заключается в том, чтобы логически хранить эти данные в зависимости от того, как вы будете их искать. Это означает, что если вы хотите искать в списке по имени, вы должны отсортировать список по имени. Есть несколько проблем с этой стратегией. Я озвучу их в виде вопросов для читателей:

  1. Что делать, если вы хотите искать данные несколькими способами?
  2. Как бы вы справились с добавлением новых данных в список? Данный способ быстрый?
  3. Что бы вы сделали с обновлениями?
  4. Что такое O-нотация в этих задачах?

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

Возьмем пример ниже.

Небольшая таблица, которая легко считывается с диска.
        +─────+─────────+──────────────+
| id  | name    | city         |
+─────+─────────+──────────────+
| 1   | Mahdi   | Ottawa       |
| 2   | Elon    | Mars         |
| 3   | Jeff    | Orbit        |
| 4   | Klay    | Oakland      |
| 4   | Klay    | Oakland      |
| 5   | Lebron  | Los Angeles  |
+─────+─────────+──────────────+
    

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

Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

SSD vs. HDD

Основное различие между твердотельным накопителем (SSD) и жестким диском (HDD) заключается в том, как данные хранятся и как к ним осуществляется доступ. Жесткие диски используют механические вращающиеся диски и движущуюся головку чтения/записи для доступа к данным (задержка), в то время как твердотельные накопители используют гораздо более быстрые чипы памяти, особенно при чтении большого количества небольших файлов. Поэтому, если цена не является проблемой, твердотельные накопители — лучший вариант, тем более что современные твердотельные накопители почти так же надежны, как и жесткие диски.

Теперь чтение этого небольшого объема данных в памяти выполняется довольно быстро и относительно легко сканируется. Что, если данные, которые мы ищем, не могут быть полностью кэшированы в памяти? или время чтения всех данных с диска занимает слишком много времени?

Большая таблица, которая не помещается полностью в память и разбросана по диску.
        +──────────+─────────+───────────────────+
| id | name | city |
+──────────+─────────+───────────────────+
| 1        | Mahdi   | Ottawa            |
| 2        | Elon    | Mars              |
| 3        | Jeff    | Orbit             |
| 4        | Klay    | Oakland           |
| 5        | Lebron  | Los Angeles       |
| ...      | ...     | ...               |
| 1000000  | Steph   | San Francisco     |
| 1001000  | Linus   | Portland          |
+───────+─────────+──────────────────────+

    

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

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


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

Масштаб данных часто работает против вас, и сбалансированные деревья — первый инструмент в вашем арсенале против него.

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

Блоки

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

На очень низком уровне вы можете использовать это, чтобы рассуждать о том, насколько производительными могут быть ваши системы. Quick Maths™ может быть очень эффективным при планировании производственных мощностей.

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

Связанный список

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

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

Сбалансированные деревья (B-Tree)

<i>Структурное отличие BTree от B+Tree</i>
Структурное отличие BTree от B+Tree

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

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

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

B-Tree vs. B+Tree

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

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

<i>Как B+Tree используются в РСУБД</i>
Как B+Tree используются в РСУБД

Логарифмическая масштабируемость

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

В зависимости от количества элементов, на которые могут ссылаться промежуточные узлы (M), плюс общая глубина дерева (N), мы можем ссылаться M на N объектов.

Вот таблица, иллюстрирующая концепцию со значением M, равным 5.


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

Разве это не потрясающе!

Что такое транзакция?

Транзакция – это группа последовательных операций, которая представляет собой логическую единицу работы с данными. Поэтому операция должна либо произойти в полной мере, либо не произойти вовсе. Я бы сказал, что большинству систем не нужно управлять транзакциями вручную, но бывают ситуации, когда повышенная гибкость играет важную роль в достижении желаемого эффекта. Транзакции в основном касаются I в ACID – Isolation (изолированности).

Что такое ACID?

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

  1. Атомарность предотвращает частичное обновление базы данных, что может вызвать более серьезные проблемы, чем полное исключение всей серии.
  2. Согласованность гарантирует, что транзакция может перемещать базу данных из одного допустимого состояния в другое. Это позволяет соответствовать всем определенным правилам базы данных, а также предотвращать сбои в результате некорректных транзакций.
  3. Изолированность определяет, как конкретное действие отображается другим пользователям системы.
  4. Надежность – это свойство, которое гарантирует, что транзакции, которые были совершены, будут сохраняться постоянно.

Эти свойства, как правило, хорошо понятны, но их определения могут не совпадать от системы к системе в зависимости от системы базы данных. Поэтому обязательно ознакомьтесь с каждым из них для вашей производственной базы данных.

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

Как создать ручную транзакцию
        -- Manual transaction with commit. 

BEGIN;

SELECT * FROM people WHERE id =1;

COMMIT or ROLLBACK;
    

Мы сосредоточимся на времени между BEGIN и COMMIT или ROLLBACK и на том, что происходит с различными другими транзакциями, воздействующими на те же данные.

COMMIT/ROLLBACK

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

  1. COMMIT сохраняет изменения (надежность), внесенные текущей транзакцией.
  2. ROLLBACK отменяет изменения, внесенные текущей транзакцией.

Для случая, когда вы не управляете транзакциями вручную, если все запросы в транзакции завершены успешно, они фиксируются (COMMIT). Если происходит какой-либо сбой, изменения во время этой транзакции откатываются (ROLLBACK), чтобы обеспечить атомарность всего действия.

О феноменах

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

Неповторяемые чтения

<i>Пример неповторяемого чтения</i>
Пример неповторяемого чтения

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

«Грязные» чтения

<i>Пример «грязного» чтения</i>
Пример «грязного» чтения

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

Фантомное чтение

<i>Пример фантомного чтения</i>
Пример фантомного чтения

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

Range Locks (блокировки диапазона) лучше всего описывать, иллюстрируя все возможные уровни блокировки:

  1. Сериализованный доступ к базе данных — заставляет базу данных выполнять запросы один за другим — ужасный параллелизм, однако высочайший уровень согласованности.
  2. Блокировка таблицы — блокировка таблицы для вашей транзакции с немного лучшим параллелизмом, но одновременная запись в таблицу по-прежнему замедляется.
  3. Блокировка строк — блокирует строку, над которой вы работаете, даже лучше, чем при блокировкe таблиц, но если эта строка нужна нескольким транзакциям, им придется подождать.

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

4 уровня изоляции

<i>4 уровня изоляции для SQL Standard</i>
4 уровня изоляции для SQL Standard

Стандарт SQL определяет 4 стандартных уровня изоляции, которые могут и должны быть настроены глобально (могут произойти неприятные вещи, если мы не отнесемся внимательно к уровням изоляции).

REPEATABLE READ (Повторяемое чтение)

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

Как только мы делаем наше первое чтение (см. рисунок выше.), это представление блокируется на время транзакции, поэтому все, что происходит вне контекста этой транзакции, не имеет значения — committed или нет.

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

SERIALIZABLE (Упорядочиваемость)

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

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

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

Более новые распределенные базы данных используют преимущества этого уровня изоляции для обеспечения согласованности. CockroachDB — пример такой базы данных.

READ COMMITTED (Чтение фиксированных данных)

Этот режим изоляции отличается от REPEATABLE READ тем, что каждое чтение создает свой собственный непротиворечивый (зафиксированный) моментальный снимок времени. В результате этот режим изоляции подвержен фантомным чтениям, если мы выполняем несколько операций чтения в рамках одной и той же транзакции.

READ UNCOMMITTED (Чтение незафиксированных данных)

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

***

Материалы по теме

08
Авг
2022

🐍 Python, Tkinter и SQL: разрабатываем приложение для создания словарей и запоминания иностранных слов

Изучаем Tkinter и основные SQL-команды в ходе разработки программы WordMatch с графическим интерфейсом и CRUD-модулем для удобного создания и редактирования пользовательских словарей.

Обзор проекта

Приложение WordMatch включает в себя три модуля, которые могут работать и вместе, и по отдельности:

  1. Скрипт для создания пользовательского словаря.
  2. GUI интерфейс и набор CRUD операций для добавления, редактирования и удаления записей в словаре.
  3. GUI интерфейс и скрипт для проверки правильности сопоставления иностранных слов и значений, выведенных в случайном порядке.
WordMatch состоит из трех независимых скриптов
WordMatch состоит из трех независимых скриптов

Что мы изучим

  1. Как создавать базы данных, выполнять CRUD операции и запросы на языке SQL.
  2. Как обрабатывать события в элементах Listbox.
  3. Как назначить действия основным кнопкам программы и кнопке закрытия окна.

Скрипт для создания словаря

Словарь представляет собой базу данных SQLite, которая поставляется с Python по умолчанию. Для создания новой базы не придется устанавливать никакие дополнительные модули. Однако при желании можно установить набор дополнительных инструментов для работы с SQLite и один из визуальных браузеров-редакторов:

  1. SQLiteStudio
  2. DBeaver
  3. DB Browser for SQLite

Структура таблицы словаря dictionary задается в sql_create_dictionary_table скрипта create_new_db.py:

  • id – порядковый номер записи (целое число);
  • word – иностранное слово (текстовое поле);
  • meaning – значение слова на русском языке (текстовое поле).

При желании в этом описании можно задать поле для фонетической транскрипции. Для создания базы в определенной директории надо сначала убедиться, что необходимый путь к папке уже имеется на диске. Если нужную директорию не создать заранее, но указать путь к ней в коде, то выполнение скрипта завершится так:

        unable to open database file
Ошибка: не удалось подключиться к базе.

    

Если же путь вообще не указан, файл базы данных будет создан в текущей рабочей директории – в Windows это C:\Users\User.

При подключении к несуществующей базе SQLite создает файл базы автоматически, но только при условии, что указанный путь существует. Ниже приведен полный код скрипта для создания базы данных словаря. При этом папка Dictionary в поддиректории Users была создана заранее, а файл dictionary.db в ней был сгенерирован скриптом автоматически:

create_new_db.py
        import sqlite3
from sqlite3 import Error

def create_connection(db_file):
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)

    return conn

def create_table(conn, create_table_sql):
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)

def main():
    database = r"dictionary_my.db"
    # описание столбцов словаря - id номер, слово и значение
    sql_create_dictionary_table = """ CREATE TABLE IF NOT EXISTS dictionary (
                                        id integer PRIMARY KEY,
                                        word text,
                                        meaning text
                                    ); """


    # подключение к базе
    conn = create_connection(database)

    # создание таблицы dictionary
    if conn is not None:
        create_table(conn, sql_create_dictionary_table)
    else:
        print("Ошибка: не удалось подключиться к базе.")


if __name__ == '__main__':
    main()
    

    
Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека питониста»

GUI интерфейс и скрипт для набора CRUD операций

Графический интерфейс программы включает стандартные элементы Tkinter и несколько виджетов модуля Ttk. Для позиционирования элементов на поверхности окна в Tkinter есть целых три метода – pack(), place() и grid(). Мы воспользуемся последним, поскольку он предусматривает максимальную точность размещения. При использовании grid() все пространство окна делится на ряды row и столбцы column. Для каждого элемента нужно указать ряд и столбец, на пересечении которых он размещается:

        (row = 2, column = 0)
    

Еще можно указать ширину элемента, если нужно, чтобы он соответствовал ширине нескольких столбцов:

        columnspan = 2
    
Визуальный интерфейс для CRUD
Визуальный интерфейс для CRUD

SQL-запросы и команды

Первый запрос, который нам потребуется для извлечения из базы всех слов, выглядит так:

        'SELECT * FROM dictionary ORDER BY word DESC'
    

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

В функции add_word() используется команда для вставки новой записи:

        'INSERT INTO dictionary VALUES(NULL, ?, ?)'
    

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

        parameters = (self.word.get(), self.meaning.get())
    
Сообщение об успешном добавлении нового слова
Сообщение об успешном добавлении нового слова

Для удаления слова необходимо выделить соответствующую строку. Слово извлекается из выделенной строки:

        word = self.tree.item(self.tree.selection())['text']
    

И передается в качестве параметра с командой удаления:

        query = 'DELETE FROM dictionary WHERE word = ?'
self.run_query(query, (word, ))
    

В функции редактирования существующей записи мы реализуем предварительное заполнение поля формы старыми значениями – оригиналом слова и его переводом:

        value = word
value = old_meaning

    
Предварительное заполнение полей в окне редактирования
Предварительное заполнение полей в окне редактирования

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

Фактическое обновление существующей записи производится командой со следующими параметрами:

        query = 'UPDATE dictionary SET word = ?, meaning = ? WHERE word = ? AND meaning = ?'
parameters = (new_word, new_meaning, word, old_meaning)
    

Вот полный код для CRUD скрипта и его интерфейса:

        from tkinter import ttk
from tkinter import *
import sqlite3

class Dictionary:
    db_name = 'dictionary.db'

    def __init__(self, window):

        self.wind = window
        self.wind.title('Редактирование словаря')

        # создание элементов для ввода слов и значений
        frame = LabelFrame(self.wind, text = 'Введите новое слово')
        frame.grid(row = 0, column = 0, columnspan = 3, pady = 20)
        Label(frame, text = 'Слово: ').grid(row = 1, column = 0)
        self.word = Entry(frame)
        self.word.focus()
        self.word.grid(row = 1, column = 1)
        Label(frame, text = 'Значение: ').grid(row = 2, column = 0)
        self.meaning = Entry(frame)
        self.meaning.grid(row = 2, column = 1)
        ttk.Button(frame, text = 'Сохранить', command = self.add_word).grid(row = 3, columnspan = 2, sticky = W + E)
        self.message = Label(text = '', fg = 'green')
        self.message.grid(row = 3, column = 0, columnspan = 2, sticky = W + E)
        # таблица слов и значений
        self.tree = ttk.Treeview(height = 10, columns = 2)
        self.tree.grid(row = 4, column = 0, columnspan = 2)
        self.tree.heading('#0', text = 'Слово', anchor = CENTER)
        self.tree.heading('#1', text = 'Значение', anchor = CENTER)

        # кнопки редактирования записей
        ttk.Button(text = 'Удалить', command = self.delete_word).grid(row = 5, column = 0, sticky = W + E)
        ttk.Button(text = 'Изменить', command = self.edit_word).grid(row = 5, column = 1, sticky = W + E)

        # заполнение таблицы
        self.get_words()

    # подключение и запрос к базе
    def run_query(self, query, parameters = ()):
        with sqlite3.connect(self.db_name) as conn:
            cursor = conn.cursor()
            result = cursor.execute(query, parameters)
            conn.commit()
        return result

    # заполнение таблицы словами и их значениями
    def get_words(self):
        records = self.tree.get_children()
        for element in records:
            self.tree.delete(element)
        query = 'SELECT * FROM dictionary ORDER BY word DESC'
        db_rows = self.run_query(query)
        for row in db_rows:
            self.tree.insert('', 0, text = row[1], values = row[2])

    # валидация ввода
    def validation(self):
        return len(self.word.get()) != 0 and len(self.meaning.get()) != 0
    # добавление нового слова
    def add_word(self):
        if self.validation():
            query = 'INSERT INTO dictionary VALUES(NULL, ?, ?)'
            parameters =  (self.word.get(), self.meaning.get())
            self.run_query(query, parameters)
            self.message['text'] = 'слово {} добавлено в словарь'.format(self.word.get())
            self.word.delete(0, END)
            self.meaning.delete(0, END)
        else:
            self.message['text'] = 'введите слово и значение'
        self.get_words()
    # удаление слова 
    def delete_word(self):
        self.message['text'] = ''
        try:
            self.tree.item(self.tree.selection())['text'][0]
        except IndexError as e:
            self.message['text'] = 'Выберите слово, которое нужно удалить'
            return
        self.message['text'] = ''
        word = self.tree.item(self.tree.selection())['text']
        query = 'DELETE FROM dictionary WHERE word = ?'
        self.run_query(query, (word, ))
        self.message['text'] = 'Слово {} успешно удалено'.format(word)
        self.get_words()
    # рeдактирование слова и/или значения
    def edit_word(self):
        self.message['text'] = ''
        try:
            self.tree.item(self.tree.selection())['values'][0]
        except IndexError as e:
            self.message['text'] = 'Выберите слово для изменения'
            return
        word = self.tree.item(self.tree.selection())['text']
        old_meaning = self.tree.item(self.tree.selection())['values'][0]
        self.edit_wind = Toplevel()
        self.edit_wind.title = 'Изменить слово'

        Label(self.edit_wind, text = 'Прежнее слово:').grid(row = 0, column = 1)
        Entry(self.edit_wind, textvariable = StringVar(self.edit_wind, value = word), state = 'readonly').grid(row = 0, column = 2)
        
        Label(self.edit_wind, text = 'Новое слово:').grid(row = 1, column = 1)
        # предзаполнение поля
        new_word = Entry(self.edit_wind, textvariable = StringVar(self.edit_wind, value = word))
        new_word.grid(row = 1, column = 2)


        Label(self.edit_wind, text = 'Прежнее значение:').grid(row = 2, column = 1)
        Entry(self.edit_wind, textvariable = StringVar(self.edit_wind, value = old_meaning), state = 'readonly').grid(row = 2, column = 2)
 
        Label(self.edit_wind, text = 'Новое значение:').grid(row = 3, column = 1)
        # предзаполнение поля
        new_meaning= Entry(self.edit_wind, textvariable = StringVar(self.edit_wind, value = old_meaning))
        new_meaning.grid(row = 3, column = 2)

        Button(self.edit_wind, text = 'Изменить', command = lambda: self.edit_records(new_word.get(), word, new_meaning.get(), old_meaning)).grid(row = 4, column = 2, sticky = W)
        self.edit_wind.mainloop()
    # внесение изменений в базу
    def edit_records(self, new_word, word, new_meaning, old_meaning):
        query = 'UPDATE dictionary SET word = ?, meaning = ? WHERE word = ? AND meaning = ?'
        parameters = (new_word, new_meaning, word, old_meaning)
        self.run_query(query, parameters)
        self.edit_wind.destroy()
        self.message['text'] = 'слово {} успешно изменено'.format(word)
        self.get_words()

if __name__ == '__main__':
    window = Tk()
    application = Dictionary(window)
    window.mainloop()


    

Модуль для запоминания слов и проверки значений

Английские слова и их значения загружаются в два элемента Listbox. Для перемешивания слов и значений в случайном порядке используется метод shuffle из модуля random. Для обработки событий (кликов) по спискам Listbox мы напишем две функции – callback_left и callback_right. Чтобы связать функции с Listbox, нужно воспользоваться методом bind:

        self.right.bind("<<ListboxSelect>>", self.callback_right)
self.left.bind("<<ListboxSelect>>", self.callback_left)

    
Слова и значения выводятся в случайном порядке
Слова и значения выводятся в случайном порядке

Функция callback_left отслеживает клики по английским словам в левом элементе Listbox. Когда пользователь кликает по слову, функция посылает запрос в базу:

        'SELECT * from dictionary WHERE word = ?'
    

Результат запроса – отдельная запись:

        record = cursor.fetchone()
    

Второй элемент записи record[2] является значением слова, которое передается в функцию callback_right.

Функция callback_right обрабатывает клики по значениям слов в правом списке Listbox. Когда пользователь кликает по значению, функция сравнивает его со значением, полученным из callback_left. Если они совпадают – ответ является верным, и английское слово вместе с соответствующим значением удаляются из левого и правого списков:

        if click == self.trans:
    self.right.delete(ANCHOR)
    self.left.delete(ANCHOR)
    

В противном случае выводится сообщение о неверном ответе, а выделение с ошибочного значения снимается.

В GUI модуля для заучивания слов доступны две кнопки: Редактировать для вызова скрипта и визуального интерфейса редактирования словаря, а также Начать сначала для перезагрузки слов и значений. Чтобы вызвать скрипт редактирования словаря, достаточно написать простейшую функцию:

        def run_edit(self):
    os.system('edit_dictionary.py')
    

Назначение команд для кнопок выглядит так:

        ttk.Button(text="Начать сначала", command=self.restart_program).grid(row = 4, column = 1, sticky = W + E)
ttk.Button(text="Редактировать", command=self.run_edit).grid(row = 4, column = 0, sticky = W + E)
    
Редактировать словарь можно прямо из модуля запоминания слов
Редактировать словарь можно прямо из модуля запоминания слов

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

Подтверждение выхода
Подтверждение выхода

Для этого нужно задать новый протокол:

        self.wind.protocol("WM_DELETE_WINDOW", self.on_exit)
    

И добавить функцию:

            def on_exit(self):
        if messagebox.askyesno("Выйти", "Закрыть программу?"):
            self.wind.destroy()
    

Это полный код модуля word_match.py для запоминания и проверки значений слов:

word_match.py
        from tkinter import ttk
from tkinter import *
import random, os
import sqlite3

class Match:
    db_name = 'dictionary.db'

    def __init__(self, window):

        self.wind = window
        self.wind.title('Учим слова')
        self.eng, self.trans = str(), str()
        self.message = Label(text = '', fg = 'red')
        self.message.grid(row = 1, column = 0, columnspan = 2, sticky = W + E)
        # правая и левая колонки
        self.left = Listbox(height = 12, exportselection=False, activestyle='none')
        self.left.grid(row = 2, column = 0)
        self.right = Listbox(height = 12, activestyle='none')
        self.right.grid(row = 2, column = 1)
        self.right.bind("<<ListboxSelect>>", self.callback_right)
        self.left.bind("<<ListboxSelect>>", self.callback_left)
        # назначение команд кнопкам программы и х-кнопке окна
        ttk.Button(text="Начать сначала", command=self.restart_program).grid(row = 4, column = 1, sticky = W + E)
        ttk.Button(text="Редактировать", command=self.run_edit).grid(row = 4, column = 0, sticky = W + E)
        self.wind.protocol("WM_DELETE_WINDOW", self.on_exit)
        # заполняем колонки словами  
        self.get_words()
    #  закрытие программы по клику на кнопке х
    def on_exit(self):
        if messagebox.askyesno("Выйти", "Закрыть программу?"):
            self.wind.destroy()
    #  подключение к базе и передача запроса
    def run_query(self, query, parameters = ()):
        with sqlite3.connect(self.db_name) as conn:
            cursor = conn.cursor()
            result = cursor.execute(query, parameters)
            conn.commit()
        return result
 
    # запрос на извлечение всех существующих записей из базы в алфавитном порядке
    def get_words(self):
        query = 'SELECT * FROM dictionary ORDER BY word DESC'
        db_rows = self.run_query(query)
        # формирование словаря из перемешанных в случайном порядке слов и их значений
        lst_left, lst_right = [], []
        for row in db_rows:
            lst_left.append(row[1])
            lst_right.append(row[2])
        random.shuffle(lst_left)
        random.shuffle(lst_right)
        dic = dict(zip(lst_left, lst_right))
        # заполнение правой и левой колонок
        for k, v in dic.items():
            self.left.insert(END, k)
            self.right.insert(END, v)
    # обработка клика по словам в левой колонке
    def callback_left(self, event):
        self.message['text'] = ''
        if not event.widget.curselection():
            return
        # извлечение из базы значения выделенного слова
        w = event.widget
        idx = int(w.curselection()[0])
        self.eng = w.get(idx)
        with sqlite3.connect(self.db_name) as conn:
            cursor = conn.cursor()
            sqlite_select_query = 'SELECT * from dictionary WHERE word = ?'
            cursor.execute(sqlite_select_query, (self.eng,))
            record = cursor.fetchone()
            self.trans = record[2]
   
    # обработка клика в правой колонке  
    def callback_right(self, event1):
        self.message['text'] = ''
        if not event1.widget.curselection():
            return
        
        w = event1.widget
        idx = int(w.curselection()[0])
        click = w.get(idx)
        # если выбранное слово является правильным переводом, удаляем и оригинал, и значение
        if click == self.trans:
            self.right.delete(ANCHOR)
            self.left.delete(ANCHOR)
        # сообщаем о неверном значении
        else:
            self.message['text'] = 'Неправильно'
            self.right.selection_clear(0, END)
    # загружаем окно и скрипт редактирования словаря        
    def run_edit(self):
        os.system('edit_dictionary.py')
    # перезапуск программы
    def restart_program(self):
        self.message['text'] = ''
        self.left.delete(0, END)
        self.right.delete(0, END)
        self.get_words()

if __name__ == '__main__':
    window = Tk()
    window.geometry('250x245+350+200')
    application = Match(window)
    window.mainloop()


    

Готовый проект доступен в репозитории.

Материалы по теме

28
Апр
2022

🗄️ ✔️ 10 лучших практик написания SQL-запросов

Делимся рекомендациями по решению распространенных проблем с SQL, с которыми часто сталкиваются специалисты по работе с данными.

Статья является переводом. Оригинал доступен по ссылке.

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

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

Данные советы предназначены для всех специалистов, независимо от опыта. Я перечислил самые распространенные случаи в моей практике, разместив в порядке возрастания сложности.

Для примеров я буду использовать базу данных SQLite: sql-practice.com

1. Проверка уникальных значений в таблице

        SELECT count(*), count(distinct patient_id) FROM patients

    

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

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

Однако становится немного сложнее, когда у вас есть несколько столбцов, которые создают первичный ключ. Чтобы решить эту проблему, просто объедините столбцы, составляющие первичный ключ, после ключевого слова DISTINCT. Простой пример — объединение имени и фамилии для создания первичного ключа.

        SELECT count(*), count(distinct first_name || last_name) FROM patients
    

2. Поиск повторяющихся записей

        SELECT 
    first_name 
    , count(*) as ct
    
FROM patients
GROUP BY
    first_name
HAVING
    count(*) > 1
ORDER BY 
    COUNT(*) DESC
;
    

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

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

Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

3. Обработка NULL с DISTINCT

        with new_table as (
select patient_id from patients
UNION
select null
)

select 
    count(*)
  , count(distinct patient_id)
  , count(patient_id) 

from new_table
    

Результатом запроса будет значение 4531 для столбца COUNT(*) и 4530 для двух оставшихся столбцов. Когда вы указываете столбец, ключевое слово COUNT исключает нулевые значения. Однако, при использовании звездочки в подсчет включаются значения NULL. Это может сбивать с толку при проверке, является ли столбец первичным ключом, поэтому я посчитал нужным упомянуть об этом.

4. CTE > Подзапросы

        -- Use of CTE
with combined_table as (
select
  *
 
FROM patients p
JOIN admissions a 
  on p.patient_id = a.patient_id
)

, name_most_admissions as (
select
    first_name || ' ' || last_name as full_name
  , count(*)                       as admission_ct
  
FROM combined_table
)

select * from name_most_admissions
;

-- Use of sub-queries :(
select * from 
   (select
        first_name || ' ' || last_name as full_name
      , count(*)                       as admission_ct
  
    FROM (select
             *
 
          FROM patients p
          JOIN admissions a 
              on p.patient_id = a.patient_id
          ) combined_table
    ) name_most_admissions
;
    

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

5. Использование SUM и CASE WHEN вместе

        select 
     sum(case when allergies = 'Penicillin' and city = 'Burlington' then 1 else 0 end) as allergies_burl
   , sum(case when allergies = 'Penicillin' and city = 'Oakville' then 1 else 0 end)   as allergies_oak
  
from patients
    

Предложение WHERE может работать, если вы хотите суммировать количество пациентов, отвечающих определенным условиям. Но если вы хотите проверить несколько условий, вы можете использовать ключевые слова SUM и CASE WHEN вместе. Это делает код лаконичным и легко читаемым.

Данную комбинацию также можно использовать в выражении WHERE, как в примере ниже.

        select
  * 
FROM patients
WHERE TRUE
  and 1 = (case when allergies = 'Penicillin' and city = 'Burlington' then 1 else 0 end)
    

6. Будьте осторожны с датами

        with new_table as (
select
    patient_id
  , first_name
  , last_name
  , time(birth_date, '+1 second') as birth_date

from patients
where TRUE
   and patient_id = 1

UNION
  
select
    patient_id
  , first_name
  , last_name
  , birth_date 

from patients
WHERE TRUE
  and patient_id != 1
)

select 
  birth_date 
  
from new_table 
where TRUE 
  and birth_date between '1953-12-05' and '1953-12-06'
    

В этой базе данных все даты сокращены до дня. Это означает, что все значения времени столбца Birthday_date в этом примере равны 00:00:00. Однако в реальных наборах данных это обычно не так.

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

В приведенном выше примере я искусственно добавил секунду к пациенту №1. Как видите, этой 1-й секунды было достаточно, чтобы исключить пациента из результатов при использовании ключевого слова BETWEEN.

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

7. Не забывайте об оконных функциях

        select
    p.*
  , MAX(weight) over (partition by city) as maxwt_by_city
   
 from patients p
    

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

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

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

8. По возможности избегайте DISTINCT

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

Это ошибка. Если вы не можете с самого начала объяснить, почему в данных есть дубликаты, возможно, вы исключили из своего анализа какую-то полезную информацию. Вы всегда должны быть в состоянии объяснить, почему вы помещаете distinct в таблицу и почему есть дубликаты. Использование WHERE обычно предпочтительнее, так как вы можете увидеть то, что исключается.

9. Форматирование SQL

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

Вы можете заметить, что в примерах я использовал TRUE в WHERE выражении. Это было сделано для того, чтобы все аргументы в выражении WHERE начинались с AND. Таким образом, аргументы начинаются с одного и того же места.

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

10. Совет по отладке

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

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

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

***

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

Материалы по теме

14
Апр
2022

📜 Основные SQL-команды и запросы с примерами, которые должен знать каждый разработчик

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

Кратко об SQL

Хранить информацию в базах данных (БД) – обычная практика для среднего и крупного бизнеса. Такой подход позволяет при необходимости легко получить доступ к нужным сведениям. Чтобы работать с такими базами, необходимо изучить SQL — структурированный язык запросов, используемый для извлечения и обработки хранящейся информации.

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

1. Язык определения данных

Язык определения данных или DDL позволяет создавать БД, описывать их структуру, а также устанавливать правила размещения в них данных.

Команды DDL:

  1. ALTER — добавить, изменить или удалить столбцы.
  2. COLLATE — оператор сопоставления.
  3. CREATE — создать таблицы.
  4. DROP — оператор удаления объектов из базы данных.
  5. DISABLE TRIGGER — отключить триггер.
  6. ENABLE TRIGGER — включить триггер.
  7. RENAME — переименовать.
  8. UPDATE STATISTICS — обновить статистику.

2. Язык обработки данных DML

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

Запросы DML:

  1. BULK INSERT — для загрузки данных из файла в таблицу.
  2. SELECT — оператор запроса, возвращающий нам необходимые сведения.
  3. DELETE — удаляет строки из таблиц.
  4. UPDATE — изменяет табличные данные.
  5. INSERT — вставляет новые записи.
  6. UPDATETEXT — обновляет текст.
  7. MERGE — смесь операторов UPDATE и INSERT.
  8. WRITETEXT — запись текста.
  9. READTEXT — чтение текста.

3. Язык управления данными или DCL

Язык управления данными или DCL позволяет настроить работу с базой данных путем установки необходимых разрешений и прав СУБД.

DCL -запросы:

  1. GRANT — управление доступом к базе данных.
  2. REVOKE — отмена выданных привилегий.
  3. DENY — отнимает у пользователя возможность выполнять действия.

4. Язык управления транзакциями TCL

Язык управления транзакциями TCL служит для управления изменениями, происходящими в результате DML-запросов, и объединяет эти DML-запросы в наборы транзакций.

TCL команды:

  1. BEGIN — определяем начало транзакции.
  2. COMMIT — фиксируем изменения.
  3. ROLLBACK — откатывает все изменения.
Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

5. Основные команды SQL: Select

Будет сложно охватить в одной маленькой статье все особенности работы с SQL. Поэтому давайте рассмотрим виды SQL-запросов с использованием ключевого слова SELECT. Пожалуй, это наиболее часто используемая команда, необходимая для извлечения (чтения) нужной нам информации из базы данных.

Давайте разберем по пунктам практические примеры применения таких запросов на примере созданной нами в БД таблицы Clients с информацией о покупателях условного магазина.

Cousomer_id First_name Last_name Age Country
1 Абрам Рабинович 31 Россия
2 Денис Петров 22 Россия
3 Иван Сидоров 22 Латвия
4 Денис Кузнецов 25 Латвия
5 Сара Рабинович 28 Беларусь

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

1. Запрашивать фамилии и имена всех клиентов. Оператор FROM в этой конструкции указывает, из какой именно таблицы вы хотите ее (эту информацию) взять.

Например:

        SELECT first_name, last_name
FROM Clients;
    

2. Для выбора всех столбцов из таблицы используем символ *.

        SELECT *
FROM Clients;

    

3. Команда SELECT может иметь необязательный оператор WHERE, позволяющий извлекать записи, соответствующие определенным условиям. Здесь мы выбираем из таблицы всех клиентов по фамилии Рабинович:

        SELECT *
FROM Clients;
WHERE last_name = 'Рабинович';

    

4. Здесь с помощью Select мы хотим получить всех клиентов из России:

        SELECT age
FROM Clients;
WHERE country = 'Россия';

    
Примечание
В SQL мы должны заключать текстовые данные в одинарные или двойные кавычки.

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

        SELECT *
FROM Clients;
WHERE age > 25;

    

6. А тут мы выбираем всех клиентов, по фамилии Рабинович проживающих в России.

        SELECT *
FROM Clients;
WHERE last_name = Рабинович' AND country = 'Россия';

    

7. Здесь SELECT выведет результат, если все условия, разделенные оператором AND, будут истинны. В данном случае команда выберет имена всех клиентов по фамилии Петров из России:

        SELECT first_name
FROM Clients;
WHERE country = 'Россия' AND last_name = 'Петров';

    

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

8. Оператор SQL NOT выбирает строку, если заданное условие равно FALSE. Здесь мы выбираем имена и фамилии клиентов, проживающих во всех странах, кроме Латвии.

        SELECT first_name, last_name
FROM Clients;
WHERE NOT country = 'Латвия

    

9. Также можно комбинировать несколько операторов AND, OR и NOT в одном запросе. В этом примере мы хотим выбрать клиентов из России или Беларуси, возраст которых меньше 26 .

        SELECT *
FROM Clients;
WHERE (country = 'Россия' OR country = 'Беларусь') AND age < 26;

    

10. Оператор SQL SELECT DISTINCT выбирает уникальные, отличные от других, строки из таблицы. Здесь мы выводим неодинаковые страны проживания клиентов.

        SELECT DISTINCT country
FROM Clients;

    

11. А здесь мы выбираем неодинаковые комбинации параметров страна + имя.

        SELECT DISTINCT country, first_name
FROM Clients;

    

12. Если нам нужно подсчитать количество уникальных строк, мы можем использовать функцию COUNT() в совокупности с оператором DISTINCT. Здесь команда SELECT возвращает количество уникальных стран:

        SELECT COUNT(DISTINCT country)
FROM Clients;

    

13. Ключевое слово AS используется для присвоения столбцам или всей таблице временного имени, которое позже можно использовать для его или ее идентификации соответственно. Здесь переименовываем столбец first_name на name.

        SELECT first_name AS name
FROM Clients;

    

14. Оператор TOP используется для выбора фиксированного количества строк из базы данных. В примере ниже мы выбираем первые 2 строки из таблицы.

        SELECT TOP 2 *
FROM Clients;

    

15. Ключевое слово LIMIT используется со следующими системами баз данных: MySQL, PostgreSQL, SQLite. Здесь мы ограничиваем вывод имени и возраста клиента двумя первыми строками таблицы.

        SELECT first_name, age
FROM Clients;
LIMIT 2;

    

16. Оператор IN используется с предложением WHERE для сопоставления значений в списке. Здесь мы выбираем имена клиентов, проживающих либо в России, либо в Латвии.

        SELECT first_name, country
FROM Clients;
WHERE country IN ('Россия', 'Латвия');

    

17. Оператор IN также можно использовать для выбора строк с определенным значением. Здесь выбираются строки со значением Россия в поле country.

        SELECT first_name, country
FROM Clients;
WHERE 'Россия' in (country);

    

18. Ключевое выражение ORDER BY используется для сортировки результирующего набора выводимых данных в порядке возрастания или убывания. В этом примере мы выбираем всех клиентов, а затем сортируем их в порядке возрастания по имени (т. е. по алфавиту).

        SELECT *
FROM Clients;
ORDER BY first_name;

    

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

        SELECT *
FROM Clients;
ORDER BY age ASC;

    

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

20. Также мы можем использовать ORDER BY при работе с несколькими столбцами. В конструкции ниже мы выбираем все записи, а затем сортируем их по имени. Если имя повторяется более одного раза, SQL сортирует данные по возрасту.

        SELECT *
FROM Clients;
ORDER BY first_name, age;

    

21. Можем использовать ORDER BY вместе с ключевым словом WHERE. В примере мы сначала выбираем данные о фамилии и возрасте клиентов не из России. Затем выбранные записи сортируем в порядке убывания по фамилии:

        SELECT last_name, age
FROM Clients;
WHERE NOT country = 'UK'
ORDER BY last_name DESC;

    

22. Здесь мы группируем строки по параметру country и подсчитываем количество повторяющихся стран с помощью функции COUNT().

        SELECT country, COUNT(*) AS number
FROM Clients;
GROUP BY country;

    
***

Это далеко не полный список вариантов использования команды SELECT при работе с базами данных. Комбинаций довольно много. Однако, важно понять базовый принцип написания таких запросов. По большому счету, язык запросов не так сложен и основные команды при должной усидчивости можно освоить за неделю. Ну а дальше – практика, а практический опыт накапливается долго. Удачи в обучении.

Материалы по теме

13
Мар
2022

🐘 Руководство по SQL для начинающих. Часть 3: усложняем запросы, именуем вложенные запросы, анализируем скорость запроса

В последней части цикла статей мы составим сложные запросы, поработаем с вложенными запросами, поговорим о сохранности данных и немного о необходимости индексов.

Самостоятельное объединение (self join)

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


Давайте для начала добавим поле с идентификатором лучшего друга best_friend_id в таблицу учеников и вставим в него некоторые данные.

        ALTER TABLE students
ADD best_friend_id INT;

UPDATE students
SET best_friend_id = 5
WHERE id = 1;

UPDATE students
SET best_friend_id = 4
WHERE id = 2;

UPDATE students
SET best_friend_id = 2
WHERE id = 3;

UPDATE students
SET best_friend_id = 2
WHERE id = 4;

UPDATE students
SET best_friend_id = 1
WHERE id = 5;

SELECT * FROM students;

/*
 id  | name     | classroom_id | best_friend_id
 --- | -------- | ------------ | --------------
   1 | Adam     |            1 |              5
   2 | Betty    |            1 |              4
   3 | Caroline |            2 |              2
   4 | Dina     |       [null] |              2
   5 | Evan     |       [null] |              1
*/
    

Итак, в таблице мы храним id лучшего друга для каждого ученика. Это эффективно, но не очень читабельно. Чтобы определить, кто же является лучшим другом, нам придётся соединить таблицу саму с собой. Возьмем таблицу students и сделаем соединение (join) снова с таблицей students, в качестве ключей для связи у нас будет id студента и id лучшего друга (best_friend_id). Определим псевдонимы таблиц x и y для улучшения понимания кода.

        SELECT
    x.name,
    y.name AS best_friend
FROM
    students AS x
INNER JOIN
    students AS y
    ON y.id = x.best_friend_id;

/*
 name     | best_friend
 -------- | -----------
 Adam     | Evan
 Betty    | Dina
 Caroline | Betty
 Dina     | Betty
 Evan     | Adam
*/
    

Оконные функции (Window functions)

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

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

        SELECT
    s.name,
    g.score,
    AVG(g.score) OVER (
        PARTITION BY s.name
    )
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id;

/*
 name  | score | avg
 ------| ----- | ----------
 Adam  |    82 | 80.8000...
 Adam  |    82 | 80.8000...
 Adam  |    80 | 80.8000...
 Adam  |    75 | 80.8000...
 Adam  |    85 | 80.8000...
 Betty |    74 | 70.4000...
 Betty |    75 | 70.4000...
 ...   |   ... |        ...
*/
    

Для агрегирующих операторов AVG, MIN, или MAX, каждая строка в группировке PARTITION BY будет отображать одинаковое значение. Возможно, для определенных видов анализа это и понадобится, но на самом деле, сила оконных функций в другом.

Возможность ранжирования данных более полезный случай. Попробуем сделать это с оценками наших учеников. Чтобы ранжировать оценки всех учеников мы будем использовать оператор RANK() OVER, в который мы передадим нужный нам столбец.

        SELECT
    s.name,
    g.score,
    RANK() OVER (
        ORDER BY g.score
    )
FROM
    grades AS g
INNER JOIN
    students AS s
    ON s.id = g.student_id;

/*
 name  | score | rank
 ----- | ----- | ----
 Betty |    64 |    1
 Dina  |    64 |    1
 Evan  |    67 |    3
 ...   |   ... |  ...
*/
    

Добавим всего одну строку в наш запрос, а именно PARTITION BY s.name к оператору OVER. В итоге мы получили ранжирование в рамках одного студента.

        SELECT
    s.name,
    g.score,
    RANK() OVER (
        PARTITION BY s.name  -- ranks by student
        ORDER BY g.score
    )
FROM
    grades AS g
INNER JOIN
    students AS s
    ON s.id = g.student_id;

/*
 name     | score | rank
 -------- | ----- | ----
 Adam     |    75 |    1
 Adam     |    80 |    2
 Adam     |    82 |    3
 Adam     |    82 |    3
 Adam     |    85 |    5
 Betty    |    64 |    1
 Betty    |    69 |    2
 Betty    |    70 |    3
 Betty    |    74 |    4
 Betty    |    75 |    5
 Caroline |    90 |    1
 Caroline |    92 |    2
 ...      |   ... |  ...
*/
    

У оконных функций есть еще несколько интересных возможностей. Например, функции распределения cumulative distribution, функции dense и percent ranks. dense_rank – функция возвращает ранг каждой строки, но в отличие от функции RANK, она для одинаковых значений возвращает ранг, не пропуская следующий. С функциями lag и lead вы можете более подробно ознакомиться по ссылке.


Оператор WITH

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

Например, мы хотим сравнить оценку ученика (колонка grades) с его средним баллом. Сделать это в одном запросе вроде бы несложно, нам сначала нужно вычислить средний балл с помощью оператора GROUP BY, а затем написать что-то вроде g.score > avg. Начнем с агрегации GROUP BY.

        SELECT
    s.name,
    ROUND(AVG(g.score),1) AS avg
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
GROUP BY
    s.name;

/*
 name     | avg
 -------- | ----
 Dina     | 79.6
 Evan     | 83.4
 Betty    | 70.4
 Caroline | 94.6
 Adam     | 80.8
*/
    

Это было просто. Но как же нам сравнить каждую оценку со средним баллом? Взгляните на примеры запросов ниже, все они выдадут ошибку.

        SELECT
    s.name,
    ROUND(AVG(g.score),1) AS avg,
    g.score > avg
    ...
    -- ERROR: column "avg" does not exist

SELECT
    s.name,
    ROUND(AVG(g.score),1) AS avg,
    g.score > ROUND(AVG(g.score),1)
    ...
    -- ERROR: column "g.score" must appear in the GROUP BY
    -- clause or be used in an aggregate function
    

Мы можем дважды использовать оконные функции, но это выглядит как минимум не очень читабельно.

        SELECT
    s.name,
    AVG(g.score) OVER (PARTITION BY s.name),
    g.score > AVG(g.score) OVER (PARTITION BY s.name)
    ...
    

Чтобы наш запрос был читабельным и масштабируемым, будем использовать WITH. Разделим наш запрос на два подзапроса: первый будет считать средний балл, а второй добавит записи с индивидуальными оценками grades.

        WITH averages AS (
    SELECT
        s.id,
        ROUND(AVG(g.score),1) AS avg_score
    FROM
        students AS s
    INNER JOIN
        grades AS g
        ON s.id = g.student_id
    GROUP BY
        s.id
)
SELECT
    s.name,
    g.score,
    a.avg_score,
    g.score > a.avg_score AS above_avg
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
INNER JOIN
    averages AS a
    ON a.id = s.id;

/*
 name  | score | avg_score | above_avg
 ----- | ----- | --------- | ---------
 Adam  |    82 |      80.8 | true
 Adam  |    82 |      80.8 | true
 Adam  |    80 |      80.8 | false
 Adam  |    75 |      80.8 | false
 Adam  |    85 |      80.8 | true
 Betty |    74 |      70.4 | true
 Betty |    75 |      70.4 | true
*/
    

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

Запросы могут быть чудовищно длинные. Например в компании Meta (Facebook), мне встречался запрос, содержащий в себе 1000 строк и вызывал сразу 25 таблиц. Этот запрос был бы совершенно нечитаемым без применения оператора WITH, который разграничивает отдельные участки кода и присваивает им псевдонимы.

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


Давайте разберем еще один вариант. Предположим, в нашей школе уволили директора, и система оценок претерпела изменения. У нас появился такой показатель как средневзвешенный балл, который вычисляется как оценка (grades) умноженная на коэффициент сложности (weight). Теперь для сдачи экзамена необходимо иметь: средневзвешенный балл по всем предметам не ниже 85% или этот же балл не ниже 70% по собственному проекту. Объединение данной логики в один блок CASE WHEN достаточно сложно, но, если мы разделим запрос с помощью WITH всё станет гораздо проще.

Давайте начнем с того, что выявим учеников, у которых средневзвешенный балл больше 85%.

        SELECT
    s.name
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
GROUP BY
    s.name
HAVING
    SUM(g.score * a.weight) > 85;

/*
 name
 --------
 Caroline
*/
    

Прекрасно. Теперь узнаем у кого из учеников больше 70% по персональному проекту.

        SELECT
    s.name
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
WHERE
    a.name = 'biography'
    AND g.score > 70

/*
 name
 --------
 Adam
 Caroline
 Evan
*/
    

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

        SELECT DISTINCT
    name
FROM
    students
WHERE
    name IN <people_who_passed_final>
    OR name IN <people_who_passed_project>;
    

Это очень просто с использованием WITH. Мы присвоим псевдонимы нашим запросам weighted_pass и project_pass, а потом объединим их.

        WITH weighted_pass AS (
    SELECT
        s.name
    FROM
        students AS s
    INNER JOIN
        grades AS g
        ON s.id = g.student_id
    INNER JOIN
        assignments AS a
        ON a.id = g.assignment_id
    GROUP BY
        s.name
    HAVING
        SUM(g.score * a.weight) > 85
),
project_pass AS (
    SELECT
        s.name
    FROM
        students AS s
    INNER JOIN
        grades AS g
        ON s.id = g.student_id
    INNER JOIN
        assignments AS a
        ON a.id = g.assignment_id
    WHERE
        a.name = 'biography'
        AND g.score > 70
)
SELECT DISTINCT
    name
FROM
    students
WHERE
    name IN (SELECT name FROM weighted_pass)
    OR name IN (SELECT name FROM project_pass);

/*
 name
 --------
 Evan
 Caroline
 Adam
*/
    

Смотрим вглубь – EXPLAIN

В завершении данной статьи, давайте разберем еще одну важную тему. Чем больше мы изучаем SQL, тем больше способов построения сложных запросов нам известно. Когда лучше использовать EXCEPT, а когда NOT IN? Нужно ли нам использовать несколько JOIN для объединения таблиц, либо лучше применить WITH и UNION ALL?

И в итоге, как нам понять, что один запрос более эффективен, чем другой?

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

        EXPLAIN
SELECT
    s.id AS student_id,
    g.score
FROM
    students AS s
LEFT JOIN
    grades AS g
    ON s.id = g.student_id
WHERE
    g.score > 90
ORDER BY
    g.score DESC;
    
/*
 QUERY PLAN
 ----------
 Sort (cost=80.34..81.88 rows=617 width=8)
 [...] Sort Key: g.score DESC
 [...] -> Hash Join (cost=16.98..51.74 rows=617 width=8)
 [...] Hash Cond: (g.student_id = s.id)
 [...] -> Seq Scan on grades g (cost=0.00..33.13 rows=617 width=8)
 [...] Filter: (score > 90)
 [...] -> Hash (cost=13.10..13.10 rows=310 width=4)
 [...] -> Seq Scan on students s (cost=0.00..13.20 rows=320 width=4)
*/
    

Можно пойти еще дальше и использовать оператор EXPLAIN ANALYZE, который отобразит еще более детальную информацию (например, время выполнения каждой части запроса и используемую память).

        EXPLAIN ANALYZE
SELECT
    s.id AS student_id,
    g.score
FROM
    students AS s
LEFT JOIN
    grades AS g
    ON s.id = g.student_id
WHERE
    g.score > 90
ORDER BY
    g.score DESC;
    
/*
 QUERY PLAN
 ----------
 Sort (cost=80.34..81.88 rows=617 width=8)
   (actual tiem=0.169..0.171 rows=6 loops=1)
 [...] Sort Key: g.score DESC
 [...] Sort Method: quicksort Memory: 25kB
 [...] -> Hash Join (cost=16.98..51.74 rows=617 width=8)
   (actual time=0.115..0.145 rows=6 loops=1)
 [...] Hash Cond: (g.student_id = s.id)
 [...] -> Seq Scan on grades g (cost=0.00..33.13 rows=617 width=8)
   (actual time=0.045..0.052 rows=6 loops=1)
 [...] Filter: (score > 90)
   Rows removed by Filter: 19
 [...] -> Hash (cost=13.10..13.10 rows=310 width=4)
   (actual time=0.059..0.060 rows=5 loops=1)
 [...] Buckets: 1024 Batches: 1 Memory Usage: 9kB
 [...] -> Seq Scan on students s (cost=0.00..13.10 rows=310 width=4)
    (actual time=0.022..0.027 rows=5 loops=1)
 Planning Time: 0.379 ms
 Execution Time: 0.227 ms
*/
    

Например в результате выше можно увидеть, что Postgres последовательно сканирует (Seq Scan) таблицы grades и students, потому что они не индексированы. Иначе говоря, Postgres не знает что строка внизу таблицы имеет id меньше или больше чем строка наверху таблицы. В нашем случае, это не имеет особого значения ввиду размеров нашей базы данных, но если бы мы работали с миллионами строк данных, то нам определенно потребовалось бы найти и устранить такие узкие места в производительности.

***

Данная статья была обзором тех навыков в SQL, которые вам точно понадобятся сразу после получения базовых знаний. Мы начали с установки Postgres и pgAdmin, тем самым получив возможность экспериментировать с базой данных на своём компьютере.

Затем мы узнали про полезные синтаксические конструкции, которые позволили нам составлять более сложные запросы. Мы начали с фильтрации данных и узнали, чем отличается WHERE от HAVING. Далее мы познакомились с условными конструкциями, узнали как сегментировать данные с помощью CASE WHEN и обрабатывать null, используя COALESCE. Мы перешли от горизонтального к вертикальному объединению таблиц с помощью операций над множествами, разобрав как UNION, UNION ALL, INTERSECT, и EXCEPT работают с повторяющимися строками в таблицах. И в конце второй части статьи узнали как создавать массивы и работать с ними.

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

И конечно же это далеко не всё. Есть еще много функций, про которые стоит знать, например CAST (для приведения одного типа данных к другому, например float к integer), или функции, определяемые пользователем (user-defined functions), которые можно использовать для дальнейшего упрощения кода. Всё это безусловно очень полезно, но я бы рекомендовал всегда думать об оптимизации запросов. Даже в FAANG компаниях с практически неограниченными вычислительными ресурсами, запросы могут завершаться с ошибкой в том случае, если они используют больше памяти, чем может предоставить сервер. Выбор правильного подхода к написанию запроса, упрощает работу с потоком данных, что снижает шанс получить гневный звонок в полночь.

Дополнение

1. CASCADE

Что же на самом деле происходит когда мы используем CASCADE при удалении таблицы?

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

        SELECT
    s.name,
    s.classroom_id,
    c.teacher
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | classroom_id | teacher
 -------- | ------------ | -------
 Adam     |            1 | Mary
 Betty    |            1 | Mary
 Caroline |            2 | Jonah
 Dina     |       [null] | [null]
 Evan     |       [null] | [null]
*/

DROP TABLE classrooms CASCADE;

/*
DROP TABLE
Query returned successfully in 71 msec.
*/

SELECT * FROM students;

/*
 id | name     | classroom_id | best_friend_id
 -- | -------- | ------------ | --------------
  1 | Adam     |            1 |              5
  2 | Betty    |            1 |              4
  3 | Caroline |            2 |              2
  4 | Dina     |       [null] |              2
  5 | Evan     |       [null] |              1
*/
    

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

        CREATE TABLE classrooms (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    teacher VARCHAR(100)
);

/*
CREATE TABLE
Query returned successfully in 139 msec.
*/

INSERT INTO classrooms
    (teacher)
VALUES
    ('Dr. Random'),
    ('Alien Banana');

/*
INSERT 0 2
Query returned successfully in 99 msec.
*/

SELECT
    s.name,
    s.classroom_id,
    c.teacher
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | classroom_id | teacher
 -------- | ------------ | -----------
 Adam     |            1 | Dr. Random
 Betty    |            1 | Dr. Random
 Caroline |            2 | Alien Banana
 Dina     |       [null] | [null]
 Evan     |       [null] | [null]
*/
    

Так произошло, потому что CASCADE удалил внешний ключ в таблице students. Мы можем это проверить. Изменим значение classroom_id в таблице students, поскольку оно больше не является внешним ключом и не связано с таблицей classrooms, запрос выполнится успешно. Но если мы попробуем сделать то же самое с полем student_id в таблице grades, мы получим ошибку, потому что student_idэто внешний ключ.

        UPDATE students
SET classroom_id = 10
WHERE id = 1;

/*
UPDATE 1
Query returned successfully in 37 msec.
*/

UPDATE grades
SET student_id = 10
WHERE id = 1;
/*
ERROR:  insert or update on table "grades" violates foreign key
    constraint "fk_students"
DETAIL:  Key (student_id)=(10) is not present in table
    "students".
SQL state: 23503
*/
    

И еще одно замечание по поводу CASCADE. Если мы используем ON DELETE CASCADE при создании внешнего ключа в таблице students, а потом удаляем запись в classrooms, у нас также исчезнут связанные строки в таблице students. Такой способ может быть полезен при работе с личными данными пользователей, например мы хотим удалить все данные о клиенте или о сотруднике.

        DROP TABLE IF EXISTS students CASCADE;

CREATE TABLE students (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100),
    classroom_id INT,
    CONSTRAINT fk_classrooms
        FOREIGN KEY(classroom_id)
        REFERENCES classrooms(id) ON DELETE CASCADE
);

INSERT INTO students
    (name, classroom_id)
 VALUES
    ('Adam', 1),
    ('Betty', 1),
    ('Caroline', 2);

SELECT * FROM students;
/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
  2 | Betty    |            1
  3 | Caroline |            2
*/

DELETE FROM classrooms
WHERE id = 1;

SELECT * FROM students;
/*
 id | name     | classroom_id
 -- | -------- | ------------
  3 | Caroline |            2
*/
    

2. Сохранность данных

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

3. Длина строк

Мы установили, что тип данных в колонке учитель – это строка с максимальной длиной в 100 символов, так как не предполагаем, что будут значения длиннее. Но на самом ли деле мы экономим таким образом место на диске?

С технической точки зрения, в Postgres не имеет значения, какую длину строки мы определили (10, 100 или 500 символов). Указание длины строки – это скорее хороший способ коммуникации между разработчиками, потому что таким образом вы указываете, что вы ожидаете получить в этом поле.

Но, например, в MySQL длина строк имеет значение: временные таблицы и таблицы MEMORY будут хранить строки одинаковой длины и дополнять их до максимального значения. Это означает, что, если вы указали тип поля VARCHAR(1000), то все данные в этой колонке будут дополняться до указанной длины, даже если строка состоит из 100 символов.

4. Оператор If

Если вам интересно, ниже пример того, как в Postgres выглядит синтаксис с оператором if.

        DO $$

BEGIN
    IF
        (SELECT COUNT(*) FROM grades) >
        (SELECT COUNT(*) FROM students)
    THEN
        RAISE NOTICE 'More grades than students.';
    ELSE
        RAISE NOTICE 'Equal or more students than grades.';
    END IF;

END $$;

/*
NOTICE: More grades than students.
*/
    

5. Индексы

При увеличении базы данных, установка индексов становится критически важной. Для примера, давайте создадим индекс на поле с оценками в таблице grades:

        CREATE INDEX
    score_index
ON
    grades(score);
    

Тем не менее, если сейчас мы применим оператор EXPLAIN ANALYZE, мы увидим, что Postgres по-прежнему выполняет последовательное сканирование. Так происходит потому, что Postgres уже достаточно неплохо оптимизирован. Если количество записей в таблице невелико, быстрее выполнить последовательное сканирование, нежели использовать индексы. То есть Postgres сам выбрал наиболее быстрый путь.

***

Материалы по теме

16
Фев
2022

🐘 Руководство по SQL для начинающих. Часть 2: фильтрация данных, запрос внутри запроса, работа с массивами

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

Фильтрация данных с помощью операторов WHERE и HAVING

Скорее всего, вы уже знакомы с оператором для фильтрации данных WHERE и, возможно, слышали об операторе HAVING. Но чем же конкретно они отличаются? Давайте сделаем несколько запросов к таблице успеваемости (grades), чтобы в этом разобраться. Воспользуемся оператором ORDER BY RANDOM(), чтобы выбрать произвольные данные, затем LIMIT 5, чтобы запрос выдал только 5 записей. Упорядочивать все строки только ради примера достаточно неэффективно, но если таблица небольшая – это допустимо.

        SELECT
    *
FROM
    grades
ORDER BY
    RANDOM()
LIMIT
    5;

/*
 id | assignment_id | score | student_id
 -- | ------------- | ----- | ----------
 14 |             4 |   100 |          3
 22 |             2 |    91 |          5
 23 |             3 |    85 |          5
 16 |             1 |    81 |          4
  9 |             4 |    64 |          2
*/
    

Каждая строка отображает оценку ученика по определенному предмету. Давайте узнаем средний балл каждого ученика. Для этого нам понадобится:

  1. GROUP BYдля группировки по ученикам.
  2. AVG(score)для вычисления среднего значения.
  3. ROUNDдля округления полученных значений.
        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
GROUP BY
    student_id
ORDER BY
    student_id;

/*
 student_id | avg_score
 ---------- | ---------
          1 |      80.8
          2 |      70.4
          3 |      94.6
          4 |      79.6
          5 |      83.4
*/
    

Теперь давайте представим, что из предыдущего запроса, нам нужны только те строки, где средний балл (avg_score) больше, чем 50 и меньше, чем 75. То есть запрос должен отобразить только ученика со student_id=2. Что произойдет при использовании оператора WHERE?

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
WHERE
    score BETWEEN 50 AND 75
GROUP BY
    student_id
ORDER BY
    student_id;
    
/*
 student_id | avg_score
 ---------- | ---------
          1 |      75.0
          2 |      70.4
          3 |      64.0
          4 |      67.0
*/
    

Результаты выглядят совершенно неверными. Ученик с id=5 не отображается в результате запроса, а ученики с id 1, 3 и 4 на месте. К тому же их средний балл (avg_score) изменился. А что если бы это были данные какого-нибудь важного отчета? Есть вероятность как минимум растеряться.

Давайте вспомним, что оператор HAVING является агрегирующей функцией. Такие функции обрабатывают набор строк для подсчета и возвращения одного значения.

Теперь посмотрим, что изменится при использовании оператора HAVING.

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
GROUP BY
    student_id
HAVING
    ROUND(AVG(score),1) BETWEEN 50 AND 75
ORDER BY
    student_id;
    
/*
 student_id | avg_score
 ---------- | ---------
          2 |      70.4
*/
    

Эти два запроса выдают совершенно разные результаты, потому что операторы WHERE и HAVING фильтруют данные на разных этапах агрегации. WHERE обрабатывает данные перед агрегацией, а HAVING после, и фильтрует уже результаты.


Результат агрегации в запросе с оператором WHERE изменился, потому что мы изменили входные данные для подсчета среднего балла каждого ученика. У ученика с id=5 нет оценок в диапазоне с 50 по 75, поэтому он был исключен из запроса. В то время как оператор HAVING отфильтровал результаты уже после подсчета.

Когда вы освоите применение операторов WHERE и HAVING по отдельности, можете попробовать использовать их вместе. Например: мы хотим отобразить учеников, чей средний балл только по домашним работам не меньше 50 и не больше 75 баллов.

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades AS g
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
WHERE
    a.category = 'homework'
GROUP BY
    student_id
HAVING
    ROUND(AVG(score),1) BETWEEN 50 AND 75;

/*
 student_id | avg_score
 ---------- | ---------
          2 |      74.5
*/
    
Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

Условные операторы: CASE WHEN и COALESCE

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

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

        SELECT
    score,
    CASE
        WHEN score < 60 THEN 'F'
        WHEN score < 70 THEN 'D'
        WHEN score < 80 THEN 'C'
        WHEN score < 90 THEN 'B'
        ELSE 'A'
    END AS letter
FROM
    grades;

/*
 score | letter
 ----- | ------
    82 | B
    82 | B
    80 | B
    75 | C
   ... | ...
*/
    

Логика, которую мы передаем в блок CASE WHEN может охватывать сразу несколько колонок. Давайте выведем в результат запроса колонку instructor, за основу возьмем таблицу students. Например, если ученику назначен учитель, то в эту колонку запишем имя учителя, если нет, то имя ученика.

        SELECT
    name,
    teacher,
    CASE
        WHEN teacher IS NOT NULL THEN teacher
        ELSE name
    END AS instructor
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | teacher | instructor
 -------- | ------- | ----------
 Adam     | Mary    | Mary
 Betty    | Mary    | Mary
 Caroline | Jonah   | Jonah
 Dina     | [null]  | Dina
 Evan     | [null]  | Evan
*/
    

Если мы работаем с данными, которые могут не иметь значения, то есть являются null, оператор COALESCE – лучший выбор. COALESCE – проверка на null, то есть при передаче в него параметров null, он вернет первое значение, не являющееся null. Перепишем предыдущий запрос.

        SELECT
    name,
    teacher,
    COALESCE(teacher, name)
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | teacher | instructor
 -------- | ------- | ----------
 Adam     | Mary    | Mary
 Betty    | Mary    | Mary
 Caroline | Jonah   | Jonah
 Dina     | [null]  | Dina
 Evan     | [null]  | Evan
*/
    

Четвертая строка в этом запросе, делает то же самое, что и строки с четвертой по седьмую в предыдущем. То есть, если значение в колонке teacher не null, возвращаем имя учителя, если null, возвращаем имя ученика.

COALESCE будет пропускать все переданные ему аргументы, пока не дойдет до элемента со значением не null. Если же все аргументы имеют значение null, то в возвращаемом значении тоже будет null.

        SELECT
    COALESCE(NULL, NULL, NULL, 4);
/*
 coalesce
 --------
        4
 */

 SELECT
    COALESCE(NULL);
/*
 coalesce
 --------
 [null]
 */
    

И наконец, в Postgres есть еще оператор условия IF , однако он используется для управления несколькими запросами сразу, но не одним. Даже если вы data scientist или data engineer маловероятно, что вы будете им пользоваться. Если вы хотите освоить работу с данным оператором, попробуйте воспользоваться платформой Airflow.

Операции над множествами: UNION, INTERSECT, и EXCEPT

При использовании оператора JOIN мы к одной таблице горизонтально присоединяем другую таблицу. В запросе ниже видно, мы получили данные об ученике с именем Adam из трех таблиц students, grades и assignments. В качестве ключа для связки таблиц использовали поле id.

        SELECT
    s.name,
    g.score,
    a.category
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
WHERE
    s.name = 'Adam';

/*
 name | score | category
 ---- | ----- | --------
 Adam |    82 | homework
 Adam |    82 | homework
 Adam |    80 | exam
 Adam |    75 | project
 Adam |    85 | exam
*/
    

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

Давайте представим, что с системой успеваемости в нашей школе произошли очень странные изменения. Теперь для определения того, сдал ученик экзамен или нет используются не оценки. Ученик сдаст экзамен если: его имя начинается с буквы А или В или же в его имени только 5 букв. Мы можем написать 2 запроса, которые покажут нам всех учеников, подходящих под каждое условие, а затем используем оператор UNION ALL, чтобы объединить результаты в одну таблицу.

        SELECT
    *
FROM (
    SELECT
        name,
        'Name starts with A/B' as reason
    FROM
        students
    WHERE
        LEFT(name,1) IN ('A', 'B')
) AS x

UNION ALL

SELECT
    *
FROM (
    SELECT
        name,
        'Name is 5 letters long' AS reason
    FROM
        students
    WHERE
        LENGTH(name) = 5
) AS y;

/*
 name  | reason
 ----  | ------
 Adam  | Name starts with A/B
 Betty | Name starts with A/B
 Betty | Name is 5 letters long
*/
    

Здесь, на строках с 4 по 11 и с 18 по 24 мы впервые увидели вложенные запросы (или подзапросы). Обратите внимание, что таким запросам необходимо присваивать имена (в нашем случае x и y) чтобы работал оператор UNION ALL.

Также вы могли обратить внимание, что мы использовали оператор UNION ALL, а не просто UNION. Дело в том, что UNION ALL возвращает все строки, в то время как UNION убирает дубли. То есть если в двух подзапросах будут две одинаковые строки, UNION покажет в результатах только одну. Именно для этого запроса ничего не изменится, потому что ученик Betty удовлетворяет обоим условиям, а вот если не выводить колонку reason в результате мы увидим имя Betty только один раз.

        SELECT
    *
FROM (
    SELECT
        name  -- <- нет колонки `reason`
    FROM
        students
    WHERE
        LEFT(name,1) IN ('A', 'B')
) AS x

UNION  -- <- UNION, а не UNION ALL

SELECT
    *
FROM (
    SELECT
        name  -- <- нет колонки `reason`
    FROM
        students
    WHERE
        LENGTH(name) = 5
) AS y;

/*
 name
 -----
 Adam
 Betty   <- Только одна запись, потому что использовали оператор UNION
*/
    

При выборе UNION или UNION ALL подумайте, нужны ли вам повторяющиеся значения. При написании сложных запросов я предпочитаю использовать UNION ALL, чтобы убедиться, что в результирующей таблице то количество строк, которое нужно, и, если есть дубли, значит, где-то раньше я, скорее, всего ошибся с соединениями таблиц (JOIN). Чем раньше вы будете фильтровать данные в запросе, тем эффективнее он будет работать.


Операторы UNION и UNION ALL возвращают все строки из подзапросов (в случае с UNION без дублей). Два других оператора INTERSECT и EXCEPT, позволяют нам вернуть только те строки, которые соответствуют определенным критериям. INTERSECT (пересечение) вернет только те строки, которые присутствуют в обоих запросах, а EXCEPT (исключение) вернет строки из подзапроса А, которых нет в подзапросе Б.


В следующем запросе попробуем работу оператора INTERSECT, который отобразит строки, присутствующие в обоих подзапросах (id 2 и id 3). В отличие от UNION нам не нужно присваивать имена вложенным запросам.

        SELECT
    *
FROM
    students
WHERE
    id IN (1,2,3)

INTERSECT

SELECT
    *
FROM
    students
WHERE
    id IN (2,3,4);

/*
 id | name     | classroom_id
 -- | -------- | ------------
  2 | Betty    |            1
  3 | Caroline |            2
*/
    

А теперь изменим запрос и применим оператор EXCEPT, который выведет строки из подзапроса А, которых нет в подзапросе Б (в нашем случае строка с id=1).

        SELECT
    *
FROM
    students
WHERE
    id IN (1,2,3)

EXCEPT

SELECT
    *
FROM
    students
WHERE
    id IN (2,3,4);

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
*/
    

Операции над множествами дают нам возможность комбинировать результаты запросов (UNION), просматривать пересекающиеся записи (INTERSECT) и извлекать отличающиеся данные (EXCEPT). Больше не нужно сравнивать результаты запросов вручную.

Функции для работы с массивами

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

Одна из полезных функций ARRAY_AGG позволяет преобразовать строки в массив. В следующем запросе мы написали ARRAY_AGG(score) и использовали группировку по имени (GROUP BY name) чтобы отобразить массив, включающий в себя все оценки каждого ученика.

        SELECT
    name,
    ARRAY_AGG(score) AS scores
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
GROUP BY
    name
ORDER BY
    name;
    
/*
 name     | scores
 -------- | ------
 Adam     | {82,82,80,75,85}
 Betty    | {74,75,70,64,69}
 Caroline | {96,92,90,100,95}
 Dina     | {81,80,84,64,89}
 Evan     | {67,91,85,93,81}
*/
    

Также в нашем арсенале есть следующие функции:

  1. CARDINALITY – выводит количество элементов в массиве.
  2. ARRAY_REPLACE – заменяет указанные элементы.
  3. ARRAY_REMOVE – удаляет указанные элемент.
        SELECT
    name,
    ARRAY_AGG(score) AS scores,
    CARDINALITY(ARRAY_AGG(score)) AS length,
    ARRAY_REPLACE(ARRAY_AGG(score), 82, NULL) AS replaced
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
GROUP BY
    name
ORDER BY
    name;

/*
 name     | scores            | length | replaced
 -------- | ----------------- | ------ | --------------------
 Adam     | {82,82,80,75,85}  |      5 | {NULL,NULL,80,75,85}
 Betty    | {74,75,70,64,69}  |      5 | {74,75,70,64,69}
 Caroline | {96,92,90,100,95} |      5 | {96,92,90,100,95}
 Dina     | {81,80,84,64,89}  |      5 | {81,80,84,64,89}
 Evan     | {67,91,85,93,81}  |      5 | {67,91,85,93,81}
*/
    

UNNESTеще одна функция, которая может вам пригодиться. Её действие противоположно функции ARRAY_AGG, то есть она позволяет разделить массив на отдельные строки.

        SELECT
    'name' AS name,
    UNNEST(ARRAY[1, 2, 3]);
    
/*
 name  | unnest
 ----  | ------
 name  |      1
 name  |      2
 name  |      3
*/
    

В этой части статьи мы с вами разобрали:

  • фильтрацию данных с помощью операторов WHERE и HAVING;
  • условные операторы CASE WHEN и COALESCE;
  • операции над множествами;
  • функции для работы с массивами.

В финальной части статьи разберем:

  • присоединение таблицы к самой себе (self join);
  • оконные функции (window function);
  • посмотрим вглубь запросов (explain).
***

Материалы по теме

16
Фев
2022

🐘 Руководство по SQL для начинающих. Часть 2: фильтрация данных, запрос внутри запроса, работа с массивами

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

Фильтрация данных с помощью операторов WHERE и HAVING

Скорее всего, вы уже знакомы с оператором для фильтрации данных WHERE и, возможно, слышали об операторе HAVING. Но чем же конкретно они отличаются? Давайте сделаем несколько запросов к таблице успеваемости (grades), чтобы в этом разобраться. Воспользуемся оператором ORDER BY RANDOM(), чтобы выбрать произвольные данные, затем LIMIT 5, чтобы запрос выдал только 5 записей. Упорядочивать все строки только ради примера достаточно неэффективно, но если таблица небольшая – это допустимо.

        SELECT
    *
FROM
    grades
ORDER BY
    RANDOM()
LIMIT
    5;

/*
 id | assignment_id | score | student_id
 -- | ------------- | ----- | ----------
 14 |             4 |   100 |          3
 22 |             2 |    91 |          5
 23 |             3 |    85 |          5
 16 |             1 |    81 |          4
  9 |             4 |    64 |          2
*/
    

Каждая строка отображает оценку ученика по определенному предмету. Давайте узнаем средний балл каждого ученика. Для этого нам понадобится:

  1. GROUP BYдля группировки по ученикам.
  2. AVG(score)для вычисления среднего значения.
  3. ROUNDдля округления полученных значений.
        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
GROUP BY
    student_id
ORDER BY
    student_id;

/*
 student_id | avg_score
 ---------- | ---------
          1 |      80.8
          2 |      70.4
          3 |      94.6
          4 |      79.6
          5 |      83.4
*/
    

Теперь давайте представим, что из предыдущего запроса, нам нужны только те строки, где средний балл (avg_score) больше, чем 50 и меньше, чем 75. То есть запрос должен отобразить только ученика со student_id=2. Что произойдет при использовании оператора WHERE?

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
WHERE
    score BETWEEN 50 AND 75
GROUP BY
    student_id
ORDER BY
    student_id;
    
/*
 student_id | avg_score
 ---------- | ---------
          1 |      75.0
          2 |      70.4
          3 |      64.0
          4 |      67.0
*/
    

Результаты выглядят совершенно неверными. Ученик с id=5 не отображается в результате запроса, а ученики с id 1, 3 и 4 на месте. К тому же их средний балл (avg_score) изменился. А что если бы это были данные какого-нибудь важного отчета? Есть вероятность как минимум растеряться.

Давайте вспомним, что оператор HAVING является агрегирующей функцией. Такие функции обрабатывают набор строк для подсчета и возвращения одного значения.

Теперь посмотрим, что изменится при использовании оператора HAVING.

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
GROUP BY
    student_id
HAVING
    ROUND(AVG(score),1) BETWEEN 50 AND 75
ORDER BY
    student_id;
    
/*
 student_id | avg_score
 ---------- | ---------
          2 |      70.4
*/
    

Эти два запроса выдают совершенно разные результаты, потому что операторы WHERE и HAVING фильтруют данные на разных этапах агрегации. WHERE обрабатывает данные перед агрегацией, а HAVING после, и фильтрует уже результаты.


Результат агрегации в запросе с оператором WHERE изменился, потому что мы изменили входные данные для подсчета среднего балла каждого ученика. У ученика с id=5 нет оценок в диапазоне с 50 по 75, поэтому он был исключен из запроса. В то время как оператор HAVING отфильтровал результаты уже после подсчета.

Когда вы освоите применение операторов WHERE и HAVING по отдельности, можете попробовать использовать их вместе. Например: мы хотим отобразить учеников, чей средний балл только по домашним работам не меньше 50 и не больше 75 баллов.

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades AS g
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
WHERE
    a.category = 'homework'
GROUP BY
    student_id
HAVING
    ROUND(AVG(score),1) BETWEEN 50 AND 75;

/*
 student_id | avg_score
 ---------- | ---------
          2 |      74.5
*/
    
Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

Условные операторы: CASE WHEN и COALESCE

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

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

        SELECT
    score,
    CASE
        WHEN score < 60 THEN 'F'
        WHEN score < 70 THEN 'D'
        WHEN score < 80 THEN 'C'
        WHEN score < 90 THEN 'B'
        ELSE 'A'
    END AS letter
FROM
    grades;

/*
 score | letter
 ----- | ------
    82 | B
    82 | B
    80 | B
    75 | C
   ... | ...
*/
    

Логика, которую мы передаем в блок CASE WHEN может охватывать сразу несколько колонок. Давайте выведем в результат запроса колонку instructor, за основу возьмем таблицу students. Например, если ученику назначен учитель, то в эту колонку запишем имя учителя, если нет, то имя ученика.

        SELECT
    name,
    teacher,
    CASE
        WHEN teacher IS NOT NULL THEN teacher
        ELSE name
    END AS instructor
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | teacher | instructor
 -------- | ------- | ----------
 Adam     | Mary    | Mary
 Betty    | Mary    | Mary
 Caroline | Jonah   | Jonah
 Dina     | [null]  | Dina
 Evan     | [null]  | Evan
*/
    

Если мы работаем с данными, которые могут не иметь значения, то есть являются null, оператор COALESCE – лучший выбор. COALESCE – проверка на null, то есть при передаче в него параметров null, он вернет первое значение, не являющееся null. Перепишем предыдущий запрос.

        SELECT
    name,
    teacher,
    COALESCE(teacher, name)
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | teacher | instructor
 -------- | ------- | ----------
 Adam     | Mary    | Mary
 Betty    | Mary    | Mary
 Caroline | Jonah   | Jonah
 Dina     | [null]  | Dina
 Evan     | [null]  | Evan
*/
    

Четвертая строка в этом запросе, делает то же самое, что и строки с четвертой по седьмую в предыдущем. То есть, если значение в колонке teacher не null, возвращаем имя учителя, если null, возвращаем имя ученика.

COALESCE будет пропускать все переданные ему аргументы, пока не дойдет до элемента со значением не null. Если же все аргументы имеют значение null, то в возвращаемом значении тоже будет null.

        SELECT
    COALESCE(NULL, NULL, NULL, 4);
/*
 coalesce
 --------
        4
 */

 SELECT
    COALESCE(NULL);
/*
 coalesce
 --------
 [null]
 */
    

И наконец, в Postgres есть еще оператор условия IF , однако он используется для управления несколькими запросами сразу, но не одним. Даже если вы data scientist или data engineer маловероятно, что вы будете им пользоваться. Если вы хотите освоить работу с данным оператором, попробуйте воспользоваться платформой Airflow.

Операции над множествами: UNION, INTERSECT, и EXCEPT

При использовании оператора JOIN мы к одной таблице горизонтально присоединяем другую таблицу. В запросе ниже видно, мы получили данные об ученике с именем Adam из трех таблиц students, grades и assignments. В качестве ключа для связки таблиц использовали поле id.

        SELECT
    s.name,
    g.score,
    a.category
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
WHERE
    s.name = 'Adam';

/*
 name | score | category
 ---- | ----- | --------
 Adam |    82 | homework
 Adam |    82 | homework
 Adam |    80 | exam
 Adam |    75 | project
 Adam |    85 | exam
*/
    

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

Давайте представим, что с системой успеваемости в нашей школе произошли очень странные изменения. Теперь для определения того, сдал ученик экзамен или нет используются не оценки. Ученик сдаст экзамен если: его имя начинается с буквы А или В или же в его имени только 5 букв. Мы можем написать 2 запроса, которые покажут нам всех учеников, подходящих под каждое условие, а затем используем оператор UNION ALL, чтобы объединить результаты в одну таблицу.

        SELECT
    *
FROM (
    SELECT
        name,
        'Name starts with A/B' as reason
    FROM
        students
    WHERE
        LEFT(name,1) IN ('A', 'B')
) AS x

UNION ALL

SELECT
    *
FROM (
    SELECT
        name,
        'Name is 5 letters long' AS reason
    FROM
        students
    WHERE
        LENGTH(name) = 5
) AS y;

/*
 name  | reason
 ----  | ------
 Adam  | Name starts with A/B
 Betty | Name starts with A/B
 Betty | Name is 5 letters long
*/
    

Здесь, на строках с 4 по 11 и с 18 по 24 мы впервые увидели вложенные запросы (или подзапросы). Обратите внимание, что таким запросам необходимо присваивать имена (в нашем случае x и y) чтобы работал оператор UNION ALL.

Также вы могли обратить внимание, что мы использовали оператор UNION ALL, а не просто UNION. Дело в том, что UNION ALL возвращает все строки, в то время как UNION убирает дубли. То есть если в двух подзапросах будут две одинаковые строки, UNION покажет в результатах только одну. Именно для этого запроса ничего не изменится, потому что ученик Betty удовлетворяет обоим условиям, а вот если не выводить колонку reason в результате мы увидим имя Betty только один раз.

        SELECT
    *
FROM (
    SELECT
        name  -- <- нет колонки `reason`
    FROM
        students
    WHERE
        LEFT(name,1) IN ('A', 'B')
) AS x

UNION  -- <- UNION, а не UNION ALL

SELECT
    *
FROM (
    SELECT
        name  -- <- нет колонки `reason`
    FROM
        students
    WHERE
        LENGTH(name) = 5
) AS y;

/*
 name
 -----
 Adam
 Betty   <- Только одна запись, потому что использовали оператор UNION
*/
    

При выборе UNION или UNION ALL подумайте, нужны ли вам повторяющиеся значения. При написании сложных запросов я предпочитаю использовать UNION ALL, чтобы убедиться, что в результирующей таблице то количество строк, которое нужно, и, если есть дубли, значит, где-то раньше я, скорее, всего ошибся с соединениями таблиц (JOIN). Чем раньше вы будете фильтровать данные в запросе, тем эффективнее он будет работать.


Операторы UNION и UNION ALL возвращают все строки из подзапросов (в случае с UNION без дублей). Два других оператора INTERSECT и EXCEPT, позволяют нам вернуть только те строки, которые соответствуют определенным критериям. INTERSECT (пересечение) вернет только те строки, которые присутствуют в обоих запросах, а EXCEPT (исключение) вернет строки из подзапроса А, которых нет в подзапросе Б.


В следующем запросе попробуем работу оператора INTERSECT, который отобразит строки, присутствующие в обоих подзапросах (id 2 и id 3). В отличие от UNION нам не нужно присваивать имена вложенным запросам.

        SELECT
    *
FROM
    students
WHERE
    id IN (1,2,3)

INTERSECT

SELECT
    *
FROM
    students
WHERE
    id IN (2,3,4);

/*
 id | name     | classroom_id
 -- | -------- | ------------
  2 | Betty    |            1
  3 | Caroline |            2
*/
    

А теперь изменим запрос и применим оператор EXCEPT, который выведет строки из подзапроса А, которых нет в подзапросе Б (в нашем случае строка с id=1).

        SELECT
    *
FROM
    students
WHERE
    id IN (1,2,3)

EXCEPT

SELECT
    *
FROM
    students
WHERE
    id IN (2,3,4);

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
*/
    

Операции над множествами дают нам возможность комбинировать результаты запросов (UNION), просматривать пересекающиеся записи (INTERSECT) и извлекать отличающиеся данные (EXCEPT). Больше не нужно сравнивать результаты запросов вручную.

Функции для работы с массивами

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

Одна из полезных функций ARRAY_AGG позволяет преобразовать строки в массив. В следующем запросе мы написали ARRAY_AGG(score) и использовали группировку по имени (GROUP BY name) чтобы отобразить массив, включающий в себя все оценки каждого ученика.

        SELECT
    name,
    ARRAY_AGG(score) AS scores
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
GROUP BY
    name
ORDER BY
    name;
    
/*
 name     | scores
 -------- | ------
 Adam     | {82,82,80,75,85}
 Betty    | {74,75,70,64,69}
 Caroline | {96,92,90,100,95}
 Dina     | {81,80,84,64,89}
 Evan     | {67,91,85,93,81}
*/
    

Также в нашем арсенале есть следующие функции:

  1. CARDINALITY – выводит количество элементов в массиве.
  2. ARRAY_REPLACE – заменяет указанные элементы.
  3. ARRAY_REMOVE – удаляет указанные элемент.
        SELECT
    name,
    ARRAY_AGG(score) AS scores,
    CARDINALITY(ARRAY_AGG(score)) AS length,
    ARRAY_REPLACE(ARRAY_AGG(score), 82, NULL) AS replaced
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
GROUP BY
    name
ORDER BY
    name;

/*
 name     | scores            | length | replaced
 -------- | ----------------- | ------ | --------------------
 Adam     | {82,82,80,75,85}  |      5 | {NULL,NULL,80,75,85}
 Betty    | {74,75,70,64,69}  |      5 | {74,75,70,64,69}
 Caroline | {96,92,90,100,95} |      5 | {96,92,90,100,95}
 Dina     | {81,80,84,64,89}  |      5 | {81,80,84,64,89}
 Evan     | {67,91,85,93,81}  |      5 | {67,91,85,93,81}
*/
    

UNNESTеще одна функция, которая может вам пригодиться. Её действие противоположно функции ARRAY_AGG, то есть она позволяет разделить массив на отдельные строки.

        SELECT
    'name' AS name,
    UNNEST(ARRAY[1, 2, 3]);
    
/*
 name  | unnest
 ----  | ------
 name  |      1
 name  |      2
 name  |      3
*/
    

В этой части статьи мы с вами разобрали:

  • фильтрацию данных с помощью операторов WHERE и HAVING;
  • условные операторы CASE WHEN и COALESCE;
  • операции над множествами;
  • функции для работы с массивами.

В финальной части статьи разберем:

  • присоединение таблицы к самой себе (self join);
  • оконные функции (window function);
  • посмотрим вглубь запросов (explain).
***

Материалы по теме

16
Фев
2022

🐘 Руководство по SQL для начинающих. Часть 2: фильтрация данных, запрос внутри запроса, работа с массивами

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

Фильтрация данных с помощью операторов WHERE и HAVING

Скорее всего, вы уже знакомы с оператором для фильтрации данных WHERE и, возможно, слышали об операторе HAVING. Но чем же конкретно они отличаются? Давайте сделаем несколько запросов к таблице успеваемости (grades), чтобы в этом разобраться. Воспользуемся оператором ORDER BY RANDOM(), чтобы выбрать произвольные данные, затем LIMIT 5, чтобы запрос выдал только 5 записей. Упорядочивать все строки только ради примера достаточно неэффективно, но если таблица небольшая – это допустимо.

        SELECT
    *
FROM
    grades
ORDER BY
    RANDOM()
LIMIT
    5;

/*
 id | assignment_id | score | student_id
 -- | ------------- | ----- | ----------
 14 |             4 |   100 |          3
 22 |             2 |    91 |          5
 23 |             3 |    85 |          5
 16 |             1 |    81 |          4
  9 |             4 |    64 |          2
*/
    

Каждая строка отображает оценку ученика по определенному предмету. Давайте узнаем средний балл каждого ученика. Для этого нам понадобится:

  1. GROUP BYдля группировки по ученикам.
  2. AVG(score)для вычисления среднего значения.
  3. ROUNDдля округления полученных значений.
        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
GROUP BY
    student_id
ORDER BY
    student_id;

/*
 student_id | avg_score
 ---------- | ---------
          1 |      80.8
          2 |      70.4
          3 |      94.6
          4 |      79.6
          5 |      83.4
*/
    

Теперь давайте представим, что из предыдущего запроса, нам нужны только те строки, где средний балл (avg_score) больше, чем 50 и меньше, чем 75. То есть запрос должен отобразить только ученика со student_id=2. Что произойдет при использовании оператора WHERE?

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
WHERE
    score BETWEEN 50 AND 75
GROUP BY
    student_id
ORDER BY
    student_id;
    
/*
 student_id | avg_score
 ---------- | ---------
          1 |      75.0
          2 |      70.4
          3 |      64.0
          4 |      67.0
*/
    

Результаты выглядят совершенно неверными. Ученик с id=5 не отображается в результате запроса, а ученики с id 1, 3 и 4 на месте. К тому же их средний балл (avg_score) изменился. А что если бы это были данные какого-нибудь важного отчета? Есть вероятность как минимум растеряться.

Давайте вспомним, что оператор HAVING является агрегирующей функцией. Такие функции обрабатывают набор строк для подсчета и возвращения одного значения.

Теперь посмотрим, что изменится при использовании оператора HAVING.

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
GROUP BY
    student_id
HAVING
    ROUND(AVG(score),1) BETWEEN 50 AND 75
ORDER BY
    student_id;
    
/*
 student_id | avg_score
 ---------- | ---------
          2 |      70.4
*/
    

Эти два запроса выдают совершенно разные результаты, потому что операторы WHERE и HAVING фильтруют данные на разных этапах агрегации. WHERE обрабатывает данные перед агрегацией, а HAVING после, и фильтрует уже результаты.


Результат агрегации в запросе с оператором WHERE изменился, потому что мы изменили входные данные для подсчета среднего балла каждого ученика. У ученика с id=5 нет оценок в диапазоне с 50 по 75, поэтому он был исключен из запроса. В то время как оператор HAVING отфильтровал результаты уже после подсчета.

Когда вы освоите применение операторов WHERE и HAVING по отдельности, можете попробовать использовать их вместе. Например: мы хотим отобразить учеников, чей средний балл только по домашним работам не меньше 50 и не больше 75 баллов.

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades AS g
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
WHERE
    a.category = 'homework'
GROUP BY
    student_id
HAVING
    ROUND(AVG(score),1) BETWEEN 50 AND 75;

/*
 student_id | avg_score
 ---------- | ---------
          2 |      74.5
*/
    
Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

Условные операторы: CASE WHEN и COALESCE

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

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

        SELECT
    score,
    CASE
        WHEN score < 60 THEN 'F'
        WHEN score < 70 THEN 'D'
        WHEN score < 80 THEN 'C'
        WHEN score < 90 THEN 'B'
        ELSE 'A'
    END AS letter
FROM
    grades;

/*
 score | letter
 ----- | ------
    82 | B
    82 | B
    80 | B
    75 | C
   ... | ...
*/
    

Логика, которую мы передаем в блок CASE WHEN может охватывать сразу несколько колонок. Давайте выведем в результат запроса колонку instructor, за основу возьмем таблицу students. Например, если ученику назначен учитель, то в эту колонку запишем имя учителя, если нет, то имя ученика.

        SELECT
    name,
    teacher,
    CASE
        WHEN teacher IS NOT NULL THEN teacher
        ELSE name
    END AS instructor
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | teacher | instructor
 -------- | ------- | ----------
 Adam     | Mary    | Mary
 Betty    | Mary    | Mary
 Caroline | Jonah   | Jonah
 Dina     | [null]  | Dina
 Evan     | [null]  | Evan
*/
    

Если мы работаем с данными, которые могут не иметь значения, то есть являются null, оператор COALESCE – лучший выбор. COALESCE – проверка на null, то есть при передаче в него параметров null, он вернет первое значение, не являющееся null. Перепишем предыдущий запрос.

        SELECT
    name,
    teacher,
    COALESCE(teacher, name)
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | teacher | instructor
 -------- | ------- | ----------
 Adam     | Mary    | Mary
 Betty    | Mary    | Mary
 Caroline | Jonah   | Jonah
 Dina     | [null]  | Dina
 Evan     | [null]  | Evan
*/
    

Четвертая строка в этом запросе, делает то же самое, что и строки с четвертой по седьмую в предыдущем. То есть, если значение в колонке teacher не null, возвращаем имя учителя, если null, возвращаем имя ученика.

COALESCE будет пропускать все переданные ему аргументы, пока не дойдет до элемента со значением не null. Если же все аргументы имеют значение null, то в возвращаемом значении тоже будет null.

        SELECT
    COALESCE(NULL, NULL, NULL, 4);
/*
 coalesce
 --------
        4
 */

 SELECT
    COALESCE(NULL);
/*
 coalesce
 --------
 [null]
 */
    

И наконец, в Postgres есть еще оператор условия IF , однако он используется для управления несколькими запросами сразу, но не одним. Даже если вы data scientist или data engineer маловероятно, что вы будете им пользоваться. Если вы хотите освоить работу с данным оператором, попробуйте воспользоваться платформой Airflow.

Операции над множествами: UNION, INTERSECT, и EXCEPT

При использовании оператора JOIN мы к одной таблице горизонтально присоединяем другую таблицу. В запросе ниже видно, мы получили данные об ученике с именем Adam из трех таблиц students, grades и assignments. В качестве ключа для связки таблиц использовали поле id.

        SELECT
    s.name,
    g.score,
    a.category
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
WHERE
    s.name = 'Adam';

/*
 name | score | category
 ---- | ----- | --------
 Adam |    82 | homework
 Adam |    82 | homework
 Adam |    80 | exam
 Adam |    75 | project
 Adam |    85 | exam
*/
    

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

Давайте представим, что с системой успеваемости в нашей школе произошли очень странные изменения. Теперь для определения того, сдал ученик экзамен или нет используются не оценки. Ученик сдаст экзамен если: его имя начинается с буквы А или В или же в его имени только 5 букв. Мы можем написать 2 запроса, которые покажут нам всех учеников, подходящих под каждое условие, а затем используем оператор UNION ALL, чтобы объединить результаты в одну таблицу.

        SELECT
    *
FROM (
    SELECT
        name,
        'Name starts with A/B' as reason
    FROM
        students
    WHERE
        LEFT(name,1) IN ('A', 'B')
) AS x

UNION ALL

SELECT
    *
FROM (
    SELECT
        name,
        'Name is 5 letters long' AS reason
    FROM
        students
    WHERE
        LENGTH(name) = 5
) AS y;

/*
 name  | reason
 ----  | ------
 Adam  | Name starts with A/B
 Betty | Name starts with A/B
 Betty | Name is 5 letters long
*/
    

Здесь, на строках с 4 по 11 и с 18 по 24 мы впервые увидели вложенные запросы (или подзапросы). Обратите внимание, что таким запросам необходимо присваивать имена (в нашем случае x и y) чтобы работал оператор UNION ALL.

Также вы могли обратить внимание, что мы использовали оператор UNION ALL, а не просто UNION. Дело в том, что UNION ALL возвращает все строки, в то время как UNION убирает дубли. То есть если в двух подзапросах будут две одинаковые строки, UNION покажет в результатах только одну. Именно для этого запроса ничего не изменится, потому что ученик Betty удовлетворяет обоим условиям, а вот если не выводить колонку reason в результате мы увидим имя Betty только один раз.

        SELECT
    *
FROM (
    SELECT
        name  -- <- нет колонки `reason`
    FROM
        students
    WHERE
        LEFT(name,1) IN ('A', 'B')
) AS x

UNION  -- <- UNION, а не UNION ALL

SELECT
    *
FROM (
    SELECT
        name  -- <- нет колонки `reason`
    FROM
        students
    WHERE
        LENGTH(name) = 5
) AS y;

/*
 name
 -----
 Adam
 Betty   <- Только одна запись, потому что использовали оператор UNION
*/
    

При выборе UNION или UNION ALL подумайте, нужны ли вам повторяющиеся значения. При написании сложных запросов я предпочитаю использовать UNION ALL, чтобы убедиться, что в результирующей таблице то количество строк, которое нужно, и, если есть дубли, значит, где-то раньше я, скорее, всего ошибся с соединениями таблиц (JOIN). Чем раньше вы будете фильтровать данные в запросе, тем эффективнее он будет работать.


Операторы UNION и UNION ALL возвращают все строки из подзапросов (в случае с UNION без дублей). Два других оператора INTERSECT и EXCEPT, позволяют нам вернуть только те строки, которые соответствуют определенным критериям. INTERSECT (пересечение) вернет только те строки, которые присутствуют в обоих запросах, а EXCEPT (исключение) вернет строки из подзапроса А, которых нет в подзапросе Б.


В следующем запросе попробуем работу оператора INTERSECT, который отобразит строки, присутствующие в обоих подзапросах (id 2 и id 3). В отличие от UNION нам не нужно присваивать имена вложенным запросам.

        SELECT
    *
FROM
    students
WHERE
    id IN (1,2,3)

INTERSECT

SELECT
    *
FROM
    students
WHERE
    id IN (2,3,4);

/*
 id | name     | classroom_id
 -- | -------- | ------------
  2 | Betty    |            1
  3 | Caroline |            2
*/
    

А теперь изменим запрос и применим оператор EXCEPT, который выведет строки из подзапроса А, которых нет в подзапросе Б (в нашем случае строка с id=1).

        SELECT
    *
FROM
    students
WHERE
    id IN (1,2,3)

EXCEPT

SELECT
    *
FROM
    students
WHERE
    id IN (2,3,4);

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
*/
    

Операции над множествами дают нам возможность комбинировать результаты запросов (UNION), просматривать пересекающиеся записи (INTERSECT) и извлекать отличающиеся данные (EXCEPT). Больше не нужно сравнивать результаты запросов вручную.

Функции для работы с массивами

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

Одна из полезных функций ARRAY_AGG позволяет преобразовать строки в массив. В следующем запросе мы написали ARRAY_AGG(score) и использовали группировку по имени (GROUP BY name) чтобы отобразить массив, включающий в себя все оценки каждого ученика.

        SELECT
    name,
    ARRAY_AGG(score) AS scores
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
GROUP BY
    name
ORDER BY
    name;
    
/*
 name     | scores
 -------- | ------
 Adam     | {82,82,80,75,85}
 Betty    | {74,75,70,64,69}
 Caroline | {96,92,90,100,95}
 Dina     | {81,80,84,64,89}
 Evan     | {67,91,85,93,81}
*/
    

Также в нашем арсенале есть следующие функции:

  1. CARDINALITY – выводит количество элементов в массиве.
  2. ARRAY_REPLACE – заменяет указанные элементы.
  3. ARRAY_REMOVE – удаляет указанные элемент.
        SELECT
    name,
    ARRAY_AGG(score) AS scores,
    CARDINALITY(ARRAY_AGG(score)) AS length,
    ARRAY_REPLACE(ARRAY_AGG(score), 82, NULL) AS replaced
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
GROUP BY
    name
ORDER BY
    name;

/*
 name     | scores            | length | replaced
 -------- | ----------------- | ------ | --------------------
 Adam     | {82,82,80,75,85}  |      5 | {NULL,NULL,80,75,85}
 Betty    | {74,75,70,64,69}  |      5 | {74,75,70,64,69}
 Caroline | {96,92,90,100,95} |      5 | {96,92,90,100,95}
 Dina     | {81,80,84,64,89}  |      5 | {81,80,84,64,89}
 Evan     | {67,91,85,93,81}  |      5 | {67,91,85,93,81}
*/
    

UNNESTеще одна функция, которая может вам пригодиться. Её действие противоположно функции ARRAY_AGG, то есть она позволяет разделить массив на отдельные строки.

        SELECT
    'name' AS name,
    UNNEST(ARRAY[1, 2, 3]);
    
/*
 name  | unnest
 ----  | ------
 name  |      1
 name  |      2
 name  |      3
*/
    

В этой части статьи мы с вами разобрали:

  • фильтрацию данных с помощью операторов WHERE и HAVING;
  • условные операторы CASE WHEN и COALESCE;
  • операции над множествами;
  • функции для работы с массивами.

В финальной части статьи разберем:

  • присоединение таблицы к самой себе (self join);
  • оконные функции (window function);
  • посмотрим вглубь запросов (explain).
***

Материалы по теме

07
Фев
2022

🐘 Руководство по SQL для начинающих. Часть 1: создание базы данных, таблиц и установка связей между таблицами

Пошаговая инструкция по установке, настройке и наполнению базы данных PostgreSQL с помощью pgAdmin и SQL-запросов.

Установка

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

Первый шаг – установить SQL

Мы будем использовать PostgreSQL (Postgres) достаточно распространенный SQL диалект. Для этого откроем страницу загрузки, выберем операционную систему (в моем случае Windows), и запустим установку. Если вы установите пароль для вашей базы данных, постарайтесь сразу не забыть его, он нам дальше понадобится. Поскольку наша база будет локальной, можете использовать простой пароль, например: admin.

Следующий шаг – установка pgAdmin

pgAdmin – это графический интерфейс пользователя (GUI – graphical user interface), который упрощает взаимодействие с базой данных PostgreSQL. Перейдите на страницу загрузки, выберите вашу операционную систему и следуйте указаниям (в статье используется Postgres 14 и pgAdmin 4 v6.3.).

После установки обоих компонентов открываем pgAdmin и нажимаем Add new server. На этом шаге установится соединение с существующим сервером, именно поэтому необходимо сначала установить Postgres. Я назвал свой сервер home и использовал пароль, указанный при установке.

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

Мы можем создать таблицы напрямую в pgAdmin, но вместо этого мы напишем код, который можно будет использовать в дальнейшем, например, для пересоздания таблиц. Для создания запроса, который создаст наши таблицы, нажимаем правой кнопкой мыши на postgres (пункт расположен в меню слева home Databases (1) postgres и далее выбираем Query Tool.


Начнем с создания таблицы классов (classrooms). Таблица будет простой: она будет содержать идентификатор id и имя учителя – teacher. Напишите следующий код в окне запроса (query tool) и запустите (run или F5).

        DROP TABLE IF EXISTS classrooms CASCADE;

CREATE TABLE classrooms (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    teacher VARCHAR(100)
);
    

В первой строке фрагмент DROP TABLE IF EXISTS classrooms удалит таблицу classrooms, если она уже существует. Важно учитывать, что Postgres, не позволит нам удалить таблицу, если она имеет связи с другими таблицами, поэтому, чтобы обойти это ограничение (constraint) в конце строки добавлен оператор CASCADE. CASCADE – автоматически удалит или изменит строки из зависимой таблицы, при внесении изменений в главную. В нашем случае нет ничего страшного в удалении таблицы, поскольку, если мы на это пошли, значит мы будем пересоздавать всё с нуля, и остальные таблицы тоже удалятся.

Добавление DROP TABLE IF EXISTS перед CREATE TABLE позволит нам систематизировать схему нашей базы данных и создать скрипты, которые будут очень удобны, если мы захотим внести изменения – например, добавить таблицу, изменить тип данных поля и т. д. Для этого нам просто нужно будет внести изменения в уже готовый скрипт и перезапустить его.

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

Также вы могли обратить внимание на четвертую строчку. Здесь мы определили, что колонка id является первичным ключом (primary key), что означает следующее: в каждой записи в таблице это поле должно быть заполнено и каждое значение должно быть уникальным. Чтобы не пришлось постоянно держать в голове, какое значение id уже было использовано, а какое – нет, мы написали GENERATED ALWAYS AS IDENTITY, этот приём является альтернативой синтаксису последовательности (CREATE SEQUENCE). В результате при добавлении записей в эту таблицу нам нужно будет просто добавить имя учителя.

И в пятой строке мы определили, что поле teacher имеет тип данных VARCHAR (строка) с максимальной длиной 100 символов. Если в будущем нам понадобится добавить в таблицу учителя с более длинным именем, нам придется либо использовать инициалы, либо изменять таблицу (alter table).

Теперь давайте создадим таблицу учеников (students). Новая таблица будет содержать: уникальный идентификатор (id), имя ученика (name), и внешний ключ (foreign key), который будет указывать (references) на таблицу классов.

        DROP TABLE IF EXISTS students CASCADE;

CREATE TABLE students (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100),
    classroom_id INT,
    CONSTRAINT fk_classrooms
        FOREIGN KEY(classroom_id)
        REFERENCES classrooms(id)
);
    

И снова мы перед созданием новой таблицы удаляем старую, если она существует, добавляем поле id, которое автоматически увеличивает своё значение и имя с типом данных VARCHAR (строка) и максимальной длиной 100 символов. Также в эту таблицу мы добавили колонку с идентификатором класса (classroom_id), и с седьмой по девятую строку установили, что ее значение указывает на колонку id в таблице классов (classrooms).

Мы определили, что classroom_id является внешним ключом. Это означает, что мы задали правила, по которым данные будут записываться в таблицу учеников (students). То есть Postgres на данном этапе не позволит нам вставить строку с данными в таблицу учеников (students), в которой указан идентификатор класса (classroom_id), не существующий в таблице classrooms. Например: у нас в таблице классов 10 записей (id с 1 до 10), система не даст нам вставить данные в таблицу учеников, у которых указан идентификатор класса 11 и больше.

Невозможно вставить данные, поскольку в таблице классов нет записи с id = 1
        INSERT INTO students
    (name, classroom_id)
VALUES
    ('Matt', 1);

/*
ERROR: insert or update on table "students" violates foreign
    key constraint "fk_classrooms"
DETAIL: Key (classroom_id)=(1) is not present in table
    "classrooms".
SQL state: 23503
*/
    

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

        INSERT INTO classrooms
    (teacher)
VALUES
    ('Mary'),
    ('Jonah');

SELECT * FROM classrooms;

/*
 id | teacher
 -- | -------
  1 | Mary
  2 | Jonah
*/
    

Прекрасно! Теперь у нас есть записи в таблице классов, и мы можем добавить данные в таблицу учеников, а также установить нужные связи (с таблицей классов).

        INSERT INTO students
    (name, classroom_id)
 VALUES
    ('Adam', 1),
    ('Betty', 1),
    ('Caroline', 2);

SELECT * FROM students;

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
  2 | Betty    |            1
  3 | Caroline |            2
*/
    

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

Конечно же, нет. Мы установили внешний ключ, и он будет блокировать запись, поскольку ссылка на несуществующий id класса невозможна, но мы можем в качестве идентификатора класса (classroom_id) передать null. Это можно сделать двумя способами: указанием null при записи значений, либо просто передачей только имени.

        -- явно определим значение NULL
INSERT INTO students
    (name, classroom_id)
VALUES
    ('Dina', NULL);

-- неявно определим значение NULL
INSERT INTO students
    (name)
VALUES
    ('Evan');

SELECT * FROM students;

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
  2 | Betty    |            1
  3 | Caroline |            2
  4 | Dina     |       [null]
  5 | Evan     |       [null]
*/
    

И наконец, давайте заполним таблицу успеваемости. Этот параметр, как правило, формируется из нескольких составляющих – домашние задания, участие в проектах, посещаемость и экзамены. Мы будем использовать две таблицы. Таблица заданий (assignments), как понятно из названия, будет содержать данные о самих заданиях, и таблица оценок (grades), в которой мы будем хранить данные о том, как ученик выполнил эти задания.

        DROP TABLE IF EXISTS assignments CASCADE;
DROP TABLE IF EXISTS grades CASCADE;

CREATE TABLE assignments (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    category VARCHAR(20),
    name VARCHAR(200),
    due_date DATE,
    weight FLOAT
);

CREATE TABLE grades (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    assignment_id INT,
    score INT,
    student_id INT,
    CONSTRAINT fk_assignments
        FOREIGN KEY(assignment_id)
        REFERENCES assignments(id),
    CONSTRAINT fk_students
        FOREIGN KEY(student_id)
        REFERENCES students(id)
);
    

Вместо того чтобы вставлять данные вручную, давайте загрузим их с помощью CSV-файла. Вы можете скачать файл из этого репозитория или создать его самостоятельно. Имейте в виду, чтобы разрешить pgAdmin доступ к данным, вам может понадобиться расширить права доступа к папке (в моем случае – это папка db_data).

        COPY assignments(category, name, due_date, weight)
FROM 'C:/Users/mgsosna/Desktop/db_data/assignments.csv'
DELIMITER ','
CSV HEADER;
/*
COPY 5
Query returned successfully in 118 msec.
*/

COPY grades(assignment_id, score, student_id)
FROM 'C:/Users/mgsosna/Desktop/db_data/grades.csv'
DELIMITER ','
CSV HEADER;
/*
COPY 25
Query returned successfully in 64 msec.
*/
    

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

        SELECT
    c.teacher,
    a.category,
    ROUND(AVG(g.score), 1) AS avg_score
FROM
    students AS s
INNER JOIN classrooms AS c
    ON c.id = s.classroom_id
INNER JOIN grades AS g
    ON s.id = g.student_id
INNER JOIN assignments AS a
    ON a.id = g.assignment_id
GROUP BY
    1,
    2
ORDER BY
    3 DESC;

/*
 teacher | category  | avg_score
 ------- | --------- | ---------
 Jonah   |  project  |     100.0
 Jonah   |  homework |      94.0
 Jonah   |  exam     |      92.5
 Mary    |  homework |      78.3
 Mary    |  exam     |      76.0
 Mary    |  project  |      69.5
*/
    

Отлично! Мы установили, настроили и наполнили базу данных.

***

Итак, в этой статье мы научились:

  • создавать базу данных;
  • создавать таблицы;
  • наполнять таблицы данными;
  • устанавливать связи между таблицами;

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

В следующей части мы разберем:

  • виды фильтраций в запросах;
  • запросы с условиями типа if-else;
  • новые виды соединений таблиц;
  • функции для работы с массивами;

Материалы по теме

20
Дек
2021

Курс «Python для пентестера»

Изучите тонкости работы с языком программирования Python и его использование в сфере кибербезопасности. Максимум практики и поддержка инструктора при выполнении заданий.
— Читать дальше «Курс «Python для пентестера»»

09
Дек
2021

Факультет Java-разработки

Освойте один из самых распространенных языков программирования и получите востребованную профессию. После обучения сможете создавать сетевые чаты, приложения, мобильные игры и другие сервисы.
— Читать дальше «Факультет Java-разработки»

10
Ноя
2021

📱 Выбираем хостинг для мобильного приложения. Часть вторая: базы данных и системы кеширования

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

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

Этот цикл статей написан совместно с нашим партнером – компанией Selectel.

Другие статьи цикла:

Базы данных для мобильных приложений

Начнем обзор с классических реляционных СУБД, плавно перейдем к NoSQL и завершим обзор решениями для кэширования. Я не поклонник MySQL и ничего хорошего или плохого я об этой базе данных не скажу. Есть причины по которым предпочитаю ей не пользоваться. Поэтому сразу перейдем к PostgreSQL.


PostgreSQL

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

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

PostgreSQL поддерживает расширяемость множеством способов, такие как хранимые функции и процедуры, использование процедурных языков PL/PGSQL, Perl, Python и др.. Многие расширения предоставляют дополнительные функции, включая PostGIS – модуль для геопространственного анализа.

Если у вас уже есть модель структурированных данных, то PostgreSQL будет лучшим вариантом.


MongoDB

MongoDB – это система управления базами данных, ориентированная на документы, и в настоящее время она является самым популярным на рынке решением NoSQL.

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

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

PostgreSQL vs MongoDB по-честному

Различные тесты показали, что PostgreSQL превосходит MongoDB в рабочих нагрузках хранилищ данных и анализа данных. Однако при сравнении операций JSON между PostgreSQL и MongoDB есть тесты, которые показывают преимущества для обеих баз данных.

Можно посмотреть различные бенчмарки сравнивающие эти две базы данных, и каждый хвалит свое решение. Если же подойти в вопросу с технической стороны, то PostgreSQL использует бинарные деревья для индексирования, которые отлично работают на HDD. В тоже время большинство современных баз данных (RocksDB, MongoDB, Tarantool и т.д.) используют LSM-дерево (Log-structured merge-tree – журнально структурированное дерево со слиянием) вместо классического B-Tree.

Бинарное дерево ориентировано на операции чтения и поиск, дерево LSM ориентировано на доступ к часто изменяемым данным и работу с SDD.

Index Clients TPS – Транзакций в секунду
Inclusive B-Tree 1 9387
Inclusive B-Tree 10 18761
RocksDB FDW 1 138350
RocksDB FDW 10 122369
RocksDB 1 166333
RocksDB 10 141482

[https://postgrespro.ru/list/thread-id/2503727] пруф от независимого разработчика, поэтому этот источник, вызывает больше доверия, чем рекламные материалы проектов.

Как видите, разница примерно в 10 раз.

Система управления базами данных Postgres в 4–15 раз быстрее MongoDB при тестировании производительности транзакций, проведенном OnGres – компанией, специализирующейся на предоставлении программного обеспечения и услуг для баз данных и спонсируемой EnterpriseDB.

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

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

PostgreSQL представляет классический подход к хранению структурированных, данных в виде таблиц. С другой стороны, MongoDB требует от разработчика переосмысления подхода к хранению данных. Конечно, эта СУБД не дает полной свободы действий, но предоставляет дополнительный инструмент, повышающий производительность базы данных – коллекции.

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

Инструменты кэширования для мобильных приложений

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


Redis

Redis позиционирует себя как система управления базами данных класса NoSQL, работающая с парами ключ-значение. К базовым характеристикам можно отнести то, что Redis хранит данные в оперативной памяти (In Memory), при этом обеспечивает постоянное хранение и на дисковых накопителях (персистентность). Одно из ключевых отличий Redis от других решений заключается в поддержке различных типов данных: строки, списки, множества, хеш-таблицы, упорядоченные множества. Еще одним важным отличием является реализация системы обмена сообщениями по модели «издатель-подписчик» (PubSub). C его помощью вы сможете создать шину данных приложения, открывать каналы, подписываться и публиковать сообщения.

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

Помимо этого Redis имеет возможность масштабирования, поддерживает транзакции и пакетную обработку данных.

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

Начиная с Redis 4.0 доступен новый режим освобождения наименее часто используемых данных – LFU. Этот режим может обеспечивать лучшую эффективность в определенных ситуациях, поскольку в этом случае Redis отслеживает частоту доступа. Данные, которые используются редко, удаляются, в то время как наиболее используемые имеют более высокий шанс остаться в памяти.

Итог

Выбор базы данных для вашего приложения во многом зависит от специфики проекта и уровня ваших разработчиков. Если использование PostgreSQL можно считать начальным уровнем, то разработка на основе MongoDB уже требует знаний сравнимых с уровнем Intermediate.

Дальнейшее увеличение производительности базы данных возможно при использовании движков хранилищ данных (встраиваемых key-value хранилищ). Здесь стоит обратить внимание на такие решения, как RocksDB и BadgerDB. Однако они требуют от разработчика еще более серьезных познаний в системах управления базами данных, сопоставимых с уровнем Senior.

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

Естественно, Redis не является единственным решением. Для осознания потребности в реализации собственной системы кэширования, нужно понимать процесс сериализации и десериализации происходящий при каждом запросе к данным, отправке сообщения по шине данных или взаимодействии с Rest API.

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

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

Selectel предлагает серверы, оснащенные 4-768 ГБ ОЗУ, 2-72 ядрами ЦП, а также с возможностью подключить графический ускоритель и выбрать в качестве сервера даже Raspberry Pi 4 (4/64 ГБ) и Mac mini для iOS-разработчиков.

08
Окт
2021

Конференция HighLoad++ 2021

Крупнейшая в Европе IT-конференция для разработчиков высоконагруженных систем. В программе более 130 докладов, персональные консультации и полезные знакомства.
— Читать дальше «Конференция HighLoad++ 2021»

31
Авг
2021

📊 Путеводитель по Big Data для начинающих: методы и техники анализа больших данных

Методы и техники анализа Big Data: Machine Learning, Data mining, краудсорсинг, нейросети, предиктивный и статистический анализ, визуализация, смешение и интеграция данных, имитационные модели. Как разобраться во множестве названий и аббревиатур? Читайте наш путеводитель.

Machine Learning

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

Как методы машинного обучения применяют при анализе больших данных:

  • Осуществление маркетинговых исследований и сегментации информации. Это дает возможность предприятию понимать рынок, узнавая потребности целевой аудитории. В СМИ машинное обучение помогает определить, какой контент нравится аудитории, а какой нет.
  • Изучение поведения клиентов.
В особенности преимущества машинного обучения в Big Data видны в процессе построения IoT-систем. Технологию используют Tesla Motors и Nest, а также IoT-платформы: AWS IoT Greengrass ML Inference, SageMaker, Google Cloud IoT.
В особенности преимущества машинного обучения в Big Data видны в процессе построения IoT-систем. Технологию используют Tesla Motors и Nest, а также IoT-платформы: AWS IoT Greengrass ML Inference, SageMaker, Google Cloud IoT.

Нейронные сети и распознавание образов

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

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

Вариант использования нейросетей с распознаванием образов – различать фото мужчин и женщин.

Для этого потребуется:

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

Data mining

Термин Data mining появился благодаря математику Григорию Пятецкому-Шапиро в 1989 году. Добыча данных – это интеллектуальный анализ, необходимый для выявления закономерностей в массиве разнородной информации.

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

Основные задачи, решаемые Data mining:

  • Анализ отклонений выявляет резко отличающиеся данные, нетипичные для потока.
  • Ассоциации предназначены для поиска повторяющейся информации из различных источников.
  • Регрессионный анализ позволяет найти факторы, которые влияют на какой-то заданный параметр.
  • Классификация предназначена для разделения сведений по группам, где находятся схожие элементы.
  • Кластеризация выполняет распределение полученных записей по ранее составленным классам.

Краудсорсинг

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

Для разового опроса или сбора/обработки отзывов покупателей невыгодно разрабатывать систему искусственного интеллекта. Однако для регулярной обработки данных и их анализа стоит использовать системы, которые основаны на машинном обучении или Data Mining. Машины способны выполнять сложный анализ, который основан на математических методах (имитационное моделирование или статистика).

Предиктивная аналитика

Прогнозная, предсказательная или предиктивная аналитика составляет прогноз на основании накопившейся информации, отвечая на вопрос «Что может произойти?». Данные получают методами моделирования, математической статистики, машинного обучения, Data mining и т.д.


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

Принцип работы Predictive analytics:

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

Имитационное моделирование

Отличие имитационного моделирования от предиктивной аналитики в том, что прогнозы делаются не на реальных, а на теоретически возможных или желаемых данных. Построение моделей происходит с использованием методов Big Data, при помощи которых эксперимент проводят, если можно так выразиться, в виртуальной реальности.

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

Статистический анализ

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

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

К статистике относят анализ временных рядов и А/В тестирование. A/B testing или split testing – это маркетинговый метод исследования, при котором сравнивают контрольную группу элементов с наборами тестовых групп с измененными параметрами, чтобы определить, какие факторы улучшают целевые показатели.
К статистике относят анализ временных рядов и А/В тестирование. A/B testing или split testing – это маркетинговый метод исследования, при котором сравнивают контрольную группу элементов с наборами тестовых групп с измененными параметрами, чтобы определить, какие факторы улучшают целевые показатели.

Методы для получения статистических результатов:

  • Корреляционный анализ для выявления взаимосвязей и того, как изменение одних показателей влияет на другие.
  • Подсчет процентного соотношения результатов исследования.
  • Динамические ряды для оценивания частоты и интенсивности изменений условий на протяжении времени.
  • Вычисление среднего показателя.

Визуализация аналитических данных

Для упрощения процесса анализа информации используют метод визуализации данных. Выполнить визуализацию Big Data можно при помощи средств виртуальной реальности и «больших экранов». Визуальные образы воспринимаются лучше текста, поскольку 90% информации человек получает через зрение.

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

Результаты выводят в виде диаграмм, карт, графиков, гистограмм, 3-D моделей, либо пиктограмм. Инструменты для визуализации больших данных: Qlik, Microsoft (Excel, Power BI), Tableau (tableau desktop, tableau public), Orange и Microstrategy.
Результаты выводят в виде диаграмм, карт, графиков, гистограмм, 3-D моделей, либо пиктограмм. Инструменты для визуализации больших данных: Qlik, Microsoft (Excel, Power BI), Tableau (tableau desktop, tableau public), Orange и Microstrategy.

Смешение и интеграция данных

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

Методы, благодаря которым можно использовать записи из разных источников:

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

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

Для интеграции можно использовать стандартный метод, основанный на ETL-процессе (извлечение данных из источника, преобразование к единому формату и очищение от избыточной информации, загрузка в хранилище). Экосистема Hadoop и БД NoSQL имеют свои инструменты для извлечения, преобразования и загрузки информации. После процедуры интеграции, происходит анализ и дальнейшая обработка материалов.

***

Если вы только начинаете путь в профессии, обратите внимание на Факультет аналитики Big Data образовательной онлайн-платформы GeekBrains. Вы научитесь собирать и анализировать данные, извлекать полезную информацию и находить закономерности. После обучения сможете проверять гипотезы и помогать бизнесу принимать взвешенные решения. Занятия под руководством опытных наставников и поддержка опытных HR помогут вам продвинуться по карьерной лестнице. Специализированный опыт не потребуется: программа предполагает освоение профессиональных навыков с нуля.

31
Авг
2021

📊 С чего начать погружение в Big Data?

Как стать специалистом по Big Data? Какое направление выбрать: Data Scientist, Data Analyst или Data Engineer? С чего начать изучение этого направления, разберем информацию для начинающих IT-шников.

Генерация и обмен большими данными между устройствами происходит практически в каждой социальной сфере. С Big Data работают такие гиганты Google, Uber, IBM, Amazon, оптимизируя работу с клиентами, снижая риск мошенничества и угроз безопасности данных. Специалисты по Big Data необходимы в сферах: маркетинга, поисковых технологий, ритейла, социальных сетях, играх, персонализации, речевых технологиях, финансовых учреждениях и в рекомендательных системах.

Вакансии Data Scientist, Data Engineer и Data Analyst все чаще встречаются в объявлениях с привлекательно высокими зарплатами. С чего начать погружение в Big Data? Чтобы влиться в это направление, рассмотрим основные знания, навыки и технологии, которые стоит изучить новичку для поиска работы.


Направления в Big Data

Существует три основных направления специалистов по Big Data: Data Science, Data Engineer, Data Analyst. Всем им желательно иметь высшее образование (бакалавр, магистр) по техническим специальностям: компьютерные системы, информационные технологии, экономическая кибернетика или подобным.

Разберем направления работы экспертов по большим данным:

  • Аналитикой занимаются Data Scientist и Data Analyst, в их обязанности входит формирование гипотез, поиск закономерностей в наборах данных (dataset), визуализация информации, подготовка данных к моделированию, разработка алгоритмов Machine Learning (машинного обучения), интерпретация полученных данных, а также изучение предметной области или бизнес-процесса.
  • Инженерия относится к профессиям Data Engineer и администратор. Такие специалисты занимаются поддержкой, созданием и настройкой программной и аппаратной инфраструктуры системы сбора, хранения и обработки информации, а также аналитикой массивов и информационных потоков, в том числе конфигурированием облачных (Cloud) и локальных кластеров.

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

Для работы с большими данными, необходимо иметь хотя бы базовые знания:


Что нужно знать Data Scientist?

Исследователь, ученый по данных (Data Scientist) в основном занимается извлечением полезной информации из массивов сведений.

Основные знания, которыми должен обладать специалист Data Scientist:

  • Математика, а точнее теория вероятностей, статистика и дискретная математика. Знания математики необходимы и для машинного обучения, что предполагает также использование линейной алгебры.
  • Различные информационные технологии, средства и методы для интеллектуального анализа данных (Data Mining): языки программирования (Haskell, R, Julia, Python), среды для статического анализа данных (MatLab, R-Studio, Jupyter Notebook), структуры и алгоритмы данных, машинное обучение и иные ветви искусственного интеллекта (генетические алгоритмы, искусственные нейронные сети, deep learning).
  • Понимание предметной области.

Что следует изучать Data Engineer?

Роли в Data Engineering:

  • Database Administrator должен обладать обширными знаниями по базам данным, уметь настраивать СУБД и уровни доступа пользователей, обслуживать и обеспечивать бесперебойную работу системы.
  • Data Architect создает основу для управления данными, их прием из разных источников, интеграцию и обслуживание. Для этого стоит использовать Apache Pig, SQL, Apache Spark, Hive, XML, Apache Zookeeper, Apache Kafka и тому подобные инструменты.
  • Data Engineer должен уметь пользоваться инструментами для работы с базами данных, знать языки программирования (Python или Java), уметь пользоваться системами AWS, HDFS (Hadoop S3 Buckets, MapReduce).

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


Какой базой должен обладать Data Analyst

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

Типа анализа данных:

  • Описательный – для сбора характеристик, обработки полученной информации.
  • Прогнозный направлен на прогнозирование будущих результатов.
  • Диагностический помогает обнаружить ошибки в данных.
  • Предписательный включает перечисленные выше типы анализа информации.
В обязанности аналитика также входят задачи по Business Inteligence (BI) и оптимизации процессов на производстве. Специалист должен знать методы анализа бизнес-процессов: SWOT, ABC, IDEF, BPMN, ССП, PDCA, EPC и прочие.

Базовые навыки Data Analyst:

  • Умение извлекать данные из различных источников (Hadoop, MS SQL, MySQL и др.).
  • Обработка информации с использованием Scala, R, Python или Java.
  • Визуализация структурированных данных с применением Qlik, Plotly или Tableau.
  • Формирование исследования, которое соответствует категории бизнес-задачи.
  • Предоставление гипотез в соответствии с задачами бизнеса.

Дополнительно аналитик может использовать Apache Storm, Apache Kinesis, Apache Spark Streaming.

Специалистам по Big Data нужно уметь строить графические модели, используя байесовские и нейронные сети, кластеризацию и виды анализа. Data Scientist, Data Analyst или Data Engineer должны обладать навыками работы с Data Lakes (озерами данных), а также разбираться в вопросах безопасности и управления данными (Data Governance). Стать экспертом поможет углубленная проработка каждого из навыков.

***

Если вы только начинаете путь в профессии, обратите внимание на Факультет аналитики Big Data образовательной онлайн-платформы GeekBrains. Вы научитесь собирать и анализировать данные, извлекать полезную информацию и находить закономерности. После обучения сможете проверять гипотезы и помогать бизнесу принимать взвешенные решения. Занятия под руководством опытных наставников и поддержка опытных HR помогут вам продвинуться по карьерной лестнице. Специализированный опыт не потребуется: программа предполагает освоение профессиональных навыков с нуля.

25
Июл
2021

📊 Как стать дата-инженером в 2021 году?

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

Мир науки о данных быстро меняется, и это открыло новые возможности и вакансии в области инженерии данных. Давно хотели разобраться, как стать дата-инженером, но не знали с чего начать? Тогда эта статья для вас.

Типичные обязанности дата-инженера:

  • Исследовательский анализ данных.
  • Извлечение данных из массивов разнородной информации.
  • Оценка и очистка наборов данных.
  • Подготовка и написание логики ETL (процессов обработки/преобразования информации).
  • Построение конвейеров данных для распределения по нескольким серверам.
  • Сбор и запрос данных.
  • Объединение данных.
  • Создание хранилищ данных.
  • Оптимизация доставки данных.
  • Нормализация и моделирование данных.
  • Перепроектирование инфраструктуры данных для масштабируемости.
  • Использование инструментов для обслуживания данных.
  • Помощь специалистам по исследованию данных в оптимизации продуктов.

Главные технические компетенции дата-инженера:

  • Языки программирования Python, C++, Java и Scala.
  • Знание алгоритмов и структур данных.
  • Системы управления базами данных (SQL и NoSQL).
  • Инфраструктура – облачные вычисления.
  • Потоковая передача данных – Apache Beam.

Как стать успешным дата-инженером в 2021 году?


Изучите языки программирования

Требования индустрии вращаются вокруг Python и Scala. Чтобы создавать качественное ПО, вам потребуется подтянуть навыки владения этими языками и получить практический опыт работы с инструментами для инженерии данных. Также желательно владеть такими языками, как Java и C++.

Освойте SQL

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


Изучите облачные технологии

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

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

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

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

Обзор ресурсов для изучения

Алгоритмы и структуры данных

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

Python

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

SQL

Облачные технологии

  • Hadoop. Система для обработки больших объемов данных. Курс посвящен методам обработки больших объемов данных с помощью системы Hadoop. Завершив обучение, вы получите базовые знания об основных способах хранения и методов обработки больших объемов данных, а также поймете принципы работы распределенных систем в контексте фреймворка Hadoop.
Из более продвинутых курсов хочется отметить англоязычный GCP: Complete Google Data Engineer and Cloud Architect Guide на Udemy, где 28 часов контента дополняются 25 статьями и 48 ресурсами для скачивания. Программа обучения дает полное представление о дисциплине.

Итог

Если вам нравится работать в спокойной обстановке и проводить чистку необработанных данных, карьера в области дата-инженерии – правильный выбор. Самое трудное – создать портфолио и накопить опыт для получения первой работы. Значительное увеличение объема облачных услуг стало одной из основных причин столь высокого спроса на инженеров по обработке данных.

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

***

Если вы только начинаете путь в профессии, обратите внимание на курс Data Engineering образовательной онлайн-платформы GeekBrains. Занятия под руководством опытных наставников и поддержка опытных HR помогут вам продвинуться по карьерной лестнице. Вы научитесь собирать и обрабатывать данные, проектировать хранилища и работать с инфраструктурой. Специализированный опыт для этого не потребуется: программа предполагает освоение профессиональных навыков с нуля.

12
Июл
2021

Интенсив ASP.NET Core Fundamentals

.NET-разработчик и тренер Microsoft проведёт 11 онлайн-сессий по работе с платформой ASP.NET Core и научит создавать современные кроссплатформенные приложения.
— Читать дальше «Интенсив ASP.NET Core Fundamentals»

10
Июн
2021

Конференция SECON 2021

В программе конференции доклады от экспертов, общение с коллегами, новые знакомства, выставка и развлечения в атмосфере летнего фестиваля.
— Читать дальше «Конференция SECON 2021»

04
Июн
2021

Курс «Профессия Data Scientist» с трудоустройством

За 2 года освоите профессию Data Science: научитесь программировать на Python и R, применять алгоритмы машинного обучения и визуализировать данные.
— Читать дальше «Курс «Профессия Data Scientist» с трудоустройством»

31
Май
2021

Курс «Backend-разработка на Node.js»

За 4 месяца станете fullstack-разработчиком, будете создавать бэкенд веб-приложений на Node.js и расширите возможности ваших проектов на JavaScript с помощью языка TypeScript.
— Читать дальше «Курс «Backend-разработка на Node.js»»

26
Май
2021

Курс «Fullstack-разработчик на JavaScript»

За полтора года с нуля научитесь программировать на JavaScript и TypeScript и освоите передовые технологии — React, Node.js, Docker, Express.js, Nest.js, Firebase, MongoDB.
— Читать дальше «Курс «Fullstack-разработчик на JavaScript»»

12
Май
2021

Java. Backend-практикум

Бесплатный онлайн-курс для начинающих Java-разработчиков. За 8 недель получите систематизированную базу знаний и создадите свой проект для портфолио.
— Читать дальше «Java. Backend-практикум»