时间戳

首页 > 解决方案 > 如何使用多个近似匹配执行复杂 SQL 连接并仅返回第一个匹配

sql - 如何使用多个近似匹配执行复杂 SQL 连接并仅返回第一个匹配

问题描述

我正在尝试在 SQL 中执行左连接，我需要检查多个匹配条件，并且仅在对右表进行某种排序操作后保留右表中的第一个匹配项。

下面是我的左表。（无空值）

日期 顾客 店铺 产品 Customer_Score
2020 年 1 月 1 日 C1 S1 P1 2
2020 年 1 月 2 日 C2 S1 P2 8
2020 年 1 月 5 日 C3 S2 P1 6
2020 年 1 月 6 日 C4 S2 P2 10
2020 年 1 月 7 日 C1 S2 P3 2
2020 年 1 月 8 日 C2 S2 P4 4

这是正确的表（仅在产品列中允许空值）

店铺 产品 Min_Customer_Score Valid_From 有效 Percent_Discount
S1 P1 4 2020 年 1 月 1 日 2020 年 1 月 5 日 10
S1 P1 5 2020 年 1 月 1 日 2020 年 1 月 5 日 11
S1 P1 7 2020 年 1 月 1 日 2020 年 1 月 5 日 12
S1 5 2020 年 1 月 1 日 2020 年 1 月 5 日 13
S2 P1 4 2020 年 1 月 1 日 2020 年 1 月 5 日 14
S2 P2 4 2020 年 1 月 1 日 2020 年 1 月 5 日 15
S2 6 2020 年 1 月 1 日 2020 年 1 月 5 日 16
S2 9 2020 年 1 月 1 日 2020 年 1 月 5 日 17
S2 P1 4 2020 年 1 月 6 日 2020 年 1 月 8 日 18
S2 P2 4 2020 年 1 月 6 日 2020 年 1 月 8 日 19
S2 6 2020 年 1 月 6 日 2020 年 1 月 8 日 20
S2 9 2020 年 1 月 6 日 2020 年 1 月 8 日 21

我想首先按产品（最后为空）对正确的表进行排序，然后按 Min_Customer_Score（升序）。 然后我想从匹配以下条件的第一行中提取 Min_Customer_Score 和 Discount 值：

  1. Left.Date >= Right.Valid_From
  2. Left.Date <= Right.Valid_To
  3. Left.Shop = Right.Shop
  4. Left.Product = Right.Product或Right.Product = null
  5. Left.Customer_Score >= Right.Min_Customer_Score

我的最终结果应该如下所示。

日期 顾客 店铺 产品 Customer_Score Min_Customer_Score Percent_Discount
2020 年 1 月 1 日 C1 S1 P1 2 无效的 无效的
2020 年 1 月 2 日 C2 S1 P2 8 5 13
2020 年 1 月 5 日 C3 S2 P1 6 4 14
2020 年 1 月 6 日 C4 S2 P2 10 4 19
2020 年 1 月 7 日 C1 S2 P3 2 无效的 无效的
2020 年 1 月 8 日 C2 S2 P4 4 无效的 无效的

基本上，我想为每次购买找到合适的折扣，将 Right.Product 中的空值视为适用于所有其他产品的默认折扣。

我熟悉左连接以及在 SQL 中使用子查询。但我什至不明白从哪里开始做如此复杂的查询。我还提到了其他建议使用的答案ROW_NUMBER() OVER (PARTITION BY，但在这种情况下无法解决。

编辑： 这是我到目前为止能够解决的问题。

SELECT left_table.*, right_table.Percent_Discount, right_table.Min_Customer_Score
  , ROW_NUMBER() OVER (
  PARTITION BY left_table.Date, left_table.Customer, left_table.Shop, left_table.Product 
  ORDER BY right_table.Product DESC right_table.Min_Customer_Score ASC) as row_num
LEFT JOIN right_table
  ON left_table.Date >= right_table.Valid_From
  AND left_table.Date <= right_table.Valid_To
  AND left_table.Shop>= right_table.Shop
  AND (left_table.Product = right_table.Product OR right_table.Product is NULL)
  AND left_table.Customer_Score >= right_table.Min_Customer_Score
WHERE row_num = 1

但它给了我以下错误

ERROR:  column "row_num" does not exist
LINE: WHERE row_num = 1

标签： sqlpostgresqljoin

解决方案

使用apply：

select l.*, r.*
from left l outer apply
     (select top (1)
      from right r
      where l.Date >= r.Valid_From and
            l.Date <= r.Valid_To and
            l.Shop = r.Shop and
            (l.Product = r.Product or r.Product = null) and
            (l.Customer_Score >= r.Min_Customer_Score)
      order by (case when product is not null then 1 else 2 end),
               Min_Customer_Score asc
     ) r

推荐阅读