티스토리 뷰
반응형
SQL Terms, Functions, and Concepts | MongoDB Aggregation Operators |
---|---|
WHERE | $match |
GROUP BY | $group |
HAVING | $match |
SELECT | $project |
ORDER BY | $sort |
LIMIT | $limit |
SUM() | $sum |
COUNT() | $sum |
join | New in version 3.2. |
Examples
The following table presents a quick reference of SQL aggregation statements and the corresponding MongoDB statements. The examples in the table assume the following conditions:
The SQL examples assume two tables,
orders
andorder_lineitem
that join by theorder_lineitem.order_id
and theorders.id
columns.The MongoDB examples assume one collection
orders
that contain documents of the following prototype:{ cust_id: "abc123", ord_date: ISODate("2012-11-02T17:04:11.102Z"), status: 'A', price: 50, items: [ { sku: "xxx", qty: 25, price: 1 }, { sku: "yyy", qty: 25, price: 1 } ] }
SQL Example | MongoDB Example | Description |
---|---|---|
SELECT COUNT(*) AS count
FROM orders
| db.orders.aggregate( [
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] )
| Count all records from orders |
SELECT SUM(price) AS total
FROM orders
| db.orders.aggregate( [
{
$group: {
_id: null,
total: { $sum: "$price" }
}
}
] )
| Sum the price field from orders |
SELECT cust_id,
SUM(price) AS total
FROM orders
GROUP BY cust_id
| db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
}
] )
| For each unique cust_id , sum the price field. |
SELECT cust_id,
SUM(price) AS total
FROM orders
GROUP BY cust_id
ORDER BY total
| db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
},
{ $sort: { total: 1 } }
] )
| For each unique cust_id , sum the price field, results sorted by sum. |
SELECT cust_id,
ord_date,
SUM(price) AS total
FROM orders
GROUP BY cust_id,
ord_date
| db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
},
total: { $sum: "$price" }
}
}
] )
| For each uniquecust_id , ord_date grouping, sum the price field. Excludes the time portion of the date. |
SELECT cust_id,
count(*)
FROM orders
GROUP BY cust_id
HAVING count(*) > 1
| db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
count: { $sum: 1 }
}
},
{ $match: { count: { $gt: 1 } } }
] )
| For cust_id with multiple records, return the cust_id and the corresponding record count. |
SELECT cust_id,
ord_date,
SUM(price) AS total
FROM orders
GROUP BY cust_id,
ord_date
HAVING total > 250
| db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
},
total: { $sum: "$price" }
}
},
{ $match: { total: { $gt: 250 } } }
] )
| For each unique cust_id , ord_date grouping, sum the price field and return only where the sum is greater than 250. Excludes the time portion of the date. |
SELECT cust_id,
SUM(price) as total
FROM orders
WHERE status = 'A'
GROUP BY cust_id
| db.orders.aggregate( [
{ $match: { status: 'A' } },
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
}
] )
| For each unique cust_id with status A , sum the price field. |
SELECT cust_id,
SUM(price) as total
FROM orders
WHERE status = 'A'
GROUP BY cust_id
HAVING total > 250
| db.orders.aggregate( [
{ $match: { status: 'A' } },
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
},
{ $match: { total: { $gt: 250 } } }
] )
| For each unique cust_id with status A , sum the price field and return only where the sum is greater than 250. |
SELECT cust_id,
SUM(li.qty) as qty
FROM orders o,
order_lineitem li
WHERE li.order_id = o.id
GROUP BY cust_id
| db.orders.aggregate( [
{ $unwind: "$items" },
{
$group: {
_id: "$cust_id",
qty: { $sum: "$items.qty" }
}
}
] )
| For each unique cust_id , sum the corresponding line item qty fields associated with the orders. |
SELECT COUNT(*)
FROM (SELECT cust_id,
ord_date
FROM orders
GROUP BY cust_id,
ord_date)
as DerivedTable
| db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
}
}
},
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] ) |
출처: https://docs.mongodb.com/manual/reference/sql-aggregation-comparison/
반응형
공지사항
최근에 올라온 글
최근에 달린 댓글
- Total
- Today
- Yesterday
링크
TAG
- 루미큐브 종류
- 유가바이트디비
- JMW 헤어드라이기기
- 홈 오피스
- weka
- 오미크론
- 르세라핌
- 로니카 BCS
- 고체 향수
- 솔리드 쿨론
- 별이 빚나는 밤
- 배당급
- Sybase IQ
- 증권정보포털
- Pixel Pals
- yugabyteDB
- 파나소닉 비데 DL-EH10KWS
- 빈센트 반 고흐
- 카카오 에드
- 브리다 정수기
- VARIDESK
- GKRS
- 매직 트랙패드2
- 화분벌레
- node.js
- 별잉 빛나는 밤
- 코라나 19
- 남설 팔찌
- 톡토기
- Life Chair
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | 6 | 7 |
8 | 9 | 10 | 11 | 12 | 13 | 14 |
15 | 16 | 17 | 18 | 19 | 20 | 21 |
22 | 23 | 24 | 25 | 26 | 27 | 28 |
29 | 30 | 31 |
글 보관함