Блог

Я действительно не знаю, как это назвать, но это не так сложно объяснить

В принципе, у меня есть такой результат

 Similarity ColumnA   ColumnB   ColumnC
1          SomeValue NULL      SomeValue
2          NULL      SomeB     NULL
3          SomeValue NULL      SomeC
4          SomeA     NULL      NULL
  

Этот результат создается путем сопоставления набора строк с другой таблицей. Каждая строка также содержит некоторые значения для этих столбцов ..C, которые я не хочу каким-либо образом агрегировать.

Что-то вроде min / max работает очень хорошо, но я не могу понять, как заставить его учитывать наибольшее сходство, а не только значение min / max. На самом деле мне не нужны значения min / max, мне нужно первое ненулевое значение с наибольшим сходством.

В идеале результат должен выглядеть следующим образом

 ColumnA   ColumnB   ColumnC
SomeA     SomeB     SomeC
  

Я хотел бы иметь возможность эффективно присоединяться к временному результату для вычисления остальных, и я изучал различные варианты. Кое-что, что я рассматривал, это создание агрегата SQL Server CLR, который выдает «первое» ненулевое значение, но я не уверен, существует ли вообще такая вещь, как first или last при запуске агрегата для результата.

Хорошо, итак, я разобрался, у меня изначально были проблемы с тем, что UPDATE FROM и JOIN плохо сочетались друг с другом. Я рассчитывал, что UPDATE это просто произойдет несколько раз, и это даст мне правильные результаты, однако, такой гарантии от SQL Server нет (на самом деле это неопределенное поведение, и, хотя оно, казалось, работало, у нас ничего подобного не будет), но поскольку вы можете работать UPDATE с CTE, я объединил это с OUTER APPLY , чтобы выбрать ровно 1 строку, чтобы дополнить отсутствующее значение, если это возможно.

Вот и все, что касается тестовых данных.

 DECLARE @cost TABLE (
    make nvarchar(100) not null,
    model nvarchar(100),
    a numeric(18,2),
    b numeric(18,2)
);

INSERT @cost VALUES ('a%', null, 100, 2);
INSERT @cost VALUES ('a%', 'a%', 149, null);
INSERT @cost VALUES ('a%', 'ab', 349, null);
INSERT @cost VALUES ('b', null, null, 2.5);
INSERT @cost VALUES ('b', 'b%', 249, null);
INSERT @cost VALUES ('b', 'b', null, 3);

DECLARE @unit TABLE (
    id int,
    make nvarchar(100) not null,
    model nvarchar(100)
);

INSERT @unit VALUES (1, 'a', null);
INSERT @unit VALUES (2, 'a', 'a');
INSERT @unit VALUES (3, 'a', 'ab');
INSERT @unit VALUES (4, 'b', null);
INSERT @unit VALUES (5, 'b', 'b');

DECLARE @tmp TABLE (
    id int,
    specificity int,
    a numeric(18,2),
    b numeric(18,2),
    primary key(id, specificity)
);

INSERT @tmp 
OUTPUT inserted.* --FOR DEBUGGING
SELECT 
    unit.id
    , ROW_NUMBER() OVER (
        PARTITION BY unit.id 
        ORDER BY cost.make DESC, cost.model DESC
    ) AS specificity
    , cost.a
    , cost.b
FROM @unit unit
INNER JOIN @cost cost ON unit.make LIKE cost.make
    AND (cost.model IS NULL OR unit.model LIKE cost.model)
;

--fix the holes
WITH tmp AS (
    SELECT * 
    FROM @tmp 
    WHERE specificity = 1 
        AND (a IS NULL OR b IS NULL) --where necessary
)
UPDATE tmp
SET 
    tmp.a = COALESCE(tmp.a, a.a)
    , tmp.b = COALESCE(tmp.b, b.b)
OUTPUT inserted.* --FOR DEBUGGING
FROM tmp
OUTER APPLY ( 
    SELECT TOP 1 a 
    FROM @tmp a 
    WHERE a.id = tmp.id 
        AND a.specificity > 1 
        AND a.a IS NOT NULL 
    ORDER BY a.specificity
    ) a
OUTER APPLY ( 
    SELECT TOP 1 b 
    FROM @tmp b 
    WHERE b.id = tmp.id 
        AND b.specificity > 1 
        AND b.b IS NOT NULL 
    ORDER BY b.specificity
    ) b
;