wiki:AdvancedReports

Context Navigation

Version 8 (modified by 211101, 5 days ago) ( diff )
--

Напредни извештаи од базата (SQL и складирани процедури)

Листа на трансакции кои го надминале балансот на една сметка подредени хронолошки

CREATE OR REPLACE FUNCTION get_transactions_exceeding_account_balance(
    p_account_name TEXT
)
RETURNS TABLE (
    user_id INT,
    user_name TEXT,
    account_name TEXT,
    transaction_id INT,
    transaction_name TEXT,
    transaction_amount NUMERIC,
    transaction_date TIMESTAMP,
    calculated_balance NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH cumulative_balances AS (
        SELECT
            u.user_id,
            u.user_name,
            ta.account_name,
            t.transaction_id,
            t.transaction_name,
            tb.spent_amount AS transaction_amount,
            t.date AS transaction_date,
            SUM(tb.earned_amount - tb.spent_amount)
                OVER (PARTITION BY ta.transaction_account_id ORDER BY t.date)
                AS calculated_balance
        FROM transaction_account ta
        JOIN "user" u ON ta.user_id = u.user_id
        JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
        JOIN transaction t ON tb.transaction_id = t.transaction_id
        WHERE ta.account_name = p_account_name
    )
    SELECT *
    FROM cumulative_balances
    WHERE transaction_amount > calculated_balance
      AND transaction_amount > 0
    ORDER BY transaction_date DESC;
END;
$$;

Надминување на баланс на една сметка со трансакција во сегашно време

CREATE OR REPLACE FUNCTION get_current_account_overdrafts()
RETURNS TABLE (
    user_id INT,
    user_name TEXT,
    account_name TEXT,
    current_balance NUMERIC,
    transaction_id INT,
    transaction_name TEXT,
    transaction_amount NUMERIC,
    transaction_date TIMESTAMP
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        u.user_id,
        u.user_name,
        ta.account_name,
        ta.balance,
        t.transaction_id,
        t.transaction_name,
        tb.spent_amount,
        t.date
    FROM transaction_account ta
    JOIN "user" u ON ta.user_id = u.user_id
    JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    JOIN transaction t ON tb.transaction_id = t.transaction_id
    WHERE tb.spent_amount > ta.balance
      AND tb.spent_amount > 0
    ORDER BY t.date DESC;
END;
$$;

Листа на трансакции кои го надминале балансот на сите сметки вкупно подредени хронолошки

CREATE OR REPLACE FUNCTION get_transactions_exceeding_user_total_balance()
RETURNS TABLE (
    user_id INT,
    user_name TEXT,
    transaction_id INT,
    transaction_name TEXT,
    transaction_amount NUMERIC,
    transaction_date TIMESTAMP,
    calculated_total_balance NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH cumulative_totals AS (
        SELECT
            u.user_id,
            u.user_name,
            t.transaction_id,
            t.transaction_name,
            tb.spent_amount AS transaction_amount,
            t.date AS transaction_date,
            SUM(tb.earned_amount - tb.spent_amount)
                OVER (PARTITION BY u.user_id ORDER BY t.date)
                AS calculated_total_balance
        FROM transaction_account ta
        JOIN "user" u ON ta.user_id = u.user_id
        JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
        JOIN transaction t ON tb.transaction_id = t.transaction_id
    )
    SELECT *
    FROM cumulative_totals
    WHERE transaction_amount > calculated_total_balance
      AND transaction_amount > 0
    ORDER BY user_id, transaction_date DESC;
END;
$$;

Надминување на вкупниот баланс на сите сметки на корисникот во сегашно време

Овде ќе го собереме моменталниот баланс на сите сметки на корисникот и ќе провериме дали сумата на моменталната трансакцијата ја надминува таа вкупна вредност

WITH total_user_balance AS (
    SELECT 
        u.user_id,
        SUM(ta.balance) AS total_balance
    FROM 
        transaction_account ta
    JOIN user u ON ta.user_id = u.user_id
    GROUP BY 
        u.user_id
)
SELECT 
    u.user_id,
    u.user_name,
    SUM(tb.spent_amount) AS total_transaction_amount,
    tub.total_balance AS user_total_balance
FROM 
    transaction_account ta
JOIN user u ON ta.user_id = u.user_id
JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN total_user_balance tub ON u.user_id = tub.user_id
WHERE 
    t.date <= CURRENT_DATE -- Само активни трансакции до денешен датум
GROUP BY 
    u.user_id, u.user_name, tub.total_balance
HAVING 
    SUM(tb.spent_amount) > tub.total_balance -- Трансакциите го надминуваат ВКУПНИОТ моментален баланс
ORDER BY 
    u.user_id;

Сумарни приходи и расходи по месеци

Сумарните приходи и расходи (состојба) во целиот систем, поделени по месеци, со цел да се видат проблематичните месеци, се пресметува вкупниот приход и расход за секој месец, а разликата меѓу нив ќе ја даде состојбата

CREATE OR REPLACE FUNCTION get_monthly_system_balance()
RETURNS TABLE (
    month TEXT,
    total_income NUMERIC,
    total_expense NUMERIC,
    net_balance NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        TO_CHAR(t.date, 'YYYY-MM'),
        SUM(tb.earned_amount),
        SUM(tb.spent_amount),
        SUM(tb.earned_amount) - SUM(tb.spent_amount)
    FROM transaction t
    JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    GROUP BY TO_CHAR(t.date, 'YYYY-MM')
    ORDER BY month;
END;
$$;

Проблематичен месец е месец каде што net_balance е негативен. Резултатите можат да се прикажат во графикон (на пример, бар график или линиски график) за подобра анализа. Може да се додаде процентуална промена меѓу месеците за да се откријат трендовите во приходите и трошоците

Број на трансакции што ги надминуваат приходите на сметка

WITH CumulativeBalances AS (
    SELECT 
        t.transaction_id,
        ta.account_name,
        SUM(tb.earned_amount - tb.spent_amount) OVER (
            PARTITION BY ta.transaction_account_id
            ORDER BY t.date
        ) AS calculated_balance,
        tb.spent_amount
    FROM 
        transaction_account ta
    JOIN 
        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    JOIN 
        transaction t ON tb.transaction_id = t.transaction_id
)
SELECT 
    account_name, 
    COUNT(transaction_id) AS transactions_exceeding_balance
FROM 
    CumulativeBalances
WHERE 
    spent_amount > calculated_balance -- Кога потрошениот износ го надминува пресметаниот баланс
    AND spent_amount > 0
GROUP BY 
    account_name
ORDER BY 
    transactions_exceeding_balance DESC;

Ако сакаме да ги добиеме вкупните трансакции кои ги надминуваат средствата низ целиот систем, без групирање по сметки:

WITH CumulativeTotalBalances AS (
    SELECT 
        t.transaction_id,
        u.user_id,
        SUM(tb.earned_amount - tb.spent_amount) OVER (
            PARTITION BY u.user_id
            ORDER BY t.date
        ) AS calculated_total_balance,
        tb.spent_amount
    FROM 
        transaction_account ta
    JOIN 
        user u ON ta.user_id = u.user_id
    JOIN 
        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
    JOIN 
        transaction t ON tb.transaction_id = t.transaction_id
)
SELECT 
    COUNT(transaction_id) AS total_transactions_exceeding_balance
FROM 
    CumulativeTotalBalances
WHERE 
    spent_amount > calculated_total_balance -- Кога потрошениот износ го надминува кумулативниот баланс
    AND spent_amount > 0;

Сумарни податоци за тагови

Сумирани податоци каде редовите се месеци, а колоните се тагови, а во полината се покажуваат различни сумирани податоци:

Вкупен број на трансакции

DO $$
DECLARE
    col_list TEXT; -- Листа на тагови за изведување на пивот
    dynamic_query TEXT; -- Динамичко SQL прашање
BEGIN
    -- Чекор 1: Динамички се генерира листа на колони
    SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN 1 ELSE 0 END) AS %I', tag_name, tag_name), ', ')
    INTO col_list
    FROM tag;

    -- Чекор 2: Се гради динамичкото прашање
    dynamic_query := format(
        'SELECT 
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
        FROM 
            transaction t
        JOIN 
            tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
        JOIN 
            tag tg ON tat.tag_id = tg.tag_id
        GROUP BY 
            transaction_month
        ORDER BY 
            transaction_month;',
        col_list
    );

    -- Чекор 3: Извршување на динамичкото прашање
    EXECUTE dynamic_query;
END $$;

Вкупно примени средства

DO $$
DECLARE
    col_list TEXT; -- Листа на тагови за изведување на пивот
    dynamic_query TEXT; -- Динамичко SQL прашање
BEGIN
    -- Чекор 1: Динамички се генерира листа на колони
    SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN tb.earned_amount ELSE 0 END) AS %I', tag_name, tag_name), ', ')
    INTO col_list
    FROM tag;

    -- Чекор 2: Се гради динамичкото прашање
    dynamic_query := format(
        'SELECT 
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
        FROM 
            transaction t
        JOIN 
            transaction_breakdown tb ON t.transaction_id = tb.transaction_id
        JOIN 
            tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
        JOIN 
            tag tg ON tat.tag_id = tg.tag_id
        GROUP BY 
            transaction_month
        ORDER BY 
            transaction_month;',
        col_list
    );

    -- Чекор 3: Извршување на динамичкото прашање
    EXECUTE dynamic_query;
END $$;

Вкупно потрошени средства

DO $$
DECLARE
    col_list TEXT; -- Листа на тагови за изведување на пивот
    dynamic_query TEXT; -- Динамичко SQL прашање
BEGIN
    -- Чекор 1: Динамички се генерира листа на колони
    SELECT STRING_AGG(DISTINCT format('SUM(CASE WHEN tg.tag_name = %L THEN tb.spent_amount ELSE 0 END) AS %I', tag_name, tag_name), ', ')
    INTO col_list
    FROM tag;

    -- Чекор 2: Се гради динамичкото прашање
    dynamic_query := format(
        'SELECT 
            TO_CHAR(t.date, ''YYYY-MM'') AS transaction_month,
            %s
        FROM 
            transaction t
        JOIN 
            transaction_breakdown tb ON t.transaction_id = tb.transaction_id
        JOIN 
            tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
        JOIN 
            tag tg ON tat.tag_id = tg.tag_id
        GROUP BY 
            transaction_month
        ORDER BY 
            transaction_month;',
        col_list
    );

    -- Чекор 3: Извршување на динамичкото прашање
    EXECUTE dynamic_query;
END $$;

Просечно трошење во последните 3 дена за цел систем

Просечно дневно трошење за последните три дена, земајќи ги предвид сите трансакции:

SELECT 
    AVG(daily_spending) AS average_spending_last_3_days
FROM (
    SELECT 
        t.date::date AS transaction_date, 
        SUM(tb.spent_amount) AS daily_spending
    FROM 
        transaction t
    JOIN 
        transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    WHERE 
        t.date >= CURRENT_DATE - INTERVAL '2 DAY'
        AND t.date < CURRENT_DATE + INTERVAL '1 DAY'
    GROUP BY 
        t.date::date
) daily_totals;

Просечно трошење во последните 3 дена за корисник

Просечно дневно трошење за последните 3 дена за одреден корисник:

SELECT 
    AVG(daily_spending) AS average_spending_last_3_days
FROM (
    SELECT 
        t.date::date AS transaction_date, 
        SUM(tb.spent_amount) AS daily_spending
    FROM 
        transaction t
    JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
    JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
    WHERE 
        ta.user_id = 101 -- ID на конкретниот корисник
        AND t.date >= CURRENT_DATE - INTERVAL '2 DAY'
        AND t.date < CURRENT_DATE + INTERVAL '1 DAY'
    GROUP BY 
        t.date::date
) daily_totals;

Вкупно потрошено во тековниот месец за цел систем

Вкупна сума на трошоци во тековниот месец

SELECT 
    SUM(tb.spent_amount) AS total_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
WHERE 
    EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);

Вкупно потрошено во тековниот месец за корисник

Вкупна сума на трошоци во тековниот месец на еден корисник

SELECT 
    SUM(tb.spent_amount) AS total_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
WHERE 
    ta.user_id = 101 -- ID на конкретниот корисник
    AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE);

Дневен буџет до крајот на месецот за корисник

Дневен буџет базиран на вкупниот баланс на сите сметки на корисникот

SELECT 
    SUM(ta.balance) / (DATE_PART('day', DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 MONTH') - INTERVAL '1 DAY') - DATE_PART('day', CURRENT_DATE)) AS daily_budget
FROM 
    transaction_account ta
WHERE 
    ta.user_id = 101; -- ID на конкретниот корисник

Долг на кредитна картичка од минатиот месец за цел систем

Вкупна сума потрошена од сметките обележани како „кредитна картичка“ за минатиот месец

SELECT 
    SUM(tb.spent_amount) AS credit_card_debt
FROM 
    transaction_breakdown tb
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
JOIN transaction t ON tb.transaction_id = t.transaction_id
WHERE 
    (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
    AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');

Долг на кредитна картичка од минатиот месец за корисник

Потрошено од сметки означени како „кредитна картичка“ за минатиот месец за еден корисник

SELECT 
    SUM(tb.spent_amount) AS credit_card_debt
FROM 
    transaction_breakdown tb
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
JOIN transaction t ON tb.transaction_id = t.transaction_id
WHERE 
    ta.user_id = 101 -- ID на конкретниот корисник
    AND (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
    AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');

Трендови на трошење според тагови за цел систем

Трендови на трошење за секој таг во последните шест месеци

SELECT 
    tg.tag_name, 
    DATE_TRUNC('month', t.date) AS month,
    SUM(tb.spent_amount) AS total_spent
FROM 
    tag tg
JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
JOIN transaction t ON tat.transaction_id = t.transaction_id
JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
WHERE 
    t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
GROUP BY 
    tg.tag_name, month
ORDER BY 
    tg.tag_name, month;

Трендови на трошење според тагови за корисник

Сумирање на трошењата според тагови во последните 6 месеци за одреден корисник

SELECT 
    tg.tag_name, 
    DATE_TRUNC('month', t.date) AS month,
    SUM(tb.spent_amount) AS total_spent
FROM 
    tag_assigned_to_transaction tat
JOIN transaction t ON tat.transaction_id = t.transaction_id
JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
JOIN tag tg ON tat.tag_id = tg.tag_id
WHERE 
    ta.user_id = 101 -- ID на конкретниот корисник
    AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
GROUP BY 
    tg.tag_name, month
ORDER BY 
    tg.tag_name, month;

Вкупно трошење според тагови

Вкупно трошење групирано според тагови за тековниот месец

SELECT 
    tg.tag_name,
    SUM(tb.spent_amount) AS total_spent
FROM 
    tag tg
JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
JOIN transaction t ON tat.transaction_id = t.transaction_id
JOIN transaction_breakdown tb ON t.transaction_id = tb.transaction_id
WHERE 
    EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE)
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY 
    tg.tag_name
ORDER BY 
    total_spent DESC;

Сметки со највисоко вкупно трошење во изминатата година

SELECT 
    ta.account_name, 
    SUM(tb.spent_amount) AS total_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
WHERE 
    t.date >= NOW() - INTERVAL '1 YEAR'
GROUP BY 
    ta.account_name
ORDER BY 
    total_spent DESC
LIMIT 10;

Најчести времиња за трансакции

Во кој час од денот корисниците најчесто вршат трансакции

SELECT 
    EXTRACT(HOUR FROM t.date) AS transaction_hour, 
    COUNT(*) AS transaction_count
FROM 
    transaction t
GROUP BY 
    transaction_hour
ORDER BY 
    transaction_count DESC;

Трансакции според тагови со највисоко трошење за цел систем

SELECT 
    tg.tag_name, 
    SUM(tb.spent_amount) AS total_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
JOIN tag tg ON tat.tag_id = tg.tag_id
GROUP BY 
    tg.tag_name
ORDER BY 
    total_spent DESC;

Трансакции според тагови со највисоко трошење за корисник

Сумирање на трошењата според тагови за одреден корисник

SELECT 
    tg.tag_name, 
    SUM(tb.spent_amount) AS total_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
JOIN tag_assigned_to_transaction tat ON t.transaction_id = tat.transaction_id
JOIN tag tg ON tat.tag_id = tg.tag_id
WHERE 
    ta.user_id = 101 -- ID на конкретниот корисник
GROUP BY 
    tg.tag_name
ORDER BY 
    total_spent DESC;

Годишни трендови на трансакции за цел систем

Трошења според сметки за секој квартал од тековната година

SELECT 
    ta.account_name, 
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
WHERE 
    EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY 
    ta.account_name
ORDER BY 
    ta.account_name;

Годишни трендови на трансакции за корисник

Трошења по квартали за тековната година за одреден корисник

SELECT 
    ta.account_name, 
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
WHERE 
    ta.user_id = 101 -- ID на конкретниот корисник
    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY 
    ta.account_name
ORDER BY 
    ta.account_name;

Неактивни тагови

Идентификување тагови кои не биле користени во изминатиот месец

SELECT 
    tg.tag_name
FROM 
    tag tg
LEFT JOIN tag_assigned_to_transaction tat ON tg.tag_id = tat.tag_id
LEFT JOIN transaction t ON tat.transaction_id = t.transaction_id
WHERE 
    t.date IS NULL OR t.date < NOW() - INTERVAL '1 MONTH';

[wiki:= Напредни извештаи од базата (SQL и складирани процедури)