none
Select retorna valor truncado RRS feed

  • Pergunta

  • Olá amigos, estou com o seguinte problema: Preciso calcular a disponibilidade percentual de um equipamento durante um determinado mês (o equipamento X ficou disponivel 95,5% durante o mês de janeiro por exemplo).

     

    A tabela ID_DISPONIBILIDADE é formada da seguinte forma:

     

    id nvarchar(20)

    dia int

    mes int

    ano int

    disponibilidade float

    indisponibilidade float

    tempo_disponivel int (número de segundos no dia que o equipamento fica disponivel, no máximo 86400)

    tempo_indisponivel int

    tipo_relatorio nvarchar(10)

     

    Um registro de exemplo seria:

    IZBR401AV  |  17  |  10  |  2008  | 0,93424767255  |  0,06575231254  |  80719 | 5681  |  DFT

     

    Para calcular a disponibilidade percentual mensal de equipamento fiz uma procedure que recebe três parâmetros: os numeros do mes e ano (@mes, @ano) e o numero de dias (@ndias) do mes indicado (31 para janeiro e 29 para fevereiro em anos bissextos). Eis o sql que desenvolvi:

     

    SELECT 100*(SUM(tempo_disponivel)/(@ndias*86400)) AS Expr1

    FROM ID_DISPONIBILIDADE

    WHERE (tipo_relatorio = 'DFT') AND (mes = @mes) AND (ano = @ano) AND (id = 'IZBR401AV')

     

    Porém como não posso garantir que para todos os dias do mês especificado existe um registro nessa tabela modifiquei o SQL acima para:

     

    DECLARE @ndias int

    SELECT @ndias=COUNT(*) FROM ID_DISPONIBILIDADE

    WHERE (tipo_relatorio = 'DFT') AND (mes = @mes) AND (ano = @ano) AND (id = 'IZBR401AV')

     

    SELECT 100*(SUM(tempo_disponivel)/(@ndias*86400)) AS Expr1

    FROM ID_DISPONIBILIDADE

    WHERE (tipo_relatorio = 'DFT') AND (mes = @mes) AND (ano = @ano) AND (id = 'IZBR401AV')

     

    O problema é que quando eu executo os SQL's acima, só recebo zero como resultado. Creio que deva ser por causa dos tipos de dados envolvidos (tempo_disponivel - int e resultado do cálculo em float). A resposta deveria ser 70,86.

    MINHA PRIMEIRA PERGUNTA: Como resolvo o problema acima???

     

    SEGUNDA PERGUNTA:

    O SQL acima faz parte de uma instrução maior que busca a disponibilidade e indisponibilidade de todos os equipamentos em um determinado mês. Até onde desenvolvi seria algo como:

     

    SELECT C.id_barra, C.descricao, C.tolerancia,

     

    ( SELECT 100*(SUM(B.tempo_disponivel)/(@ndias*86400)) AS Expr1

    FROM ID_DISPONIBILIDADE B

    WHERE (B.tipo_relatorio = 'DFT') AND (B.mes = @mes) AND (B.ano = @ano) AND (C.id_barra = B.id) ) AS DISPONIBILIDADE,

     

    ( SELECT 100*(SUM(B.tempo_indisponivel)/(@ndias*86400)) AS Expr1

    FROM ID_DISPONIBILIDADE B

    WHERE (B.tipo_relatorio = 'DFT') AND (B.mes = @mes) AND (B.ano = @ano) AND (C.id_barra = B.id) ) AS INDISPONIBILIDADE,

     

    FROM DFT C, ID_DISPONIBILIDADE A

    WHERE C.id_barra = A.id AND mes = @mes AND ano = @ano

    GROUP BY C.id_barra, C.descricao, C.tolerancia

     

    A tabela DFT tem os id's dos equipamentos:

     

    id_barra      nvarchar(20)

    descricao    nvarchar(20)

    tolerancia   int

     

    Um registro seria algo como: LITF604M0VBV4   |   LIBP4-02   |  5

     

    PERGUNTA 2: Ficaria certo esse sql? Está otimizado? Como corrigir?

     

    Pessoal, desculpa o tamanho do post mas tentei dar o máximo de informações possivel. Agradeço desde já pela atenção e fico no aguardo online.

     

     

    quarta-feira, 23 de janeiro de 2008 14:56

Respostas

  • Olá Newton,

     

    Provavelmente, a precisão e a escala do Numeric não foram suficientes. Ao invés de usar NUMERIC(3,2), tente um intervalo mais amplo como NUMERIC (10,2). Essa última suporta números até 10.000.000 por exemplo.

     

    [ ]s,

     

    Gustavo

     

    quarta-feira, 23 de janeiro de 2008 17:23

Todas as Respostas

  • Olá Newton,

     

    Segundo o padrão ANSI, toda vez que há uma divisão numérica, o numerador é implicitamente convertido para INT. Como o cálculo da disponibilidade tenderá sempre a ser um valor numérico entre 0 e 1, a parte inteira será igual a zero. Para contornar esse problema, você deve converter explicitamente o numerador. Ex:

     

    Code Snippet

    -- Essa instrução considera somente a parte inteira

    SELECT 1 / 2

    -- Essa instrução considera a parte inteira e a parte decimal

    SELECT CAST(1 AS NUMERIC(3,2)) / 2

     

     

    Sobre sua instrução SELECT, você está utilizando uma subquerie do tipo Expression (está no lugar de uma coluna) e Correlated (correlaciona resultados da query externa e interna). Você pode substituí-la por um JOIN, mas não creio que haverá muita otimização. Para garantir o desempenho máximo usando uma técnica desse tipo, você precisa garantir duas coisas:

     

    - Indexe as colunas id_barra e id (das tabelas Disponibilidade e DTF)

    - Se possível, crie um índice sobre a coluna tempo disponível e ID (se o campo id tiver um índice clustered não o inclue nesse segundo índice

     

    Vale a pena lembrar, que segundo a gerência de disponibilidade do ITIL, o seu cálculo está incorreto. O cálculo da disponibilidade deve levar em conta o tempo em que o serviço ficou disponível, o tempo máximo em que ele poderia ficar disponível, mas também o tempo de paradas planejado. Isso significa que se o mês tem 720 horas (30 * 24), e o serviço ficou disponível apenas por 700 horas, a disponibilidade seria 97,22% (700 / 720). No entanto, essa parada foi planejada e nesse caso não é considerada no cálculo. Para esse meu exemplo, mesmo com a parada, a disponibilidade ainda é de 100%. A parada foi planejada e não ocasionada por conta de uma falha.

     

    [ ]s,

     

    Gustavo

    quarta-feira, 23 de janeiro de 2008 16:47
  •  

    Fiz o que vc recomendou, porém o seguinte erro foi disparado:

     

    Arithmetic overflow error converting int to data type numeric.

     

    Eis o sql modificado:

    SELECT CAST( SUM(tempo_disponivel) AS NUMERIC(3,2))/(@ndias*86400) AS Expr1

    FROM ID_DISPONIBILIDADE

    WHERE (tipo_relatorio = 'DFT') AND (mes = @mes) AND (ano = @ano) AND (id = 'IZBR401AV')

     

     

    O que fazer agora?

    quarta-feira, 23 de janeiro de 2008 17:16
  • Olá Newton,

     

    Provavelmente, a precisão e a escala do Numeric não foram suficientes. Ao invés de usar NUMERIC(3,2), tente um intervalo mais amplo como NUMERIC (10,2). Essa última suporta números até 10.000.000 por exemplo.

     

    [ ]s,

     

    Gustavo

     

    quarta-feira, 23 de janeiro de 2008 17:23
  •  

    Gustavo, aumentei a precisão como vc recomendou e deu certo, porém com o novo SQL nenhum resultado foi retornado.

    Eis o novo SQL:

     

    SELECT C.id_barra, C.descricao,

    ( SELECT 100*(CAST(SUM(tempo_disponivel) AS NUMERIC(10,2))/(SELECT COUNT(*) FROM ID_DISPONIBILIDADE X WHERE (X.tipo_relatorio = 'DFT') AND (X.mes = @mes) AND (X.ano = @ano) AND (C.id_barra = X.id))*86400)

    FROM ID_DISPONIBILIDADE B WHERE (B.tipo_relatorio = 'DFT') AND (B.mes = @mes) AND (B.ano = @ano) AND (C.id_barra = B.id) ) AS Disp,

     

    ( SELECT 100*(CAST(SUM(tempo_disponivel) AS NUMERIC(10,2))/(SELECT COUNT(*) FROM ID_DISPONIBILIDADE X WHERE (X.tipo_relatorio = 'DFT') AND (X.mes = @mes) AND (X.ano = @ano) AND (C.id_barra = X.id))*86400)

    FROM ID_DISPONIBILIDADE B WHERE (B.tipo_relatorio = 'DFT') AND (B.mes = @mes) AND (B.ano = @ano) AND (C.id_barra = B.id) ) AS Indisp

     

    FROM DFT C, ID_DISPONIBILIDADE A WHERE C.id_barra = A.id AND A.mes = @mes AND A.ano = @ano

    GROUP BY C.id_barra, C.descricao

     

    Esse sql deveria retornar as disponibilidades/indisponibilidades de todos equipamentos durante o mês e ano especificados. Analisando as tabelas, vc acha que esta errado esse sql???

     

    Agradeço a atenção e fico no aguardo online.

    quarta-feira, 23 de janeiro de 2008 17:59
  • Olá Newton,

     

    Nenhum resultado é retornado quer dizer que não veio linhas ou os resultados continuam zerados ? Seria bom você testar as queries individuais primeiro. Veja se suas subqueries retornam alguma coisa.

     

    [ ]s,

     

    Gustavo

     

    quarta-feira, 23 de janeiro de 2008 18:22
  • Gustavo, encontrei o problema: os id_barra da tabela DFT estavam como nvarchar(10) enquanto que os id's da tabela ID_DISPONIBILIDADE estavam com nvarchar(20); por isso nenhum resultado era retornado. Corrigir apagando os dados mandando inserir registros atualizados.

     

    O SQL Final ficou assim:

     

    CREATEPROCEDURE [dbo].[RelatorioMensal_DFT]

    @mes int,

    @ano int

    AS

    SET NOCOUNT ON --- O QUE ISSO SIGNIFICA?????????????

     

    SELECT C.id_barra,

    100*CAST(SUM(A.tempo_disponivel) AS NUMERIC(10,2))/(86400*(SELECT COUNT(*) FROM ID_DISPONIBILIDADE X WHERE X.id = C.id_barra AND X.mes = @mes AND X.ano = @ano)) AS Disp,

    100*CAST(SUM(A.tempo_indisponivel) AS NUMERIC(10,2))/(86400*(SELECT COUNT(*) FROM ID_DISPONIBILIDADE X WHERE X.id = C.id_barra AND X.mes = @mes AND X.ano = @ano)) AS InDisp

    FROM DFT C, ID_DISPONIBILIDADE A WHERE C.id_barra = A.id AND A.mes = @mes AND A.ano = @ano AND C.id_barra = A.id

    GROUP BY C.id_barra

    ORDER BY C.id_barra

     

     

    Muito obrigado pela ajuda Gustavo; vou marcar como resposta.

     

    Queria te pedir mais uma coisa: não sou DBA e por isso não estou acostumado com termos tais como índices, índices clustered, shrink, tablespace, entre outros termos. Porém aqui na empresa terei que assumir esse papel tanto com SQL Server Express Edition 2005 quanto com PostgreSQL. Quero te pedir links de manuais, tutoriais, sites sobre esses termos (indices, indices clustered shrink,tablespace, politicas de backup) que expliquem o que são, para que servem e/ou quando utilizar (de maneira genérica)

     

    Valeu pela ajuda. um grande abraço. t+

     

     

     

     

     

    quarta-feira, 23 de janeiro de 2008 19:54
  • Olá Newton,

     

    Foi o que pensei. O problema não era a consulta, mas a comparação dos campos. Por isso pedi para você testar as subqueries primeiro.

     

    Me desculpe pelos termos mais aprofundados. Quando vemos uma dúvida, pensamos na resposta, mas as vezes simplesmente repassamos o pensamento na íntegra (ainda bem que não utilizei as palavras tiebreaker e predicado (rs)).

     

    Eu diria "Não se preocupe". Não dá para aprender tantos termos da noite para o dia. Sou DBA já tem mais de 5 anos e ainda tenho muitos "termos" para aprender. Com estudo, persistência e dedicação você vai aprender esses e muitos outros.

     

    Tenho escrito alguns artigos sobre SQL Server 2005 em um site onde sou colunista. Você poderá encontrar alguns artigos práticos que talvez o ajudem no dia a dia. Dê uma olhada no link abaixo: http://www.plugmasters.com.br/sys/colunistas/145/Gustavo-Maia-Aguiar

     

    Outros sites que considero interessantes são:

     

    - http://www.sqlservercentral.com

    - http://www.devmedia.com.br

    - http://www.mssqlcity.com

    - www.sql-server-performance.com

     

    Tenho certa dificuldade em recomendar manuais, visto que sou um apto devorador de livros e costumo utilizá-los como minha principal referência. Posso recomendar alguns se você quiser.

     

    Como você está trabalhando com dois SGBDs, acho que um pouco de fundamentos é importante. Algumas boas bibliografias com o Navathe, Silberschatz e Date podem ser valiosas. Elas não vão mostrar como fazer um backup, implementar um cluster ou ainda montar uma query SQL como a sua, mas vão dar toda a base teórica para que você compreenda muitas coisas.

     

    Na parte de SQL Server, para você que está iniciando, eu recomendaria algumas bibliografias

     

    - Microsoft SQL Server 2005 - Técnicas Aplicadas

    - SQL Server 2005 para desenvolvedores

    - SQL Server 2005: Curso Completo

    - Microsoft SQL Server 2005 - Fundamento de banco de dados

     

    Depois que ler esses quatro (a leitura é bem tranqüila) posso recomendar algo mais avançado. Só pediria que se for o caso, iniciemos um novo post para não poluir esse.

     

    No resto mais, que bom que resolveu o seu problema.

     

    [ ]s,

     

    Gustavo

     

     

    quinta-feira, 24 de janeiro de 2008 00:22