Знающие люди, помогите пж с SQL.

Question

Знающие люди, помогите пж с SQL.

Андрей Гангстер Профи (871), на голосовании 6 месяцев назад

1) Задача посчитать баланс на счете в каждую операцию по счету с учетом поступления и списания средств итоговой суммой.

Пусть есть две таблицы «Счет» и «Движение по счету»

create table accounts(account_id int primary key, num_accounts bigint not null); - «Счет» (первичный ключ счета, номер счета)

create table accounts_logs(id int primary key, account_id_accounts int references accounts(account_id), value_s float not null default 0, create_dt datetime not null default NOW()); - «Движение по счету» (первичный ключ счета, внешний ключ от счета, сумма пополнения-списания, дата проведения операции)

Таблицы заполняются следующими данными:

insert into accounts values(1, 1234567890);

insert into accounts_logs values(1, 1,100, '1977-03-01'),(2, 1,200, '1977-03-02'),
(3, 1,250, '1977-03-03'),(4, 1,-200, '1977-03-04'),
(5, 1,50, '1977-03-05'),(6, 1,500, '1977-03-06'),
(7, 1,-100, '1977-03-07'),(8, 1,-700, '1977-03-08'),
(9, 1,2000, '1977-03-10');
Связь между таблицами: для каждого счета (таблица accounts первичный ключ) происходит движение как в увеличение, так и в уменьшение баланса по счету (таблица accounts_logs внешний ключ);

Итог получить в наборе данных: account_id – первичный ключ счета, trans_summ – сумма пополнения или списания, balance – баланс по счету с учетом суммы пополнения-списания, trans_dt – дата проведения операции по счету.

2) На основании связи таблиц payment_logs – phones – accounts построить результирующий набор данных, отражающий баланса на счете. Сумма баланса должна увеличиваться накопительными вариантом по мере поступления платежей. Пополнения производятся по номеру телефона, записываются в payment_logs. Добавить нулевой баланс с 1900 года на момент первого платежа. Закрывающей датой должна являться текущая.

Пусть есть две таблицы «Счет», «Телефон» и «Движение по счету телефона»

create table accounts(account_id int primary key, num int);

create table phones(phone_num int primary key, account int references accounts(account_id), num varchar(23));

create table payment_logs(value float default 0, create_dt datetime, phone int references phones(phone_num));

Таблицы заполняются следующими данными:

insert into accounts values(1, 123456789),(2, 987654321);
insert into phones values(1,1,'+7-910-506-57-74'),(2,2,'+7-916-393-50-97');
insert into payment_logs values(500.50,'2024-01-01 00:00:00',1),(500.50,'2024-02-01 00:00:00',1),
(900.10,'2024-01-01 00:00:00',2),(900.10,'2024-02-01 00:00:00',2);
Связь между таблицами: в таблице phones зафиксирована связь телефона(первичный ключ телефона) и счета(внешний ключ от телефона).

Добавить нулевой баланс с 1900 года на момент первого платежа. Дату «до» проставить в строке последнего платежа поставив текущей.

Итог получить в наборе данных: account_id – первичный ключ счета, balance – баланс по счету с учетом суммы пополнения-списания, effective_from – дата от, effective_to – дата до.

Answer 1

 SELECT 
    al.account_id_accounts AS account_id, 
    al.value_s AS trans_summ, 
    SUM(al.value_s) OVER(PARTITION BY al.account_id_accounts ORDER BY al.create_dt) AS balance, 
    al.create_dt AS trans_dt 
FROM 
    accounts_logs al 
JOIN accounts a ON 
    al.account_id_accounts = a.account_id 
ORDER BY 
    al.account_id_accounts, 
    al.create_dt;

 WITH balance_cte AS ( 
    SELECT  
        a.account_id, 
        pl.value, 
        pl.create_dt, 
        LAG(pl.create_dt, 1, '1900-01-01') OVER(PARTITION BY p.account ORDER BY pl.create_dt) as effective_from, 
        pl.create_dt as effective_to 
    FROM  
        accounts a 
    JOIN  
        phones p ON a.account_id = p.account 
    JOIN  
        payment_logs pl ON p.phone_num = pl.phone 
), 
union_cte AS ( 
    SELECT account_id, 0 as value, '1900-01-01' as create_dt, '1900-01-01' as effective_from, MIN(create_dt) OVER(PARTITION BY account_id) as effective_to FROM balance_cte 
    UNION ALL 
    SELECT * FROM balance_cte 
) 
SELECT  
    account_id, 
    SUM(value) OVER(PARTITION BY account_id ORDER BY create_dt) as balance, 
    effective_from, 
    effective_to 
FROM  
    union_cte 
ORDER BY  
    account_id, effective_from;

Answer 2

Результат

 +----------+----------+-------+----------+ 
|account_id|trans_summ|balance|trans_dt  | 
+----------+----------+-------+----------+ 
|1         |100       |100    |1977-03-01| 
|1         |200       |300    |1977-03-02| 
|1         |250       |550    |1977-03-03| 
|1         |-200      |350    |1977-03-04| 
|1         |50        |400    |1977-03-05| 
|1         |500       |900    |1977-03-06| 
|1         |-100      |800    |1977-03-07| 
|1         |-700      |100    |1977-03-08| 
|1         |2000      |2100   |1977-03-10| 
+----------+----------+-------+----------+

sqlite:

 SELECT 
    account_id, 
    value_s AS trans_summ, 
    SUM(value_s) OVER (PARTITION BY account_id ORDER BY create_dt) AS balance, 
    create_dt AS trans_dt 
FROM 
    accounts_logs;

Не проверял