Data Science. SQL hub

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

***

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

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

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

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

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

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

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

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

@sqlhub

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

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

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

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

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

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

@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 с реального собеседования

Вводные данные
Есть таблица анализов 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 с реального собеседования

Вводные данные
Есть таблица анализов 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

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

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

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

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

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

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

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

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

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

@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

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

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

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

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

@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

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

Решить задачу без использования подзапроса можно. Правда, для этого используются нестандартные средства. Метод основывается на конструкции 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

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

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

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

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

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

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

@data_analysis_ml

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

Дана база данных.
Найдите модели принтеров, имеющих самую высокую цену. Вывести: 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

🖥 Вопрос с собеседования 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

🖥 Вопрос с собеседования 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

🖥 Вопрос с собеседования 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

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

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

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

@sqlhub

➡️ Airtable: рецепт молниеносного создания баз данных и таблиц

Airtable представляет собой платформу из разряда low-code, т.е. практически не требует написания программного кода. Она позволяет любому создавать реляционные базы и визуализировать данные, не требуя для этого даже знания SQL и Excel.

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

создать базу данных с записями;
выбрать конкретные данные по заданным условиям;
отобразить отфильтрованные данные в виде диаграммы (столбчатой, круговой);
поделиться данными проекта онлайн.
Задание простое и выполнимое посредством Excel. Но оно имеет тенденцию к усложнению и, как следствие, требует больше времени. Выбирая Airtable, вы получаете значительное преимущество: сразу начинаете работать с удобным онлайн GUI.

В статье мы рассмотрим основные преимущества и возможности Airtable на практическом примере.

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

@sqlhub

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

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

airbnb_search_details

id:int
price:float
property_type:varchar
room_type:varchar
amenities:varchar
accommodates:int
bathrooms:int
bed_type:varchar
cancellation_policy:varchar
cleaning_fee:bool
city:varchar
host_identity_verified:varchar
host_response_rate:varchar
host_since:datetime
neighbourhood:varchar
number_of_reviews:int
review_scores_rating:float
zipcode:int
bedrooms:int
beds:int

Ответ

SELECT city, property_type, avg(bedrooms) as n_bedrooms_avg, avg(bathrooms) as n_bathrooms_avg
FROM airbnb_search_details
GROUP BY city, property_type
ORDER BY city


@sqlhub

🚀 Big Data - действительно полезный канал для всех, кто работает с данными.

- огромный набор бесплатных наборов данных
- инструменты для работы с данными
- обучающие материалы для датасаентистов
- лучшие практики Машинного Обучения
- перевод книг и статей

🔥 Подписаться