MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE daily
(id nvarchar(30),
 username nvarchar(30),     
 netprofit nvarchar(30),    
 date datetime
);

insert into daily
values
('1','testuser01','20','2020/09/27 14:00:00'),
('2','testuser02','30','2020/09/27 14:00:00'),
('3','testuser03','40','2020/09/27 14:00:00'),
('4','testuser02','20','2020/09/26 14:00:00'),
('5','testuser03','30','2020/09/26 14:00:00'),
('6','testuser03','30','2020/08/27 14:00:00'),
('7','testuser01','20','2020/08/27 14:00:00'),
('8','testuser03','30','2019/12/01 14:00:00'),
('9','testuser01','20','2019/12/01 14:00:00');


select *
from daily;


id
username
netprofit
date


1
testuser01
20
2020-09-27 14:00:00

2
testuser02
30
2020-09-27 14:00:00

3
testuser03
40
2020-09-27 14:00:00

4
testuser02
20
2020-09-26 14:00:00

5
testuser03
30
2020-09-26 14:00:00

6
testuser03
30
2020-08-27 14:00:00

7
testuser01
20
2020-08-27 14:00:00

8
testuser03
30
2019-12-01 14:00:00

9
testuser01
20
2019-12-01 14:00:00


-- 每天 : row_number()
select username,netprofit,date,
row_number()
over(partition by date order by date desc,netprofit desc,username) as rank
from daily
order by date desc,netprofit desc


username
netprofit
date
rank


testuser03
40
2020-09-27 14:00:00
1

testuser02
30
2020-09-27 14:00:00
2

testuser01
20
2020-09-27 14:00:00
3

testuser03
30
2020-09-26 14:00:00
1

testuser02
20
2020-09-26 14:00:00
2

testuser03
30
2020-08-27 14:00:00
1

testuser01
20
2020-08-27 14:00:00
2

testuser03
30
2019-12-01 14:00:00
1

testuser01
20
2019-12-01 14:00:00
2


-- 每月 : row_number()
select username,sum(netprofit),date_format(date,'%Y-%m') as date,
row_number()
over(partition by date_format(date,'%Y-%m') order by date_format(date, '%Y-%m') desc,sum(netprofit) desc) as rank
from daily
GROUP BY date_format(date,'%Y-%m'), username
order by date_format(date,'%Y-%m') desc,netprofit desc


username
sum(netprofit)
date
rank


testuser03
70
2020-09
1

testuser02
50
2020-09
2

testuser01
20
2020-09
3

testuser03
30
2020-08
1

testuser01
20
2020-08
2

testuser03
30
2019-12
1

testuser01
20
2019-12
2


-- 每月 : rank()
select username,sum(netprofit),date_format(date,'%Y-%m') as date,
rank()
over(partition by date_format(date,'%Y-%m') order by date_format(date, '%Y-%m') desc,sum(netprofit) desc) as rank
from daily
GROUP BY date_format(date,'%Y-%m'), username
order by date_format(date,'%Y-%m') desc,netprofit desc


username
sum(netprofit)
date
rank


testuser03
70
2020-09
1

testuser02
50
2020-09
2

testuser01
20
2020-09
3

testuser03
30
2020-08
1

testuser01
20
2020-08
2

testuser03
30
2019-12
1

testuser01
20
2019-12
2


-- 每月 : dense_rank()
select username,sum(netprofit),date_format(date,'%Y-%m') as date,
dense_rank()
over(partition by date_format(date,'%Y-%m') order by date_format(date, '%Y-%m') desc,sum(netprofit) desc) as rank
from daily
GROUP BY date_format(date,'%Y-%m'), username
order by date_format(date,'%Y-%m') desc,netprofit desc


username
sum(netprofit)
date
rank


testuser03
70
2020-09
1

testuser02
50
2020-09
2

testuser01
20
2020-09
3

testuser03
30
2020-08
1

testuser01
20
2020-08
2

testuser03
30
2019-12
1

testuser01
20
2019-12
2

id	username	netprofit	date
1	testuser01	20	2020-09-27 14:00:00
2	testuser02	30	2020-09-27 14:00:00
3	testuser03	40	2020-09-27 14:00:00
4	testuser02	20	2020-09-26 14:00:00
5	testuser03	30	2020-09-26 14:00:00
6	testuser03	30	2020-08-27 14:00:00
7	testuser01	20	2020-08-27 14:00:00
8	testuser03	30	2019-12-01 14:00:00
9	testuser01	20	2019-12-01 14:00:00

username	sum(netprofit)	date	rank
testuser03	70	2020-09	1
testuser02	50	2020-09	2
testuser01	20	2020-09	3
testuser03	30	2020-08	1
testuser01	20	2020-08	2
testuser03	30	2019-12	1
testuser01	20	2019-12	2