SQL 每月每日結算排名Ranking

sql c#

Ks 2020-09-27 13:26:45 ‧ 2495 瀏覽

分享至

MariaDb 每月每日結算排名Ranking

目前有一張table 來記錄每天的各個user的netprofit

daily profit table結構這個表回去結算每天的daily profit

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

主管要求做出以下的部分,
按照每天profit來進行ranking排名

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

按照每月profit來進行ranking排名

username         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

請問有辦法做出這樣的View嗎?
還是有其它建議的做法, 比如開多2個table來分別進行結算?

謝謝

Ks iT邦新手 3 級 ‧ 2020-09-27 13:56:22 檢舉

補充說明

感謝各位, daily的部分我有找到解答了

https://stackoverflow.com/questions/48760256/rank-by-date-and-qty-in-sql

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 2020-09-27 15:27:49

最佳解答

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;

-- 每天 : 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

-- 每月 : row_number()
select username,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,netprofit desc) as rank
from daily
order by date_format(date,'%Y-%m') desc,netprofit desc

Demo

使用 row_number(),rank(),dense_rank() 時機依照需求自行變更

回應 3
分享
檢舉

Ks iT邦新手 3 級 ‧ 2020-09-27 20:19:06 檢舉

感謝大大的答案,

請問如果每月要合併username的話就加
GROUP BY date_format(date,'%Y-%m'), username對吧?

我這裡加上之后是得到我想要的答案
demo

rogeryao iT邦超人 7 級 ‧ 2020-09-27 21:14:52 檢舉

我不確定你想要的答案是什麼,最好先想過一遍,另外再發一篇吧.
(每月要合併username => 如何合併 ???)

提示如下(用你的demo),可能跟你想的不一樣 :
('4','testuser02','50','2020/09/26 14:00:00'),
('5','testuser03','60','2020/09/26 14:00:00'),

==> '50','60' 不見了

Ks iT邦新手 3 級 ‧ 2020-09-27 21:28:19 檢舉

糾正一下, 我加入了sum(netprofit)在每月
目前這樣是我想得到的每月的答案咯

就是把同一個username的每個月netprofit加總起來
然後做ranking

感謝
demo

登入發表回應

skyksl066

iT邦新手 4 級 ‧ 2020-09-27 14:03:21

SELECT
    `username`,
    `netprofit`,
    DATE_FORMAT(`date`,'%Y/%m'),
    RANK() OVER(
    PARTITION BY YEAR(`date`),
    MONTH(`date`)
ORDER BY
    `netprofit`
DESC
) rank
FROM
    `rank` #日結完可以直接放在這邊轉月