Декларативне SQLScript: SELECT, JOIN, CTE

Декларативне SQLScript

Декларативне SQLScript — частина мови, у якій програміст описує що має бути обчислено, а HANA сама вирішує як. На основі коду будується data flow graph, оптимізатор паралелізує обчислення, обрізає непотрібні гілки, кешує спільні підвирази. Імперативні конструкції (цикли, IF) ламають цю оптимізацію — тому, де можливо, тримайся декларативного стилю.

Дозволені у декларативному коді тільки три речі:

• SELECT-statement-и
• виклики декларативних процедур і функцій (без DML, без циклів)
• виклики plan-операторів CE-engine (MAP_MERGE, MAP_REDUCE)

Здається, обмаль — але SELECT покриває переважну частину задач.

Table variables

Локальні табличні змінні всередині блоку. Тип може виводитися автоматично:

DO BEGIN
   -- 1) Тип з присвоєння SELECT
   lt_status = SELECT * FROM status;

   -- 2) Тип з вихідного параметра процедури
   CALL statustexts (iv_langu => 'EN', et_result => lt_text);

   -- 3) Явна декларація через TABLE
   DECLARE lt_x TABLE (id INT, val NVARCHAR(50));

   -- 4) Декларація з типу таблиці БД
   DECLARE lt_t TABLE LIKE tasks;

   SELECT * FROM :lt_status;
   SELECT * FROM :lt_text;
END;

При читанні — префікс : (SELECT FROM :lt_x), при записі — без (lt_x = SELECT ...).

⚠️ Грабля: table variable ≠ table в БД

Table variable існує тільки всередині блоку. У SQL Console напряму не використовуєш — тільки в анонімному блоці (DO BEGIN ... END) або всередині процедури/функції.

SELECT — структура запиту

SELECT <field-list>
   FROM <data-source>
   [WHERE <condition>]
   [GROUP BY <columns>]
   [HAVING <condition>]
   [ORDER BY <sorting>]
   [LIMIT <n> [OFFSET <m>]];

Усі клаузули, крім SELECT і FROM, опціональні. У SQLScript можна навіть FROM опускати: SELECT CURRENT_DATE; працює.

Field list

SELECT
   id,                                        -- проста колонка
   firstname || ' ' || lastname AS name,      -- вираз з alias
   CASE sex WHEN 'F' THEN 'Жін.'
            WHEN 'M' THEN 'Чол.'
            ELSE '' END AS sex_text,          -- CASE
   COALESCE(team, 0) AS team,                 -- SQL function
   (SELECT COUNT(*) FROM tasks t              -- скалярний підзапит
       WHERE t.assignee = u.id) AS task_cnt
FROM users u;

AS опціональне, але читається краще з ним. У AMDP — alias-и обовʼязкові скрізь, де результат повертається в ABAP-структуру.

Aggregate expressions

SELECT status,
       COUNT(*)        AS cnt,
       SUM(effort)     AS total_effort,
       AVG(effort)     AS avg_effort,
       MIN(due_date)   AS earliest,
       MAX(due_date)   AS latest,
       STRING_AGG(title, ', ') AS titles  -- конкатенація рядків
FROM tasks
GROUP BY status;

Якщо у field-list є хоч одна aggregate-функція, всі неагреговані колонки мають бути в GROUP BY. Без GROUP BY агрегація йде по всьому набору — повертається один рядок.

Window functions

Те саме, що aggregate — але без скорочення кількості рядків. Для кожного рядка обчислюється значення з оточуючої “вікна”:

SELECT id,
       status,
       COUNT(*)   OVER (PARTITION BY status) AS status_cnt,
       SUM(effort) OVER (PARTITION BY assignee
                         ORDER BY due_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                  AS running_total,
       ROW_NUMBER() OVER (ORDER BY due_date) AS rn,
       RANK()       OVER (PARTITION BY assignee ORDER BY effort DESC) AS rk
FROM tasks;

Корисні window-функції: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTILE.

FROM clause

Джерелом може бути:

• таблиця БД
• view або CDS view
• table variable (FROM :lt_x)
• table parameter
• table UDF (FROM udf_xxx(args))
• підзапит (FROM (SELECT ...) AS x)
• DUMMY

SELECT *
FROM tasks AS t,                            -- alias таблиці
     udf_users('EN') AS u,                  -- виклик UDF
     ( SELECT id FROM projects ) AS p,      -- inline subquery
     :lt_local AS l;                        -- table variable

Joins

SELECT t.id, t.title, u.firstname, p.name
FROM tasks AS t
   INNER JOIN users    AS u ON u.id = t.assignee
   LEFT  OUTER JOIN projects AS p ON p.id = t.project
   RIGHT OUTER JOIN deps     AS d ON d.task_id = t.id
   FULL  OUTER JOIN logs     AS l ON l.task_id = t.id
   CROSS JOIN config         AS c;            -- декартів добуток

Тип	Поведінка
INNER JOIN	Тільки рядки з матчем у обох таблицях
LEFT OUTER JOIN	Усі рядки лівої + матчі правої (NULL якщо нема)
RIGHT OUTER JOIN	Дзеркальний LEFT
FULL OUTER JOIN	Усі з обох сторін, NULL де нема матчу
CROSS JOIN	Кожен з кожним (без ON)

Equi-join — порівняння на рівність (a.id = b.id). Виконується значно швидше за non-equi/theta join (<, >, BETWEEN).

⚠️ Грабля: LEFT JOIN + WHERE

LEFT JOIN + умова WHERE right.field = 'X' неявно перетворює його в INNER JOIN — рядки без матчу відфільтровуються (бо NULL = 'X' дає UNKNOWN). Якщо хочеш зберегти рядки без матчу — переноси умову в ON або додавай OR right.field IS NULL.

WHERE

Деталі — на сторінці Умови WHERE. Коротко:

SELECT * FROM tasks
WHERE status IN (1, 2, 3)
  AND due_date BETWEEN '2024-01-01' AND '2024-12-31'
  AND title LIKE '%review%'
  AND assignee IS NOT NULL
  AND EXISTS (SELECT 1 FROM users WHERE id = tasks.assignee);

GROUP BY

Стандартна агрегація:

SELECT status, assignee, COUNT(*) AS cnt
FROM tasks
GROUP BY status, assignee;

GROUPING SETS

Кілька рівнів групування в одному запиті — корисно для звітів з тоталами:

SELECT assignee, status, COUNT(*) AS cnt
FROM tasks
GROUP BY GROUPING SETS (
   (status, assignee),     -- по парах
   (assignee),             -- тоталь по виконавцю
   ()                      -- загальний тоталь
)
ORDER BY assignee, status NULLS LAST;

Замість GROUPING SETS для типових патернів є скорочення:

• ROLLUP(a, b, c) = GROUPING SETS ((a,b,c), (a,b), (a), ())
• CUBE(a, b) = усі підмножини (4 для 2 колонок)

HAVING

Фільтр після групування. На відміну від WHERE, дозволяє звертатися до результатів aggregate-функцій:

SELECT assignee, COUNT(*) AS cnt
FROM tasks
GROUP BY assignee
HAVING COUNT(*) > 5;

ORDER BY + LIMIT + OFFSET

SELECT *
FROM tasks
ORDER BY due_date DESC NULLS LAST,
         status ASC
LIMIT 20 OFFSET 40;       -- сторінка 3 при page size 20

NULLS FIRST / NULLS LAST — куди ставити NULL-и в сортуванні.

WITH (CTE)

Common Table Expression — іменований підзапит, що використовується тільки в одному SELECT. Корисно, коли той самий підзапит потрібен кілька разів:

WITH lt_overdue AS (
        SELECT a.id, b.firstname || ' ' || b.lastname AS assignee,
               a.title, a.due_date, a.status, a.effort
        FROM tasks AS a
        INNER JOIN users AS b ON a.assignee = b.id
        WHERE a.status NOT IN (5, 6)
     )

SELECT 'Overdue' AS reason, *
FROM lt_overdue
WHERE due_date < CURRENT_DATE

UNION ALL

SELECT 'Check estimate' AS reason, *
FROM lt_overdue
WHERE planned_effort <= effort * 1.1;

В SQLScript WITH рідко потрібний — для тих самих задач використовують table variables. Але table variable — окремий statement, тому потребує блоку. CTE — компактніший варіант для одного запиту.

Set-операції

SELECT carrid FROM tab1
UNION       -- обʼєднання, без дублікатів
SELECT carrid FROM tab2;

SELECT carrid FROM tab1
UNION ALL   -- обʼєднання, з дублікатами (швидше)
SELECT carrid FROM tab2;

SELECT carrid FROM tab1
INTERSECT   -- перетин
SELECT carrid FROM tab2;

SELECT carrid FROM tab1
EXCEPT      -- різниця (рядки tab1, яких нема в tab2)
SELECT carrid FROM tab2;

Альтернативи INTERSECT / EXCEPT:

-- INTERSECT через INNER JOIN
SELECT DISTINCT t.assignee
FROM tasks AS t
INNER JOIN projects AS p ON t.assignee = p.project_manager;

-- EXCEPT через NOT EXISTS
SELECT DISTINCT t.assignee
FROM tasks AS t
WHERE NOT EXISTS (
   SELECT 1 FROM projects AS p WHERE p.project_manager = t.assignee
);

Підзапити

Скалярний — повертає одне значення, використовується в SELECT/WHERE:

SELECT id, title,
       (SELECT COUNT(*) FROM logs WHERE task_id = t.id) AS log_cnt
FROM tasks t
WHERE effort > (SELECT AVG(effort) FROM tasks);

Табличний — у FROM, як inline view:

SELECT * FROM (
   SELECT assignee, COUNT(*) AS cnt FROM tasks GROUP BY assignee
) AS x
WHERE cnt > 10;

Корельований — посилається на колонки зовнішнього запиту (як t.id у попередньому прикладі). Виконується для кожного рядка — обережно з продуктивністю на великих таблицях.

⚠️ Грабля: складний підзапит — у table variable

Глибоко вкладені підзапити погано читаються і важко тестуються. Виноси у table variable — і код стане зрозумілим, і кожний крок можна перевірити окремим SELECT-ом.

Alias-и

Псевдоніми для колонок і таблиць — AS опціональне, але рекомендоване:

SELECT t.id   AS task_id,
       t.title AS task_title,
       a.firstname || ' ' || a.lastname AS assignee_name,
       p.firstname || ' ' || p.lastname AS pm_name
FROM tasks   AS t
INNER JOIN users AS a ON a.id = t.assignee
INNER JOIN users AS p ON p.id = t.project_manager;

Без alias-ів users зустрівся б двічі і колонки стали неоднозначні. Alias-и обовʼязкові й коли результат йде в ABAP (через AMDP) і структура очікує конкретних імен полів.

MAP_MERGE

Виклик function-and-flatten: викликаємо table-функцію для кожного рядка вхідної таблиці і конкатенуємо результати через UNION ALL. Альтернатива циклу — виконується паралельно:

-- Функція: для одного інтервалу повертає всі дати в ньому
CREATE FUNCTION udf_days (iv_id INT, iv_from DATE, iv_to DATE)
RETURNS TABLE (id INT, dt DATE)
AS BEGIN
   RETURN SELECT :iv_id AS id,
                 GENERATED_PERIOD_START AS dt
          FROM SERIES_GENERATE_DATE(
                 INCREMENT_BY => 'INTERVAL 1 DAY',
                 MIN_VALUE    => :iv_from,
                 MAX_VALUE    => ADD_DAYS(:iv_to, 1));
END;

DO BEGIN
   lt_intervals = SELECT * FROM intervals;
   lt_dates     = MAP_MERGE(:lt_intervals,
                            udf_days(:lt_intervals.id,
                                     :lt_intervals.from_date,
                                     :lt_intervals.to_date));
   SELECT * FROM :lt_dates;
END;

Замість виклику UDF у циклі (імперативно) — MAP_MERGE виконує паралельно і повертає одну розгорнуту таблицю.

MAP_REDUCE

MAP_MERGE + редукція по ключу. Map-функція повертає проміжні результати, reduce-функція аґрегує їх по ключових групах:

MAP_REDUCE(<input-table>,
           <map-function>,     -- викликається для кожного рядка
           <reduce-function>); -- викликається для кожної унікальної ключової групи

Етапи:

1. Вхідні дані — <input-table>.
2. <map-function> викликається для кожного рядка.
3. Результати обʼєднуються UNION ALL у проміжну таблицю.
4. <reduce-function> викликається для кожної унікальної ключової комбінації.
5. Результати обʼєднуються UNION ALL у фінал.

Класичний паттерн для розпаралелювання обчислень над великими наборами даних. Map і reduce можуть містити імперативну логіку — і вона все одно паралелізується.

MAP_MERGE і MAP_REDUCE — єдині імперативні функції, дозволені в декларативному коді, бо HANA знає, як їх паралелізувати.

Коли імперативне ламає декларативність

Декларативна процедура/функція позначається як READ-ONLY. Якщо в ній зʼявляється:

• цикл (FOR, WHILE)
• курсор
• IF/ELSE навколо цілих statement-ів
• DML (INSERT, UPDATE, DELETE)
• виклик процедури з DML

— стиль стає імперативним і HANA не може застосувати декларативну оптимізацію. Деталі — на сторінці Імперативне SQLScript.

Правило: для типового SELECT-pipeline (filter → join → aggregate → result) тримай усе декларативним. Імперативні конструкції тільки якщо без них дійсно не обійтись.

Що далі

• Типи даних і функції SQLScript — рядкові, дата-час, числові, конвертації
• Запис у БД (CRUD) — INSERT, UPDATE, MERGE, DELETE
• Імперативне SQLScript — змінні, цикли, курсори, винятки
• Основи мови SQLScript — синтаксис, процедури, функції