SQL: от А до Я

SQL: от А до Я


Coding

Alias


При объединении таблиц нам необходимо указать, какой столбец и из какой таблицы мы хотим сопоставить, и какие столбцы мы хотим вернуть в результатах.

Если мы этого не сделаем, будет выдана ошибка, поскольку база данных не знает, что мы имеем в виду.

select
orders.order_item, 
inventory.inventory_item
from orders 
inner join inventory 
on orders.order_item = inventory.inventory_item

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

select
o.order_item, 
i.inventory_item
from orders o
inner join inventory i 
on o.order_item = i.inventory_item

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


Begin Transaction

Транзакции SQL используются для отлова ошибок при внесении изменений в таблицы. Во время выполнения оператора UPDATE или DELETE изменение автоматически фиксируется.

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

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

begin transaction

update orders
set status = 'sent'
where order_id = '12345'

update orders
set status = 'sent'
where order_id = '54321'

commit transaction


CTEs v Subqueries

CTE (Common Table Expressions) используются для фильтрации наборов данных и присвоения им имен, к которым можно вернуться в запросе позже.

Я использую их при работе с большими таблицами, например, чтобы получить все нужные мне столбцы из таблицы 'orders', а затем получить все нужные мне столбцы из таблицы 'инвентаризации'. Затем на последнем этапе объедините их.

with orderset as
(select
order_id,
order_item
from orders
where order_id between '12345' and '54321'
),

with inventoryset as
(select 
inventory_item
inventory_balance
from inventory
)

select
orderset.order_id,
inventoryset.inventory_balance
from inventoryset
join orderset on orderset.order_item = inventoryset.inventory_item


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


Design

Таблицы Datamarts организованы в одной из двух форм. Схема «Звезда» и «Снежинка» состоят из двух типов таблиц.

  • Факты - это подсчет, сколько раз что-то случалось.
  • Размеры - (или Dims), которые описывают атрибут.

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

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


ETL

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

Эта задача решается инженерами данных или разработчиками баз данных в команде.


Function

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


Group By

Агрегатные функции позволяют нам выполнять вычисления на полях.Наиболее распространенными являются SUM, COUNT, MIN, MAX, AVERAGE.

Например, чтобы увидеть общую сумму, подлежащую оплате для каждого элемента в таблице заказов, мы можем использовать сумму суммы столбца amount_due и GROUP BY

select 
order_item, 
sum(amount_due) 
from orders
group by order_item;


Heaped Storage

Heaped Storage - это термин для таблиц, которые живут в базе данных без индексов. Данные расположены в произвольном порядке, и новые данные просто добавляются по мере поступления. 

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

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

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


Integrity

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

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


Join

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

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


Key

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

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


Lock

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


Massive Parallel Processing

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

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


Normalisation

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

Процесс нормализации базы данных состоит из нескольких этапов:

  • 1-я нормальная форма - устраняет дубликаты столбцов во всех таблицах и добавляет первичный ключ.
  • 2nd Normal Form - создайте отношения через Foreign Keys.
  • 3-я нормальная форма - поля не должны быть получены из других полей. то есть. удаление столбца Total, который умножает столбец количества и цены. Вместо этого его следует рассчитывать, выполняя запрос, а не сохраняя его в таблице.


OLTP v OLAP

OLTP и OLAP относятся к разным типам баз данных и инструментам, которые выполняют разные функции.

  • OLTP - Online Transaction Processing - используется для быстрой обработки данных и мгновенно реагирует на запросы.
  • OLAP - Online Analytics Processing - используется для хранения исторических данных и интеллектуального анализа данных.



Privileges

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

GRANT ALL ON <schemaname.tablename> TO <username>  
-- if you would like them to SELECT, UPDATE and DELETE

GRANT SELECT ON <schemaname.tablename> TO <username> 
-- if you would like them to be able to only SELECT


Query Plan

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

В PostgreSQL мы можем проверить план запроса, используя:

  • EXPLAIN - показать план выполнения выписки
  • EXPLAIN ANALYZE - также вызывает выполнение запроса
explain

select
orders.order_item, 
inventory.inventory_item
from orders 
inner join inventory 
on orders.order_item = inventory.inventory_item


Recovery

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

В зависимости от организации эти решения могут распространяться на:

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


System Tables

Системные таблицы содержат информацию обо всех объектах в базе данных. Иногда это называется информационной схемой или системным каталогом.

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


Truncate v Drop

При обновлении таблицы новыми данными используйте команду TRUNCATE. Он удаляет все строки из таблицы, не удаляя формат и заголовки.


Union

В то время как JOIN объединяет строки столбцов по горизонтали, UNION объединяет результаты по вертикали. Использование UNION объединяет результаты двух запросов в один столбец и удаляет дубликаты. Если в вашем запросе несколько столбцов, они должны быть в том же порядке, чтобы завершить UNION.

  • UNION - объединяет две таблицы или наборы данных по горизонтали и удаляет дубликаты
  • UNION ALL - делает то же самое, но не удаляет дубликаты


View

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


Window Function

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

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

select
order_id,
order_name
order_date,
rank() over(order amount_due desc) [rank]
from dbo.orders


XML

Мы можем импортировать файлы в таблицы с помощью мастера импорта / экспорта. Но они не должны быть просто CSV или TXT файлами. Используя несколько строк кода, мы также можем импортировать xml и json.


Year

Вот несколько советов по работе с датами:

  • Труднее работать с датами, которые хранятся в виде строк, поэтому убедитесь, что они никогда не представляют даты.
  • Не разбивайте год, месяц и день на отдельные столбцы. Это значительно усложняет написание и фильтрацию запросов.
  • Всегда используйте UTC для своего часового пояса. Если у вас есть сочетание не-UTC и UTC, это делает понимание данных намного сложнее.
  • Функции DATEDIFF и DATEADD из SQL Server не существуют в PostgreSQL, узнайте больше о различиях между двумя системами:

Zero

NULL означает, что значение неизвестно, не ноль и не пусто. Это затрудняет сравнение значений, если вы сравниваете NULL с NULL.

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


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


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




Report content on this page

Report Page

Please submit your DMCA takedown request to dmca@telegram.org