SQL и Анализ данных

Better Database Design 💡

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

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

CREATE SCHEMA Orders;
CREATE SCHEMA Shipping;

-- Orders module can only access its schema
CREATE USER OrdersUser WITH DEFAULT_SCHEMA = Orders;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Orders TO OrdersUser;

-- Shipping module can only access its schema
CREATE USER ShippingUser WITH DEFAULT_SCHEMA = Shipping;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Shipping TO ShippingUser;

⚡ Идея проста:
Создавайте отдельного пользователя для каждой схемы и используйте разные connection string в приложении. Это уменьшает риски и делает архитектуру чище.

Microsoft SQL Interview Question — разбор №2: Early Responders

Задача: найти топ-3 пользователей, которые чаще всего отвечали на сообщения в течение 5 минут в августе 2022.
Нужно вывести их sender_id и число «быстрых ответов».

Данные: таблица messages(message_id, sender_id, receiver_id, content, sent_date).

Подход:
— Фильтруем август полуинтервалом [2022-08-01, 2022-09-01).
— Группируем переписку по паре пользователей (conv_id = min(sender,receiver)–max(sender,receiver)).
— Используем LAG по conv_id, чтобы взять предыдущее сообщение в диалоге.
— Быстрый ответ — когда направление сменилось и прошло ≤ 5 минут.

Решение (T-SQL):

WITH aug AS (
  SELECT message_id, sender_id, receiver_id, sent_date
  FROM   messages
  WHERE  sent_date >= '2022-08-01'
     AND sent_date <  '2022-09-01'
),
conv AS (
  SELECT *,
         CONCAT(
           IIF(sender_id < receiver_id, sender_id, receiver_id), '-',
           IIF(sender_id < receiver_id, receiver_id, sender_id)
         ) AS conv_id
  FROM aug
),
seq AS (
  SELECT *,
         LAG(sender_id) OVER (PARTITION BY conv_id ORDER BY sent_date, message_id) AS prev_sender,
         LAG(sent_date) OVER (PARTITION BY conv_id ORDER BY sent_date, message_id) AS prev_time
  FROM conv
),
fast AS (
  SELECT sender_id
  FROM   seq
  WHERE  prev_sender IS NOT NULL
     AND sender_id <> prev_sender
     AND DATEDIFF(minute, prev_time, sent_date) <= 5
)
SELECT TOP (3)
       sender_id,
       COUNT(*) AS fast_reply_count
FROM   fast
GROUP BY sender_id
ORDER BY COUNT(*) DESC, sender_id;

Вариант с учётом ничьих (возьмёт всех на 1-3 местах):

WITH aug AS (
  SELECT message_id, sender_id, receiver_id, sent_date
  FROM   messages
  WHERE  sent_date >= '2022-08-01'
     AND sent_date <  '2022-09-01'
),
conv AS (
  SELECT *,
         CONCAT(
           IIF(sender_id < receiver_id, sender_id, receiver_id), '-',
           IIF(sender_id < receiver_id, receiver_id, sender_id)
         ) AS conv_id
  FROM aug
),
seq AS (
  SELECT *,
         LAG(sender_id) OVER (PARTITION BY conv_id ORDER BY sent_date, message_id) AS prev_sender,
         LAG(sent_date) OVER (PARTITION BY conv_id ORDER BY sent_date, message_id) AS prev_time
  FROM conv
),
fast AS (
  SELECT sender_id
  FROM   seq
  WHERE  prev_sender IS NOT NULL
     AND sender_id <> prev_sender
     AND DATEDIFF(minute, prev_time, sent_date) <= 5
),
agg AS (
  SELECT sender_id, COUNT(*) AS fast_reply_count
  FROM   fast
  GROUP BY sender_id
),
ranked AS (
  SELECT sender_id, fast_reply_count,
         DENSE_RANK() OVER (ORDER BY fast_reply_count DESC) AS rnk
  FROM   agg
)
SELECT sender_id, fast_reply_count
FROM   ranked
WHERE  rnk <= 3
ORDER BY fast_reply_count DESC, sender_id;

Почему так:
— Диапазон дат без функций сохраняет использование индекса по sent_date.
— LAG по conv_id гарантирует, что сравниваем соседние сообщения в одном диалоге.
— Проверяем смену направления (sender_id ≠ prev_sender) и порог по времени (≤ 5 минут).

💡 SQL: фильтрация по агрегатам с HAVING !!!

Иногда нужно отобрать только те группы, которые удовлетворяют условию по агрегату.
Для этого используется HAVING — он применяется после GROUP BY.

SELECT 
    customer_id,
    COUNT(order_id) AS orders_count,
    SUM(amount)     AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000
   AND COUNT(order_id) >= 5;

🔎 Так можно сразу выбрать клиентов, у которых больше 5 заказов и общая сумма превышает 1000.
HAVING работает с SUM(), COUNT(), AVG(), MAX() и другими агрегатами.

❌ Классический поиск по ключевым словам даёт ограниченные результаты.

В примере выше, такой селективный нашел только одно совпадение: "Machine Learning Overview".

✅ А вот pgvector ищет по смыслу и находит связанные концепции.
Пример запроса возвращает 5 релевантных документов:
– Machine Learning Overview
– Data Mining Basics
– Introduction to AI
– Deep Learning Guide

Семантический поиск > ключевого 🔥

@machinelearning_interview

🦆 Крутой интерактивный тренажёр по SQL

Учитесь писать SQL-запросы через игру:
- Пошаговые уроки с живым редактором — пишешь код и сразу видишь результат.
- Задачи и мини-квесты, где вы помогаете Дакберту пробираться сквозь потоки данных.
- Работает бесплатно, прямо в браузере или на телефоне.

🎮 Попробовать: https://dbquacks.com/

💡 SQL: группировка по неделям

Иногда нужно посчитать статистику не по дням, а по неделям.
Для этого можно использовать функции работы с датами.

-- PostgreSQL
SELECT 
    DATE_TRUNC('week', order_date) AS week_start,
    COUNT(*) AS orders_count
FROM orders
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY week_start;

-- MySQL
SELECT 
    YEARWEEK(order_date, 1) AS year_week,
    COUNT(*) AS orders_count
FROM orders
GROUP BY YEARWEEK(order_date, 1)
ORDER BY year_week;

🔎 Так можно быстро увидеть динамику заказов по неделям.
Аналогично работает и для month, quarter, year.

💡 SQL-трюк: фильтрация по "первому в группе"

Часто нужно взять только первую запись из каждой группы (например, последние покупки клиентов).
Мало кто знает, что это можно сделать без подзапросов с помощью ROW_NUMBER().

SELECT *
FROM (
    SELECT 
        customer_id,
        order_id,
        order_date,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
) t
WHERE rn = 1;

🔎 Здесь ROW_NUMBER() нумерует строки внутри каждой группы customer_id.
rn = 1 оставляет только самую свежую запись по каждому клиенту.

👨‍🔧 CoreNN — база данных для работы с векторами и эмбеддингами в масштабе миллиардов, с сублинейным временем отклика на обычных серверах.

📌 Пример: поиск по 1 млрд эмбеддингов комментариев Reddit из индекса 4,8 ТБ на диске занимает всего 15 мс.

🔧 Ключевые особенности:
- Работает на дешёвых SSD/flash-накопителях, а не в дорогой DRAM → в 40–100 раз дешевле.
- Масштабирование: от 1 до 1 млрд векторов в одном индексе.
- Upsert и delete оптимизируют локальный граф, освобождают место и сохраняют скорость — без полной перестройки.
- Поддерживает параллельные запросы и обновления без блокировок.

🔗 https://github.com/wilsonzlin/CoreNN

🆕 PDF Arranger — лёгкий и удобный инструмент для работы с PDF.

✨ Возможности:
- Объединение и разделение файлов
- Поворот и обрезка страниц
- Перестановка и удаление страниц
- Интуитивный drag-and-drop интерфейс

💻 Доступен для Linux, Windows (включая портативную версию) и BSD.
Полностью опенсорс (GPL-3.0).

Идеален, если нужно быстро подготовить PDF к печати или презентации — без сложных настроек.

📌 GitHub

#PDF #opensource #Linux #devtools

🎮 Учим SQL через захватывающую аркадную игру

Разработчики замутили настоящий олдскульный шедевр, который сделает из вас МАСТЕРА баз данных и точно не даст заскучать.

• Проходим уровни, собираем пазлы вместе с уткой DuckDB и прокачиваем SQL на максимум.
• Квесты, задачи, подсказки — всё как в настоящем приключении.
• Работает прямо в браузере и даже на телефоне.

Любые запросы к базам — щёлкаем как семечки 👉 https://dbquacks.com/.

🚀 dumper — простой CLI для резервного копирования баз данных

Что это?
dumper — это утилита командной строки для создания бэкапов разных СУБД (PostgreSQL, MySQL и др.) с гибкой настройкой подключения и хранения. Инструмент ориентирован на разработчиков и админов, которым нужны быстрые и надёжные бэкапы.

Ключевые возможности:
- Поддержка нескольких СУБД.
- Гибкая конфигурация источников и мест хранения.
- Простота использования прямо из терминала.

Почему стоит обратить внимание:
- Лёгкая альтернатива тяжёлым системам бэкапов.
- Ускоряет рабочий процесс админов и devops.
- Надёжный инструмент для production и pet-проектов.

👉 Репозиторий: github.com/elkirrs/dumper

⚡️ SQL на Python: безопасные IN-списки без лимитов + потоковая выборка

Используй «расширяемые» параметры IN в SQLAlchemy + потоковую выборку — это безопаснее, быстрее и не бьёт по памяти. Вместо склейки списка ID в строку передай Python-список как параметр с expanding=True: драйвер сам подставит нужное число плейсхолдеров и корректно закэширует план. Совмести это с итерацией по результатам (chunks) — и обрабатывай миллионы строк без OOM. Ставь лайк и подпишись на нас, каждый день мы публикуем полезные и не банальные советы для разработчиков.

pip install sqlalchemy pandas
from typing import Iterable, Sequence, Dict, Any
import pandas as pd
from sqlalchemy import create_engine, text, bindparam, tuple_
from sqlalchemy.engine import Engine, Result

# Пример: engine для Postgres/Oracle/MySQL и т.д.
# engine = create_engine("postgresql+psycopg://user:pass@host/db")
# engine = create_engine("oracle+oracledb://user:pass@tnsname")

def stream_query_in(engine: Engine, table: str, id_list: Sequence[int], chunk_rows: int = 50_000) -> Iterable[pd.DataFrame]:
    """
    Потоково выбирает строки по большому списку ID:
    - безопасно подставляет массив в IN (:ids) через expanding=True
    - возвращает пачки DataFrame фиксированного размера (chunk_rows)
    """
    # 1) Готовим SQL с расширяемым параметром
    stm = text(f"SELECT * FROM {table} WHERE id IN :ids").bindparams(
        bindparam("ids", expanding=True)
    )
    with engine.connect() as conn:
        # 2) Выполняем запрос одним списком (драйвер сам развернёт IN)
        result: Result = conn.execution_options(stream_results=True).execute(stm, {"ids": list(id_list)})

        # 3) Потоково собираем фреймы по chunk_rows строк
        batch = []
        for row in result:
            batch.append(dict(row._mapping))
            if len(batch) >= chunk_rows:
                yield pd.DataFrame.from_records(batch)
                batch.clear()
        if batch:
            yield pd.DataFrame.from_records(batch)

def fetch_df_in(engine: Engine, table: str, id_list: Sequence[int]) -> pd.DataFrame:
    """Если нужен единый DataFrame — аккуратно склеиваем поток."""
    frames = list(stream_query_in(engine, table, id_list))
    return pd.concat(frames, ignore_index=True) if frames else pd.DataFrame()

# Пример использования:
# big_ids = range(1, 2_000_000)
# for df_chunk in stream_query_in(engine, "orders", big_ids, chunk_rows=100_000):
#     process(df_chunk)  # обрабатываем пачками без OOM

# Бонус: многоколонночный IN (tuple IN) без ручной генерации SQL
def stream_query_tuple_in(engine: Engine, table: str, pairs: Sequence[Dict[str, Any]]):
    """
    pairs: [{ "country":"US", "city":"NYC" }, ...]
    SELECT * FROM table WHERE (country, city) IN ((:country_1,:city_1), ...)
    """
    stm = text(f"""
        SELECT * FROM {table}
        WHERE (country, city) IN :keys
    """).bindparams(bindparam("keys", expanding=True))

    # Подготовим данные как кортежи
    key_tuples = [(p["country"], p["city"]) for p in pairs]

    with engine.connect() as conn:
        for row in conn.execution_options(stream_results=True).execute(stm, {"keys": key_tuples}):
            yield dict(row._mapping)

# Пример:
# pairs = [{"country":"US","city":"NYC"}, {"country":"JP","city":"TOKYO"}]
# for rec in stream_query_tuple_in(engine, "warehouses", pairs):
#     ...

💡 Полезный хинт для Oracle SQL — ускоряем поиск по последним записям с KEEP (DENSE_RANK ...)

Вместо подзапросов для поиска последнего или первого значения по группе можно использовать аналитическую агрегацию с KEEP (DENSE_RANK ...). Это делает код короче и часто быстрее.

📍 Пример: для каждой акции получить последнюю цену

select symbol,
       max(price) keep (dense_rank last order by ts) as last_price,
       max(ts)    as last_ts
from trades
group by symbol;

📍 Первый тик за день:

select trunc(ts) as trade_day,
       symbol,
       min(price) keep (dense_rank first order by ts) as first_price
from trades
group by trunc(ts), symbol;

⚡ Плюсы:

- Нет лишних джоинов и подзапросов
- Работает прямо в GROUP BY
- Удобно для задач «последнее значение по группе»

🐘 Tarantool — необычная платформа, сочетающая in-memory базу данных с полноценным сервером приложений на Lua. Проект имеет два движка хранения: in-memory с WAL и LSM-дерево, поддерживает ANSI SQL и асинхронную репликацию.

Инструмент имеет встроенный JIT-компилятор LuaJIT, позволяющий исполнять бизнес-логику прямо рядом с данными. При этом сохраняется совместимость с внешними СУБД вроде PostgreSQL через коннекторы. Проект полезен для высоконагруженных веб-сервисов, кэширующих слоёв и систем обработки очередей сообщений.

🤖 GitHub

@databases_tg

👣 Schema — CLI-инструмент для работы с БД (SQLite, libSQL, PostgreSQL, MySQL, MariaDB)
GitHub: gigagrug/schema

Что умеет:
- schema -i — инициализация проекта (по умолчанию SQLite + `./schema/dev.db`) или подключение к выбранной БД
- schema -pull — импорт существующей структуры
- Миграции:
- создание SQL-файлов
- применение (`schema -migrate`)
- выборочное выполнение (`schema -migrate="имя"`)
- Миграции с данными для повторного использования
- Выполнение произвольных SQL прямо в консоли
- Встроенная TUI-студия для интерактивной работы с БД

📌 Почему стоит попробовать:
- Быстрые и наглядные миграции
- Поддержка нескольких СУБД
- Легкое подключение к существующей базе
- Совмещает миграции, генерацию данных и интерактивный интерфейс

📌 Apache-2.0 • ⭐ 22 звезды • 8 релизов • последний — 2 августа 2025

Если нужен удобный CLI для миграций и работы с базами — Schema точно стоит внимания.

👉 Github

🚀 GLM-4.5V — новый лидер в open-source визуальном рассуждении .

Модель показывает лучшие результаты в своём классе среди открытых решений, лидируя на 41 бенчмарке.

📌 Возможности:
- Image Reasoning — понимание изображений, анализ нескольких изображений, распознавание объектов.
- Video Understanding — раскадровка длинных видео, определение событий, которые происходят на кадрах из видео.
- GUI-задачи — понимание интрефейсов, распознавание иконок, кнопок и тд, помощь в управлении рабочим столом.
- Сложный анализ графиков и документов — разбор различных отчётов, извлечение информации их них.
- Grounding — точная локализация элементов на изображениях.

📌 Особенности:
🟠 Основана на GLM-4.5-Air и использует наработки из GLM-4.1V-Thinking.

🟠 Архитектура — MoE с 106B параметров для эффективного масштабирования.

Здесь можно почитать про GLM-4.5, а здесь посмотреть техрепорт, там много интересного.

🟢 Hugging Face: http://huggingface.co/zai-org/GLM-4.5V
🟢 GitHub: http://github.com/zai-org/GLM-V
🟢 Документация API: http://docs.z.ai/guides/vlm/glm-4.5v
🟢 Попробовать: http://chat.z.ai

@ai_machinelearning_big_data

#GLM #opensource #vlm

🖥 Задача (Oracle SQL, продвинутая): управление запасами с MODEL, скользящим спросом и детектом «stockout»

Таблицы:
• items(item_id NUMBER, init_qty NUMBER) — стартовый остаток по товару
• movements(item_id NUMBER, ts DATE, qty NUMBER) — движения: приход (qty>0) и расход (qty<0), событий может быть несколько в день

Нужно:
1) Построить помесячный/подневный календарь по каждому item_id между мин(ts) и max(ts).
2) Посчитать ежедневный итоговый остаток (onhand), применяя суммарные дневные движения к старту, даже если в конкретный день не было событий.
3) Найти «окна нулевого остатка» (stockout): количество, самую длинную протяжённость и дату первого пополнения после самого длинного окна.
4) Спрогнозировать риск обнуления в ближайшие 14 дней при текущем тренде: взять 7-дневное скользящее среднее спроса (по расходам), спроецировать остаток и отметить товары, которые уйдут в ноль.

-- Демоданные
WITH items AS (
  SELECT 101 item_id, 50 init_qty FROM dual UNION ALL
  SELECT 102,  5 FROM dual
),
movements_raw AS (
  SELECT 101 item_id, DATE'2025-08-01' ts,  30 qty FROM dual UNION ALL -- приход
  SELECT 101, DATE'2025-08-02',            -20     FROM dual UNION ALL -- расход
  SELECT 101, DATE'2025-08-04',            -40     FROM dual UNION ALL -- перерасход → stockout
  SELECT 101, DATE'2025-08-06',             60     FROM dual UNION ALL -- пополнение
  SELECT 102, DATE'2025-08-01',            - 2     FROM dual UNION ALL
  SELECT 102, DATE'2025-08-03',            - 3     FROM dual UNION ALL
  SELECT 102, DATE'2025-08-05',              5     FROM dual
),
-- 1) Свернём движения по дням (может быть несколько событий в день)
movements AS (
  SELECT item_id, ts, SUM(qty) qty_per_day
  FROM movements_raw
  GROUP BY item_id, ts
),

-- 1) Календарь на ежедневной сетке для каждого товара
date_bounds AS (
  SELECT item_id,
         LEAST(MIN(ts), TRUNC(SYSDATE)) AS dmin,
         GREATEST(MAX(ts), TRUNC(SYSDATE)) AS dmax
  FROM movements
  GROUP BY item_id
),
calendar AS (
  SELECT b.item_id, (b.dmin + LEVEL - 1) d
  FROM date_bounds b
  CONNECT BY LEVEL <= b.dmax - b.dmin + 1
  AND PRIOR item_id = item_id
  AND PRIOR SYS_GUID() IS NOT NULL
),

-- 2) Посчитаем ежедневный остаток MODEL-ом
onhand AS (
  SELECT item_id, d, onhand, qty_per_day
  FROM (
    SELECT c.item_id,
           c.d,
           NVL(m.qty_per_day, 0) qty_per_day,
           i.init_qty
    FROM calendar c
    JOIN items i USING(item_id)
    LEFT JOIN movements m ON m.item_id = c.item_id AND m.ts = c.d
  )
  MODEL
    PARTITION BY (item_id)
    DIMENSION BY (d)
    MEASURES (init_qty, qty_per_day, CAST(NULL AS NUMBER) AS onhand)
    RULES SEQUENTIAL ORDER (
      onhand[MIN(d)] = init_qty[MIN(d)] + qty_per_day[MIN(d)],
      onhand[FOR d FROM MIN(d)+1 TO MAX(d)] =
        onhand[CV(d)-1] + qty_per_day[CV(d)]
    )
),

-- 3) Найдём окна нулевого остатка и их длины
stockout_runs AS (
  SELECT *
  FROM onhand
  MATCH_RECOGNIZE (
    PARTITION BY item_id
    ORDER BY d
    MEASURES
      FIRST(d) AS start_d,
      LAST(d)  AS end_d,
      COUNT(*) AS days_zero
    ONE ROW PER MATCH
    PATTERN (z+)
    DEFINE
      z AS onhand = 0
  )
),

-- 3) Для самого длинного окна найдём дату первого пополнения после него
longest_zero AS (
  SELECT s.item_id, s.start_d, s.end_d, s.days_zero,
         LEAD(s.end_d) OVER (PARTITION BY s.item_id ORDER BY s.days_zero, s.end_d) dummy
  FROM (
    SELECT s.*, ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY days_zero DESC, end_d) rn
    FROM stock

🔍 DVC — Git для данных и ML-моделей. Этот инструмент делает для данных то же, что Git для кода — позволяет отслеживать изменения, переключаться между версиями и работать в команде без хаоса.

DVC не загружает тяжелые файлы в Git-репозиторий, а хранит их в облаке или локально, записывая только метаданные. Особенно удобна интеграция с ML-пайплайнами: можно настраивать зависимости между этапами обработки данных и обучения, а он будет перезапускать только изменившиеся части. При этом инструмент отлично дополняет MLflow: первый управляет версиями данных, второй — трекит эксперименты.

🤖 GitHub