SQLize Online / PHPize Online  /  SQLtest Online

create table del_test_oper_2_0 (client_pin varchar(25), value_day varchar(25), turnover int); INSERT ALL INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-06-02', 2000) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-06-03', 3000) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-06-04', 1000) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-06-05', 10000) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-06-24', 20000) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-06-30', 35000) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-07-06', 3500) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-07-08', 10500) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-07-09', 5000) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-07-15', 100000) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-07-21', 85000) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-07-27', 95000) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ345','2019-07-01', 1500) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ345','2019-07-02', 2500) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ345','2019-07-20', 4000) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ345','2019-07-22', 65000) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ345','2019-07-23', 25000) INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ345','2019-07-28', 15000) SELECT * FROM DUAL; COMMIT; select * from del_test_oper_2_0; with q1 as ( ---сумма транзакций в день на клиента select VALUE_DAY ,CLIENT_PIN ,sum (TURNOVER) as sum_TURN_day from del_test_oper_2_0 group by VALUE_DAY ,CLIENT_PIN ) ,q2 as ( ---Расчет кешбека на день по условиям select distinct VALUE_DAY ,CLIENT_PIN ,SUM_TURN_DAY ,SUM_TURN_DAY * 0.05 as sum_cashbak --без ограничения from q1 order by VALUE_DAY ) ,q3 as ( ---Сумма транзакций и кешбека с ограничениями и без на клиента в месяц select LAST_DAY(TO_DATE(VALUE_DAY, 'yyyy.mm.dd')) AS VALUE_mnt ,CLIENT_PIN ,sum(SUM_TURN_DAY) as SUM_TURN_mnt ,sum(SUM_CASHBAK) as SUM_CASHBAK_mnt from q2 group by LAST_DAY(TO_DATE(VALUE_DAY, 'yyyy.mm.dd')) ,CLIENT_PIN ) ,q4 as (--- Общая сумма транзакций и рассчитанный кешбек на месяц по всем клиентам select LAST_DAY(TO_DATE(a.VALUE_DAY, 'yyyy.mm.dd')) AS VALUE_mnt ,sum(a.SUM_TURN_DAY) as SUM_TURN_mnt ,sum(a.SUM_CASHBAK) as SUM_CASHBAK_mnt ,case when SUM_TURN_DAY * 0.05 >=1000 then 1000 else SUM_TURN_DAY * 0.05 end as cashback_sum_day -- с ограничением from q2 a --left join q3 b on a.CLIENT_PIN=b.CLIENT_PIN group by LAST_DAY(TO_DATE(VALUE_DAY, 'yyyy.mm.dd')) ) select * from q4