# Aula 9

## Utilizando operadores LIKE, IN e BETWEEN

Nossas buscas começaram de maneira bem simples. Até algumas aulas atrás, conseguimos encontrar valores que poderiam ser comparáveis. Números e textos maiores ou menores do que outros. Antes, conseguíamos encontrar dados específicos com o comparador de igual ou desigualdade, mas notamos que informações em forma de texto são difíceis de serem encontradas apenas com esses operadores. Aprendemos então a utilizar o `LIKE`, um operador bem útil que permitiu encontrarmos informações dentro de textos.

## Utilizando o operador BETWEEN

Até o momento, quando buscávamos um intervalo de número, de tempo, usávamos algo como o seguinte:

    SELECT * FROM netflix where date_added >= '2018-01-01' AND date_added <= '2019-12-31';

Quando tínhamos várias condições de intervalo isso ficava um pouco mais difícil de ler. E para facilitar nossa leitura, podemos utilizar o operador `BETWEEN`. Ele funciona assim:

    SELECT count(*) FROM netflix where date_added BETWEEN '2018-01-01' AND '2019-12-31';

Funciona da mesma forma que anterior. Mas e se quiséssemos fazer o contrário?
E se quiséssemos saber registros que não estão dentro de um intervalo?

Poderiamos utilizar o operador `NOT` junto com o `BETWEEN`, da seguinte forma:

    SELECT count(*) FROM netflix where date_added NOT BETWEEN '2018-01-01' AND '2019-12-31';

**Observação:** este formato de data, `AAAA-MM-DD`, segue o padrão de formato de datas chamado `ISO-8601`, sendo um padrão bastante comum dentro da computação.

## Utilizando o operador IN

Bom, agora já conseguimos buscar informações dentro de textos, buscar por palavras não exatas, buscar por intervalos. Podemos deixar nossas buscas ainda mais fortes se quisermos buscar registros que sejam iguais a algum elemento dentro de uma lista de opções.

Não entendeu? Não tem problema vamos traduzir isso.

Vamos supor que queiramos todas as produções que sejam **exclusivamente** (ou seja, sem o operador `%`) da Argentina, Chile ou Colombia. Como faríamos isso?

Provavelmente iriamos para a seguinte solução:

    SELECT count(*) FROM netflix
    WHERE country = 'Argentina' or country = 'Chile' or country = 'Colombia';

Encontraríamos 75 produções, mas já vimos ao longo dos exercícios o quão longo esse código pode ficar. Para facilitar nossa busca poderiamos utilizar o operador `IN` da seguinte forma:

    SELECT count(*) FROM netflix
    WHERE country IN ('Argentina', 'Chile', 'Colombia');

Prontinho, ambas consultas têm o mesmo resultado.

### Utilizando mais do LIKE

**Observação**: Vamos usar o dataset do netflix nessa explicação;

Até o momento, utilizamos o like da seguinte forma:

    SELECT * FROM netflix WHERE casting LIKE 'Bill Gates';

Isso nos dava um resultado equivalente a buscar pelo operador de igualdade (`=`). Se quisessemos o contrário disso, poderiamos dizer `NOT LIKE 'Bill Gates'` da seguinte forma:

    SELECT * FROM netflix WHERE casting NOT LIKE 'Bill Gates';

Mas o que realmente nos dava um novo poder era utilizar outro símbolo, o `%` dentro da busca de texto.

Poderiamos agora, ver se a `Emma Stone` estrelou algum filme sozinha ou em qualquer nível de importância dentro de uma produção, da seguinte forma:

    SELECT * FROM netflix WHERE casting LIKE '%Emma Stone%';

O símbolo de `%` é um divisor de águas pra gente, e gente pode usar outro operador para tornar nossas buscas ainda mais fortes.

Vamos agora buscar por todos os países que terminam com `ia` no nome, como Angentina ou Colombia. Como faríamos isso?

    SELECT * FROM netflix WHERE country like '%ia';

Isso funciona, que ótimo. Mas agora como buscaríamos por países que a quarta última letra é `r` e as últimas duas são `ia`?

Utilizando o operador `%` somente não nos ajudaria tanto, mas com o operador `_` poderiamos fazer nossa busca de maneira bem simples.

Poderiamos fazer assim, ó:

    SELECT * FROM netflix WHERE country like '%r_ia';

Mas ainda temos um problema, como pegariamos os nomes individuais enquanto buscamos por strings complexas?

Imagine agora que queremos buscar por todos nomes de atores que a segunda e terceira letra são, respectivamente, `oe`, então Joey e Zoe entrariam na nossa condição.

Vamos lembrar como pegamos a lista de todos os atores?

    select distinct unnest(string_to_array(casting, ', ')) as actor_name
    from netflix
    order by actor_name desc;

Vamos supor agora que queremos fazer exatamente como dizemos antes: buscar atores que a segunda e terceira letra de seu nome sejam, respectivamente, `oe`.

A gente poderia fazer assim:

    select distinct unnest(string_to_array(casting, ', ')) as actor_name
    from netflix
    where actor_name like '_oe%'
    order by actor_name desc;

A busca por `_oe%` siginfica que eu estou buscando quaisquer valores como letra inicial, que a segunda e terceira letram sejam específicas, e qualquer outra letra depois delas não seja importante.

Vamos rodar pra ver? É, não roda.

Nosso código não roda porque é assim que o SQL funciona, estamos tentando aplicar uma condição de filtragem de um campo que ainda sequer foi calculado.

Mais a frente vamos ver como resolvemos este problema.

## Ordem de Execução

Um problema comum que tivemos até o momento foi entender a ordem em que as coisas acontecem dentro do SQL.

É bem possível a gente tentou usar um campo dentro de um WHERE que só existia no SELECT e recebeu um montão de erros. Isso acontece porque existe uma forma bem estruturada (sem trocadilhos com o nome da linguagem) em como a gente consegue fazer consultas, filtragens agrupamentos e ele funciona assim:

Vamos assumir a seguinte consulta:

    select country, type, count(country)
    from netflix
    where country <> 'United States' and type='TV Show'
    group by country, type
    order by count(country) desc
    limit 5;

Você saberia dizer a ordem que as ações são executadas?

Não criemos pânico!

A ordem é a seguinte:

1. `FROM` e `JOIN` são feitos logo de início buscando as informações.
2. Logo depois o `WHERE` é aplicado filtrando as informações.
3. Em seguida, `GROUP BY` é chamado.
4. `SELECT` define então quais as colunas que vamos obter.
5. A ordenação feita pelo `ORDER BY` é feita por último.
6. E então, paginação como `LIMIT` e `OFFSET` por último.

As informações só ficam disponíveis nos níveis abaixo apenas quando a ação anterior acabou. De uma maneira mais clara, você não vai conseguir utilizar campos que estão definidos apenas no `SELECT` como funções agregadoras como `COUNT`, `AVG` e `SUM` dentro de um `WHERE`.

Exemplo:

**O código abaixo não vai funcionar**

    select avg(split_part(duration, ' ', 1)::real) as media
    from netflix where media > 160;

Receberemos o erro `ERROR: column "media" does not exist`, ou seja, a coluna média não existe, já que o `WHERE` tem ordem de execução anterior ao do `SELECT`, passo onde a coluna `media` é montada.

Como a gente pode resolver isso?

A solução pode estar no uso de subqueries ou subconsultas!

## Subqueries (Subconsultas)

Uma subquery é basicamente uma consulta pré-montada que permite a gente resolver problemas como o anterior ou até mesmo este aqui:

    select distinct unnest(string_to_array(casting, ', ')) as actor_name
    from netflix
    where actor_name like '_oe%'
    order by actor_name desc;

Como poderiamos fazer uma consulta dentro dessa coluna que só é montada no passo do `SELECT`?

Poderiamos fazer assim:

    select actors.actor_name from 
    (
        select distinct unnest(string_to_array(casting, ', ')) as actor_name
        from netflix
    ) as actors
    where actors.actor_name like '_oe%'
    order by actors.actor_name desc;

Por qual razão isso é possível? A gente pode entender `actors` como uma tabela de uma coluna só, por isso podemos fazer subqueries deste formato. Interessante, né?

A gente não está limitado em fazer subqueries apenas no `FROM`, mas também podemos fazer no `WHERE` e também no `SELECT`.

No `WHERE` é bem simples, basta entender o funcionamento do `IN`.

Apesar de não ser a única forma de resolver esse problema, vamos imaginar que a gente queira filtrar por países produtores de conteúdo de maneira individual, não basta pegarmos apenas o campo countries.

    SELECT countries FROM netflix GROUP BY countries;

Precisariamos quebrar a string.

Fariamos isso da seguinte forma:

    select distinct unnest(string_to_array(country, ', ')) as country_name
    from netflix
    order by country_name asc
    ;

Vamos imaginar agora que queremos filtrar pelos países que possuem primeira letra A ou C, fariamos da seguinte forma:

    select * from 
        (select distinct unnest(string_to_array(country, ', ')) as name
        from netflix
        order by name asc
        ) as country
    where country.name like 'A%' or country.name like 'C%';
;

Agora vamos usar essa subquery em um `WHERE`, buscando pelas produções que são de algum desses países de maneira exclusiva. Teriamos algo assim:

select * from netflix where country in (
	select * from 
	(
		select distinct unnest(string_to_array(country, ', ')) as name
		from netflix
		order by name asc
	) as country
	where country.name like 'A%' or country.name like 'C%'
);

Subqueries dentro de subqueries. Nesse caso, fizemos o seguinte:

1. Separamos todos os países do nosso dataset e montamos uma coluna com cada um deles separadinhos.
2. Filtramos em uma subquery todos os países que começam com a letra A ou e C, e fazemos isso porque estamos buscando por países de maneira individual e que o processo de filtragem ocorre depois de termos montado a coluna.
3. Depois buscando de fato todas as produções exclusivas destes países.

Doido, não é?

Bom, agora vamos ver o último formato de subquery que é dentro do `SELECT`.

Vamos supor agora que queremos buscar a duração máxima dos filmes produzidos por cada países (não de maneira exclusiva), como faríamos isso?

O primeiro passo seria buscar sobre todas durações de filmes.

    SELECT country, duration FROM netflix;     

Bom, aqui não teríamos a duração máxima, mas todas as durações. Além disso, ainda não agrupamos pelos países, vamos fazer isso.

    SELECT country, max(split_part(duration, ' ', 1)::real) as max_duration 
    FROM netflix
    GROUP BY country, duration;

Mas agora exceto se a gente agrupasse pelos outros campos, a gente não poderia buscar pelas colunas de título dos filmes. Péssimo, não é?

Um exemplo disso seria o seguinte:

    SELECT country, max(split_part(duration, ' ', 1)::real) as max_duration, title FROM netflix where type = 'Movie' GROUP BY country, duration;

Isso daria erro, ``` ERROR: column "netflix.title" must appear in the GROUP BY clause or be used in an aggregate ```.

Como resolveriamos isso? SUBQUERY!

Poderiamos criar a seguinte subquery:

    SELECT
        country,
        (
            select max(split_part(duration, ' ', 1)::real) as max_duration
            from netflix as n2
            where n2.country = n1.country
        ),
        title
    FROM netflix as n1;

Isso parece muito com nosso `INNER JOIN`, não é? O princípio aqui é o mesmo, a gente une duas tabelas por um elo (uma condição).

Nesse caso, teriamos sempre a duração máxima de filmes daquele país e todos títulos ainda a disposição para filtrar futuramente.