Преглед: v_customer_loyalty_full_v2

Својство	Вредност
Шема	kbnteam
Категорија	Лојалност на клиентите
Поврзани индекси	Индекси за лојалност на клиентите

Опис

Ги прикажува клиентот, компанијата, поените, статусот и нивото на лојалност, бројот на нарачки, вкупната потрошувачка и датумот на последната нарачка.

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

Еден ред за секој член на програмата за лојалност. Кај член без нарачки бројот и потрошувачката се нула, а последната нарачка е NULL.

Зависности

Табела / преглед	Употреба
kbnteam.customer_order	Клиентски нарачки, датуми, статуси и износи.
kbnteam.customer_loyalty	Членство и поени во програмата за лојалност.
kbnteam.customer	Клиенти и компаниите на кои припаѓаат.
kbnteam.company	Податоци за компаниите.
kbnteam.api_user	Лични и контактни податоци за корисниците.
kbnteam.customer_loyalty_status	Статуси на членството.
kbnteam.loyalty_tier	Нивоа и поволности во програмата за лојалност.

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

Колона	Извор / пресметка	Опис
cus_loyalty_id	customer_loyalty.cus_loyalty_id	Идентификатор на членството.
customer_user_id	customer_loyalty.user_id	Идентификатор на клиентот.
company_id	customer.company_id	Идентификатор на компанијата.
company_name	company.company_name	Назив на компанијата.
user_first_name	api_user.user_first_name	Име на клиентот.
user_last_name	api_user.user_last_name	Презиме на клиентот.
user_email	api_user.user_email	Е-пошта на клиентот.
user_phone_no	api_user.user_phone_no	Телефон на клиентот.
cus_loyalty_curr_points	customer_loyalty.cus_loyalty_curr_points	Тековни поени за лојалност.
cus_loyalty_joined_at	customer_loyalty.cus_loyalty_joined_at	Датум и време на зачленување.
cus_loyalty_status_id	customer_loyalty_status.cus_loyalty_status_id	Идентификатор на статусот на членството.
cus_loyalty_status_name	customer_loyalty_status.cus_loyalty_status_name	Назив на статусот на членството.
tier_id	loyalty_tier.tier_id	Идентификатор на нивото.
tier_name	loyalty_tier.tier_name	Назив на нивото.
tier_discount_percentage	loyalty_tier.tier_discount_percentage	Процент на попуст.
tier_free_delivery_eligibility	loyalty_tier.tier_free_delivery_eligibility	Право на бесплатна достава.
tier_priority_support	loyalty_tier.tier_priority_support	Право на приоритетна поддршка.
order_count	COALESCE(order_stats.order_count, 0)	Вкупен број на клиентски нарачки.
total_spent	COALESCE(order_stats.total_spent, 0)::numeric(14,2)	Збир на износите на клиентските нарачки.
last_order_at	order_stats.last_order_at	Датум и време на последната нарачка.

SQL дефиниција

CREATE OR REPLACE VIEW kbnteam.v_customer_loyalty_full_v2 AS
WITH order_stats AS (
    SELECT
        o.customer_user_id,
        COUNT(*) AS order_count,
        SUM(o.order_total)::numeric(14,2) AS total_spent,
        MAX(o.order_datetime) AS last_order_at
    FROM kbnteam.customer_order o
    GROUP BY o.customer_user_id
)
SELECT
    cl.cus_loyalty_id,
    cl.user_id AS customer_user_id,
    cu.company_id,
    cmp.company_name,
    au.user_first_name,
    au.user_last_name,
    au.user_email,
    au.user_phone_no,
    cl.cus_loyalty_curr_points,
    cl.cus_loyalty_joined_at,
    cls.cus_loyalty_status_id,
    cls.cus_loyalty_status_name,
    lt.tier_id,
    lt.tier_name,
    lt.tier_discount_percentage,
    lt.tier_free_delivery_eligibility,
    lt.tier_priority_support,
    COALESCE(os.order_count, 0) AS order_count,
    COALESCE(os.total_spent, 0)::numeric(14,2) AS total_spent,
    os.last_order_at
FROM kbnteam.customer_loyalty cl
JOIN kbnteam.customer cu ON cu.user_id = cl.user_id
JOIN kbnteam.company cmp ON cmp.company_id = cu.company_id
JOIN kbnteam.api_user au ON au.user_id = cl.user_id
JOIN kbnteam.customer_loyalty_status cls ON cls.cus_loyalty_status_id = cl.cus_loyalty_status_id
JOIN kbnteam.loyalty_tier lt ON lt.tier_id = cl.tier_id
LEFT JOIN order_stats os ON os.customer_user_id = cl.user_id;

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

Примерите користат илустративни идентификатори и датуми. За споредливи мерења се избираат вредности што постојат во базата и се задржуваат истите податоци, филтри, услови за кеширање и број на повторувања. Статистиките за табелите треба да бидат ажурирани во двете состојби.

Тест прашалници

BEGIN READ ONLY;
SET LOCAL statement_timeout = '60s';

-- Тест 1: по компанија
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM kbnteam.v_customer_loyalty_full_v2
WHERE company_id = 1;

-- Тест 2: по ниво
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM kbnteam.v_customer_loyalty_full_v2
WHERE tier_id = 1;

-- Тест 3: по клиент
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM kbnteam.v_customer_loyalty_full_v2
WHERE customer_user_id = 1;

ROLLBACK;

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

Основната состојба ги задржува индексите на примарните клучеви и уникатните ограничувања. Останатите присутни индекси се евидентираат одделно. При споредба на заеднички индекси се користи истата почетна состојба за секој преглед.

Метрика	Тест 1 (по компанија)	Тест 2 (по ниво)	Тест 3 (по клиент)
Planning Time	_ ms	_ ms	_ ms
Execution Time	_ ms	_ ms	_ ms
Rows Returned	_	_	_
Начин на читање по табела	_	_	_
Shared Hit Blocks	_	_	_
Shared Read Blocks	_	_	_

-- Планови и резултати пред дополнителното индексирање:

Дополнителни и заеднички индекси

customer_loyalty(user_id) има уникатен индекс. Индексот по клиент и датум не го содржи order_total, па пресметката на вкупната потрошувачка и натаму бара читање на износите.

Индекс	Табела и колони	Намена
idx_customer_order_customer_user_id_order_datetime	customer_order(customer_user_id, order_datetime DESC)	Пронаоѓање нарачки по клиент; поддршка за пристап по датум во рамки на клиентот.
idx_customer_company_id	customer(company_id)	Пронаоѓање клиенти по компанијата на која припаѓаат.

CREATE INDEX IF NOT EXISTS idx_customer_order_customer_user_id_order_datetime
ON kbnteam.customer_order (customer_user_id, order_datetime DESC);

CREATE INDEX IF NOT EXISTS idx_customer_company_id
ON kbnteam.customer (company_id);

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

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'kbnteam'
  AND indexname IN (
    'idx_customer_order_customer_user_id_order_datetime',
    'idx_customer_company_id'
)
ORDER BY tablename, indexname;

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

Мерењето ги користи истите тест прашалници и услови како почетното мерење.

Метрика	Тест 1 (по компанија)	Тест 2 (по ниво)	Тест 3 (по клиент)
Planning Time	_ ms	_ ms	_ ms
Execution Time	_ ms	_ ms	_ ms
Rows Returned	_	_	_
Начин на читање по табела	_	_	_
Shared Hit Blocks	_	_	_
Shared Read Blocks	_	_	_

-- Планови и резултати по дополнителното индексирање:

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

Метрика	Пред	По	Промена (%)
Execution Time (Тест 1)	_ ms	_ ms	_ %
Execution Time (Тест 2)	_ ms	_ ms	_ %
Execution Time (Тест 3)	_ ms	_ ms	_ %

Промената се пресметува како 100 × (време пред − време по) / време пред, кога почетното време е поголемо од нула. Негативна вредност означува побавно извршување.

Изборот меѓу секвенцијално читање, индексно читање и други планови зависи од обемот на податоците, селективноста на филтрите и статистиките. Индексите не гарантираат забрзување или отстранување на сортирањата и спојувањата. Плановите и времињата се утврдуваат со мерење.