sexta-feira, 2 de dezembro de 2011

SELECT FROM SAMPLE

Um recurso muito interessante e pouco conhecido da clausula FROM é a opção SAMPLE. Ela deve ser acompanhada de um número entre (0,100),  mas nunca nos limites 0 e 100. Este número indica a probabilidade individual de cada linha da tabela retornar na resposta da query. Por exemplo:

select obj_id, object_name
  from my_objs sample(10);

Detalhando um pouco, priemiro vou criar uma tabela e verificar o número de linhas.

create sequence my_obj_seq;

create table my_objs as
select my_obj_seq.nextval obj_id, owner, object_name, object_id
  from all_objects;
  
select count(*)
  from my_objs;

No meu Oracle retornou 17.900.  Agora tente algo simples como:

select obj_id, object_name
  from my_objs;

Obviamente todas as linhas da tabela retornarão na resposta. Incluindo o SAMPLE, temos:

select obj_id, object_name
  from my_objs sample(10);

E a chance de cada linha estar na resposta é de apenas 10%. Se executar repetidas vezes, o conjunto resposta será diferente a cada execução por que, para cada linha, o Oracle decide inclui-la ou não na resposta. Ou seja, a resposta passa a ser aleatória.

Neste ponto, você executa:

select count(*) from(  
select obj_id, object_name
  from my_objs sample(10);

E a resposta é 1.755! Opa, mas isto não é 10% de 17.900. Repetindo o mesmo SELECT COUNT(*) a resposta é 1.829! E na terceira vez é 1.793! 

Estaria tudo errado? Não deveria ser exatamente 10% do total de linhas na tabela, nesta caso exatos 1.790? 

O Oracle está correto. A chance de cada linha estar na resposta é 10%, mas eventualmente haverá mais linhas na resposta - digamos que deram sorte - ou menos linhas - estavam com azar. Se você precisar de um número exato, basta utilizar uma consulta aninhada:

select * from(
select obj_id, object_name
  from my_objs sample(100))
where rownum <= 10; 

Eu utilizei este recurso para selecionar um subconjunto aleatório de linhas da tabela. Isto fazia parte de um teste que seria repetido diversas vezes, mas era importante que o conjunto de dados fosse diferente a cada execução, afinal seria desnecessário calcular várias vezes o mesmo valor.


6 comentários:

  1. Este comentário foi removido pelo autor.

    ResponderExcluir
  2. 1. Acrescente a essa script, após as operações de inserção de registos, as instruções (SELECT) que executam as seguintes queries:

    a. Quais as cidades (id_cidade, cidade) que não têm lojas da marca "Caramelo", id_marca = 8?

    select id_cidade, cidade
    from cidades
    where id_cidade not in
    ( select id_cidade
    from lojas
    where id_marca = 8);



    b. Que marcas (id_marca, marca) não têm qualquer loja?

    select id_marca, marca
    from marcas
    where not exists
    ( select *
    from lojas
    where lojas.id_marca = marcas.id_marca);

    ou

    select id_marca, marca
    from marcas
    where id_marca not in
    ( select id_marca
    from lojas);

    c. Qual é a marca ou marcas (marca) que tem o maior número de lojas numa qualquer cidade?
    select marca
    from marcas
    where id_marca in
    ( select id_marca
    from lojas
    where num_lojas =
    ( select max(num_lojas)
    from lojas));

    d. Quais as cidades (id_cidade, cidade)que têm lojas pelo menos das mesmas marcas presentes na cidade "Madrid", id_cidade = 4?
    select id_cidade, cidade
    from cidades
    where id_cidade <> 4 and
    not exists
    ( select id_marca
    from lojas
    where id_cidade = 4
    and id_marca not in
    ( select id_marca
    from lojas
    where lojas.id_cidade = cidades.id_cidade));

    e. Que marcas (marca) estão presente em todas as cidades?
    select marca
    from marcas
    where not exists
    ( select id_cidade
    from cidades
    where id_cidade not in
    ( select id_cidade
    from lojas
    where lojas.id_marca = marcas.id_marca));

    ou

    select marca
    from marcas
    where
    ( select count(*)
    from cidades ) =
    ( select count(*)
    from lojas
    where lojas.id_marca = marcas.id_marca);

    ResponderExcluir
  3. Este comentário foi removido pelo autor.

    ResponderExcluir
  4. 4. Vai ter que alterar a base de dados. Para tal, vai proceder a alterações na script original e criar uma nova (que deverá submeter para avaliação- ver regras no início do enunciado). Vai alterar a script de modo a cumprir os novos requisitos para a base de dados abaixo indicados e a executar sem erros, inclusivé com as inserções já previstas. É necessário assegurar a integridade referencial da nova base de dados.

    a. A base de dados vai ter que também armazenar o catálogo de cada marca. Deste modo será possível saber que produtos cada marca tem e qual o preço. Cada produto de uma dada marca é descrito pela sua designação e preço. Exemplo de produtos para a marca "Zara"
    MOCASSIN FREE AVEC RUBAN 39,99€
    PANTALON EN VELOURS 29,90€
    sweat-shirt à capuche homewear 14,99€
    ROBE AVEC FERMETURE ÉCLAIR CENTRALE 16,99€

    Insira alguns registos na(s) nova(s) tabela(s) que venha a criar através da inclusão de comandos insert na script.

    A solução passa pela criação de uma tabela de produtos, que irá armazenar a informação de todos os produtos de
    todas as marcas. Cada um dos produtos é caracterizado pela marca a que pertence (chave estrangeira). A relação entre "produtos" e "marcas" é de 1:M.

    create table produtos

    ( id_produto int,

    designacao varchar(100),

    preco decimal(6,2),

    id_marca int,

    constraint chave_pk_produtos primary key (id_produto ),

    constraint chave_fk_produtos_marcas foreign key (id_marca) references marcas(id_marca)

    ) engine = INNODB;

    insert produtos (id_produto, designacao, preco, id_marca) values (1, "PANTALON EN VELOURS", 29.90, 1);

    insert produtos (id_produto, designacao, preco, id_marca) values (2, "Jeans Hellboy", 140.00, 4);

    insert produtos (id_produto, designacao, preco, id_marca) values (3, "Nike 3.0", 69.90, 10);

    insert produtos (id_produto, designacao, preco, id_marca) values (4, "Jacket XPTO", 24.90, 10);

    insert produtos (id_produto, designacao, preco, id_marca) values (5, "ROBE AVEC FERMETURE ÉCLAIR CENTRALE", 16.99, 1);

    insert produtos (id_produto, designacao, preco, id_marca) values (6, "O Regresso do Heroi", 9.90, 2);

    ResponderExcluir
  5. b. Cada marca tem uam equipa de pessoas em cada cidade, para gerir o conjunto de lojas nessa cidade. Esta equipa tem dimensão variável, consoante o número de ljoas que cada marca tem na cidade em causa. A informação que se regista para cada equipa é o nome da pessoa e o número de telemóvel.

    A base de dados deverá responder a perguntas como quais são os colaboradores que a marca "Zara" tem em Roma, ou para que marca e em que cidade trabalha o colaborador "Miguel Alves Monteiro" e qual o seu número de telemóvel, ou ainda qual é a equipa completa que a "Armani" possui.

    Insira alguns registos na(s) nova(s) tabela(s) que venha a criar através da inclusão de comandos insert na script.

    A solução passa por introduzir uma tabela de colaboradores, com os nomes e os nº de telemóvel. Esta tabela tem um relacionamento de 1:M com marcas (uma marca tem vários colaboradores e um colaborador trabalha para uma marca apenas) e também com cidades (uma cidade tem
    vários colaboradores, de várias marcas, e um colaborador está destacado apenas para uma cidade). Isto implica a inclusão das chaves primárias de marcas e de cidades como foreign keys de colaboradores.

    create table colaboradores
    ( id_colaborador int,
    nome varchar(100),
    telemovel varchar(20),
    id_marca int,
    id_cidade int,
    constraint chave_pk_colaboradores primary key (id_colaborador ),
    constraint chave_fk_colaboradores_marcas foreign key (id_marca) references marcas(id_marca),
    constraint chave_fk_colaboradores_cidades foreign key (id_cidade) references cidades(id_cidade)
    ) engine = INNODB;

    insert colaboradores ( id_colaborador, nome, telemovel, id_marca, id_cidade) values (1, "Joaquim Almeida", "+351999999999", 1, 3);

    insert colaboradores ( id_colaborador, nome, telemovel, id_marca, id_cidade) values (2, "John Lopez", "+351999954999", 1, 3);

    insert colaboradores ( id_colaborador, nome, telemovel, id_marca, id_cidade) values (3, "Stuart James", "+231939999989", 2, 5);

    insert colaboradores ( id_colaborador, nome, telemovel, id_marca, id_cidade) values (4, "Zahir Provence", "+231939945989", 2, 7);

    ResponderExcluir
  6. 2. Views
    a. Crie uma view que forme uma tabela com o números de marcas presentes por cidade. Essa view deverá apresentar os campos id_cidade, cidade, num_marcas. Exemplo de um registo seria (1, "Braga", 3), que indica que a cidade "Braga" tem lojas de 3 marcas. Acrescente o comando que cria a view à script que manipulou no exercício anterior.

    create view total_marcas as
    select marcas.id_marca, marca, count(*) as num_marcas
    from marcas, lojas
    where marcas.id_marca = lojas.id_marca
    group by marcas.id_marca;

    b. Utilizando a view que criou, acrescente à script uma query que mostre a cidade (cidade) com mais marcas presentes.

    select cidade
    from total_marcas
    where num_marcas =
    ( select max(num_marcas)
    from total_marcas);

    3. Analise as expressões álgebricas indicadas e transcreva para este enunciado a pergunta que está subjacente a cada expressão:



    a. T = Π id_cidade, cidade (σ populacao > 2000000 (Cidades))

    R = Π id_cidade, cidade (Cidades∞Lojas∞σ nacionalidade = "Itália" (Marcas))

    S = R T

    R: Quais as cidades (id_cidade, cidade) qte têm mais de 2.000.000 de habitantes e possuem lojas de marcas italianas.

    b. Marcas – Marcas ∞ (Π id_marca (σ população < 500000 ( Cidades ) ∞ Lojas ) )

    R: Quais as marcas que não estão presnetes em cidades com menos de 500000 habitantes?

    ResponderExcluir