Цель
Я хочу объединить четыре столбца строки в одно значение с именем COMBINED_VALUE
.
Применяются следующие строгие правила:
COMBINED_VALUE
COMBINED_VALUE
INT
, три столбца определяются как NVARCHAR(100)
.Примечания
Среди первых вещей, которые я попробовал, было BINARY_CHECKSUM
. Это не удается, в результате чего для разных строк получается одно и то же значение:
SELECT
a, b,
binary_checksum_failing = BINARY_CHECKSUM(a,b)
FROM
(VALUES ('i01', CONVERT(DECIMAL(9,2), 100)),
('i01', CONVERT(DECIMAL(9,2), 10)),
('i01', CONVERT(DECIMAL(9,2), 1)) )v(a, b)
HASHBYTES
с «SHA2_256» работает лучше, но для этого нужен хорошо подготовленный COMBINED_VALUE
в качестве входных данных. Если это обеспечено, у него есть шанс создать «очень» уникальные хэши. Я обнаружил, что объединение столбцов должно работать, но необходимо заменить NULL и разделить столбцы.
Ниже вы найдете два фрагмента кода, показывающие мой текущий прогресс и дополнительные примечания. В первой части показано решение, основанное на преобразовании в NVARCHAR
, во второй части используется VARBINARY
. В строках примеров показаны крайние случаи и попытка спровоцировать сбой.
Вопросы
COMBINED_VALUE
при прохождении проверки?COMBINED_VALUE
? Есть ли более быстрый способ? Какова лучшая практика в целом?Сценарий №1:
DROP TABLE IF EXISTS #t
CREATE TABLE #t
(
id INT IDENTITY(1,1)
,DESCRIPTION NVARCHAR(100)
,number INT
,word1 NVARCHAR(100)
,word2 NVARCHAR(100)
,word3 NVARCHAR(100)
)
INSERT INTO #t(DESCRIPTION, number, word1, word2, word3)
VALUES
('base row' , 1, 'aa', 'aa', 'aa')
,('trailing space in different places', 1, 'aa', 'aa', 'aa ')
,('trailing space in different places', 1, 'aa', 'aa ', 'aa')
,('NULLs in different places' , 1, NULL, 'aa', 'aa')
,('NULLs in different places' , 1, 'aa', NULL, 'aa')
,('different rows, screwery with NULL', 1, 'aa', 'aa', NULL)
,('different rows, screwery with NULL', 1, 'aa', 'aa', '*NULL*')
,('different rows, screwery with sep.', 1, 'aa' , 'aa/SEP\aa', 'aa')
,('different rows, screwery with sep.', 1, 'aa/SEP\aa', 'aa', 'aa')
,('longer values'
, 1111111111, 'abcdefghijklmn', 'abcdefghijklmn', 'abcdefghijklmn')
DECLARE
@NULL_replacement NVARCHAR(10) = '*NULL*'
,@separator NVARCHAR(10) = '/SEP\'
;WITH cte AS
(
SELECT
id, DESCRIPTION, number,
word1, word2, word3,
COMBINED_VALUE,
valid_f
FROM
#t
OUTER APPLY
(SELECT valid_f = IIF(COUNT(*) = 0, 1, 0)
FROM (VALUES (word1)
,(word2)
,(word3) )v(v)
JOIN (VALUES (@NULL_replacement)
,(@separator ) )w(w)
ON v LIKE '%' + w + '%' COLLATE Czech_BIN2
)validator
OUTER APPLY
(SELECT COMBINED_VALUE = CONCAT_WS(
@separator
,COALESCE(CONVERT(NVARCHAR(100), number), @NULL_replacement)
,COALESCE(CONVERT(NVARCHAR(100), word1 ), @NULL_replacement)
,COALESCE(CONVERT(NVARCHAR(100), word2 ), @NULL_replacement)
,COALESCE(CONVERT(NVARCHAR(100), word3 ), @NULL_replacement))
)combiner
)
SELECT
id
,DESCRIPTION
,number
,word1_encapsulated = '>' + word1 + '<'
,word2_encapsulated = '>' + word2 + '<'
,word3_encapsulated = '>' + word3 + '<'
,COMBINED_VALUE_encapsulated = '>' + COMBINED_VALUE + '<'
,valid_f
,repeats
,COMBINED_VALUE_dlen = DATALENGTH(COMBINED_VALUE)
FROM
cte o
OUTER APPLY
(SELECT repeats = COUNT(*)
FROM cte i
WHERE o.COMBINED_VALUE LIKE i.COMBINED_VALUE COLLATE Czech_BIN2
)repeatfinder
ORDER BY
id
Я считаю приведенный выше пример доказательством того, что мне нужно заменить NULL и использовать разделитель. См. строки 4 и 5. Если эти столбцы объединить с помощью CONCAT
, вы получите тот же результат. Подробности смотрите в рубрике DESCRIPTION
.
NULL замена и разделитель
COMBINED_VALUE
Скрипт №2
После вышесказанного я придумал во многом похожий запрос, в котором VARBINARY
используется для уменьшения размера вывода, особенно для числовых данных. Замена нуля и разделитель встречаются «очень редко». Коды ASCII для них помечены как «неиспользуемые» в таблице ASCII.
DECLARE
@separator_bin VARBINARY(5) =
CONVERT(VARBINARY(5), CHAR(129)+CHAR(141)+CHAR(143)+CHAR(144)+CHAR(157))
,@NULL_repl_bin VARBINARY(5) =
CONVERT(VARBINARY(5), CHAR(157)+CHAR(144)+CHAR(143)+CHAR(141)+CHAR(129))
DECLARE
@separator_vc VARCHAR(10) = CONVERT(VARCHAR(10), @separator_bin, 2)
,@NULL_repl_vc VARCHAR(10) = CONVERT(VARCHAR(10), @NULL_repl_bin, 2)
;WITH cte AS
(
SELECT
id, DESCRIPTION, number,
word1, word2, word3,
COMBINED_VALUE,
valid_f, word1_binary
FROM
#t
OUTER APPLY
(SELECT
number_binary = CONVERT(VARBINARY(8000), number)
,word1_binary = CONVERT(VARBINARY(8000), word1 )
,word2_binary = CONVERT(VARBINARY(8000), word2 )
,word3_binary = CONVERT(VARBINARY(8000), word3 )
)binaries
OUTER APPLY
(SELECT valid_f = IIF(COUNT(*) = 0, 1, 0)
FROM (VALUES (CONVERT(VARCHAR(8000), number_binary, 2))
,(CONVERT(VARCHAR(8000), word1_binary , 2))
,(CONVERT(VARCHAR(8000), word2_binary , 2))
,(CONVERT(VARCHAR(8000), word3_binary , 2)) )v(v)
JOIN (VALUES (@separator_vc)
,(@NULL_repl_vc) )w(w)
ON CHARINDEX(w, v COLLATE Latin1_General_BIN2) > 0
)validator
OUTER APPLY
(SELECT COMBINED_VALUE =
COALESCE(number_binary, @NULL_repl_bin)
+ @separator_bin + COALESCE(word1_binary , @NULL_repl_bin)
+ @separator_bin + COALESCE(word2_binary , @NULL_repl_bin)
+ @separator_bin + COALESCE(word3_binary , @NULL_repl_bin)
)combiner
)
SELECT
id
,DESCRIPTION
,number
,word1_encapsulated = '>' + word1 + '<'
,word2_encapsulated = '>' + word2 + '<'
,word3_encapsulated = '>' + word3 + '<'
,COMBINED_VALUE
,valid_f
,repeats
,COMBINED_VALUE_dlen = DATALENGTH(COMBINED_VALUE)
,word1_varchar = CONVERT(VARCHAR(8000), word1_binary, 2)
,separator_varchar = @separator_varchar
FROM
cte o
OUTER APPLY
(SELECT repeats = COUNT(*)
FROM cte i
WHERE o.COMBINED_VALUE = i.COMBINED_VALUE
)repeatfinder
ORDER BY
id
@TN И, чтобы подчеркнуть главное, нет необходимости в санитарной обработке ввода. Отличный ответ!
Думаю, главный вопрос в том, почему вы используете HASHBYTES
в нескольких столбцах. Вы пытаетесь упредить механизм хранения, прокручивая свой собственный хэш-индекс? Если это так, SHA256, вероятно, в любом случае будет излишним, и вам все равно придется проверять фактические значения столбца, чтобы убедиться в совпадении.
@Charlieface Я не могу использовать хэш в качестве идентификатора строк?
Вы могли бы, но зачем? Он имеет риск коллизий и обычно не более эффективен, чем простое использование обычного первичного ключа для нескольких столбцов. Или используйте суррогатный IDENTITY
первичный ключ. Вы не показали свой вариант использования того, что вы на самом деле пытаетесь сделать с этими хэшами, или почему обычных ограничений недостаточно.
Я делаю это регулярно для дельта-анализа с постоянным вычисляемым столбцом по следующей схеме: [ContentHash] AS (CONVERT([binary](32),hashbytes('SHA2_256',concat(N'',col1,col2,col3)))) PERSISTED,
. Начальный N''
означает, что это будет двухбайтовая строка. Конечно, вы можете использовать любой вид ÌSNULL
, CASE
или CONVERT
, чтобы обеспечить конкретную замену значений.
небольшое дополнение: вы можете добавить разделитель, чтобы «aaa/bb/c» не создавал тот же хэш, что и «aa/ab/bc». Кстати: Concat очень надежен в работе с типами данных.
@GottfriedLesigang То же самое, я постоянно использую что-то подобное, просто пытаюсь сделать его неуязвимым для атак и оптимизировать здесь. Также вам следует добавить замену NULL
. А использование типа данных BINARY
делает его дешевле. И вам следует принять участие в этом. После всего этого вам не нужен разделитель. Возможно, вам следует - никаких гарантий с моей стороны - использовать метод одобренного ответа @T N.
Я мог бы предложить двоичную конкатенацию, которая включает в себя комбинацию следующих значений для каждого столбца:
IIF(col IS NULL THEN 0x01, 0x00)
,ISNULL(CAST(DATALENGTH(col) AS VARBINARY(MAX)), 0x)
иISNULL(CAST(col AS VARBINARY(MAX)), 0x)
.Части нулевого индикатора и длины данных могут быть выборочно опущены для типов столбцов с необнуляемым значением и фиксированной длины соответственно.
Это будет однозначно обрабатывать любую комбинацию столбцов с нулевым значением и переменной длины, и вам не нужно беспокоиться о разделителях в ваших данных.
Объединенный результат можно передать в хорошую хэш-функцию, например SHA-256. Вероятность коллизии SHA-256 составляет примерно 1 каждые 10 миллиардов лет, если вы добавляете в свою базу данных миллион уникальных строк каждую фемтосекунду.
SELECT
t.*,
--C.CombinedValue,
H.HashValue
FROM #t t
CROSS APPLY (
SELECT
+ IIF(number IS NULL, 0x01, 0x00)
+ ISNULL(CAST(DATALENGTH(number) AS VARBINARY(MAX)), 0x) -- Optional
+ ISNULL(CAST(number AS VARBINARY(MAX)), 0x)
+ IIF(word1 IS NULL, 0x01, 0x00)
+ ISNULL(CAST(DATALENGTH(word1) AS VARBINARY(MAX)), 0x)
+ ISNULL(CAST(word1 AS VARBINARY(MAX)), 0x)
+ IIF(word2 IS NULL, 0x01, 0x00)
+ ISNULL(CAST(DATALENGTH(word2) AS VARBINARY(MAX)), 0x)
+ ISNULL(CAST(word2 AS VARBINARY(MAX)), 0x)
+ IIF(word3 IS NULL, 0x01, 0x00)
+ ISNULL(CAST(DATALENGTH(word3) AS VARBINARY(MAX)), 0x)
+ ISNULL(CAST(word3 AS VARBINARY(MAX)), 0x)
AS CombinedValue
) C
CROSS APPLY (
SELECT HASHBYTES('SHA2_256',C.CombinedValue) AS HashValue
) H
ORDER BY T.id
При желании вышеизложенное можно легко изменить для использования CTE.
Примеры результатов:
См. эту db<>fiddle для демонстрации.
Я увидел ваш ответ только после того, как опубликовал свой. У тебя лучше. :-)
Есть какая-то конкретная причина для MAX
? 8000 это штраф?
Приведенный выше код предназначен для общего случая, включая большое разнообразие типов и общую длину двоичных данных, которая потенциально превышает 8000 байт. В вашем случае VARBINARY(8000) должен работать, но я не думаю, что будет какая-либо измеримая разница в производительности.
Отличный ответ, отличная концепция. Мне также нравится общая идея @Thorsten, но эта работает и эффективна. Кроме того, позволяет избежать проверки каждого поля на наличие запрещенного содержимого.
@Der U: Тогда, пожалуйста, рассмотрите возможность принятия этого ответа вместо моего. :-)
@ThorstenKettner Ого, у меня было. А потом я принял и твое. Чтобы оба были приняты. Чего ты не можешь. Что я теперь вспомнил. Да...
Насколько я знаю, в символе NVARCHAR можно хранить любое двоичное значение. Если у вас есть NVARCHAR2(100) и вам нужно какое-то значение, уникально представляющее эту строку, вам понадобится столько же байтов, сколько и исходная строка, включая место для хранения ее длины. Нет смысла каким-то образом преобразовывать эту строку, если у вас в любом случае останется столько же места, как и после некоторого шифрования.
Это означает, что лучше всего объединить ваши значения.
Учитывая, что SQL Server, пожалуй, худшая СУБД с точки зрения приведения и форматирования, это становится немного неуклюжим. Я думаю, вот оно:
SELECT
LEFT(COALESCE(str1, '') + REPLICATE(' ', 100), 100) +
RIGHT(REPLICATE('0', 3) + CAST(COALESCE(LEN(str1 + 'x') - 1, 999) AS NVARCHAR(3)), 3) +
LEFT(COALESCE(str2, '') + REPLICATE(' ', 100), 100) +
RIGHT(REPLICATE('0', 3) + CAST(COALESCE(LEN(str2 + 'x') - 1, 999) AS NVARCHAR(3)), 3) +
LEFT(COALESCE(str3, '') + REPLICATE(' ', 100), 100) +
RIGHT(REPLICATE('0', 3) + CAST(COALESCE(LEN(str3 + 'x') - 1, 999) AS NVARCHAR(3)), 3) +
CASE WHEN int1 < 0 THEN '-' WHEN int1 > 0 THEN '+' ELSE ' ' END +
COALESCE(RIGHT(REPLICATE('0', 11) + CAST(ABS(int1) AS NVARCHAR(11)), 11), '99999999999') AS code
FROM mytable;
Демо: https://dbfiddle.uk/zR5QshZg
Мне нравится общая идея этого подхода. Выходные данные всегда имеют одинаковую длину. Я не совсем понимаю, как обращаться с «int1», но мне все же кажется, что (хотя и не очень эффективно), это позволяет хорошо выполнить свою работу.
@Der U: Спасибо. Я форматирую int1 с ведущим знаком +/-, а затем с ведущими нулями. В PostgreSQL и Oracle это будет просто COALESCE(TO_CHAR(int1, 'S00000000000'), ' 99999999999')
.
Между тем, я понял, вы применяете знак, в том числе для положительных чисел, и место для нулей. Хорошие новости: SELECT FORMAT(COALESCE(int1, 99999999999), '+00000000000;-00000000000; 00000000000')
, (обратите внимание на пробел.)
@Der U: Приятно это знать. Я посмотрел FORMAT
и просто сделал это снова. Я не нашел «0» в списке спецификаторов документированного формата. Кажется, это хорошо спрятано или даже недокументировано.
Только что проверил себя и обнаружил, что применяются правила .NET Строки пользовательского числового формата. Ноль кажется хорошо документированным. Я не знал этого с такой степенью уверенности. Я просто попробовал однажды и обнаружил поведение, похожее на форматирование ячеек Excel. В частности, то, что вы можете форматировать по-разному для трех «диапазонов», очень похоже.
«Сможете ли вы заставить представленные сценарии провалиться?» Используйте только надуманные случаи со значениями, содержащими выбранные строки-разделители, как вы продемонстрировали на своих «ерундовых» примерах. «Есть ли лучший способ получить COMBINED_VALUE?» Да, поскольку индикаторы длины и нулевых значений отделены (вне диапазона) от данных, это устраняет необходимость использования магических строк в качестве разделителей или индикаторов нулевых значений.