SQLize | PHPize | SQLtest

--https://sqlize.online/sql/oracle21/c3d60ebfbfdb2829afc312408113bf7c/ -- DROP -- DROP TABLE clients; -- DROP TABLE accounts; ------------------------ -- CREATE INSERT CREATE TABLE clients (client_id VARCHAR(1), FIO VARCHAR(255) , Region VARCHAR(50), account_num INT); INSERT INTO clients VALUES ('A','Иванов','Москва',111); INSERT INTO clients VALUES ('A','Иванов','Москва',222); INSERT INTO clients VALUES ('B','Петров','Иваново',333); INSERT INTO clients VALUES ('C','Сидоров','Москва',444); INSERT INTO clients VALUES ('D','Новиков','Москва',555); INSERT INTO clients VALUES ('E','Шварценеггер','Лос-Анджелес',666); INSERT INTO clients VALUES ('D','Новиков','Москва',777); INSERT INTO clients VALUES ('F','Морозов','г.Москва',888); INSERT INTO clients VALUES ('G','Цветков','Москва',999); INSERT INTO clients VALUES ('G','Цветков','Москва',1111); INSERT INTO clients VALUES ('H','Шубин','Монтевидео',2222); INSERT INTO clients VALUES ('K','Крюков','Москва',3333); CREATE TABLE accounts (value_day varchar(25), Summa_USD int, Account_num INT); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-01-01',15000,111); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-02-01',10000,111) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',5000,222) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',30000,333) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-04-01',20000,444) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-01-01',200000,666); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-02-01',400000,666); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',200000,666); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-04-01',198000,666) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-01-01',5000,555); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-02-01',15000,555); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',10000,555) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-01-01',2000,777); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-03-01',20000,777); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-08-01',10000,777) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-02-01',10000,888); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-04-01',2000,888); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2012-07-01',20500,888) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-01-01',45000,999); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-02-01',545000,999); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-03-01',0,999) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-01-01',1000,1111); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-02-01',10000,1111); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-03-01',21000,1111); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-04-01',19500,1111) ; INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-01-01',10000,2222); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-02-01',20000,2222); INSERT INTO accounts (value_day , Summa_USD , Account_num) VALUES ('2013-03-01',60000,2222) ; COMMIT; ------------------------ SELECT * FROM clients; SELECT * FROM accounts; ------------------------ select * from clients cl join accounts ac on ac.Account_num = cl.Account_num; ------------------------ -- select distinct -- Account_num, -- last_value(Summa_USD) over (partition by Account_num order by value_day -- rows between unbounded preceding and unbounded following) "на последнюю дату" -- from accounts -- ; -- Задание 1 -- Вывести список клиентов из Москвы с суммарными остатками по клиенту -- и проставить флаг превышения средств свыше 20 000 -- на последнюю дату. with acc_last_sum as ( select distinct Account_num, last_value(Summa_USD) over (partition by Account_num order by value_day rows between unbounded preceding and unbounded following) from accounts ) select * from acc_last_sum; -- select -- -- Region -- client_id -- ,FIO -- ,sum(last_sum) -- -- ,case -- -- -- when sum(Summa_USD) > 20000 then true -- -- -- else false -- -- when sum( -- -- last_value(Summa_USD) over (partition by Account_num order by value_day -- -- rows between unbounded preceding and unbounded following) -- -- ) > 20000 then 1 -- -- else 0 -- -- end "флаг" -- from clients cl -- join acc_last_sum ac on ac.Account_num = cl.Account_num -- where -- -- lower(Region) like '%м%с%к%' --ilike -- -- replace(Region, 'г.') like 'Москва' -- lower(Region) in ('москва','г.москва') -- group by -- Region, client_id, FIO -- ) -- select * from temp1 -- ;