= Преглед: v_contracts_revenue =

||= Својство ||= Вредност ||
|| Шема || `kbnteam` ||
|| Категорија || Договори и приходи ||
|| Поврзани индекси || [wiki:ContractsRevenueIndex Индекси за договори и приходи] ||

== Опис ==

За секој договор ги прикажува бројот на компаниски и клиентски нарачки и вкупниот приход за парот компанија–ресторан.

Статистиката ги опфаќа сите нарачки, независно од датумот и статусот на договорот или нарачката. Целиот износ на нарачката се брои кај секој вклучен ресторан. Договорите за истиот пар компанија–ресторан ја прикажуваат истата статистика.

Еден ред за секој договор. Нарачките без јадења и пијалоци немаат поврзан ресторан и не влегуваат во статистиката. Договорите без соодветни нарачки имаат нулти броеви и приход.

== Зависности ==

||= Табела / преглед ||= Употреба ||
|| `kbnteam.order_meal` || Јадења вклучени во нарачките. ||
|| `kbnteam.meal` || Јадења, цени и поврзаност со категорија и ресторан. ||
|| `kbnteam.order_drink` || Пијалоци вклучени во нарачките. ||
|| `kbnteam.drink` || Пијалоци, количини, цени и ресторан. ||
|| `kbnteam.customer_order` || Клиентски нарачки, датуми, статуси и износи. ||
|| `kbnteam.company_order` || Поврзување на компаниските нарачки со компанијата и доставата. ||
|| `kbnteam.contract` || Договори меѓу компаниите и рестораните. ||
|| `kbnteam.company` || Податоци за компаниите. ||
|| `kbnteam.restaurant` || Податоци за рестораните. ||
|| `kbnteam.contract_status` || Називи на статусите на договорите. ||

== Излезни колони ==

||= Колона ||= Извор / пресметка ||= Опис ||
|| `contract_id` || `contract.contract_id` || Идентификатор на договорот. ||
|| `company_id` || `company.company_id` || Идентификатор на компанијата. ||
|| `company_name` || `company.company_name` || Назив на компанијата. ||
|| `rest_id` || `restaurant.rest_id` || Идентификатор на ресторанот. ||
|| `rest_name` || `restaurant.rest_name` || Назив на ресторанот. ||
|| `contract_status_name` || `contract_status.contract_status_name` || Статус на договорот. ||
|| `contract_start_date` || `contract.contract_start_date` || Почетен датум на договорот. ||
|| `contract_end_date` || `contract.contract_end_date` || Краен датум на договорот. ||
|| `company_order_count` || `COALESCE(contract_metrics.company_order_count, 0)` || Број на различни компаниски нарачки. ||
|| `customer_order_count` || `COALESCE(contract_metrics.customer_order_count, 0)` || Број на клиентски нарачки. ||
|| `total_revenue` || `COALESCE(contract_metrics.total_revenue, 0)::numeric(14,2)` || Збир на износите за парот компанија–ресторан. ||

== SQL дефиниција ==

{{{
#!sql
CREATE OR REPLACE VIEW kbnteam.v_contracts_revenue AS
WITH order_restaurants AS (
    SELECT
        om.order_id,
        m.rest_id
    FROM kbnteam.order_meal om
    JOIN kbnteam.meal m ON m.meal_id = om.meal_id

    UNION

    SELECT
        od.order_id,
        d.rest_id
    FROM kbnteam.order_drink od
    JOIN kbnteam.drink d ON d.drink_id = od.drink_id
),
contract_metrics AS (
    SELECT
        co.company_id,
        orr.rest_id,
        COUNT(DISTINCT co.comp_order_id) AS company_order_count,
        COUNT(*) AS customer_order_count,
        SUM(o.order_total)::numeric(14,2) AS total_revenue
    FROM kbnteam.customer_order o
    JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id
    JOIN order_restaurants orr ON orr.order_id = o.order_id
    GROUP BY co.company_id, orr.rest_id
)
SELECT
    ct.contract_id,
    cmp.company_id,
    cmp.company_name,
    r.rest_id,
    r.rest_name,
    cs.contract_status_name,
    ct.contract_start_date,
    ct.contract_end_date,
    COALESCE(cm.company_order_count, 0) AS company_order_count,
    COALESCE(cm.customer_order_count, 0) AS customer_order_count,
    COALESCE(cm.total_revenue, 0)::numeric(14,2) AS total_revenue
FROM kbnteam.contract ct
JOIN kbnteam.company cmp ON cmp.company_id = ct.company_id
JOIN kbnteam.restaurant r ON r.rest_id = ct.rest_id
JOIN kbnteam.contract_status cs ON cs.contract_status_id = ct.contract_status_id
LEFT JOIN contract_metrics cm
    ON cm.company_id = ct.company_id
   AND cm.rest_id = ct.rest_id;
}}}

== Индексна поддршка ==

`contract(company_id, rest_id)` поддржува и услов само по company_id. Индексот со rest_id како прва колона ја поддржува другата насока на пребарување.

||= Индекс ||= Табела и колони ||= Намена ||
|| `idx_company_order_company_id` || `company_order(company_id)` || Пронаоѓање компаниски нарачки по компанија. ||
|| `idx_customer_order_comp_order_id` || `customer_order(comp_order_id)` || Пронаоѓање клиентски нарачки по компаниска нарачка. ||
|| `idx_order_meal_order_id_meal_id` || `order_meal(order_id, meal_id)` || Пристап до јадењата на нарачката и групирање по order_id. ||
|| `idx_order_drink_order_id_drink_id` || `order_drink(order_id, drink_id)` || Пристап до пијалоците на нарачката и групирање по order_id. ||
|| `idx_contract_company_id_rest_id` || `contract(company_id, rest_id)` || Пребарување договори по компанија и ресторан и групирање по компанија. ||
|| `idx_contract_rest_id_company_id` || `contract(rest_id, company_id)` || Пронаоѓање договори почнувајќи од ресторан. ||

{{{
#!sql
CREATE INDEX IF NOT EXISTS idx_company_order_company_id
ON kbnteam.company_order (company_id);

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

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_contract_company_id_rest_id
ON kbnteam.contract (company_id, rest_id);

CREATE INDEX IF NOT EXISTS idx_contract_rest_id_company_id
ON kbnteam.contract (rest_id, company_id);
}}}

=== Проверка на индексите ===

{{{
#!sql
SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'kbnteam'
  AND indexname IN (
    'idx_company_order_company_id',
    'idx_customer_order_comp_order_id',
    'idx_order_meal_order_id_meal_id',
    'idx_order_drink_order_id_drink_id',
    'idx_contract_company_id_rest_id',
    'idx_contract_rest_id_company_id'
)
ORDER BY tablename, indexname;
}}}

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

=== SELECT ===

Мерењата се евидентирани во DBeaver. Времињата се во милисекунди (ms).

{{{
#!sql
SELECT * FROM kbnteam.v_contracts_revenue
WHERE company_id = 250;
}}}

||= Метрика ||= Пред индексирање ||= По индексирање ||
|| Клиентско време (ms) || 2555 || 1966 ||
|| Вратени редови || 0 || 0 ||

Измереното време е пократко за 589 ms, односно 23,1%. Двете извршувања враќаат нула редови, па резултатот се однесува на овој конкретен филтер.

=== INSERT и UPDATE ===

Тестирани се по еден INSERT и еден UPDATE врз `kbnteam.contract`, главната табела на погледот. Се тестира додавање договор и продолжување на неговиот рок.

||= Операција ||= Execute (ms) ||= Fetch (ms) ||= Вратени редови ||
|| INSERT || 14 || 13 || 1 ||
|| UPDATE || 13 || 14 || 1 ||

Execute е времето на извршување прикажано во DBeaver. Fetch вредностите се преземени од дополнетата евиденција на тестирањето. Ова се клиентски времиња, изразени во милисекунди.

Двете операции се успешни и враќаат по еден ред преку RETURNING. По мерењето промените се вратени со ROLLBACK.

INSERT и UPDATE се извршуваат со прифатлива брзина. Овие мерења ја опишуваат тестираната состојба на базата и не се споредба пред и по индексирање.