SQL vs Mongo - consultas com agrupamento
Veja exemplos de consultas com agrupamento no SQL e seus equivalentes no Mongo
O Mongo é um dos mais populares bancos de dados não relacionais. Ao contrário do SQL , onde os dados de uma tabela seguem o mesma estrutura, no Mongo, as informações são agrupadas em coleções, onde os documentos armazenados não seguem um formato pré-definido.
Neste artigo, vamos dar uma olhada em queries de agrupamento e em recursos mais avançados, comparando as queries do SQL e seus equivalentes no Mongo.
Para ver um artigo comparando as operações básicas, você pode clicar aqui.
Agregação
Agregação é o procedimento de processar dados em uma ou mais etapas, onde o resultado de cada etapa é utilizado na etapa seguinte, de modo a retornar um resultado combinado. Ela serve para obter resultados como o total de registros de um determinado tipo, o maior ou o menor valor. No Mongo, isto é obtido através do método aggregate
db.<nome_da_coleção>.aggregate([])
Para os seguintes exemplos, vamos trabalhar com uma collection chamada clientes, que tenha documentos com os seguintes campos.
_id, nome, endereco, avaliacao, categoria, data_cadastro
O que vamos aprender a fazer com esta collection são as seguintes operações:
* Agrupando resultados por tipo.
* Agrupando resultados por tipo e condição.
* Contando registros.
* Exibindo ou ocultando colunas.
* Criando uma nova coleção com os resultados.
* Funções de Agregação.
* Operadores lógicos.
* Operadores de comparação.
* Agrupando registros por data.
* Retornar registros onde a média de avaliação sejá maior que um determinado valor.
* Retornando registros distintos.
Agrupando resultados por tipo
SQL
SELECT categoria, count(*) as total from clientes GROUP BY categoria
Mongo
db.clientes.aggregate([{$group: {_id: "$categoria", total: {$sum: 1}}}])
O método aggregate recebe um array com os parâmetros. O operador $group define o que vai retornar, o "_id" se refere ao campo que vai ser agrupado, no caso, categoria. Foi criado um campo chamado total, que utiliza o operador $sum para fazer a contagem dos registros.
Agrupando resultados por tipo e condição
SQL
SELECT categoria, count(*) as total from clientes where avaliacao = 10 GROUP BY categoria
Mongo
db.clientes.aggregate([{$match: {avaliacao: 10}}, {$group: {_id: "$categoria", total: {$sum: 1}}}])
Neste exemplo, foi acrescentando um objeto a mais no array aggregate. Foi acrescentando um objeto usando o operador $match, contendo a condição a ser avaliada, que são clientes com nota 10 de avaliação.
Contando registros
SQL
SELECT count(*) as total_clientes_informatica from clientes where categoria ='informatica'
Mongo
db.clientes.aggregate([{$match: {categoria: "informatica"}}, {$count: "total_clientes_informatica"}])
Exibindo ou ocultando colunas.
Após utilizar agregação você pode definir quais colunas serão exibidas através do operador $projection, ao adicionar o nome do campo, você pode informar 1 para exibir e 0 para inibir. Da mesma forma que o SQL, existe a opção de limitar a quantidade de resultados.
SQL
SELECT TOP 10 nome, categoria, avaliacao from clientes where avaliacao = 10
SELECT nome, categoria, avaliacao from clientes where avaliacao = 10 LIMIT 10
Mongo
db.clientes.aggregate([{$match: {avaliacao: 10}}, {$project: {_id: 0, nome: 1, categoria: 1, avaliacao: 1}}, {$limit: 10}])
Criando uma nova coleção com os resultados
SQL
select id, nome, categoria, avaliacao into clientes_atualizado from clientes ORDER by categoria DESC avaliacao ASC
Mongo
db.clientes.aggregate([{$project: {_id: 0, nome: 1, categoria: 1, avaliacao: 1}}, {$sort: {categoria: -1, avaliacao: 1}}, {$out: "clientes_atualizado}])
Utilizando o operador $sort podemos ordenar os resultados de acordo com os campos selecionados, quando se informa "1" a ordem é crescente, ao informar "-1' a ordem é decrescente. O operador $out serve para informar o nome da nova coleção que vai receber os resultados.
Funções de Agregação
No SQL temos SUM, AVG, MAX e MIN, no Mongo também temos operadores que fazem o mesmo, na ordem, $sum, $avg, $max e $min.
* SUM retorna a soma de um determinado campo
* AVG retorna a média de um determinado campo
* MAX retorna o maior valor de um determinado campo
* MIN retorna o maior valor de um determinado campo
Um exemplo utilizando todos estes operadores:
SQL
SELECT categoria, sum(avaliacao) as total_avaliacao, AVG(avaliacao) as media_avaliacao, MAX(avaliacao) as nota_maxima, MIN(avaliacao) as nota_minima from clientes WHERE avalicao <> 0 GROUP BY categoria
Mongo
db.clientes.aggregate([{$match: {avaliacao: {$ne: 0}}}, {$group: {_id: "$categoria", total_avaliacao: {$sum: "$avaliacao"}, media_avaliacao: {$avg: "$avaliacao"}, nota_maxima: {$max: "$avaliacao"}, nota_minima: {$min: "$avaliacao"}}}])
No método aggregate, utilizados dois objetos, o primeiro usa o operador $match para filtrar os registros pelo campo avaliacao, o critério é aqueles que não tem o valor zero (o operador $ne significa not equal). O segundo objeto utiliza o operador $group para criar as colunas agrupadas com as quatro operações, todas sobre o campo avaliacao.
Operadores lógicos
Da mesma forma que o SQL possui os operadores AND, OR e NOT, o Mongo possui operadores lógicos como $and, $or, $not e $nor. Veja um exemplo:
SQL
SELECT * from clientes where categoria = 'informatica' and avaliacao = 10
Mongo
db.clientes.aggregate([{$match: {$and: [{categoria: "informatica"}, {avaliacao: 10}]}}])
Perceba que o operador $and recebe um array com os campos que devem ser avaliados.
Operadores de comparação
Veja uma lista dos operadores de comparação no SQL e seus equivalentes no Mongo.
* > - Maior que. O equivalente no Mongo é o operador $gt
* < - Menor que. O equivalente no Mongo é o operador $lt
* <> - Diferente de. O equivalente no Mongo é o operador $nte
* = - Igual. No Mongo, é o operador $eq
* <= - Menor ou igual. No Mongo é o operador $lte
* >= Maior ou igual. No Mongo é o operador $gte
Um exemplo:
SQL
select * from clientes where categoria = "informatica" and avaliacao >= 6 and avaliacao < 8
Mongo
db.clientes.aggregate([{$match: {$and: [{avaliacao: "informatica"}, {avaliacao: {$gte: 6, $lt: 8}}]}}])
Neste exemplo, filtramos pela categoria "informatica" os registros com nota de avaliacao maior ou igual a 6 mas menores que 8.
Agrupando registros por data
Neste exemplo vamos ver como fazer a contagem do total de cadastros de clientes por dia de uma determinada categoria
SQL
SELECT YEAR(data_cadastro) as ano, MONTH(data_cadastro) as mes, DAY(data_cadastro) as mes, count(*) as total from clientes WHERE categoria = 'padaria'
Mongo
db.clientes.aggregate([
{ $match: {categoria: "padaria"}},
{ $group: {'_id': {
ano: { $year: "$data_cadastro" },
mes: { $month: "$data_cadastro" },
dia: { $dayOfMonth: "$data_cadastro" }
},
total: {$sum: 1}}},
])
Retornar registros onde a média de avaliação sejá maior que um determinado valor
No SQL existe o operador HAVING para aplicar um filtro nos resultados de um agrupamento. No Mongo, utilizando o método aggregate, podemos fazer o mesmo, ou seja, inserir um objeto com o agrupamento e depois um objeto com o filtro.
Neste exemplo, vamos listar as categorias que possuem uma média de avalicação maior ou igual que 8.
SQL
SELECT categoria, AVG(avaliacao) as media FROM clientes GROUP BY categoria HAVING AVG(avaliacao)>=8
Mongo
db.clientes.aggregate([
{$group : {_id : "$categoria", media : {$avg : "$avaliacao"}}},
{ $match: { media : { $gt: 8} } }])
Retornando registros distintos.
O Mongo também possui DISTINCT para retornar registros únicos, neste caso não se faz necessário agregação.
Aqui tem dois exemplos, um retornando as categorias distintas, e outro retornado o total.
Retornando as categorias distintas ordenadas
SQL
SELECT distinct categoria from clientes order by categoria
Mongo
db.clientes.distinct('categoria').sort()
Contando o total de categorias
SQL
SELECT count(distinct categoria) from clientes
Mongo
db.clientes.distinct('categoria').length
Na continuação deste artigo, você vai ver como trabalhar com expressões regulares no SQL e no Mongo!