дата инженеретта
3.45K subscribers
326 photos
28 videos
4 files
136 links
мелкое — крупно,
в глубоком разговоре
мудрость приходит

по вопросам сюда: @aigul_sea
Download Telegram
📌Помимо #spark, также буду вести рубрики #sql_tips, #python_tips, возможно, что-то еще по мере появления идей. Там будут лайфхаки, о которых я узнала на рабочих задачах или во время чтения книжек📖

Например, про временные таблицы⏳

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

В MS SQL Server есть два вида: локальные (#) и глобальные (##):

CREATE TABLE #localTempTable (...)

CREATE TABLE ##globalTempTable (...)


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

На временные таблицы также можно навешивать индексы🗂
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥7❤6⚡3
💡Ответ💡
Все варианты валидны.

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

1) оригинальные поля
ORDER BY column_name


2) вычисляемые поля
ORDER BY fn(column_name)


3) элиасы (псевдонимы) полей
SELECT column_name AS column_name_alias
...
ORDER BY column_name_alias


4) числовые индексы полей (начиная с 1)
ORDER BY 1


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

А вам какой способ больше нравится?

#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥1
💡Ответ💡
Верные варианты: 6, 7, 8, 9, 10.

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

В группировке можно использовать:
1) оригинальные поля
GROUP BY column_name


2) вычисляемые поля
GROUP BY fn(column_name)


3) элиасы (псевдонимы) полей❕
SELECT column_name AS column_name_alias
...
GROUP BY column_name_alias

В Postgres, Clickhouse прокатит, а в MS SQL Server уже нет.

4) числовые индексы полей (начиная с 1)❕
GROUP BY 1

Аналогично

5) оригинальные поля в вычислениях
SELECT fn(column_name)
...
GROUP BY column_name


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

Колонки и индексы можно комбинировать, но как по мне - лучше придерживаться одного стиля, иначе получается какая-то каша🥣

#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
❤5🔥2👍1
👻Про явное приведение типов

1️⃣ При работе со строками в sql для экономии памяти лучше ограничивать максимальную длину поля: varchar(100). В MS SQL Server также есть возможность указать varchar(max). Она расширяет количество символов, но тогда на это поле нельзя навесить индексы.

2️⃣ Если мы хотим поджойнить две таблицы, но поля имеют разные типы, то нужно кастить одно из них:
SELECT *
FROM test1
JOIN test2
ON cast(test1.id as varchar) = test2.id

или
    ON test1.id = cast(test2.id as int)


Некоторые бд сами подсвечивают ошибку при запуске запроса, а некоторые начинают его выполнение, но затрачивают сильно больше ресурсов на неявное преобразование. И запрос, который мог отработать за 5 минут, может отработать за 30 или не отработать вообще. Поэтому за соответствием типов важно следить🤓

#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
💯6⚡5❤2❤‍🔥2👏1
🫡Кастомная сортировка

Бывает так, что есть поле, по которому нужно отсортировать, но по алфавиту ну никак не подходит. Тогда можно искусственно создать поле для сортировки через case when и его аналоги:

SELECT color
FROM test
ORDER BY
CASE color
WHEN 'red' THEN 1
WHEN 'orange' THEN 2
WHEN 'yellow' THEN 3
WHEN 'green' THEN 4
WHEN 'blue' THEN 5
WHEN 'indigo' THEN 6
WHEN 'violet' THEN 7
END


#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥38
К чему был предыдущий вопрос?

🧳На днях у нас с коллегой возник такой кейс: есть 50 таблиц, содержащих колонку с датой, но там есть дата, дата и время и unix timestamp. Цель - нужно их все объединить. БД - ClickHouse.

Итерация 1:
SELECT 
max(
if(
table_name LIKE 'offline%',
toDate(FROM_UNIXTIME(dateTime::INT)),
toDate(dateTime)
)
) AS max_date
FROM schema.source_table

Количество считанных строк - 46 млрд. Время работы - 2,5 минуты. Ну... долго ждать, давайте еще раз внимательно посмотрим🤓

Итерация 2 - вынесли приведение даты со временем к дате в самый конец:
SELECT
toDate(
max(
if(
table_name LIKE 'offline%',
FROM_UNIXTIME(dateTime::INT),
dateTime
)
)
) AS max_date
FROM schema.source_table

Количество считанных строк - 46 млрд. Время работы - 1,5 минуты. Уже лучше, но можно ли еще быстрее?

Итерация 3 - вынесли приведение unix timestamp к дате со временем:
SELECT
toDate(
if(
table_name LIKE 'offline%',
FROM_UNIXTIME(max(dateTime)::INT),
max(dateTime)
)
) AS max_date
FROM schema.source_table

Количество считанных строк - 25 млн. Время работы - 6 секунд. Супер! Нам этом остановимся👍
В итоге мы сначала находим максимальные значения и только потом кастуем. Выигрыш во времени - в 25 раз.

🕒Время и строки можно посмотреть в системной табличке system.query_log в столбцах query_duration_ms, read_rows.

🐢В первых двух случаях в плане запроса есть шаги:

ReadFromMergeTree (schema.source_table) - читаем таблицу

Expression ((Conversion before UNION + (Projection + Before ORDER BY))) - кастуем к дате в каждом подзапросе

🐆В третьем:

ReadFromStorage (MergeTree(with Aggregate projection _minmax_count_projection)) - вот эта проекция сильно быстрее, чем скан всей таблицы, а лишних приведений уже нет. Profit👍

#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥6👏6❤‍🔥1💯1🆒1
Как переименовать базу данных?

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

Что нужно сделать?
Диалект - MS SQL Server
--1. Переключиться в другую базу
USE master;
GO
--2. Установить однопользовательский режим
ALTER DATABASE [old_database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
--3. Поменять название
ALTER DATABASE [old_database] MODIFY NAME = [new_database];
GO
--4. Восстановить режим
ALTER DATABASE [new_database] SET MULTI_USER;
GO


#sql_tips
🔥15🌚1
Как создать пустую таблицу из другой?

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

Есть два варианта, которые будут работать везде:

1️⃣ TOP/LIMIT
SELECT *
INTO new_table
FROM old_table
LIMIT 0


SELECT TOP 0 *
INTO new_table
FROM old_table


2️⃣ заведомо ложное условие
SELECT *
INTO new_table
FROM old_table
WHERE 1=0


#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
❤11🔥10👻4
⚔️UNION vs CASE WHEN

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

Тогда я затупила, но у вас есть шансы пропустить этот этап😁
Они проверяли знание union и case when:

1️⃣ UNION/UNION ALL - для одного столбца
SELECT 'amt_not_null' as agg_type, SUM(amount) AS val
FROM test
WHERE id IS NOT NULL
UNION ALL
SELECT 'amt_null', SUM(amount)
FROM test
WHERE id IS NULL


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

2️⃣ CASE WHEN - для разных столбцов
SELECT
SUM(CASE WHEN id IS NOT NULL THEN amount ELSE 0 END) AS amt_not_null,
SUM(CASE WHEN id IS NULL THEN amount ELSE 0 END) AS amt_null
FROM test


#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥15❤‍🔥1
👛Особенность CASE WHEN

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

SELECT
num,
CASE
WHEN num < 0 THEN '< 0'
WHEN num < 10 THEN '[0; 10)'
WHEN num < 50 THEN '[10; 50)'
WHEN num < 100 THEN '[50; 100)'
END AS interval
FROM test


Здесь не нужно ограничивать с двух сторон:
WHEN num > 0 AND num < 10 THEN '[0; 10)'

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

#sql_tips
🔥8❤2👏2
⬅️LEFT убивает индексы

Когда стоит вопрос: использовать LEFT или LIKE - надо брать второе. Особенно в базах, которые поддерживают индексацию в LIKE.

💳Пример - есть таблица с номерами карт, этот столбец индексирован, и мы хотим вытащить все MasterCard (начинаются на 5).

Какие есть варианты запроса?
SELECT * FROM accounts
WHERE LEFT(account_num, 1) = '5'

SELECT * FROM accounts
WHERE account_num LIKE '5%'


План запроса:
|--Clustered Index Scan(..., WHERE:(substring(account_num,(1),(1))=N'5'))

|--Clustered Index Seek(..., SEEK:(account_num >= N'5' AND account_num < N'6'), WHERE:(account_num like N'5%') ORDERED FORWARD)


🔫В первом случае мы убиваем навешенный индекс, потому что под капотом лежит Index Scan - вытаскиваем все строки из таблицы и ищем совпадение с подстрокой.

🔎Во втором случае под капотом уже Index Seek - сразу вытаскиваем нужную группу записей, потом фильтруем.

PROFIT👍

Проблема в использовании функций. Поэтому, если возможно, заменяйте функции поверх полей на операторы, чтобы индексы могли нормально работать:
SELECT * FROM documents
WHERE YEAR(valid_from) = 2023 and MONTH(valid_from) = 6
--Index Scan

SELECT * FROM documents
WHERE valid_from BETWEEN '2023-06-01' AND '2023-06-30'
--Index Seek 👍


#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥17👏5
❌CROSS APPLY, OUTER APPLY

❌Я буду говорить про MS SQL Server (потому что очень долго с ним работала), но в Postgres тоже есть похожие конструкции - CROSS JOIN LATERAL, LEFT JOIN LATERAL. Так что с другими бд, я надеюсь, тоже несложно разобраться.

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

1️⃣ CROSS APPLY ~ INNER JOIN

Допустим, есть функция поиска книги по автору:
CREATE FUNCTION getBookByAuthorId(@AuthorId int)
RETURNS TABLE AS RETURN
(
SELECT * FROM book
WHERE author_id = @AuthorId
)


Используем в запросе:
SELECT a.author_name, b.book_name, b.price
FROM author a
CROSS APPLY getBookByAuthorId(a.id) b


2️⃣ OUTER APPLY ~ LEFT JOIN

Ненайденные строчки также заполняются NULL.

В этом случае аналогично подзапросу в селекте:
--outer apply
SELECT a.author_name, book_list
FROM author a
OUTER APPLY (
SELECT STRING_AGG(book_name, ', ') AS book_list
FROM book b
WHERE b.author_id = a.id
) sub

--подзапрос
SELECT a.author_name, (
SELECT STRING_AGG(book_name, ', ')
FROM book b
WHERE b.author_id = a.id
) AS book_list
FROM author a
--STRING_AGG соединяет строки в одну ячейку с разделителем


С агрегирующими функциями прокатит только outer apply, потому что агрегировать поверх подзапроса нельзя. А так можно:
SELECT a.author_name, count(book_id) as book_num
FROM author a
OUTER APPLY (
SELECT b.id as book_id
FROM book b
WHERE b.author_id = a.id
) sub
GROUP BY a.author_name


#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥5👏3❤2
💡Хинт NOLOCK

🌐Если в базу параллельно пишутся данные, а вам надо оттуда читать - nolock в помощь.

Как использовать?
Диалект - MS SQL Server
SELECT *
FROM test (NOLOCK)


🔐Что происходит?
NOLOCK убирает блокировки, связанные с параллельным обращением к одним и тем же данным. Другое название - READ UNCOMMITTED, т.е. нам не нужно ждать, пока транзакция завершится. Но тут возникает вопрос с чтением грязных данных - мы можем работать с данными, которые уже сто раз изменились.

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

#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥8
Как уменьшить размер бд?

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

DBCC SHRINKDATABASE([db_name], 0);  
GO


Диалект - MS SQL Server.
Вторым аргументом указывается процент свободного места для бд в диапазоне 0-100. Чем его больше, тем быстрее происходит вставка данных, есть запас для изменения структуры и наполнения полей и т.д.

➡️Операция сжатия на больших объемах:
- супер долго выполняется
- снижает эффективность индексов и производительность (т.к. под капотом страницы с данными перемещаются)

✏️Но если в бд не пишется много данных или вообще не пишется, а дела с местом на диске прямо критичны, то это как вариант.

#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥6❤1🤔1🤡1
Про SQL Wildcards

Я решила поэкспериментировать с форматом и посмотреть, что из этого выйдет)

⭐Все знают про %, но про остальные я думала, что они существуют в регулярках в программировании, но никак не в эскуэль.

Пока не увидела в коде запросов, например:

--эквивалентны
and Action_Code like 'K[HS]'

and Action_Code = 'KH' or Action_Code = 'KS'

--тоже эквивалентны
and Assignment_Value like '%CRE[12]%'

and Assignment_Value like '%CRE1%' or Assignment_Value like '%CRE2%'


Для любителей почитать вот ссылки на оф. доки:
Postgres
MS SQL Server
MySQL

#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥30⚡2👨‍💻2💯1
Как из деления int/int получить double?

🤓Вам нужно что-то посчитать: поделить одно количество на другое и округлить результат до 2х знаков после запятой.

Так как количество - это целое число, если написать просто count1/count2, то в результате мы получим тоже целое число: 15/26 = 0. А мы хотим 0.57692307692307692308 - вот оно самое.

✨Поэтому умножаем на 1.0, и вуаля✨
SELECT round(count1 * 1.0 / count2, 2)
FROM test


#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥12❤4🌚2
👍Полезные советы

В продолжение поста про
вредные советы есть еще несколько лайфхаков (уже нормальных!):

1️⃣ Бывает полезно сделать такое форматирование, когда мы играемся с полями, чтобы удобнее закомментить ненужные:
SELECT 
e.emp_no
, e.first_name
--, e.last_name
, s.salary
FROM employees e
JOIN salaries s
ON e.emp_no = s.emp_no


2️⃣ Добавлять элиасы таблиц к полям (sql или spark - неважно), чтобы под конец огромного запроса понимать, откуда тянутся данные. То же самое 👆на спарке:
final_df = (
employees.alias('e')
.join(salaries.alias('s'), F.col('e.emp_no') == F.col('s.emp_no'), 'inner')
.select(
'e.emp_no',
'e.first_name',
's.salary'
)
)


3️⃣ А когда мы играемся с условиями, я частенько добавляю 1=1, чтобы тоже быстро закомментить и прогнать:
SELECT *
FROM employees
WHERE 1=1
--AND date_joined <= '2024-01-01'
AND is_deleted = 0
AND username != 'system'

Если бы WHERE date_joined <= '2024-01-01' шло на первом месте, то пришлось бы убирать AND перед вторым условием, удалять лишние пробелы - в общем, неудобно, если часто так делать.

#sql_tips #spark
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥10👏4💯2
Как написать джойн без джойна?

📃Пусть у нас есть таблица с клиентами (client_id, name) и заказами (order_id, amount, client_id).
Нужно вывести имя клиента и сумму всех его заказов. Если нет заказов - то 0.

🌷Подход №1 - join (все очень просто)

SELECT name, SUM(COALESCE(amount, 0)) AS total
FROM clients c
LEFT JOIN orders o
ON c.id = o.client_id
GROUP BY name
ORDER BY name


🌷Подход №2 - union

Когда что-то нужно посчитать

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

WITH grouped AS (
SELECT
client_id AS id,
null AS name,
SUM(amount) AS total
FROM orders
GROUP BY client_id
),
final AS (
SELECT id, name, null AS total FROM clients
UNION
SELECT id, null AS name, total FROM grouped
)
SELECT
MAX(name) AS name,
COALESCE(MAX(total), 0) AS total
FROM final
GROUP BY id
ORDER BY name


На этапе final у нас будет примерно такое:

id name  amount
1 Alice null
2 Bob null
3 Cathy null
1 null 2000
2 null 5000


✏️ На моем крошечном датасете в плане запросов в постгре косты второго запроса меньше в 2 раза.
В первом почти все ресурсы тратятся на Hash Join.

#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥17👍8❤2💯2