客戶表
| 客戶 ID | 段ID |
|---|---|
| 12345 | 69582 |
| 34567 | 52467 |
| 56789 | 52467 |
| 78912 | 52467 |
合約表
| 秩 | rev_attempted | 嘗試_ID |
|---|---|---|
| 1 | 75 | 264578 |
| 2 | 65 | 264578 |
| 3 | 25 | 264578 |
| 1 | 45 | 789452 |
獲勝者資料
| 嘗試_ID | 客戶 ID | 收入 |
|---|---|---|
| 264578 | 12345 | 75 |
| 234567 | 34567 | 73 |
| 468751 | 56789 | 82 |
| 789452 | 78912 | 45 |
表格圖片
小提琴鏈接
您好，為難以表達這一點提前道歉。我有 3 個包含客戶資料、合同資料和獲勝者資料的表。
客戶資料包含單個客戶及其所屬的細分市場。合同資料按我想賣給誰排名，獲勝者資料是誰贏得了合同（并不總是排名 1）。
我想找到合同獲勝者（在獲勝者表中）和他們下面的排名（在合同表中）的收入邊際差異，但我不確定如何匹配獲勝者資料表中的記錄到合同資料表記錄中，找到下面的記錄（或者如果沒有記錄）并減去嘗試獲得的收入以獲得保證金。任何指導將不勝感激。
所需的輸出如下：
| 客戶 ID | 收入 | 邊際收益 | 平均邊際收益 |
|---|---|---|---|
| 12345 | 75 | 10 | 10 |
| 78912 | 45 | 45 | 45 |
uj5u.com熱心網友回復：
我認為這樣的事情應該可以作業（沒有在表格上驗證它，所以可能需要進行一些名稱更正）。希望我能解決你的問題...
-- get rank of winners => Each row is winner per bet, that was engaged in (1:N)
WITH RankWinner AS (
SELECT DISTINCT
Winner.ClientId
,Winner.call_attempt_id AS BidGroupId
,Bid.rank AS Rank
,Winner.rev AS Revenue
FROM ClientData AS Winner
INNER JOIN BidData AS Bid ON ClientData.call_attempt_id = BidData.call_attempt_id
WHERE Winner.rev = Bid.rev_per_attempt
)
-- get "next best bets" per winner => Each row is bet per Winner
WITH MarginalRevenue AS (
SELECT
RankWinner.ClientId
,RankWinner.BidGroupId
,RankWinner.Revenue
,RankWinner.Revenue - Bid.Revenue AS MarginalRevenue
FROM RankWinner
LEFT JOIN (
-- I'm using a Subquery here to get rid of duplicate ranks, just in case they're not unique.
SELECT DISTINCT
rank AS Rank
,call_attempt_id AS GroupId
,rev_per_attempt AS Revenue
FROM BidData) AS Bid ON RankWinner.Rank - 1 = Bid.Rank -- Find the next lower rank from Winner rank
AND RankWinner.BidGroupId = Bid.GroupId;
)
-- aggregate on client
SELECT
ClientId
,SUM(Revenue) AS TotalRevenue
,SUM(MarginalRevenue) AS TotalMarginalRevenue
,AVG(MarginalRevenue) AS MarginalRevenue
,COUNT(BidGroupId) AS NumberOfBids
FROM MarginalRevenue
GROUP BY ClientId
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/533157.html
標籤：mysqlsql
上一篇：Mysql選擇SUM（不同）