如何进行一对一的内心加入

发布于 2025-01-17 23:00:20 字数 4932 浏览 4 评论 0原文

我有一个购买和退货的交易表，我想将返回交易与购买相应项目的交易匹配。（在这里，我在所有记录中使用相同的项目ID和金额为简单）

trans_id	date	item_id	amt	类型
1	2022-01-09	100	5000	购买
2	2022-01-07	100	5000 100 5000	返回
3	2022-01-06	1006	1006 1005000	购买
4	202222222222222222年-01-05	100	5000	购买
5	2022-01-04	100	5000	返回
6	2022-01-03	1003 100	5000	返回
7	2022-01-03	1003 100	5000	购买
8	2022-01-02 1002	1002 1002	100 5000	购买
9	2022-01-01	1001 100	5000	返回

匹配条件是：

退货日期必须大于或等于购买日期的
收益和购买交易必须与同一物品的ID相关，并且
每次收益的相同交易金额，必须与之匹配1个购买（以防万一有许多相关的购买，选择最新购买日期的购买。）
从3）），这意味着每次购买也必须只有1个返回。

结果应该看起来像这样。

trans_id	date	trans_id_matched	date_matched
2	2022-01-07	3	2022-01-06
5	2022-01-04	7	2022-01-03
6	2022-01-03 8 2022-03	8	2022-01-02

这是我尝试的。

with temp as (
select  a.trans_ID, a.date
        , b.trans_ID as trans_ID_matched
        , b.date as date_matched
        , row_number() over (partition by a.trans_ID, a.date order by b.date desc) as rn1
from
(
    select *
    from transaction_table
    where type = 'return'
) a
inner join
(
    select *
    from transaction_table
    where type = 'purchase'
) b
on a.item_ID = b.item_ID and a.amount = b.amount and a.date >= b.date
)
select * from temp where rn = 1

但是我得到的

trans_id	日期	trans_id_matched	date_matched
2	2022-01-07	3	2022-01-06
5	2022-01-04	7	2022-01-03
6	2022-01-01-03	7	2022-01-01-03

是 7不应在最后一行中再次使用，因为它已经与第2行中的trans ID 5匹配。 - 像购买7一样使用吗？）？

原文

I've a transaction table of purchased and returned items, and I want to match a return transaction with the transaction where that corresponding item was purchased. (Here I used the same item ID and amount in all records for simplicity)

trans_ID	date	item_ID	amt	type
1	2022-01-09	100	5000	purchase
2	2022-01-07	100	5000	return
3	2022-01-06	100	5000	purchase
4	2022-01-05	100	5000	purchase
5	2022-01-04	100	5000	return
6	2022-01-03	100	5000	return
7	2022-01-03	100	5000	purchase
8	2022-01-02	100	5000	purchase
9	2022-01-01	100	5000	return

Matching conditions are:

The return date must be greater than or equal the purchase date
The return and purchase transactions must relate to the same item's ID and same transaction amount
For each return, there must be only 1 purchase matched to it (In case there are many related purchases, choose one with the most recent purchase date. But if the most recent purchase was already used for mapping with another return, choose the second-most recent purchase instead, and so on.)
From 3), that means each purchase must be matched with only 1 return as well.

The result should look like this.

trans_ID	date	trans_ID_matched	date_matched
2	2022-01-07	3	2022-01-06
5	2022-01-04	7	2022-01-03
6	2022-01-03	8	2022-01-02

This is what I've tried.

with temp as (
select  a.trans_ID, a.date
        , b.trans_ID as trans_ID_matched
        , b.date as date_matched
        , row_number() over (partition by a.trans_ID, a.date order by b.date desc) as rn1
from
(
    select *
    from transaction_table
    where type = 'return'
) a
inner join
(
    select *
    from transaction_table
    where type = 'purchase'
) b
on a.item_ID = b.item_ID and a.amount = b.amount and a.date >= b.date
)
select * from temp where rn = 1

But what I got is

trans_ID	date	trans_ID_matched	date_matched
2	2022-01-07	3	2022-01-06
5	2022-01-04	7	2022-01-03
6	2022-01-03	7	2022-01-03

Here, the trans ID 7 shouldn't be used again in the last row as it has been already matched with trans ID 5 in the row 2. So is there any way to match trans ID 6 with 8 (or any way to tell SQL not to use the already-used one like the purchase 7) ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小矜持 2025-01-24 23:00:20

我创建了一个小提琴，结果看起来不错，但由你来测试这是否在所有情况下都可以......

I created a fiddle, the result seem OK, but it's up to you to test if this is OK on all situtations..... ????

WITH cte as (
    SELECT 
        t1.trans_ID,
        t1.[date],
        t1.item_ID,
        t1.amt,
        t1.[type],
        pur.trans_ID   trans_ID_matched,
        pur.[date]     datE_matched,
        jojo.c
    FROM table1 t1
    CROSS APPLY (
       SELECT  
           trans_ID,
           item_ID,
           [date],
           amt
       FROM table1 pur
       WHERE pur.[type] = 'purchase'  and t1.[type]='return'
         and pur.item_ID = t1.item_ID
          and pur.amt = t1.amt
          and pur.[date] <= t1.[date]
    ) pur 
    CROSS APPLY (
       SELECt count(*) as c FROM table1 WHERE trans_ID> t1.trans_ID and trans_ID<pur.trans_ID
    ) jojo
    where jojo.c <=2
) 
select 
   trans_ID,
   [date],
   item_ID,
   amt,
   CASE WHEN min(c)=0 then min(trans_ID_matched) else max(trans_ID_matched) end
from cte
group by 
   trans_ID,
   [date],
   item_ID,
   amt
order by trans_ID;

DBFIDDLE

The count(*) detects the distance between the selected trans_ID from the return and the purchase.
This might go wrong the are more than 2 adjacent 'returns'... (I am afraid it will break, so I did not test this ????).

But is's a nice problem. Hopefully this will give you any other ideas to find the correct sulution!

回复收藏 0 原文

~没有更多了~