基于多准则的完全外连接匹配

Question

我想加入两张桌子：

tableA：这张表包含了我们通过投标、来源和日期赢得的所有线索。

Bid Amount = tableA.price, Source = tableA.lead_source_id, Date = tableA.time

tableB：这个表包含了我们通过出价、源和日期丢失的所有线索。

Bid Amount = tableB.cost, Source = tableB.lead_source_id, Date = tableB.bid_at

我希望能够返回的投标数目，我们赢得和输，通过投标，按来源和日期。通常情况下，在一个或另一个表中只有记录(我们赢得了所有的投标或丢失了所有的出价)，所以看起来需要外部连接。

理想的输出将按lead_source_id、投标量和时间分组如下：

Lead Source ID, Date, Bid Amount, Won, Lost

1, 1/1/2015, $20, 5, 0 

1, 1/1/2015, $25, 0, 9

5, 1/1/2015, $30, 1, 1

10, 1/2/2015, $50, 0, 1

10, 1/2/2015, $55, 1, 0

Answer 1

尝试以下查询。

;WITH tmp AS (
SELECT lead_source_id,  [time] AS [Date], price, COUNT(*) AS won, 0 AS lost 
FROM  tableA
GROUP BY lead_source_id, [time], price 
UNION ALL
SELECT lead_source_id, bid_at AS [Date], cost AS price, 0 AS won, COUNT(*) AS lost 
FROM tableB
GROUP BY lead_source_id, bid_at, cost)

SELECT lead_source_id AS [Lead Source ID], [Date], price AS [Bid Amount], SUM(won) AS Won , SUM(lost) AS Lost
FROM tmp
GROUP BY lead_source_id, [Date], price
ORDER BY lead_source_id, [Date]

公共表表达式(tmp)根据id、日期和出价金额计算韩元/失败出价的数量，并对数据进行外部选择。