Analysis function (window function) in mysql

Everyone knows that the analysis function of Oracle is very useful for complicated statistics, but Mysql does not support the analysis function of Oracle (window function), so please refer the below solution.

create table audit_test(
user_id int,
create_date date,
income int
);

insert into audit_test values(1,’2020–03–01',100);
insert into audit_test values(1,’2020–03–02',300);
insert into audit_test values(1,’2020–03–03',200);
insert into audit_test values(1,’2020–03–04',500);
insert into audit_test values(1,’2020–03–05',500);

insert into audit_test values(2,’2020–03–01',200);
insert into audit_test values(2,’2020–03–02',300);
insert into audit_test values(2,’2020–03–03',300);
insert into audit_test values(2,’2020–03–04',500);
insert into audit_test values(2,’2020–03–05',400);

select it.user_id,
it.create_date,
it.income,
case
when @preVal = it.user_id then @curVal := @curVal + it.income
when @preVal := it.user_id then @curVal := it.income
end AS sum_income
from audit_test it, (select @preVal:=null, @curVal:=null) r
order by it.user_id asc, it.create_date asc;

Observations:

1. User variables, true only for the current client, are @preVal and @curVal;

2. (pick @preVal:=null, @curVal:=null) r Initialize the variables @preVal and @curVal;

2. The second is only used as an assignment to @preVal, implying that the condition is still valid. (“, 0, and null are all wrong in mysql);

The use of similar analysis functions (windowing functions) in mysql is described above. Correct me, please.

I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Pankaj kushwaha

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS