Например, про временные таблицы
Это один из способов оптимизации запросов. Если разбить один длинный запрос на несколько временных таблиц, то он будет работать быстрее за счет минимизации повторных вычислений. Например, если нужно переиспользовать результат или если из-за джойнов сильно разрастается количество строк.
В 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
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:
Количество считанных строк - 46 млрд. Время работы - 2,5 минуты. Ну... долго ждать, давайте еще раз внимательно посмотрим🤓
Итерация 2 - вынесли приведение даты со временем к дате в самый конец:
Количество считанных строк - 46 млрд. Время работы - 1,5 минуты. Уже лучше, но можно ли еще быстрее?
Итерация 3 - вынесли приведение unix timestamp к дате со временем:
Количество считанных строк - 25 млн. Время работы - 6 секунд. Супер! Нам этом остановимся👍
В итоге мы сначала находим максимальные значения и только потом кастуем. Выигрыш во времени - в 25 раз.
🕒 Время и строки можно посмотреть в системной табличке
🐢 В первых двух случаях в плане запроса есть шаги:
🐆 В третьем:
👍
#sql_tips
Итерация 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
#sql_tips
У меня часто бывали случаи, когда нужно было переименовать базу данных, но она не переименовывалась. В том проекте базу каждый сам создавал под свою задачу, поэтому операции с ней ни на кого не влияли.
Что нужно сделать?
Диалект - 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
2️⃣ заведомо ложное условие
#sql_tips
Бывают кейсы, когда есть табличка, но нам нужна пустая на ее основе.
Есть два варианта, которые будут работать везде:
SELECT *
INTO new_table
FROM old_table
LIMIT 0
SELECT TOP 0 *
INTO new_table
FROM old_table
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 - для одного столбца
Если разницы для запроса нет, лучше выбирать
Элиасы во втором и последующем запросах можно не указывать, они будут тянуться из первого.
2️⃣ CASE WHEN - для разных столбцов
#sql_tips
Тогда я затупила, но у вас есть шансы пропустить этот этап
Они проверяли знание union и case when:
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, потому что без проверки на дубли он более производительный.Элиасы во втором и последующем запросах можно не указывать, они будут тянуться из первого.
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
Если нам нужно добавить интервалы/категории и т.д., то можно воспользоваться этим примером:
Здесь не нужно ограничивать с двух сторон:
потому что условия проверяются по порядку и после первого попадания дальше сравниваться не будут. Поэтому нужно начинать с самых узких.
#sql_tips
Если нам нужно добавить интервалы/категории и т.д., то можно воспользоваться этим примером:
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 или LIKE - надо брать второе. Особенно в базах, которые поддерживают индексацию в LIKE.Какие есть варианты запроса?
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 JOIN LATERAL, LEFT JOIN LATERAL. Так что с другими бд, я надеюсь, тоже несложно разобраться.В чем суть?
По факту это те же самые джойны, но не с табличкой, а с результатом функции, которая возвращает табличку.
Допустим, есть функция поиска книги по автору:
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
Ненайденные строчки также заполняются 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
Как использовать?
Диалект - MS SQL Server
SELECT *
FROM test (NOLOCK)
NOLOCK убирает блокировки, связанные с параллельным обращением к одним и тем же данным. Другое название - READ UNCOMMITTED, т.е. нам не нужно ждать, пока транзакция завершится. Но тут возникает вопрос с чтением грязных данных - мы можем работать с данными, которые уже сто раз изменились.#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥8
Как уменьшить размер бд?
💿 На одном проекте в разгар сезона часто не хватало места на диске, чтобы создать новую базу данных. В таких крайних случаях можно сжать существующие (редко используемые) базы командой:
Диалект - MS SQL Server.
Вторым аргументом указывается процент свободного места для бд в диапазоне 0-100. Чем его больше, тем быстрее происходит вставка данных, есть запас для изменения структуры и наполнения полей и т.д.
➡️ Операция сжатия на больших объемах:
- супер долго выполняется
- снижает эффективность индексов и производительность (т.к. под капотом страницы с данными перемещаются)
✏️ Но если в бд не пишется много данных или вообще не пишется, а дела с местом на диске прямо критичны, то это как вариант.
#sql_tips
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
Я решила поэкспериментировать с форматом и посмотреть, что из этого выйдет)
⭐ Все знают про %, но про остальные я думала, что они существуют в регулярках в программировании, но никак не в эскуэль.
Пока не увидела в коде запросов, например:
Для любителей почитать вот ссылки на оф. доки:
Postgres
MS SQL Server
MySQL
#sql_tips
Я решила поэкспериментировать с форматом и посмотреть, что из этого выйдет)
Пока не увидела в коде запросов, например:
--эквивалентны
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, и вуаля✨
#sql_tips
Так как количество - это целое число, если написать просто count1/count2, то в результате мы получим тоже целое число: 15/26 = 0. А мы хотим 0.57692307692307692308 - вот оно самое.
SELECT round(count1 * 1.0 / count2, 2)
FROM test
#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥12❤4🌚2
В продолжение поста про вредные советы есть еще несколько лайфхаков (уже нормальных!):
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
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'
)
)
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 (все очень просто)
🌷 Подход №2 - union
Когда что-то нужно посчитать
🟢 группируем отдельно по датасетам
🟢 проставляем null в отсутствующих колонках
🟢 юнионим
🟢 группируем по ключу, если бы мы джойнили
🟢 навешиваем нужные функции сверху
На этапе final у нас будет примерно такое:
✏️ На моем крошечном датасете в плане запросов в постгре косты второго запроса меньше в 2 раза.
В первом почти все ресурсы тратятся на Hash Join.
#sql_tips
Нужно вывести имя клиента и сумму всех его заказов. Если нет заказов - то 0.
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
Когда что-то нужно посчитать
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
В первом почти все ресурсы тратятся на Hash Join.
#sql_tips
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥17👍8❤2💯2