Início / dba / Perguntas / 53578
Accepted
Matthew
Asked: 2013-11-20 09:35:36 +0800 CST

Qual é o método SQL apropriado para "Safe to SUM"?

  • 772

No SQL Server 2008 R2+, infelizmente, temos algumas nvarcharcolunas armazenando dados numéricos.

Em alguns casos, queremos SUMos dados quando eles podem ser somados. O problema é que ISNUMERIC()retorna 1 para um número de entradas com parâmetros inválidos para SUM.... Entendo o que está acontecendo aqui, mas estou procurando uma solução alternativa.

Por exemplo ISNUMERIC('10,1') = 1, mas não posso incluir esse valor em um SUM, então gostaria que esse valor fosse excluído de SUM... e quaisquer pontos de dados, de fato, que não possam ser parâmetros SUMsem serem massageados...

Então, estou me perguntando qual é a maneira preferida de determinar se o conteúdo de uma coluna alfanumérica pode ou não ser somado?

EDIT: Vale a pena notar que esta é uma decolagem desta resposta no SO:

https://stackoverflow.com/a/15833014/369775

mas não estou convencido de que os métodos fornecidos nessa resposta sejam ideais. Eu concordo com HLGEM :

https://stackoverflow.com/a/338292/369775

A melhor solução seria parar de armazenar números inteiros em uma coluna varchar. Claramente, há um problema de dados em que os dados são interpretáveis ​​como numéricos, mas não podem ser convertidos como tal.

sql-server-2008-r2 aggregate

1 respostas

  1. Best Answer

    O desafio de confiar ISNUMERIC()nisso é que ele pode analisar muitos dados que não podem ser somados e dar um passe. Não é IS_ELIGIBLE_FOR_SUM()- está apenas retornando true se a entrada puder ser convertida em qualquer um dos tipos numéricos. As vírgulas são permitidas porque a conversão para MONEY, por exemplo, funciona com sucesso:

    SELECT CONVERT(MONEY,'11,2');

    Então isso também funciona:

    SELECT SUM(CONVERT(MONEY,'11,2'));

    O que provavelmente não é o que você quer. Acho que seria mais seguro substituir as vírgulas por pontos antes de tentar executar as operações, ou apenas excluí-las e filtrar as linhas desta forma:

    DECLARE @t TABLE(n NVARCHAR(32));

INSERT @t VALUES(N'11,2'),(N'32'),(N'-32.4'),(N'32323'),(N'^'),(N'.'),(N'-');

SELECT SUM(CASE WHEN ISNUMERIC(n) = 1
    AND n NOT LIKE '%[^0-9.-]%' THEN CONVERT(MONEY,n) END)
  FROM @t
  WHERE ISNUMERIC(n) = 1 AND n NOT LIKE '%[^0-9.-]%';

    A razão pela qual você deve executar uma CASEexpressão além do filtro é que você não pode controlar como o otimizador processará a instrução - sem CASEele pode tentar executar a conversão antes do filtro.

    Obviamente, o curso de ação mais inteligente seria parar de usar o tipo de dados errado em primeiro lugar. Se você continuar armazenando dados sujos em tipos de dados imprudentes, não poderá reclamar de "ideal" ...

    • 4

relate perguntas