Skip to main content

aggregation

CORMO는 Query#group를 통해 몇가지 기본적인 집계 연산을 지원합니다.

설명CORMOSQLMongoDB
전체 개수Order.group(null, { count: { $sum: 1 } })SELECT COUNT(*) AS count
FROM orders		db.orders.aggregate([
  {$group:{_id:null,count:{$sum:1}}}
])
전체 합Order.group(null, { total: { $sum: '$price' } })SELECT SUM(price) AS total
FROM orders		db.orders.aggregate([
  {$group:{_id:null,total:{$sum:'$price'}}}
])
필터된 레코드만Order.where({ price: { $lt: 10 } })
.group(null, { count: { $sum: 1 }, total: { $sum: '$price' } })		SELECT COUNT(*) AS count, SUM(price) AS total
FROM orders
WHERE price<10		db.orders.aggregate([
  {$match:{price:{$lt:10}}},
  {$group:{_id:null,count:{$sum:1},total:{$sum:'$price'}}}
])
그룹별 집계Order.group('customer', { count: { $sum: 1 }, total: { $sum: '$price' } })SELECT customer, COUNT(*) AS count, SUM(price) AS total
FROM orders
GROUP BY customer		db.orders.aggregate([
  {$group:{_id:'$customer',count:{$sum:1},total:{$sum:'$price'}}}
])
그룹 컬럼으로 정렬Order.group('customer', { total: { $sum: '$price' } })
.order('customer')		SELECT customer, SUM(price) AS total
FROM orders
GROUP BY customer
ORDER BY customer		db.orders.aggregate([
  {$group:{_id:'$customer',total:{$sum:'$price'}}},
  {$sort:{_id:1}}
])
집계된 컬럼으로 정렬Order.group('customer', { total: { $sum: '$price' } })
.order('total')		SELECT customer, SUM(price) AS total
FROM orders
GROUP BY customer
ORDER BY total		db.orders.aggregate([
  {$group:{_id:'$customer',total:{$sum:'$price'}}},
  {$sort:{total:1}}
])
집계된 컬럼에 조건Order.group('customer', { count: { $sum: 1 } })
.where({ count: { $gte: 3 } })		SELECT customer, COUNT(*) AS count
FROM orders
GROUP BY customer
HAVING count>=3		db.orders.aggregate([
  {$group:{_id:'$customer',count:{$sum:1}}},
  {$match:{count:{$gte:3}}}
])
여러 컬럼으로 그룹Order.group('customer date', { count: { $sum: 1 } })SELECT customer, date, COUNT(*) AS count
FROM orders
GROUP BY customer, date		db.orders.aggregate([
  {$group:{_id:{customer:'$customer', date:'$date'},count:{$sum:1}}}
])
최소/최대 함수Order.group('customer', { min_price: { $min: '$price' }, max_price: { $max: '$price' } })SELECT customer, MIN(price) AS min_price, MAX(price) AS max_price
FROM orders
GROUP BY customer		db.orders.aggregate([
  {$group:{_id:'$customer',min_price:{$min:'$price'},max_price:{$max:'$price'}}}
])