Присоединиться на основе лучшего совпадения

У меня есть эти две таблицы:

Table: Estimates

| EstimateNumber | Quantity | TotalCost |
| -------------- | -------- | --------- |
| 183232         | 2000     | 5890.42   |
| 183232         | 2500     | 6935.63   |
| 183232         | 3500     | 9016.21   |
| 183232         | 3000     | 7980.28   |

Table: Jobs

| JobNumber | EstimateNumber | QuantityOrdered |
| --------- | -------------- | --------------- |
| PK05188   | 183232         | 2500            |
| PK05591   | 183232         | 3000            |

Я бы хотел это:

| Job Number | TotalCost | Other_Info_From_Corresponding_Estimates_Row |
| ---------- | --------- | ------------------------------------------ |
| PK05188    | 6935.63   | 'Example'                                  |
| PK05591    | 7980.28   | 'Example'                                  |

Где правильная сопутствующая стоимость - это стоимость с ближайшим количеством. Изначально я делал что-то вроде этого:

SELECT 

    Estimates.EstimateNumber,
    Estimates.TotalCost

FROM Estimates

INNER JOIN

    (SELECT DISTINCT

        Jobs.EstimateNumber,
        FIRST_VALUE( Estimates.Quantity ) OVER ( PARTITION BY Jobs.EstimateNumber ORDER BY ABS( Jobs.QuantityOrdered - Estimates.Quantity ) ASC ) CorrectQuantity

FROM Jobs

INNER JOIN Estimates

ON Jobs.EstimateNumber = Estimates.EstimateNumber ) AS QuantityTable

ON Estimates.EstimateNumber = QuantityTable.EstimateNumber AND Estimates.Quantity = QuantityTable.CorrectQuantity

Затем используйте это как подзапрос и сопоставьте JobNumber на основе соответствующего EstimateNumber. Однако мне не удалось понять, что, хотя JobNumber отличается, они могут иногда относиться к одной и той же оценке. Это вызывает по понятным причинам странное поведение, когда одно из заданий «выиграет» самое близкое совпадение в зависимости от основного порядка операций. И даже без этого поведения при присоединении он будет дублировать задания.

Какие-либо предложения?

Всегда ли в таблице «Оценки» EstimateNumber и Quantity уникальны?

Craig 30.03.2021 00:40

EstimateNumber не уникален, как показано в данных таблицах. Каждая величина уникальна для данного EstimateNumber.

NPC 30.03.2021 00:43
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
2
41
3

Ответы 3

Для ваших выборочных данных вы можете использовать точное совпадение:

select j.*, e.*
from jobs j left join
     estimates e
     on j.jobnumber = e.jobnumber and
        j.QuantityOrdered = e.quantity;

Однако ваш вопрос предполагает, что точное совпадение может не совпадать. Вы не указываете правила, определяющие «ближайший», но вы можете использовать для этого боковое соединение:

select j.*, e.*
from jobs j outer apply
     (select top (1) e.*
      from estimates e
      where j.jobnumber = e.jobnumber and
      order by abs(j.QuantityOrdered - e.quantity)
     ) e

Предполагая, что у вас не всегда может быть точное совпадение, из вашей формулировки, следующее должно дать вам образец вывода:

select j.JobNumber, e.*
from Jobs j
outer apply (
    select totalCost, OtherStuff from (
        select e.totalcost, e.OtherStuff, Row_Number() over(order by Abs(j.quantityordered-e.quantity)) rn
        from Estimates e
        where e.estimatenumber=j.estimatenumber
    )e where rn=1
)e

Попробуй это:

 ;with cte as(
      SELECT
        A.JobNumber,
        A.EstimateNumber,
        B.TotalCost,
        estimate_rank=row_number() over 
                      (partition by A.JobNumber,A.EstimateNumber
                       order by A.JobNumber,
                                A.EstimateNumber,ABS(A.QuantityOrdered-B.Quantity)
                             )
      FROM Jobs A
      JOIN Estimates B ON A.EstimateNumber=B.EstimateNumber
 )
 SELECT JobNumber,TotalCost
 FROM cte
 WHERE
 estimate_rank=1

Хорошие ответы должны включать объяснение в дополнение к коду, чтобы ОП мог учиться на нем.

Dale K 30.03.2021 01:14

Другие вопросы по теме