Data Science. SQL hub

💨10 практик по именованию таблиц и полей в БД Если бы каждый разработчик и аналитик соблюдал простые правила, реверс-инжиниринг стал бы приятным развлечением, а не трудоемкой работой. Чтобы облегчить работу с БД себе и коллегам, попробуйте эти простые правила: 1. Разделять слова подчеркиванием, если имя атрибута или таблицы БД состоит из 2-х и более слов. Это понятнее стиля camelCase, улучшает читаемость и снижает зависимость от платформы. Например, word_count. 2. Называть таблицы и столбцы полным и семантически понятными именами без привязки к типам данных. Экономия пары символов не даст ничего, кроме путаницы. Допустимо применять сокращения только там, где это всем известная аббревиатура. 3. Писать название атрибута со строчной буквы, чтобы избежать путаницы с ключевыми словами SQL в верхнем регистре. Это также повысит скорость набора текста. 4. Не использовать цифры в названии таблиц и столбцов. 5. Называть таблицы понятно, но кратко. 6. Называть таблицы и столбцы в единственном числе. Например, author вместо authors 7. Называть таблицы-связки в алфавитном порядке. Например, author_book 8. При создании индекса называть его по имени таблицы и столбца. Например, CREATE INDEX person_ix_first_name_last_name ON person (first_name, last_name); 9. Для столбцов типа Boolean к имени добавлять префикс is_ или has_ . Например, is_admin или has_membership. 10. Для столбцов типа Date-Time к имени добавлять суффикс _at или _time. Например, ordered_at или order_time. @sqlhub

🖥 Оператор SQL IN на понятных примерах Оператор SQL IN является одним из наиболее полезных операторов для фильтрации данных в SQL. Он позволяет выбрать строки из таблицы, которые содержат одно или несколько значений, указанных в списке. Синтаксис SQL IN Синтаксис оператора SQL IN очень прост и понятен. Он выглядит следующим образом: SELECT column1, column2, ... FROM table_name WHERE column_name IN (value1, value2, ...); где column1, column2, ... — названия столбцов таблицы, которые нужно выбрать, table_name — название таблицы, column_name — название столбца, по которому нужно выполнить фильтрацию, а value1, value2, ... — значения, которые нужно найти. Примеры использования SQL IN Для лучшего понимания работы оператора SQL IN, рассмотрим несколько примеров. Допустим, у нас есть таблица employees с полями id, name, department и salary. Мы хотим выбрать всех сотрудников из отдела sales и marketing. Мы можем сделать это, используя оператор SQL IN следующим образом: SELECT * FROM employees WHERE department IN ('sales', 'marketing'); Этот запрос вернет все строки из таблицы employees, где значение столбца department равно sales или marketing. Оператор SQL IN также может быть использован с подзапросами. К примеру, мы можем выбрать всех сотрудников, чья зарплата выше среднего значения по всей таблице: SELECT * FROM employees WHERE salary IN (SELECT AVG(salary) FROM employees); Этот запрос выберет все строки из таблицы employees, где значение столбца salary равно средней зарплате по всей таблице «employees». @sqlhub

PostgreSQL под капотом PostgreSQL — одна из наиболее популярных баз данных среди разработчиков. Интересно, из чего же она состоит? Данная серия статей разбирает PostgreSQL на уровне кода: Часть 1: Введение Часть 2: Цикл сервера Часть 3: Подготовка бэкэнда Часть 4: Инициализация бэкэнда Часть 5: Цикл бэкэнда #sql #c #бд

🖥 MySQL ускорение SQL запросов Ускорение SQL запросов в MySQL может быть достигнуто следующими способами: 1. Индексы: использование индексов может ускорить поиск и сортировку данных в ваших таблицах. 2. Оптимизация структуры таблиц: важно убедиться, что структура таблиц оптимизирована для выполнения запросов. 3. Оптимизация запросов: проверьте ваши запросы на оптимизацию, убедитесь, что вы используете правильные операторы JOIN и индексы для улучшения производительности. 4. Кэширование данных: использование кэширования данных может ускорить выполнение повторяющихся запросов. 5. Использование представлений: использование представлений может упростить запросы и улучшить их читаемость. 6. Ограничение размера выборки: используйте оператор LIMIT, чтобы выбрать только необходимые данные, это уменьшит время выполнения запроса. 7. Минимизация дубликатов данных: дубликаты данных могут увеличить размер таблицы и уменьшить производительность запросов. Удаляйте дубликаты данных или используйте оптимизированные структуры данных, такие как нормализованные таблицы. 8. Оптимизация памяти: оптимизируйте использование памяти вашей базы данных, чтобы уменьшить время обработки запросов. 9. Мониторинг производительности: важен для определения причин низкой производительности и для поиска способов ее улучшения. Он включает в себя слежение за показателями, такими как загруженность процессора, использование памяти, время ответа на запросы и т. д. 10. Использование индексов: используйте индексы, чтобы ускорить поиск данных в таблице. Обеспечьте, чтобы ваши индексы были актуальными и эффективными. 11. Оптимизация объединений: используйте оптимальные методы объединения, такие как внутреннее или внешнее объединение, чтобы ускорить выполнение запросов. 12. Использование хранимых процедур: хранимые процедуры могут ускорить выполнение повторяющихся запросов. 13. Использование кеширования: используйте кеширование, чтобы ускорить выполнение запросов и уменьшить нагрузку на базу данных. 14. Оптимизация конфигурации сервера: оптимизируйте конфигурацию сервера, такую как количество памяти и число потоков, чтобы улучшить производительность базы данных. 15. Оптимизация структуры таблиц: периодически оценивайте структуру таблиц и выполняйте необходимые изменения, чтобы улучшить производительность. 16. Оптимизация запросов: проверяйте и оптимизируйте свои SQL-запросы, чтобы улучшить их производительность. 17. Ограничение данных: ограничивайте количество возвращаемых данных, чтобы улучшить производительность. 18. Мониторинг производительности: мониторинг производительности поможет вам выявить проблемы и найти способы их устранения. 19. Обновление ПО: регулярно обновляйте ПО, используемое вашей базой данных, чтобы воспользоваться последними улучшениями производительности. 20. Использование индексов: правильное использование индексов может существенно улучшить производительность SQL-запросов. Обратите внимание, что нет единого решения для улучшения производительности SQL-запросов, и каждый случай может быть уникален. Важно понимать причины низкой производительности и применять соответствующие техники для улучшения. @sqlhub

🖥 Как откатить часть транзакции внутри функции или процедуры? На SQL вы можете сделать так: BEGIN; INSERT INTO table1 VALUES (1); SAVEPOINT my_savepoint; INSERT INTO table1 VALUES (2); ROLLBACK TO SAVEPOINT my_savepoint; --rollback previous command INSERT INTO table1 VALUES (3); COMMIT; Внутри функции или процедуры код выше завершится с ошибкой, например в is_sql.sql. Но вы можете откатить часть SQL команд в транзакции через подтранзакции: DO $TEST$ BEGIN -- here you can write DDL commands, for example, adding or deleting a table or its section -- and/or -- here you can write DML commands that modify data in tables and, thus, check the operation of triggers -- rollback all test queries raise exception using errcode = 'query_canceled'; EXCEPTION WHEN query_canceled THEN --don't do anything END $TEST$; #postgre @sqlhub

Приглашаем на вебинар о нетипичных сценариях использования Yandex Data Transfer 14 марта, 12:00–13:00 На мероприятии разберём: 🔸 какие задачи можно решить с помощью Yandex Data Transfer; 🔸 как построить пайплайны внутри платформы данных и между сервисами облака; 🔸 какие особенности трансферов стоит учесть в работе. Мы продемонстрируем сценарии для CDC, поставки из очередей и загрузки данных в витрины, которые вы сможете реализовать самостоятельно. Вас также ждёт Q&A-сессия. Присылайте вопросы в чат трансляции — спикер ответит на них в прямом эфире. Участие бесплатное, нужно только зарегистрироваться ➡️

🖥 Структура sql-запросов Общая структура запроса выглядит следующим образом: SELECT ('столбцы или * для выбора всех столбцов; обязательно') FROM ('таблица; обязательно') WHERE ('условие/фильтрация, например, city = 'Moscow'; необязательно') GROUP BY ('столбец, по которому хотим сгруппировать данные; необязательно') HAVING ('условие/фильтрация на уровне сгруппированных данных; необязательно') ORDER BY ('столбец, по которому хотим отсортировать вывод; необязательно') SELECT, FROM SELECT, FROM — обязательные элементы запроса, которые определяют выбранные столбцы, их порядок и источник данных. Выбрать все (обозначается как *) из таблицы Customers: SELECT * FROM Customers Выбрать столбцы CustomerID, CustomerName из таблицы Customers: SELECT CustomerID, CustomerName FROM Customers WHERE WHERE — необязательный элемент запроса, который используется, когда нужно отфильтровать данные по нужному условию. Очень часто внутри элемента where используются IN / NOT IN для фильтрации столбца по нескольким значениям, AND / OR для фильтрации таблицы по нескольким столбцам. Фильтрация по одному условию и одному значению: select * from Customers WHERE City = 'London' Фильтрация по одному условию и нескольким значениям с применением IN (включение) или NOT IN (исключение): select * from Customers where City IN ('London', 'Berlin') select * from Customers where City NOT IN ('Madrid', 'Berlin','Bern') Фильтрация по нескольким условиям с применением AND (выполняются все условия) или OR (выполняется хотя бы одно условие) и нескольким значениям: select * from Customers where Country = 'Germany' AND City not in ('Berlin', 'Aachen') AND CustomerID > 15 select * from Customers where City in ('London', 'Berlin') OR CustomerID > 4 GROUP BY GROUP BY — необязательный элемент запроса, с помощью которого можно задать агрегацию по нужному столбцу (например, если нужно узнать какое количество клиентов живет в каждом из городов). При использовании GROUP BY обязательно: перечень столбцов, по которым делается разрез, был одинаковым внутри SELECT и внутри GROUP BY, агрегатные функции (SUM, AVG, COUNT, MAX, MIN) должны быть также указаны внутри SELECT с указанием столбца, к которому такая функция применяется. Группировка количества клиентов по городу: select City, count(CustomerID) from Customers GROUP BY City Группировка количества клиентов по стране и городу: select Country, City, count(CustomerID) from Customers GROUP BY Country, City Группировка продаж по ID товара с разными агрегатными функциями: количество заказов с данным товаром и количество проданных штук товара: select ProductID, COUNT(OrderID), SUM(Quantity) from OrderDetails GROUP BY ProductID Группировка продаж с фильтрацией исходной таблицы. В данном случае на выходе будет таблица с количеством клиентов по городам Германии: select City, count(CustomerID) from Customers WHERE Country = 'Germany' GROUP BY City Переименование столбца с агрегацией с помощью оператора AS. По умолчанию название столбца с агрегацией равно примененной агрегатной функции, что далее может быть не очень удобно для восприятия. select City, count(CustomerID) AS Number_of_clients from Customers group by City @sqlhub

🖥 SQL запрос SELECT: значение и примеры SELECT SQL — оператор запроса, который возвращающает определённый набор данных из базы данных. Список столбцов выборки задаётся в части оператора, которая называется предложением оператора SELECT. Синтаксис SELECT SQL SELECT column1, column2, ... FROM table_name; Здесь column1, column2, … — это имена полей таблицы, из которой выбираются данные. Если вы хотите выбрать все поля, доступные в таблице, используйте следующий синтаксис: SELECT * FROM table_name; Примеры использования SELECT SQL Если у вас есть таблица employees со столбцами id, name, age, department, salary, вы можете выбрать только имена и возраст всех сотрудников следующим образом: SELECT name, age FROM employees; Чтобы выбрать все столбцы из таблицы, вы можете использовать символ *: SELECT * FROM employees; Вы также можете использовать оператор WHERE для фильтрации данных по определенным условиям. Например, чтобы выбрать только имена и возраст сотрудников младше 30 лет, используйте такой запрос: SELECT name, age FROM employees WHERE age < 30; Кроме того, есть ORDER BY для сортировки результатов по определённому столбцу. Например, можно отсортировать сотрудников по возрасту в порядке убывания: SELECT * FROM employees ORDER BY age DESC; SQL SELECT может использоваться для выполнения вычислительных операций над столбцами данных, например, для вычисления общей суммы или среднего значения. Чтобы вычислить среднюю зарплату всех сотрудников, напишем такой запрос: SELECT AVG(salary) FROM employees; Все эти примеры демонстрируют основы оператора SELECT в SQL. Также вы можете почитать о других основных командах SQL. @sqlhub

🖥 Оператор LIKE в SQL: примеры и синтаксис LIKE SQL — это оператор, который используется для поиска строк, содержащих определённый шаблон символов. Синтаксис LIKE SQL Использовать оператор LIKE в SQL очень просто: достаточно прописать стандартную команду выбора SELECT * FROM и задать условие через WHERE, где и будет использован оператор LIKE. Шаблон LIKE SQL: SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern; Существует два подстановочных знака, которые используются в сочетании с оператором LIKE: % — знак процента представляет собой ноль, один или несколько символов; _ — подчёркивание представляет собой один символ. Примеры использования LIKE SQL Представим, что вы хотите найти все имена, начинающиеся с буквы J. Для этого достаточно использовать следующий запрос: SELECT * FROM table_name WHERE name LIKE 'J%'; В данном случае символ % используется для указания любого количества символов после J. Таким образом, запрос найдёт все имена, которые начинаются с буквы J, независимо от того, какие символы следуют за ней. Ещё один пример — поиск всех адресов электронной почты, содержащих слово gmail. Для этого можно использовать следующий запрос: SELECT * FROM table_name WHERE email LIKE '%gmail%'; Здесь символы % используются для указания, что слово gmail может быть в любом месте в адресе электронной почты. Также можно использовать символ _ для указания одного символа. Например, запрос ниже найдет все имена, состоящие из шести символов. Эти имена должны начинаться с буквы J и заканчиваться буквой n: SELECT * FROM table_name WHERE name LIKE 'J____n'; Здесь каждый символ _ указывает на любой один символ. Иногда символы % и _ сами могут быть частью искомой строки. В таких случаях их нужно экранировать. Например, запрос ниже найдет все имена, содержащие символ %: SELECT * FROM table_name WHERE name LIKE '%\%%'; @sqlhub

🔎 13 Инструкций SQL для решения 90% ваших задач по обработке данных Независимо от того, являетесь ли вы новичком в SQL или имеете некоторый опыт работы с ним, эта статья предоставит вам ценную информацию для подготовки к интервью и практические советы по работе с данными . ▪ Статья @sqlhub

27 февраля стартовал финал совместного профиля «Искусственный интеллект» НТО и Академии искусственного интеллекта для школьников БФ Сбербанка «Вклад в будущее» 📌 3 дня продлится командная работа над финальной задачей. Sber AI подготовил задание по разработке алгоритма, который анализирует видео и отвечает на вопросы по его содержанию. Такое решение позволит ускорить анализ видеоинформации, а также проводить эффективный поиск по видеопоследовательностям. 📌Подробности по ссылке: https://www.ng.ru/news/760842.html По итогам командам-победителям вручат приглашения на оплачиваемую стажировку в Сбер и компании-партнеры по направлению «Машинное обучение». Призовой фонд превышает 4 млн рублей

🖥 Задание. Самые заказываемые пары товаров Проанализируйте поведение покупателей и определите, какие продукты часто покупаются вместе. Напишите запрос, который возвращает пары товаров, которые чаще всего приобретались вместе, а также сколько раз эти товары были приобретены вместе. Не включайте пары одного и того же продукта или пары, которые уже были перечислены (например, если вы перечисляете пару продуктов A и B, не перечисляйте также пару продуктов B и A). Пример результирующей таблицы: | id1 | id2 | count | |-----|-----|-------| | 17 | 63 | 4 | | 38 | 40 | 3 | | 49 | 72 | 3 | | 2 | 88 | 2 | Столбцы в результате ▪id1 - id первого товара из пары ▪id2 - id второго товара ▪count - количество раз, когда эту пару товаров заказывали в одном заказе Пишите свое решение в комментариях👇 @sqlhub

Аналитика будущего доступная сегодня Какой профит можно получить от сочетания BI, Process Mining, Task Mining и Automation в одной системе? Все ответы вы получите на вебинаре «Развитие системы активной бизнес-аналитики Proceset», который состоится 2 марта в 16:00. Спикеры: Александр Бочкин, генеральный директор Инфомаксимум и эксперт в Process Mining, Task Mining и Business Intelligence; Виталий Гудулин, руководитель департамента развития продуктов Инфомаксимум. Программа вебинара: Архитектура системы активной бизнес-аналитики Proceset Как в системе работает связка технологий Business Intelligence – Process Mining – Task Mining – Automation. Дашборд за 10 минут Self-service в деле: в режиме реального времени покажем, как быстро и просто построить любой дашборд без навыков программирования и привлечения сторонних специалистов. Обновления Proceset Кратко про основные нововведения и улучшения системы за последние полгода. Сессия «Вопрос – ответ» Все интересующие вопросы можно направить как заранее в форме, так и задавать по ходу мероприятия. Регистрируйтесь здесь

🖥 Задача Существует банковская система, где клиент может совершать транзакции и устанавливать лимит на свой аккаунт. Также клиент может сам устанавливать себе лимит, пример: 01.01.2023 клиент установил лимит в 1000руб. 02.01.2023 совершил транзакцию на 900руб, остаток лимита составляет 100руб, 03.01.2023 совершает транзакцию на 500руб, остаток лимита равен -400, соответственно лимит превышен(limit_exceed = true). 10.01.2023 клиент устанавливает лимит в 2000руб, остаток лимита = 1600, лимит не превышен. Решение select * from solva.transactions tsolva.transactions t left join( select * ,lead(setting_date,1,current_date) over(partition by user_account order by setting_date) - interval 'days 1' to_date from limits ) l on l.user_account = t.account_from and t.date_time between l.setting_date and l.to_date where t.limit_exceeded = true --теперь не нужно and t.account_from = l.user_account and t.date_time between :startdate and :enddate; Пишите свое решение в комментариях👇 @sqlhub

🖥 Третий анализ по количеству продаж Дана cлудующая структура таблиц. Задание Вывести третий анализ по количеству продаж за весь период. Столбцы в результате ▪an_id - ID анализа ▪an_name - название анализа ▪cnt - количество продаж ▪rn - ранг анализа в зависимости от продаж Важно: Обратите внимание, что название столбцов в вашем ответе должно в точности совпадать с условием. Пишите свое решение в комментариях👇 @sqlhub

У Нетологии появился тренажёр по программированию. Теперь часть практических задач можно выполнять, не выходя из личного кабинета. Тренажёр подсказывает, где студент совершил ошибку, а если ему понадобилась помощь — выдаёт подсказки и помогает связаться с преподавателем. Подобные инструменты образовательной среды очень важны для обучения, потому что помогают студенту быстрее изучать материал, при этом преподаватель всегда на связи и готов дать развёрнутую обратную связь. Внутри тренажёра можно даже подключаться к базам данных и загружать несколько файлов сразу — система позволяет полностью проверить готовый код на работоспособность. Например, если вы хотите использовать Python для работы с данными. Попробовать новый формат обучения студенты могут на курсах Нетологии по программированию → https://netolo.gy/be3x, а на курсе «Python для анализа данных» тренажер позволяет решать задачи как по Python, так и по SQL →https://netolo.gy/be3r Наглядно увидеть, как работает тренажёр, вы можете в прикреплённом видео. Реклама. ООО «Нетология»

🖥 Автоматизация выполнения запросов в SQL с помощью Python Основная идея работы скрипта – генерация запросов с учетом различных параметров и последовательное их выполнение для вставки данных в существующую таблицу или выгрузка данных в файл. Сама программа состоит из 3 частей: 1) Соединение c базой данных. 2)Определение варьируемых параметров. 3)Выполнение запросов к базе (структура построения запросов позволяет выполнять запросы последовательно или параллельно, что позволяет управлять скоростью загрузки/выгрузки данных с сервера). Соединение с БД определяется фабрикой, в которой содержатся параметры соединения с определенным сервером и определены ссылки на классы для работы с БД. db = DatabaseFactory().build('*наименование сервера*') Сами объекты для работы с БД содержат 3 метода: ▪collect– запускает запрос с помощью метода read_sql библиотеки pandas и возвращает DataFrame, содержащий результат выполненного запроса; ▪execute– запускает запросы типа CREATE, UPDATE, DELETE\TRUNCATE\DROP; ▪execute_many – используется в основном для загрузки данных внутрь БД. Сама загрузка производится с помощью BULK вставки. db.collect('select top 100 * from table') db.execute('insert into table select * from another_table') db. execute_many ('insert into from table (id, name, age) values (?,?,?)', [1,’Jhon’, 25]) Далее пользователь может задать параметры запроса с помощью метода add_var класса SqlContext. Данный метод принимает 4 параметра: наименование колонки, значения данной переменной, условие (=, <=, >=, between и т.п.) и разделитель (под разделителем понимаются команды AND и OR). context = SqlContext() context.add_var('col_name’, [1,2,3,4,5], separator='AND', condition='=') context.add_var('col_name_1’, [[‘a’,’b’,’v’], [‘a1’,’b2’,’v3’],] , separator='AND', condition='in') В случае определения нескольких параметров одновременно, в запросе они будут варьироваться по следующему правилу: сначала варьируются те параметры, которые были заданы в последнюю очередь. Если все вариации последнего параметра будут пройдены, то берутся следующее значения параметра выше и вновь перебираются все вариации последнего параметра. Так продолжается до тех пор, пока не переберутся все возможные комбинации заданных параметров. После того, как мы определили варьируемые параметры необходимо задать сам sql запрос. Для этого создаем объект SqlBuilder и вызываем метод custom_sql внутрь которого помещаем сам запрос: builder = SqlBuilder() builder.custom_sql(''' INSERT INTO insertable_table SELECT * FROM table WHERE 1=1 AND col1 in (1, 2,10,98,34) AND col2 = 9 AND col3 between ‘20200101’ and ‘20200201’ ''') или можно воспользоваться встроенными в объект методами для генерации sql (select, insert_into, create_table и т.д.): builder = SqlBuilder() builder.select([‘col1’, ‘col2’, ‘col3’]).from(‘table’) Для запуска скрипта необходимо создать объект класса SqlGenerator, объекты SqlBuilder и SqlContext и с помощью цикла запустить обработку запроса (в качестве примера был взят вариант последовательного исполнения запроса): generator = SqlGenerator(builder, context) for sql in tqdm(generator.generate()): t = time.time() db.execute(sql) print('Итоговое время работы запроса: ' + str(time.time()-t)) В итоге данный скрипт позволяет значительно сократить трудозатраты и время на выполнение рутинных запросов, чем я неоднократно пользовался в своей работе. Весь исходный код опубликован на github. @sqlhub