Преглед: v_driver_deliveries

Датотека	views/04_driver_deliveries_view.sql
Шема	kbnteam
Категорија	Нарачки и Достави
Поврзани индекси	indexes/v_driver_deliveries_index.sql
Сложеност	Висока — 2 CTE + 13 табели

Опис

Прикажува доделување на достави и поврзаниот содржај на нарачките наменет за возачите. Го следи истиот шаблон за агрегирање на ставки (CTE order_meals и order_drinks) како v_orders_full, но е ориентиран кон доставата наместо кон нарачката. Корисен за екранот на возачот во мобилната/веб апликација.

Зависности

Табела	Тип на употреба
kbnteam.delivery	Главна табела
kbnteam.delivery_status	JOIN — статус на достава
kbnteam.driver	LEFT JOIN — возач
kbnteam.api_user	LEFT JOIN (×2) — детали за возач и купувач
kbnteam.restaurant	LEFT JOIN — ресторан на возачот
kbnteam.company_order	LEFT JOIN — компаниска нарачка
kbnteam.company	LEFT JOIN — компанија
kbnteam.customer_order	LEFT JOIN — нарачка
kbnteam.customer	LEFT JOIN — купувач
kbnteam.order_meal	LEFT JOIN (CTE) — оброци
kbnteam.meal	JOIN (CTE) — назив на оброк
kbnteam.order_drink	LEFT JOIN (CTE) — пијачи
kbnteam.drink	JOIN (CTE) — назив на пијач

SQL Дефиниција

CREATE OR REPLACE VIEW kbnteam.v_driver_deliveries AS
WITH order_meals AS (
    SELECT
        x.order_id,
        string_agg(x.meal_name, ', ' ORDER BY x.meal_name) AS meals
    FROM (
        SELECT DISTINCT
            om.order_id,
            m.meal_name
        FROM kbnteam.order_meal om
        JOIN kbnteam.meal m ON m.meal_id = om.meal_id
    ) x
    GROUP BY x.order_id
),
order_drinks AS (
    SELECT
        x.order_id,
        string_agg(x.drink_name, ', ' ORDER BY x.drink_name) AS drinks
    FROM (
        SELECT DISTINCT
            od.order_id,
            d.drink_name
        FROM kbnteam.order_drink od
        JOIN kbnteam.drink d ON d.drink_id = od.drink_id
    ) x
    GROUP BY x.order_id
)
SELECT
    d.delivery_id,
    d.delivery_date,
    d.delivery_notes,
    ds.d_status_name,
    drv.user_id AS driver_user_id,
    du.user_first_name AS driver_first_name,
    du.user_last_name AS driver_last_name,
    du.user_phone_no AS driver_phone,
    r.rest_id,
    r.rest_name,
    co.comp_order_id,
    cmp.company_id,
    cmp.company_name,
    o.order_id,
    o.order_datetime,
    o.order_total,
    cu.user_id AS customer_user_id,
    au.user_first_name AS customer_first_name,
    au.user_last_name AS customer_last_name,
    au.user_email AS customer_email,
    COALESCE(om.meals, '') AS meals,
    COALESCE(od.drinks, '') AS drinks
FROM kbnteam.delivery d
JOIN kbnteam.delivery_status ds ON ds.d_status_id = d.d_status_id
LEFT JOIN kbnteam.driver drv ON drv.user_id = d.driver_user_id
LEFT JOIN kbnteam.api_user du ON du.user_id = drv.user_id
LEFT JOIN kbnteam.restaurant r ON r.rest_id = drv.rest_id
LEFT JOIN kbnteam.company_order co ON co.delivery_id = d.delivery_id
LEFT JOIN kbnteam.company cmp ON cmp.company_id = co.company_id
LEFT JOIN kbnteam.customer_order o ON o.comp_order_id = co.comp_order_id
LEFT JOIN kbnteam.customer cu ON cu.user_id = o.customer_user_id
LEFT JOIN kbnteam.api_user au ON au.user_id = cu.user_id
LEFT JOIN order_meals om ON om.order_id = o.order_id
LEFT JOIN order_drinks od ON od.order_id = o.order_id;

Тестирање на перформанси

Препорачано тест прашање

SET search_path TO kbnteam;
SET statement_timeout = '60s';

-- Тест 1: по возач и датум на достава
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM kbnteam.v_driver_deliveries
WHERE driver_user_id = 1
  AND delivery_date >= CURRENT_DATE - INTERVAL '30 days';

-- Тест 2: по компаниска нарачка
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM kbnteam.v_driver_deliveries
WHERE comp_order_id = 1;

Резултати пред индексирање

Метрика	Тест 1 (возач + датум)	Тест 2 (comp_order)
Planning Time	_ ms	_ ms
Execution Time	_ ms	_ ms
Rows Returned	_	_
delivery scan	_	_
order_meal scan	_	_

-- Излезот од EXPLAIN ANALYZE овде (пред индексирање)

Применети индекси

-- indexes/v_driver_deliveries_index.sql
CREATE INDEX IF NOT EXISTS idx_delivery_driver_user_id_delivery_date
ON kbnteam.delivery (driver_user_id, delivery_date);

CREATE INDEX IF NOT EXISTS idx_order_meal_order_id_meal_id
ON kbnteam.order_meal (order_id, meal_id);

CREATE INDEX IF NOT EXISTS idx_order_drink_order_id_drink_id
ON kbnteam.order_drink (order_id, drink_id);

CREATE INDEX IF NOT EXISTS idx_customer_order_comp_order_id
ON kbnteam.customer_order (comp_order_id);

Резултати по индексирање

Метрика	Тест 1 (возач + датум)	Тест 2 (comp_order)
Planning Time	_ ms	_ ms
Execution Time	_ ms	_ ms
Rows Returned	_	_
delivery scan	_	_
order_meal scan	_	_

-- Излезот од EXPLAIN ANALYZE овде (по индексирање)

Анализа на подобрување

Индекс	Помага на	Очекувана промена
idx_delivery_driver_user_id_delivery_date	Тест 1 — филтрирање по возач и датум	Seq Scan → Index Scan
idx_customer_order_comp_order_id	Тест 2 и JOIN со company_order	Seq Scan → Index Scan
idx_order_meal_order_id_meal_id	CTE агрегација	Seq Scan → Index Scan
idx_order_drink_order_id_drink_id	CTE агрегација	Seq Scan → Index Scan

Метрика	Пред	По	Δ Подобрување
Execution Time (Тест 1)	_ ms	_ ms	_ %
Execution Time (Тест 2)	_ ms	_ ms	_ %