Колонни и времеви бази

Накратко: Колонната база пази всяка колона отделно, за да чете бързо отчети върху милиони редове, а базата за времеви редове пази измервания с час, като тези на сензор.

Проблем

Отчетът иска две колони, а чете седем

Таблицата sales от предишния урок има седем колони: поръчка, ден, град, категория, продукт, количество и сума. Отчетът за оборота по месеци иска две от тях, day и amount.

Обикновената база пази данните по редове: всичко за една продажба е заедно на диска. За да прочете две колони, тя чете целите редове и изхвърля останалите пет. При милиард продажби това са стотици гигабайти, от които трябват малко над една четвърт.

SQLбаза: магазин

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT COUNT(*) AS rows, COUNT(DISTINCT city) AS cities, COUNT(DISTINCT category) AS categories FROM sales;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT COUNT(*) AS rows, COUNT(DISTINCT city) AS cities, COUNT(DISTINCT category) AS categories FROM sales;

▶ ИзпълниВърни заявкатаНова база

Идеята

Всяка колона отделно

Колонната база (ClickHouse, BigQuery, Snowflake, DuckDB) пази всяка колона в отделен файл. Отчетът за оборота отваря само файловете на day и amount. А колоната съдържа еднакъв вид стойности една до друга (само дати, само градове), и затова се свива много по-добре от ред, в който са смесени.

Най-простото свиване е серия: вместо „София, София, София…“ пишеш „София × 50“. Цялата колона city, подредена по град, е седем серии:

SQLбаза: магазин

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT city, COUNT(*) AS repeated
FROM sales
GROUP BY city
ORDER BY city;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT city, COUNT(*) AS repeated FROM sales GROUP BY city ORDER BY city;

▶ ИзпълниВърни заявкатаНова база

Работи само ако еднаквите стойности са една до друга. Ето колко серии има колоната, когато редовете са по реда на поръчките, и колко, когато са подредени по град:

SQLбаза: магазин

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT COUNT(*) AS runs
FROM (
  SELECT city, LAG(city, 1, '-') OVER (ORDER BY order_id, product) AS before
  FROM sales
) AS t
WHERE before IS NOT city;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT COUNT(*) AS runs FROM ( SELECT city, LAG(city, 1, '-') OVER (ORDER BY order_id, product) AS before FROM sales ) AS t WHERE before IS NOT city;

▶ ИзпълниВърни заявкатаНова база

SQLбаза: магазин

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT COUNT(*) AS runs
FROM (
  SELECT city, LAG(city, 1, '-') OVER (ORDER BY city, order_id, product) AS before
  FROM sales
) AS t
WHERE before IS NOT city;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT COUNT(*) AS runs FROM ( SELECT city, LAG(city, 1, '-') OVER (ORDER BY city, order_id, product) AS before FROM sales ) AS t WHERE before IS NOT city;

▶ ИзпълниВърни заявкатаНова база

От 128 стойности остават 39 серии, ако редовете не са подредени, и 7, ако са. Затова в ClickHouse всяка таблица се прави с ORDER BY. Редът на колоните в него е редът на подреждане на диска.

По-точно, за любопитните

LAG(city, 1, '-') дава града от предишния ред (а за първия ред '-'), и всеки ред, в който градът се сменя, започва нова серия. IS NOT вместо <> е, защото NULL <> NULL не е истина, а NULL IS NOT NULL е ясно „не“. Истинските колонни бази ползват и други начини за свиване: речник (градовете стават числа 1–7), разлики между съседни числа, и общ алгоритъм отгоре (LZ4, ZSTD). Свиване 10 пъти не е рядкост.

Идеята

Кога колонна база, кога не

Колонната база е отлична за отчети: малко колони, много редове, суми и групи. Тя е лоша за касата. Една нова поръчка значи запис в седем отделни файла, а UPDATE на един ред често значи да се пренапише цяло парче от колоната. Затова колонните бази обичат да пишат на големи пакети (хиляди реда наведнъж) и рядко да променят. Това е разделението OLTP и OLAP от предишния урок, само че на диска.

Проблем

Витрината с млечните

Хладилната витрина с млечните продукти има сензор, който на всеки 15 минути записва температурата. Над 8 °C сиренето и млякото бързо се развалят, и магазинът трябва да знае кога е станало. Такива данни са времеви ред: стойности, подредени по време, които само се добавят и почти никога не се променят. Два дни мерения:

SQLбаза: магазин

SELECT * FROM fridge ORDER BY at LIMIT 5;

SELECT * FROM fridge ORDER BY at LIMIT 5;

▶ ИзпълниВърни заявкатаНова база

SQLбаза: магазин

SELECT COUNT(*) AS readings, MIN(at) AS first, MAX(at) AS last FROM fridge;

SELECT COUNT(*) AS readings, MIN(at) AS first, MAX(at) AS last FROM fridge;

▶ ИзпълниВърни заявкатаНова база

Два дни по 96 мерения би трябвало да са 192, а са 184. Осем липсват, и ще видиш къде.

Идеята

Сваляне до часове

Никой не гледа 35 000 точки на графика за година. Мерения се групират по час (или ден) с няколко числа за всеки: средна, най-ниска и най-висока. Това е сваляне (downsampling). Часът е първите 13 знака от времето:

SQLбаза: магазин

SELECT substr(at, 1, 13) || ':00' AS hour,
       ROUND(AVG(temp), 1) AS avg_temp,
       MAX(temp) AS max_temp
FROM fridge
WHERE at >= '2026-10-24 12:00' AND at < '2026-10-24 18:00'
GROUP BY hour
ORDER BY hour;

SELECT substr(at, 1, 13) || ':00' AS hour, ROUND(AVG(temp), 1) AS avg_temp, MAX(temp) AS max_temp FROM fridge WHERE at >= '2026-10-24 12:00' AND at < '2026-10-24 18:00' GROUP BY hour ORDER BY hour;

▶ ИзпълниВърни заявкатаНова база

Средната за 14:00 е 8.9 °C, а най-високата е 9.7 °C. Вратата е стояла отворена.

Пробвай

Кога е алармата

Покажи часовете, в които поне едно мерене е над 8 °C (колони hour и max_temp, най-високата температура в часа), по ред на часа.

SQLбаза: магазин

SELECT substr(at, 1, 13) || ':00' AS hour FROM fridge LIMIT 5;

SELECT substr(at, 1, 13) || ':00' AS hour FROM fridge LIMIT 5;

Провери▶ Изпълни

Идеята

Плъзгаща средна

Сензорите понякога дават отделна грешна стойност, а витрината се отваря за секунди при всеки клиент. Затова алармата често гледа не едно мерене, а средната от последните четири (последния час). Тя се плъзга с всеки нов ред: прозоречна функция с ROWS BETWEEN 3 PRECEDING AND CURRENT ROW:

SQLбаза: магазин

SELECT *
FROM (
  SELECT at, temp,
         ROUND(AVG(temp) OVER (ORDER BY at ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 2) AS last_hour
  FROM fridge
) AS t
WHERE at BETWEEN '2026-10-24 13:30' AND '2026-10-24 15:15'
ORDER BY at;

SELECT * FROM ( SELECT at, temp, ROUND(AVG(temp) OVER (ORDER BY at ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 2) AS last_hour FROM fridge ) AS t WHERE at BETWEEN '2026-10-24 13:30' AND '2026-10-24 15:15' ORDER BY at;

▶ ИзпълниВърни заявкатаНова база

Средната е над 8 °C чак в 14:45, когато вратата е отворена от час. Едно случайно високо мерене би я вдигнало с около 1 °C, но не над 8.

Прозоречната функция е във вътрешната заявка, а периодът е отвън. WHERE се изпълнява преди прозореца: ако условието за периода е в същата заявка, средната за 13:30 не вижда 12:45, 13:00 и 13:15 и е само от едно мерене.

Пробвай

Дупката

Сензорът мери на 15 минути. Намери къде е изгубил връзка: последното мерене преди дупката и първото след нея (колони last_before и first_after).

SQLбаза: магазин

SELECT at, LAG(at) OVER (ORDER BY at) AS before FROM fridge LIMIT 5;

SELECT at, LAG(at) OVER (ORDER BY at) AS before FROM fridge LIMIT 5;

Провери▶ Изпълни

Идеята

Срок за пазене

Суровите мерения растат с 35 000 реда на година за един сензор, а магазинът има стотици. Обичайното е таблица по часове, която се пази дълго, и сурови мерения, които се пазят кратко (тук за един ден). Таблицата по часове се прави като в склада за данни, с CREATE TABLE … AS SELECT:

SQLбаза: магазин

CREATE TABLE fridge_hourly AS
SELECT substr(at, 1, 13) || ':00' AS hour,
       ROUND(AVG(temp), 2) AS avg_temp, MIN(temp) AS min_temp, MAX(temp) AS max_temp,
       COUNT(*) AS readings
FROM fridge
GROUP BY hour;

SELECT * FROM fridge_hourly WHERE hour BETWEEN '2026-10-25 02:00' AND '2026-10-25 05:00';

CREATE TABLE fridge_hourly AS SELECT substr(at, 1, 13) || ':00' AS hour, ROUND(AVG(temp), 2) AS avg_temp, MIN(temp) AS min_temp, MAX(temp) AS max_temp, COUNT(*) AS readings FROM fridge GROUP BY hour; SELECT * FROM fridge_hourly WHERE hour BETWEEN '2026-10-25 02:00' AND '2026-10-25 05:00';

▶ ИзпълниВърни заявкатаНова база

Часовете 03:00 и 04:00 ги няма и в нея: от тях няма нито едно мерене. Графиката трябва да покаже празно място, а не да свърже 02:00 и 05:00 с права линия, сякаш всичко е било наред.

Упражнение

Чистене и отчет

1. Таблицата по часове вече е направена.

Изтрий суровите мерения отпреди 25 октомври 2026. Таблицата fridge_hourly остава цяла.

SQLбаза: магазин

SELECT COUNT(*) FROM fridge;

SELECT COUNT(*) FROM fridge;

Провери▶ Изпълни

2. Отчет за деня.

За всеки ден: най-ниската, най-високата и средната температура (закръглена до 0.01) и броя мерения (колони day, min_temp, max_temp, avg_temp, readings), по ред на деня.

SQLбаза: магазин

SELECT substr(at, 1, 10) AS day FROM fridge LIMIT 3;

SELECT substr(at, 1, 10) AS day FROM fridge LIMIT 3;

Провери▶ Изпълни

Идеята

Бази за времеви редове

За времеви редове има и отделни бази: TimescaleDB (разширение на PostgreSQL, което сам дели таблицата по време и сам сваля до часове), InfluxDB и Prometheus (за метрики на сървъри). Всички правят същото като в урока: подреждат по време, свиват колоните, пазят свалени таблици и трият сурови данни след срок. Колонните ClickHouse и DuckDB също се справят добре с тях.

По-точно, за любопитните

В TimescaleDB таблица по часове, която се обновява сама, е CREATE MATERIALIZED VIEW … WITH (timescaledb.continuous) AS SELECT time_bucket('1 hour', at) …, а срокът е SELECT add_retention_policy('fridge', INTERVAL '1 day'). В SQLite няма такова: таблицата по часове и триенето се пускат по график от програмата.

Тест

Провери се

1. Отчетът сумира колоната amount в таблица със 7 колони и 100 милиона реда. Колко от данните чете колонна база?

Само първия редСамо колоната amount (и колоните от WHERE)Всичките 7 колони

2. Защо колонните бази подреждат редовете (ORDER BY в CREATE TABLE на ClickHouse)?

За да е красивоЗащото без това не работятЕднаквите стойности застават една до друга и колоната се свива до малко серии

3. Сензорът мери на 15 минути, а графиката за година трябва да е бърза. Какво се прави обикновено?

Графиката чете всички меренияМерят се по-рядкоПази се таблица по часове (или дни) с AVG, MIN и MAX, а суровите мерения се трият след срок

4. Плъзгащата средна за последния час е в подзаявка, а WHERE за периода е отвън. Защо не е в същата заявка?

Няма разликаЗащото SQLite не позволяваWHERE се изпълнява преди прозоречната функция и първите редове от периода биха загубили миналия си час

Провери

Започни урока отначало

Свързани уроци

• Нужно отпреди: CREATE TABLE: нова таблица · Естествен ключ и денормализация · Индекси · OLTP и OLAP
• Продължава в: Как се избира база
• Задачи по темата: Видове бази данни