Início / dba / Perguntas / 343714
Accepted
ToC
Asked: 2024-11-20 03:53:40 +0800 CST

Sql Server: Consulta para analisar e validar códigos

  • 772

Temos uma tabela #ValidCode com uma lista de códigos válidos como: 'A', 'B', 'C', etc. Outra tabela chamada #SourceData com dados de entrada — que vêm como uma combinação de tokens válidos e inválidos (às vezes duplicados).

Ex:

  • 'A;B;C' ( válido )
  • 'A;A;A;A;A;B' ( Válido )
  • 'ad;df;A;B' ( inválido )

Tentando encontrar uma abordagem de consulta ótima para processar essas strings para encontrar linhas válidas em #SourceData . Veja o exemplo abaixo:

DROP TABLE IF EXISTS #ValidCode
GO
CREATE TABLE #ValidCode
(
      ID        INT             IDENTITY(1,1)
    , Code      CHAR(1)
)
INSERT INTO #ValidCode (Code) VALUES ('A'), ('B'), ('C')
GO
DROP TABLE IF EXISTS #SourceData 
GO
CREATE TABLE #SourceData 
(
      ID        INT             IDENTITY(1,1)
    , Codes     VARCHAR(500)
    , Is_Valid  BIT
    , Is_Split  BIT
)

INSERT INTO #SourceData (Codes) 
VALUES    ('A;B;C')
        , ('B;A')
        , ('B;B;B;C;C;A;A;B')
        , ('B;Z;1')
        , ('B;ss;asd')


SELECT * FROM #ValidCode
SELECT * FROM #SourceData

A consulta processaria os dados na tabela #SourceData e atualizaria o sinalizador Is_Valid para que eles pudessem ser consumidos no processo subsequente.

Regras :

  • Cada token deve ser válido para que toda a linha da coluna seja válida (linhas 1 a 3)
  • Mesmo que um token seja inválido, o valor da linha inteira será inválido (linhas 4 e 5)

Então, esta é a saída preferida:

EU IA Códigos É_válido
1 ABC 1
2 B;Um 1
3 B;B;B;C;C;A;A;B 1
4 B;Z;1 0
5 B;ss;asd 0

Abordagem atual : Faça um loop por cada linha em #SourceData e divida-as no delimitador ';', depois compare-as com a tabela #ValidCode . Se todos os tokens forem individualmente válidos, marque a linha em #SourceData como válida ( flag Is_Valid ). Caso contrário, marque como inválida. A WHILEabordagem de loop funciona, mas é lenta.

O #SourceData pode ter até 3 milhões de linhas. Com cada linha tendo várias combinações de valores válidos duplicados ('A;A;A;A') e inválidos ('A;as;sdf;B')

Existe uma abordagem melhor?

Obrigado!

query-performance

2 respostas

  1. -- primeira coisa que vem à mente:

    SELECT 
    sd.ID
    , sd.Codes
    , CASE 
        WHEN NOT EXISTS (
            SELECT x.[value] 
            FROM string_split(sd.Codes, ';') as x
            LEFT OUTER JOIN #ValidCode as vc
                ON x.[value] = vc.Code
            WHERE vc.Code IS NULL
        )
        THEN 1
        ELSE 0
      END as is_valid
FROM #SourceData as sd

    Pode ser mais ideal criar uma tabela filha para os códigos SourceData divididos em linhas.

    • 1
  2. Best Answer

    Uma maneira relacional de fazer isso é dividindo o #SourceDataprimeiro (felizmente você tem acesso à STRING_SPLIT()função apesar de estar em uma versão desatualizada do SQL Server), depois obtendo as linhas que não correspondem ao seu #ValidCodese, finalmente, usando essas linhas para determinar o que Is_Validhá na #SourceDatatabela original.

    Aqui está um exemplo de como fazer isso:

    ;WITH _BadData AS
(
    SELECT DISTINCT SD.ID
    FROM #SourceData AS SD
    CROSS APPLY STRING_SPLIT(SD.Codes, ';') AS SS
    WHERE NOT EXISTS
    (
        SELECT 1 AS RowExists
        FROM #ValidCode AS VC
        WHERE SS.[value] = VC.Code
    )
)

SELECT
    SD.ID,
    SD.Codes,
    ISNULL(SD.Is_Valid, IIF(BD.ID IS NULL, 1, 0)) AS Is_Valid,
    SD.Is_Split
FROM #SourceData AS SD
LEFT JOIN _BadData AS BD
    ON SD.ID = BD.ID;

    Aqui está um repositório dbfiddle.uk demonstrando esse código .

    Observe que dependendo do tamanho #SourceDatae do plano de execução gerado, você pode querer materializar os resultados da STRING_SPLIT() função, todo o CTE em si, ou ambos, para uma tabela temporária primeiro, antes de usá-la na segunda metade da consulta acima para o final LEFT JOIN. Mas eu presumo que isso deve ser mensuravelmente melhor do que fazer um loop em suas linhas, uma por uma.

    • 1

relate perguntas