我有一個主要資料集（用戶），如下所示。
ID Username Status
1 John Active
2 Mike Active
3 Ann Deactive
4 Leta Active
5 Lena Active
6 Lara Active
7 Mitch Active
此外，我的收入表如下。
subuser hour Revenue
John_01 2/26/2022 5:00 5
Mike_01 2/26/2022 7:00 8
Mike_02 2/26/2022 7:00 22
Leta_03 2/26/2022 7:00 67
Leta_07 2/26/2022 9:00 56
Mitch_07 2/26/2022 11:00 34
現在我需要如下表。
User Total Usage
John 5
Mike 22
Leta 123
Lena 0
Lara 0
Mitch 0
在這里，我需要獲取每個用戶子字串的所有小時數的總和，并與主用戶表匹配。此外，如果相同的小時數是相同的子字串，我需要獲得最大的收入值，并且該特定小時的其他值應該被忽略。
前任：
Mike_01 2/26/2022 7:00 8
Mike_02 2/26/2022 7:00 22
這里Mike_01 2/26/2022 7:00 8應該忽略。
所以我嘗試如下。
SELECT
u.Username,
COALESCE(SUM(Revenue), 0) AS TOTAL USAGE
FROM users u
LEFT JOIN revenuetable e
ON SUBSTRING_INDEX(e.subuser, '_', 1) = u.Username AND
e.Hour BETWEEN 'XXX' and 'XXX'
where u.Status='Active'
GROUP BY
u.Username
order by u.ID.
但是如果同一小時重復，這并沒有得到最大值。有人可以告訴我我在哪里搞砸了嗎？
更新：我們還有其他使用視窗函式的方法嗎？
uj5u.com熱心網友回復：
如果使用支持的 MySQL，row_number()則連接到洗掉不需要的行的派生表。
SELECT
u.Username,
COALESCE(SUM(Revenue), 0) AS TOTAL USAGE
FROM users u
LEFT JOIN (
Select *
, row_number() OVER(partition by SUBSTRING_INDEX(e.subuser, '_', 1), hour order by revenue DESC) rn
From revenuetable ) e
ON SUBSTRING_INDEX(e.subuser, '_', 1) = u.Username AND rn = 1
e.Hour BETWEEN 'XXX' and 'XXX'
where u.Status='Active'
GROUP BY
u.Username
order by u.ID
引入此功能后，over clause將優先考慮每位用戶每小時的最高收入，因為每個此類行的“rn”列將為 1。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/433449.html