none
Consulta complexa RRS feed

  • Pergunta

  • Olá pessoal, tudo bem?

    Tenho uma base de dados q armazena os números da megasena com os seguintes campos:

    Sorteio(pk), Data_Sorteio(Date), Primeira_Dezena(int), Segunda_Dezena(int), Terceira_Dezena(int), Quarta_Dezena(int), Quinta_Dezena(int), Sexta_Dezena(int)

    Preciso fazer diversas operações com essa base, por exemplo; eu informo 6 números sorteados no programa e ele vai me retornar os dados da data do sorteio ou se existe um jogo assim.

    O problema é que as dezenas que informo na pesquisa podem estar armazena em qualquer um dos campos, não respeitando uma ordem.

    Preciso construir uma pesquisa q me retorne os dados de um jogo informado, independente de onde a dezena esteja armazenada (pode ser no campo(Primeira_Dezena ou Quinta_Dezena....)

    Estou usando entity framework, tem como construir essa pesquisa?

    Se não for possível fazer pelo EF, como ficaria por sql mesmo?

    []'s


    Se a resposta foi útil, por favor marque como útil. Leia a bíblia.

    domingo, 20 de janeiro de 2013 14:04

Respostas

  • Tianodraco,

    Importei o HTML para o Excel e do Excel para o SQL. Ficou assim:

    CREATE TABLE [dbo].[MegaSena_Original](
    [Concurso] [int] NULL,
    [Data Sorteio] [date] NULL,
    [1ª Dezena] [int] NULL,
    [2ª Dezena] [int] NULL,
    [3ª Dezena] [int] NULL,
    [4ª Dezena] [int] NULL,
    [5ª Dezena] [int] NULL,
    [6ª Dezena] [int] NULL
    ) ON [PRIMARY]

    GO

    Criei a tabela que te sugeri antes assim:

    Create Table Sorteio (
       Sorteio int not null,
       Data_Sorteio datetime not null,
       Dezena_Sorteada int not null
    )
    go
    ALTER TABLE Sorteio ADD CONSTRAINT
    Sorteio_PK PRIMARY KEY CLUSTERED 
    (
    Dezena_Sorteada,
    Sorteio

    GO

    E importei os resultados de uma para a outra assim:

    insert into Sorteio (Sorteio, Data_Sorteio, Dezena_Sorteada)
       select Concurso, [Data Sorteio en-US], [1ª Dezena] from MegaSena_Oritignal
       union
       select Concurso, [Data Sorteio en-US], [2ª Dezena] from MegaSena_Oritignal
       union
       select Concurso, [Data Sorteio en-US], [3ª Dezena] from MegaSena_Oritignal
       union
       select Concurso, [Data Sorteio en-US], [4ª Dezena] from MegaSena_Oritignal
       union
       select Concurso, [Data Sorteio en-US], [5ª Dezena] from MegaSena_Oritignal
       union
       select Concurso, [Data Sorteio en-US], [6ª Dezena] from MegaSena_Oritignal

    Prontinho!!!!

    Agora posso rodar aquelas queries com intersect que postei antes. Por exemplo:

    select sorteio from Sorteio where Dezena_Sorteada = 29
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 16
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 7
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 2
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 50
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 32

    Retorna o sorteio 716.

    Dá até para procurar parcialmente, tipo assim:

    select sorteio from Sorteio where Dezena_Sorteada = 2
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 50
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 32

    Retorna os sorteios 716 e 1365, indicando que as três dezenas foram sorteadas nesses dois concursos.

    E ainda dá para fazer assim:

    select * from MegaSena_Oritignal
    where concurso in (
    select sorteio from Sorteio where Dezena_Sorteada = 2
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 50
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 32
     )

    E ai vem o resultado completo!

    Assim você consegue a flexibilidade que busca: buscar por qualquer dezena em qualquer ordem e mais uma adicional: buscar por resultados parciais.

    Espero ter ajudado.


    Armando Lacerda

    • Marcado como Resposta Tianodraco terça-feira, 22 de janeiro de 2013 21:31
    terça-feira, 22 de janeiro de 2013 19:07

Todas as Respostas

  • se vc trouxer o resultado do banco assim:

    SELECT CONCURSO, CONVERT(VARCHAR, DEZENA01) + ';' + CONVERT(VARCHAR, DEZENA02) + ';' + CONVERT(VARCHAR, DEZENA03) + ';' 
    + CONVERT(VARCHAR, DEZENA04) + ';' + CONVERT(VARCHAR, DEZENA05) + ';' + CONVERT(VARCHAR, DEZENA06) + ';' AS DEZENAS
    FROM TABELA 

    vc obtera o resultado abaixo : 

    1 04;05;30;33;41;52;
    2 09;37;39;41;43;49;

    Ai no código vc pode usar pesquisa das dezenas pelo indexOf procurando as dezenas mais ponto-e-virgula.

    Outra solução voce poderia colocar as dezenas no banco com as dezenas na ordem crescente.
    domingo, 20 de janeiro de 2013 20:46
  • Então com EF nao dá?

    Pq eu preciso manter uma cópia desses registros em uma base de dados firebird.

    Por isso meu interesse em não usar SQL.


    Se a resposta foi útil, por favor marque como útil. Leia a bíblia.

    segunda-feira, 21 de janeiro de 2013 01:03
  • Tianodraco,

    A solução do Jose Geraldo funciona e dá para implementar em EF. Mas eu vejo um problema: performance. Você nunca vai conseguir criar um índice para auxiliar na pesquisa. Vai ser sempre tablescan (pior performance possível).

    Para resolver ambos os problemas eu sugiro outro caminho. Por este novo caminho você vai conseguir performance e dá para implementar via EF.

    Primeiro uma nova estrutura para a tabela. Ao invés de 8 colunas, apenas três:

    Create Table Sorteio (
       Sorteio int not null,
       Data_Sorteio datetime not null,
       Dezena_Sorteada int not null
    )
    go

    Uma primary key

    ALTER TABLE Sorteio ADD CONSTRAINT

    Sorteio_PK PRIMARY KEY CLUSTERED 
    (
    Dezena_Sorteada,
    Sorteio

    GO

    E um índice auxiliar.

    create index sorteio_sorteio on sorteio (sorteio)
    go

    Assim fica mais fácil. Um select como o abaixo vai achar qualquer combinação em qualquer ordem:

    select sorteio from Sorteio where Dezena_Sorteada = 3
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 18
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 42
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 50
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 65
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 73

    O query plan fica assim: index seek para todos os lados (sem scan) !!!!

    E o query plan procurando por um sorteio em particular seria assim: 

    select *
      from sorteio
     where Sorteio = 85381

    

    Para terminar, segue o script que eu gerei para testar isso tudo. 

    declare @sorteio int = 1
    declare @dezena_sorteada_1 int,  @dezena_sorteada_2 int,  @dezena_sorteada_3 int,  @dezena_sorteada_4 int,  @dezena_sorteada_5 int,  @dezena_sorteada_6 int
    set @dezena_sorteada_1 = RAND(datepart(SECOND, getdate()))

    while (@sorteio < 100000)
    begin
       set @dezena_sorteada_1 = rand() * 100
       set @dezena_sorteada_2 = @dezena_sorteada_1

       while (@dezena_sorteada_2 = @dezena_sorteada_1)
       begin
      set @dezena_sorteada_2 = rand() * 100
       end

       set @dezena_sorteada_3 = @dezena_sorteada_2
       while (@dezena_sorteada_3 = @dezena_sorteada_2 or @dezena_sorteada_3 = @dezena_sorteada_1)
       begin
      set @dezena_sorteada_3 = rand() * 100
       end

       set @dezena_sorteada_4 = @dezena_sorteada_3
       while (@dezena_sorteada_4 = @dezena_sorteada_3 or @dezena_sorteada_4 = @dezena_sorteada_2 or @dezena_sorteada_4 = @dezena_sorteada_1)
       begin
      set @dezena_sorteada_4 = rand() * 100
       end

       set @dezena_sorteada_5 = @dezena_sorteada_4
       while (@dezena_sorteada_5 = @dezena_sorteada_4 or @dezena_sorteada_5 = @dezena_sorteada_3 or @dezena_sorteada_5 = @dezena_sorteada_2 or @dezena_sorteada_5 = @dezena_sorteada_1)
       begin
      set @dezena_sorteada_5 = rand() * 100
       end

       set @dezena_sorteada_6 = @dezena_sorteada_5
       while (@dezena_sorteada_6 = @dezena_sorteada_5 or @dezena_sorteada_6 = @dezena_sorteada_4 or @dezena_sorteada_6 = @dezena_sorteada_3 or @dezena_sorteada_6 = @dezena_sorteada_2 or @dezena_sorteada_6 = @dezena_sorteada_1)
       begin
      set @dezena_sorteada_6 = rand() * 100
       end

       insert into sorteio values (@sorteio, getdate(), @dezena_sorteada_1),
                                  (@sorteio, getdate(), @dezena_sorteada_2),
                                  (@sorteio, getdate(), @dezena_sorteada_3),
                                  (@sorteio, getdate(), @dezena_sorteada_4),
                                  (@sorteio, getdate(), @dezena_sorteada_5),
                                  (@sorteio, getdate(), @dezena_sorteada_6)
       set @sorteio = @sorteio + 1
    end
    go 


    Armando Lacerda

    terça-feira, 22 de janeiro de 2013 06:53
  • Obrigado pela resposta, Armando. Essa base eu peguei da caixa economica federal e tem vários campos, inclusive as dezenas sorteadas.

    Como a planilha excel da CEF armazena cada dezena num campo, me explica melhor sua solução, vc colocou as 6 dezenas em qual campo?


    Se a resposta foi útil, por favor marque como útil. Leia a bíblia.


    • Editado Tianodraco terça-feira, 22 de janeiro de 2013 12:23
    terça-feira, 22 de janeiro de 2013 12:21
  • Tianodraco,

    Eu não conheço essa planilha da CEF. As seis dezenas estão em linhas diferentes e não em colunas diferentes. Assim facilita para criar índices e usar o INTERSECT.

    Armando Lacerda


    Armando Lacerda

    terça-feira, 22 de janeiro de 2013 13:03
  • Armando segue o link da planilha excell com os todos os resultados da megasena, diretamente do site da caixa:

    http://www1.caixa.gov.br/loterias/loterias/megasena/download.asp

    É a primeira -> Título: Resultado do Mega sena por ordem de sorteio.Zip

    Aí vc poderá ver como é o banco de dados.

    Entendi o q vc quis dizer, deverei então gerar uma tabela "Dezenas_Sorteadas" contendo as dezenas sorteadas num campo "Dezenas" por exemplo... e um campo nessa tabela chamado "Sorteio" q é uma FK para o número do sorteio na tabela original(q é um campo chamado Concurso)

    Aproveitando o tópico, após vc ver a estrutura da tabela, tem como criar um SP pra pegar as 6 colunas da tabela original e colocar já com o código do concurso, bem como suas dezenas, na tabela nova? (São 1460 registros)


    Se a resposta foi útil, por favor marque como útil. Leia a bíblia.

    terça-feira, 22 de janeiro de 2013 17:39
  • Tianodraco,

    Importei o HTML para o Excel e do Excel para o SQL. Ficou assim:

    CREATE TABLE [dbo].[MegaSena_Original](
    [Concurso] [int] NULL,
    [Data Sorteio] [date] NULL,
    [1ª Dezena] [int] NULL,
    [2ª Dezena] [int] NULL,
    [3ª Dezena] [int] NULL,
    [4ª Dezena] [int] NULL,
    [5ª Dezena] [int] NULL,
    [6ª Dezena] [int] NULL
    ) ON [PRIMARY]

    GO

    Criei a tabela que te sugeri antes assim:

    Create Table Sorteio (
       Sorteio int not null,
       Data_Sorteio datetime not null,
       Dezena_Sorteada int not null
    )
    go
    ALTER TABLE Sorteio ADD CONSTRAINT
    Sorteio_PK PRIMARY KEY CLUSTERED 
    (
    Dezena_Sorteada,
    Sorteio

    GO

    E importei os resultados de uma para a outra assim:

    insert into Sorteio (Sorteio, Data_Sorteio, Dezena_Sorteada)
       select Concurso, [Data Sorteio en-US], [1ª Dezena] from MegaSena_Oritignal
       union
       select Concurso, [Data Sorteio en-US], [2ª Dezena] from MegaSena_Oritignal
       union
       select Concurso, [Data Sorteio en-US], [3ª Dezena] from MegaSena_Oritignal
       union
       select Concurso, [Data Sorteio en-US], [4ª Dezena] from MegaSena_Oritignal
       union
       select Concurso, [Data Sorteio en-US], [5ª Dezena] from MegaSena_Oritignal
       union
       select Concurso, [Data Sorteio en-US], [6ª Dezena] from MegaSena_Oritignal

    Prontinho!!!!

    Agora posso rodar aquelas queries com intersect que postei antes. Por exemplo:

    select sorteio from Sorteio where Dezena_Sorteada = 29
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 16
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 7
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 2
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 50
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 32

    Retorna o sorteio 716.

    Dá até para procurar parcialmente, tipo assim:

    select sorteio from Sorteio where Dezena_Sorteada = 2
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 50
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 32

    Retorna os sorteios 716 e 1365, indicando que as três dezenas foram sorteadas nesses dois concursos.

    E ainda dá para fazer assim:

    select * from MegaSena_Oritignal
    where concurso in (
    select sorteio from Sorteio where Dezena_Sorteada = 2
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 50
    intersect 
    select sorteio from Sorteio where Dezena_Sorteada = 32
     )

    E ai vem o resultado completo!

    Assim você consegue a flexibilidade que busca: buscar por qualquer dezena em qualquer ordem e mais uma adicional: buscar por resultados parciais.

    Espero ter ajudado.


    Armando Lacerda

    • Marcado como Resposta Tianodraco terça-feira, 22 de janeiro de 2013 21:31
    terça-feira, 22 de janeiro de 2013 19:07
  • Cara, vc é o cara!

    Vou implementar aqui, vou por esse tópico como respondido pq foi muito bem respondido por vc e abrir outro pq tenho outra dúvida a respeito de outra consulta com essa base de dados, muito obrigado amigo.

    []'s


    Se a resposta foi útil, por favor marque como útil. Leia a bíblia.

    terça-feira, 22 de janeiro de 2013 21:31
  • Maravilha,

    Fico feliz em poder ter ajudado.

    Não sou o cara não, mas sou amigo dele. Jesus! :)

    []s,


    Armando Lacerda

    terça-feira, 22 de janeiro de 2013 22:04