none
Select total, média e desvio padrão RRS feed

  • Pergunta

  • É possível no diagrama abaixo  fazer uma consulta que retorne uma lista com o resumo estatistitico dos diferentes "nome" da tabela "PF_Proprietario" com suas respectivas diferentes "descricao" das tabelas "Meio_Amb_Comp" e "Meio_Amb_Prod" , ou seja, numero total por especie por "nome", média/nome especie  e desvio padrão/ nome especie da tabela '"Programa_Censo" ? Ou seja teríamos, a lista de nomes com com seus quantitativos individuais de cada espécie e a média e desvio padrão da especie, na figura abaixo do diagrama eu tenho uma tabela de resultado para exemplo:


    • Editado Wilson Boris terça-feira, 10 de junho de 2014 00:12 erro texto
    terça-feira, 10 de junho de 2014 00:09

Respostas

Todas as Respostas

  • Deleted
    terça-feira, 10 de junho de 2014 00:24
  • Grande José.Diz!

    Sim, na tabela Programa_Censo  o registro ou é animal de companhia ou é de produção. A coluna não preenchida fica null.

    terça-feira, 10 de junho de 2014 00:33
  • Deleted
    terça-feira, 10 de junho de 2014 01:12
  • USE [portal]
    GO
    
    /****** Object:  Table [dbo].[Programa_Censo]    Script Date: 06/10/2014 07:42:52 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Programa_Censo](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[data_Cadastro] [date] NULL,
    	[cod_Endereco] [int] NULL,
    	[cod_Funcionario] [int] NULL,
    	[cod_Proprietario] [int] NULL,
    	[cod_Animal_Companhia] [int] NULL,
    	[cod_Animal_Producao] [int] NULL,
    	[populacao] [int] NULL,
     CONSTRAINT [PK_programa_Censo] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[Programa_Censo]  WITH CHECK ADD  CONSTRAINT [FK_Programa_Censo_Animal_Companhia] FOREIGN KEY([cod_Animal_Companhia])
    REFERENCES [dbo].[Animal_Companhia] ([id])
    GO
    
    ALTER TABLE [dbo].[Programa_Censo] CHECK CONSTRAINT [FK_Programa_Censo_Animal_Companhia]
    GO
    
    ALTER TABLE [dbo].[Programa_Censo]  WITH CHECK ADD  CONSTRAINT [FK_Programa_Censo_Animal_Producao] FOREIGN KEY([cod_Animal_Producao])
    REFERENCES [dbo].[Animal_Producao] ([id])
    GO
    
    ALTER TABLE [dbo].[Programa_Censo] CHECK CONSTRAINT [FK_Programa_Censo_Animal_Producao]
    GO
    
    ALTER TABLE [dbo].[Programa_Censo]  WITH CHECK ADD  CONSTRAINT [FK_Programa_Censo_Endereco] FOREIGN KEY([cod_Endereco])
    REFERENCES [dbo].[Endereco] ([id])
    GO
    
    ALTER TABLE [dbo].[Programa_Censo] CHECK CONSTRAINT [FK_Programa_Censo_Endereco]
    GO
    
    ALTER TABLE [dbo].[Programa_Censo]  WITH CHECK ADD  CONSTRAINT [FK_Programa_Censo_PF_Funcionario] FOREIGN KEY([cod_Funcionario])
    REFERENCES [dbo].[PF_Funcionario] ([id])
    GO
    
    ALTER TABLE [dbo].[Programa_Censo] CHECK CONSTRAINT [FK_Programa_Censo_PF_Funcionario]
    GO
    
    ALTER TABLE [dbo].[Programa_Censo]  WITH CHECK ADD  CONSTRAINT [FK_Programa_Censo_PF_Proprietario] FOREIGN KEY([cod_Proprietario])
    REFERENCES [dbo].[PF_Proprietario] ([id])
    GO
    
    ALTER TABLE [dbo].[Programa_Censo] CHECK CONSTRAINT [FK_Programa_Censo_PF_Proprietario]
    GO
    
    USE [portal]
    GO
    
    /****** Object:  Table [dbo].[PF_Proprietario]    Script Date: 06/10/2014 07:44:17 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[PF_Proprietario](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[data_Cadastro] [date] NULL,
    	[nome] [nvarchar](50) NULL,
    	[data_Nasc] [date] NULL,
    	[email] [nvarchar](50) NULL,
    	[fone_Principal] [nvarchar](50) NULL,
     CONSTRAINT [PK_Ag_Soc_Proprietario] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    USE [portal]
    GO
    
    /****** Object:  Table [dbo].[Animal_Companhia]    Script Date: 06/10/2014 07:44:39 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Animal_Companhia](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[data_Cadastro] [date] NULL,
    	[nome] [nvarchar](50) NULL,
    	[cod_Sexo] [int] NULL,
    	[cod_Especie_Comp] [int] NULL,
    	[idade] [nchar](10) NULL,
    	[id_Microchip] [nvarchar](50) NULL,
    	[num_CIA] [int] NULL,
    	[cod_Raca] [int] NULL,
    	[cod_Castrado] [int] NULL,
    	[cod_Acesso_Rua] [int] NULL,
    	[pelagem] [nvarchar](50) NULL,
     CONSTRAINT [PK_Ag_Soc_Animal_Companhia] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Carteira de Identificação Animal' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Animal_Companhia', @level2type=N'COLUMN',@level2name=N'num_CIA'
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'É castrado? Sim ou Não' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Animal_Companhia', @level2type=N'COLUMN',@level2name=N'cod_Castrado'
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tem acesso a Rua? Sim ou Não' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Animal_Companhia', @level2type=N'COLUMN',@level2name=N'cod_Acesso_Rua'
    GO
    
    ALTER TABLE [dbo].[Animal_Companhia]  WITH CHECK ADD  CONSTRAINT [FK_Animal_Companhia_Adm_Sim_Nao] FOREIGN KEY([cod_Castrado])
    REFERENCES [dbo].[Adm_Sim_Nao] ([id])
    GO
    
    ALTER TABLE [dbo].[Animal_Companhia] CHECK CONSTRAINT [FK_Animal_Companhia_Adm_Sim_Nao]
    GO
    
    ALTER TABLE [dbo].[Animal_Companhia]  WITH CHECK ADD  CONSTRAINT [FK_Animal_Companhia_Adm_Sim_Nao1] FOREIGN KEY([cod_Acesso_Rua])
    REFERENCES [dbo].[Adm_Sim_Nao] ([id])
    GO
    
    ALTER TABLE [dbo].[Animal_Companhia] CHECK CONSTRAINT [FK_Animal_Companhia_Adm_Sim_Nao1]
    GO
    
    ALTER TABLE [dbo].[Animal_Companhia]  WITH CHECK ADD  CONSTRAINT [FK_Animal_Companhia_MeioAmb_Animal_Comp] FOREIGN KEY([cod_Especie_Comp])
    REFERENCES [dbo].[MeioAmb_Animal_Comp] ([id])
    GO
    
    ALTER TABLE [dbo].[Animal_Companhia] CHECK CONSTRAINT [FK_Animal_Companhia_MeioAmb_Animal_Comp]
    GO
    
    ALTER TABLE [dbo].[Animal_Companhia]  WITH CHECK ADD  CONSTRAINT [FK_Animal_Companhia_MeioAmb_AnimalComp_Raca] FOREIGN KEY([cod_Raca])
    REFERENCES [dbo].[MeioAmb_AnimalComp_Raca] ([id])
    GO
    
    ALTER TABLE [dbo].[Animal_Companhia] CHECK CONSTRAINT [FK_Animal_Companhia_MeioAmb_AnimalComp_Raca]
    GO
    
    ALTER TABLE [dbo].[Animal_Companhia]  WITH CHECK ADD  CONSTRAINT [FK_Animal_Companhia_Sexo] FOREIGN KEY([cod_Sexo])
    REFERENCES [dbo].[Sexo] ([id])
    GO
    
    ALTER TABLE [dbo].[Animal_Companhia] CHECK CONSTRAINT [FK_Animal_Companhia_Sexo]
    GO
    
    USE [portal]
    GO
    
    /****** Object:  Table [dbo].[Animal_Producao]    Script Date: 06/10/2014 07:44:55 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Animal_Producao](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[data_Cadastro] [date] NULL,
    	[cod_Especie_Producao] [int] NULL,
    	[quantidade] [int] NULL,
     CONSTRAINT [PK_Ag_Soc_Animal_Producao] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[Animal_Producao]  WITH CHECK ADD  CONSTRAINT [FK_Animal_Producao_MeioAmb_Animal_Prod] FOREIGN KEY([cod_Especie_Producao])
    REFERENCES [dbo].[MeioAmb_Animal_Prod] ([id])
    GO
    
    ALTER TABLE [dbo].[Animal_Producao] CHECK CONSTRAINT [FK_Animal_Producao_MeioAmb_Animal_Prod]
    GO
    
    USE [portal]
    GO
    
    /****** Object:  Table [dbo].[MeioAmb_Animal_Comp]    Script Date: 06/10/2014 07:45:14 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[MeioAmb_Animal_Comp](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[descricao] [nvarchar](max) NULL,
     CONSTRAINT [PK_MeioAmb_Animal_Comp] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    USE [portal]
    GO
    
    /****** Object:  Table [dbo].[MeioAmb_Animal_Prod]    Script Date: 06/10/2014 07:45:32 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[MeioAmb_Animal_Prod](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[descricao] [nvarchar](max) NULL,
     CONSTRAINT [PK_Ag_Pol_C_MeioAmb_AnimaisProd] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    

    terça-feira, 10 de junho de 2014 10:51
  • Obs.:

    1- na Tabela "Animal_Producao" no campo "quantidade"nos traz o número de animais conforme o "cod_Especie_Producao", já na Tabela "Animal_Companhia" cada registro representa um animal conforme o "cod_Especie_Companhia".

    2- Na Tabela "Programa_Censo" nós temos as respectivas chaves estrangeiras das tabelas Animal_Producao e Animal_Companhia. 

    terça-feira, 10 de junho de 2014 11:01
  • Deleted
    terça-feira, 10 de junho de 2014 17:57
  • Perfeito!

    retornou as colunas Nome | Espécie | Número

    A média e desvio padrão referem-se aos proprietários por espécie,  por exemplo, a média de cães entre os proprietários de cães e seus respectivo desvio padrão.

    terça-feira, 10 de junho de 2014 21:13
  • Perfeito!

    retornou as colunas Nome | Espécie | Número

    A média e desvio padrão referem-se aos proprietários por espécie,  por exemplo, a média de cães entre os proprietários de cães e seus respectivo desvio padrão.

    Desta forma, com um filtro para a espécie poderia listar com Order By o resumo estatístico dos proprietários que guardam o maior representação animal.


    terça-feira, 10 de junho de 2014 21:25
  • Deleted
    • Marcado como Resposta Wilson Boris quarta-feira, 11 de junho de 2014 00:57
    quarta-feira, 11 de junho de 2014 00:02
  • Nossa! ainda vou precisar entender esse código, 100%. Se tiver algo para recomendar....

    Abaixo o resultado com os valores com os registros de teste.

    Apenas alguns detalhes:

    - No excel a média canina resultou 1,666667;

    - O desvio padrão será populacional, pois será um censo e parece-me que o retorno está amostral;

    - O resultado "suíno" retornou com "null", pois tenho apenas um registro. 

     

     
    quarta-feira, 11 de junho de 2014 00:26
  • Deleted
    • Marcado como Resposta Wilson Boris quarta-feira, 11 de junho de 2014 00:57
    quarta-feira, 11 de junho de 2014 00:45
  • quarta-feira, 11 de junho de 2014 00:50
  • PERFEITO 100%.


    quarta-feira, 11 de junho de 2014 00:50
  • José.Diz,

    nas minhas tentativas tupiniquins, eu cheguei no código abaixo, porém aplicando para cada espécie. Poderia criticar o código? 

    SELECT B.nome, C.quantidade AS Quantidade, D.MediaCoelhos, E.DesvioPadraoCoelhos, CoefMin= D.MediaCoelhos - (1.96 * E.DesvioPadraoCoelhos), CoefMax= D.MediaCoelhos + (1.96 * E.DesvioPadraoCoelhos)
    	FROM Programa_Censo A, PF_Proprietario B, Animal_Producao C, (SELECT AVG(A.quantidade) as MediaCoelhos
    FROM Animal_Producao A, Programa_Censo B
    where A.cod_Especie_Producao = 7 AND B.cod_Animal_Producao = A.id) AS D, (SELECT StDevP(A.quantidade) as DesvioPadraoCoelhos
    FROM Animal_Producao A, Programa_Censo B
    where A.cod_Especie_Producao = 7 AND B.cod_Animal_Producao = A.id) AS E
    	WHERE A.cod_Proprietario = B.id AND A.cod_Animal_Producao = C.id AND C.cod_Especie_Producao = 7

    Veja o retorno:

    quarta-feira, 11 de junho de 2014 00:57
  • Deleted
    • Marcado como Resposta Wilson Boris sexta-feira, 13 de junho de 2014 16:00
    quarta-feira, 11 de junho de 2014 01:00
  • Verdade, o suíno também gostou do código, rsrs.

    A previsão é de 20.000 registros na tabela Programa_Censo e na Animal companhia. 

    A necessidade de otimização será a partir da produção?


    • Editado Wilson Boris quarta-feira, 11 de junho de 2014 01:14 erro
    quarta-feira, 11 de junho de 2014 01:13
  • Deleted
    quarta-feira, 11 de junho de 2014 01:16
  • Maravilha. Muito obrigado.
    quarta-feira, 11 de junho de 2014 01:17
  • Deleted
    • Marcado como Resposta Wilson Boris sexta-feira, 13 de junho de 2014 16:00
    quarta-feira, 11 de junho de 2014 21:49
  • Valeu GIGANTE!

    Realmente que melhoria. Muito Obrigado.

    José.Diz, infelizmente não posso aplicar este código no momento, pois já tenho minha aplicação pronta, mas sem dúvida o código 1 v5 já resolveu o problema. Neste código, onde poderia inserir um "where" filtro para a espécie?

    sexta-feira, 13 de junho de 2014 15:59
  • Jose.Diz,

    No código abaixo com decimal esperava retornar com 2 casas deimais:

    SELECT Tipo_Especie, Avg(Cast((Numero) as decimal(9,5))) as Media, 
           StDevP(Cast(Numero as decimal(9,5))) as DesvioP
      from Parte1 
      group by Tipo_Especie

    Retorno:

    sexta-feira, 13 de junho de 2014 17:55
  • Deleted
    • Marcado como Resposta Wilson Boris sexta-feira, 13 de junho de 2014 18:45
    sexta-feira, 13 de junho de 2014 18:38
  • Deleted
    sexta-feira, 13 de junho de 2014 18:44
  • No código 1 v5.
    sexta-feira, 13 de junho de 2014 18:47
  • Deleted
    • Marcado como Resposta Wilson Boris sexta-feira, 13 de junho de 2014 19:06
    sexta-feira, 13 de junho de 2014 18:56
  • Obrigado, 100%.
    sexta-feira, 13 de junho de 2014 19:06
  • Deleted
    • Marcado como Resposta Wilson Boris sexta-feira, 13 de junho de 2014 19:56
    sexta-feira, 13 de junho de 2014 19:46
  • Jose.Diz,

    ao rodar os códigos sem restrições, código 1 v8  e o código 1 v5, os dados estão retornado com valores diferentes, assim como o número de linhas (11 e 14). Não deveria retornar com valores iguais? 

    Veja o print de retorno, o primeiro é o 1 v5 e o segundo 1 v8:

     


    • Editado Wilson Boris quarta-feira, 18 de junho de 2014 11:25 complemento
    quarta-feira, 18 de junho de 2014 11:24
  • Deleted
    • Marcado como Resposta Wilson Boris quarta-feira, 18 de junho de 2014 18:33
    quarta-feira, 18 de junho de 2014 18:09
  • Perfeito, muito obrigado.
    quarta-feira, 18 de junho de 2014 18:33
  • Deleted
    • Marcado como Resposta Wilson Boris quinta-feira, 19 de junho de 2014 22:25
    quinta-feira, 19 de junho de 2014 18:09