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

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

➡️ Читать дальше

@sqlhub

🖥 Вопрос с собеседования Dropbox

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

​db_employee

id:int
first_name:varchar
last_name:varchar
salary:int
department_id:int
db_dept

id:int
department:varchar

Ответ:

select abs(max(salary) filter (where department = 'marketing') - max(salary) filter (where department = 'engineering'))
from db_employee emp
LEFT JOIN db_dept dept on emp.department_id = dept.id

@sqlhub

🖥 Вопрос с собеседования Salesforce

Найдите самую высокую цель, достигнутую сотрудником или сотрудниками, работающими под менеджером с id 13. Выведите имя сотрудника/сотрудников и достигнутую цель. Решение должно показывать самую высокую цель, достигнутую при manager_id=13, и какие сотрудники ее достигли.

salesforce_employees

id:int
first_name:varchar
last_name:varchar
age:int
sex:varchar
employee_title:varchar
department:varchar
salary:int
target:int
bonus:int
email:varchar
city:varchar
address:varchar
manager_id:int

Ответ:

select first_name, target
from salesforce_employees
where manager_id=13 and
target=(select max(target) from salesforce_employees where manager_id=13)

@sqlhub

🖥 Вопрос с собеседования Twitter

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

employee

id:int
first_name:varchar
last_name:varchar
age:int
sex:varchar
employee_title:varchar
department:varchar
salary:int
target:int
bonus:int
email:varchar
city:varchar
address:varchar
manager_id:int

ОТВЕТ:

SELECT department as department,
first_name as employee_name,
salary
FROM employee
WHERE (department, salary) IN
(SELECT department, MAX(salary)
FROM employee
GROUP BY department)

@sqlhub

🖥 Разбор практической задачи с собеседования

Дана база данных.
Найдите модели принтеров, имеющих самую высокую цену. Вывести: model, price

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

SELECT model, MAX(DISTINCT price)
FROM Printer
GROUP BY model;

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

SELECT MAX(price)
FROM Printer;

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

Итак, приходится использовать подзапрос, в котором вычисляется максимальная цена:

SELECT model, price
FROM Printer
WHERE price = (SELECT MAX(price)
FROM Printer
);

При этом подзапрос может вводиться не только с простым оператором сравнения («=»), но и с предложением IN или >= ALL.

Подзапрос можно использовать и в предложении FROM:

SELECT model, price
FROM Printer pr, (SELECT MAX(price) AS maxprice
FROM Printer
) AS mp
WHERE price = mp.maxprice;

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

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

Ставьте ❤️, если интересно увидеть решение.

@sqlhub

🖥 Работаем с данными. Кто кого: Pandas VS SQL

Еще порядка 10 лет назад для работы по исследованию данных было достаточно SQL, как инструмента для выборки данных и формирования отчетов по ним. Но время не стоит на месте, и примерно в 2012 году стала стремительно набирать популярность Python-библиотека Pandas. И вот сегодня уже сложно представить работу Data Scientist’а без данного модуля.

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

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

Pandas — это модуль для обработки и анализа данных в табличном формате и формате временных рядов на языке Python. Библиотека работает поверх математического модуля более низкого уровня NumPy. Название модуля происходит от эконометрического понятия «панельные данные» (или как его еще называют «лонгитюдные данные» — это данные, которые состоят из повторяющихся наблюдений одних и тех же выбранных единиц, при этом наблюдения производятся в последовательные периоды времени).

➡️ Читать дальше

@data_analysis_ml

🖥 Подсказки и решения к задаче

Решить задачу без использования подзапроса можно. Правда, для этого используются нестандартные средства. Метод основывается на конструкции TOP N (SQL Server), которая позволяет отобрать из отсортированного набора первые N строк. Аналогичные конструкции имеются в диалектах SQL многих СУБД. В комитете по разработке стандартов было даже зафиксировано предложение о включение подобной конструкции в стандарт языка. Так что не исключено, что к моменту выхода этой книги в свет данная конструкция уже будет стандартизована. А вот и решение:

SELECT TOP 1 WITH TIES model, price
FROM Printer
ORDER BY price DESC;

Итак, выполняется сортировка по убыванию цены. В результирующий набор попадает одна (первая — TOP 1) строка. Однако остается проблема, когда несколько принтеров из таблицы будут иметь одинаковую максимальную цену. Проблема решается при помощи предложения WITH TIES, которое включит в результирующий набор не только N строк (в нашем случае одну), но и все ниже идущие строки, у которых значения в полях сортировки (у нас — price) совпадают со значениями N-ой строки (у нас — первой).

В PostgreSQL/MySQL для ограничения количества строк, возвращаемых запросом, используется конструкция (следующая после ORDER BY)

LIMIT N [OFFSET M]

где
N - число первых строк, возвращаемых запросом в порядке, заданном сортировкой;
M - число строк, пропускаемых перед началом вывода.
Если предложение OFFSET отсутствует, то выводиться будут N строк, начиная с первой, в противном случае - N строк, начиная с M+1.

Конструкций, подобных WITH TIES, в этих СУБД нет. Поэтому для решения рассматриваемой задачи способом через сортировку так или иначе придется использовать подзапрос:


SELECT model, price
FROM Printer WHERE price =
(SELECT price FROM Printer ORDER BY price DESC LIMIT 1);

Заметим, что в отличие от MySQL, в PostgreSQL предложение OFFSET может использоваться и при отсутствии предложения LIMIT. В этом случае возвращаться будут все строки запроса кроме первых M. Так, например, чтобы вывести все строки кроме первой строки с максимальной ценой, можно написать:

SELECT model, price
FROM Printer
ORDER BY price DESC OFFSET 1;

model price
1401 150.00
1408 270.00
1433 270.00
1434 290.00

При использовании SQL Server эту же задачу можно решить так:

SELECT model, price
FROM Printer WHERE code NOT IN(
SELECT TOP 1 code
FROM Printer
ORDER BY price DESC);
Т.е. мы выбираем все строки, кроме той, которая идёт первой при сортировке цены по убыванию.
А теперь попробуйте решить эту задачу под MySQL. 🙂

При решении последней задачи следует обратить внимание на неточность ее постановки. В результате чего представленное решение на одних и тех же данных может давать разные результаты при разных запусках. В частности, у нас имеется две строки с максимальной ценой, и какая из них будет выводиться зависит от порядка, в котором СУБД будет извлекать строки. А этот порядок может меняться в зависимости от выбранного оптимизатором плана. Чтобы сделать постановку и результат! однозначными, следует указать в условии задачи однозначный порядок сортировки. Эту однозначность всегда обеспечит включение первичного ключа в конец списка столбцов сортировки, например:

ORDER BY price DESC, code
или

ORDER BY price DESC, model, code

@sqlhub

🖥 Как работать с GreenPlum через командную строку

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

При работе с СУБД GreenPlum или PostgreSQL, как правило, в качестве инструментов управления базами данных используют pgAdmin или Dbeaver. Это хорошие и универсальные инструменты, однако, за универсальность приходится платить скоростью запроса к БД и работой приложения в целом.

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

PSQL — это терминальный клиент для работы с PostgreSQL. Он позволяет вводить запросы в интерактивном режиме, отправлять их в PostgreSQL и просматривать результаты запроса. Так как GreenPlum основан на PostgreSQL, то PSQL работает и с ним.

Устанавливается PSQL при установке PostgreSQL — называется Command Line Tools, в случае Greenplum PSQL устанавливается с pgAdmin. Для его запуска надо открыть командную строку в папке с psql.exe (например, при установке PostgreSQL путь PostgreSQL\14\bin\psql.exe). C PSQL можно работать в двух режимах:

интерактивный
из командной строки (не интерактивный)

➡️ Читать дальше

@sqlhub

🖥 5 рекомендаций по оптимизации запросов SQL

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

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

➡️ Читать дальше

@sqlhub

🖥 Задачи с собеседования

Найти идентификатор с максимальным значением

depname | empno | salary |
-----------+-------+--------+
develop | 11 | 5200 |
develop | 7 | 4200 |
develop | 9 | 4500 |
develop | 8 | 6000 |
develop | 10 | 5200 |
personnel | 5 | 3500 |
personnel | 2 | 3900 |
sales | 3 | 4800 |
sales | 1 | 5000 |
sales | 4 | 4800 |


Задача: написать запрос, чтобы получить empno с самой высокой зарплатой. Убедитесь, что ваше решение обрабатывает случаи одинаковых зарплатами!

Решение:

WITH max_salary AS (
SELECT
MAX(salary) max_salary
FROM
salaries
)
SELECT
s.empno
FROM
salaries s
JOIN
max_salary ms ON s.salary = ms.max_salary

Альтернативное решение с использованием RANK():

WITH sal_rank AS
(SELECT
empno,
RANK() OVER(ORDER BY salary DESC) rnk
FROM
salaries)
SELECT
empno
FROM
sal_rank
WHERE
rnk = 1;


@sqlhub

❤️ 100+ шпаргалок для аналитиков данных и data scientists

Шпаргалки помогут:

- подготовиться к экзамену
- подготовиться к собеседованию
- в повседневной работе – быстро вспомнить нужную информацию

Сохраняйте себе, чтобы не потерять

➡️ Читать дальше

@sqlhub

✅ Машинное обучение - нейросети, dl , алгоритмы, SQL. Канал раскроет технологии машинного обучения.

💨 Data Jobs - отборные вакансии и проекты для всех, кто работает с данными.

🖥 ML interview - канал подготовит к собеседованию по работе с данными, углубленном python программировании.

🔥 English for Developers -канал поможет быстро выучить технический английский.

🖥 Задание SQL с реального собеседования

Вводные данные
Есть таблица анализов Analysis:

an_id — ID анализа;
an_name — название анализа;
an_cost — себестоимость анализа;
an_price — розничная цена анализа;
an_group — группа анализов.
Есть таблица групп анализов Groups:

gr_id — ID группы;
gr_name — название группы;
gr_temp — температурный режим хранения.
Есть таблица заказов Orders:

ord_id — ID заказа;
ord_datetime — дата и время заказа;
ord_an — ID анализа.

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

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

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

Ответ

@sqlhub

🖥 Задание SQL с реального собеседования

Вводные данные
Есть таблица анализов Analysis:

an_id — ID анализа;
an_name — название анализа;
an_cost — себестоимость анализа;
an_price — розничная цена анализа;
an_group — группа анализов.
Есть таблица групп анализов Groups:

gr_id — ID группы;
gr_name — название группы;
gr_temp — температурный режим хранения.
Есть таблица заказов Orders:

ord_id — ID заказа;
ord_datetime — дата и время заказа;
ord_an — ID анализа.

Задача Уровень: Senior

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

Рассмотрим таблицу балансов клиентов:

ClientBalance(client_id, client_name, client_balance_date, client_balance_value)

client_id — идентификатор клиента;
client_name — ФИО клиента;
client_balance_date — дата баланса клиента;
client_balance_value — значение баланса клиента.
Формулировка: в данной таблице в какой-то момент времени появились полные дубли. Предложите способ для избавления от них без создания новой таблицы.

Ответ

ставь ❤️ ,если нравится такой формат

@sqlhub

🖥 Вопросы на знание SQL

Вопрос 1. Уровень: Junior
Есть категория «хитрых» вопросов, которые особенно любят задавать Junior-специалистам. Хотя чего уж там, любым специалистам.

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

Вопрос: Как оператор GROUP BY обрабатывает поля с NULL?

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

Ответ
Учитывая, что NULL в SQL — просто отсутствие значения, то все значения NULL при группировке попадают в одну группу. Например, пусть есть таблица:

name | score
------|-------
Vasya | 5
Petya | 10
Petya | 3
Vasya | 4
NULL | 3
NULL | 8

Тогда запрос select sum(score) from table group by name даст:

name | score
------|-------
Vasya | 9
Petya | 13
NULL | 11

@sqlhub

🖥 Вопросы на знание SQL

Вопрос 2. Уровень Middle

Этот вопрос не такой хитрый, как предыдущий, а вполне себе конкретный. Однако, он требует знания оконных функций и их тонкостей, а это исконное Middle требование.

Вопрос: В чем отличие функции RANK() от DENSE_RANK()?

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

По аналогии с функцией ROW_NUMBER, оконные функции RANK и DENSE_RANK служат для нумерации строк. Однако, делают они это немного иначе: строки с одинаковым значениям получают одинаковый ранг. Для ряда задач это логично: если у двух сотрудников одинаковая зарплата, мы не можем сказать, что кто-то из них первый, а кто-то второй. Они одинаковы. Но при таком подходе возникает проблема: а какой ранг должен получить следующий сотрудник? Например, если первые два были одинаковые и у них ранги 1, то сотрудник со второй зарплатой в компании должен иметь ранг 2 или 3?

@sqlhub

🖥 Серия вопросов по СУБД (в частности Postgres). По всем пунктам можно погрузиться в достаточно низкоуровневые детали реализации, но важно и в целом оценить осведомленность кандидата об основных принципах работы базы, достаточных для большинства задач.

***

Что такое транзакция? Приведите пример, где это может пригодиться. Расскажите про свойства транзакций и уровень изолированности.

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

Что такое server side cursor и зачем он нужен?

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

Что такое VACUUM и зачем он нужен в PostgreSQL?

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

Что такое EXPLAIN? Какая разница между ним и EXPLAIN ANALYZE?

Ответ
EXPLAIN ANALYZE – в отличие от просто EXPLAIN не только показывает план выполнения запроса, но и непосредственно выполняет запрос и показывает реальное время выполнения

@sqlhub

🖥 Хороший урок с задачами SQL

Github

@sqlhub

🖥 Вопросы собеседований

Что такое нормализация и каковы ее преимущества?

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

- Лучшая организация базы данных
- Больше таблиц с небольшими строками
- Эффективный доступ к данным
- Большая гибкость для запросов
- Быстрый поиск информации
- Проще реализовать безопасность данных
- Позволяет легко модифицировать
- Сокращение избыточных и дублирующихся данных
- Более компактная база данных
- Обеспечивает согласованность данных после внесения изменений

Объясните различные типы нормализации.

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

- Первая нормальная форма (1NF) — нет повторяющихся групп в строках

- Вторая нормальная форма (2NF) — каждое неключевое (поддерживающее) значение столбца зависит от всего первичного ключа

- Третья нормальная форма (3NF) — каждое неключевое значение зависит только от первичного ключа и не имеет зависимости от другого неключевого значения столбца

@sqlhub

🖥 В чем разница между кластеризованным и некластеризованным индексами в SQL?

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

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

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

@sqlhub