FazBrowse GitHub Viewer | Trending |
URL:
| Home
Tools: [Download Repo ZIP]   [Original HTTPS Page]

SQL Tutorial · astechedu/testing@cf69ed7 · GitHub

/ testing Public

Commit cf69ed7

Browse files
astechedu
committed
SQL Tutorial
1 parent daa92c9 commit cf69ed7

4 files changed

Lines changed: 595 additions & 0 deletions

File tree

‎sqlphp/developer-notes/databases/mongodb/mongodb.aggregate.txt‎

Lines changed: 169 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,169 @@
1+
>>>> MongoDB Aggregate <<<<<
2+
3+
4+
--------------------------------------------------------------
5+
6+
--------------------------------------------------------------
7+
--------------------------------------------------------------
8+
9+
Keys: Aggregation - SUM, MAX, MIN, AVG
10+
11+
12+
Create, Insert, Find, Drop, Update, limit(),
13+
$set, $inc, $gte, $eq, $expr, $not, $in, $or, $and, $nin, $ne, $lte, $gte,
14+
sort,
15+
$addFields, $set, $project, $unset, $replaceRoot, $replaceWith, Projection,
16+
17+
18+
19+
--------------------------------------------------------------------------
20+
--------------------------------------------------------------------------
21+
22+
--------------------------------------------------------------------------
23+
24+
25+
//Mongodb aggregation
26+
https://www.artofcse.com/learning/mongodb-aggregations
27+
---------------------------------------------------------------------------
28+
29+
$min, $max:
30+
31+
db.collection.find({ max: { $max: "$price" }, min: { $min: "$price" } });
32+
33+
Output:
34+
{ max: 2000, min: 5000 }
35+
36+
37+
db.collection.aggregate([
38+
{ "$group": {
39+
"_id": null,
40+
"max": { "$max": "$price" },
41+
"min": { "$min": "$price" }
42+
}}
43+
])
44+
45+
46+
47+
48+
49+
----- Mongodb queries -----
50+
51+
52+
1. Joins: ($lookup): left outer join, right outer join, full join, inner join
53+
54+
vdo: https://youtu.be/zzVa5cvQK6w?list=PLA3GkZPtsafZydhN4nP0h7hw7PQuLsBv1&t=483
55+
56+
Ex:
57+
58+
db.cust.aggregate([{$lookup:{from:"orders",localField:"_id", foreignField:"customer_id",as:"orderDetail"}}]).pretty()
59+
60+
61+
62+
$inc, $min, $max, $mul, $unset, $rename, upsert, $push,$pop,$addToSet (Topics)
63+
$project, $bucket, aggregate(),
64+
$lookup (like Left Outer Join in mysql)
65+
66+
67+
68+
69+
//Update field but not add to the document
70+
71+
-- Decrease all age by -2
72+
db.users.updateMany({},{$inc:{age-2}})
73+
74+
--Increase age of sita to 50 only if her age is lesser than it
75+
db.users.updateOne({name:"sita"},{$max:{age:50}}) //Now updated age is 50 $max
76+
77+
-- Decrease in above
78+
db.users.updateOne({name:"sita"},{$min:{age:23}}) //Now updated age is 23 $min
79+
80+
-- Multipy by factor or 2
81+
db.users.updateOne({name:"sita"},{$mul:{age:2}}) //Now Multiply by 2 $mul
82+
83+
-- Unset
84+
db.users.updateOne({name:"sita"},{$unset:{age:200}}) //Remove age $unset
85+
86+
87+
-- Rename of field
88+
db.users.updateOne({name:"sita"},{$rename:{age:"studentAge"}}) //Rename of field, rename age with studentAge $rename
89+
90+
91+
92+
-- Rename of field
93+
db.users.updateOne({},{$rename:{age:"studentAge"}}) //Rename of all fields, rename age with studentAge $rename
94+
95+
--Insert document if condition not found (agar golu nahi milta hai toh)
96+
db.users.updateOne({name:"golu"},{$set:{age:100}},{upsert:true}) // Agar golu nahi milta toh ye document insert kardo
97+
98+
99+
100+
101+
-----------------------------------------------------------------------------------------------------------
102+
103+
104+
105+
// how to perform aggregations such as (SUM, MAX, MIN, AVG, PUSH, addToSet, FRIST, LAST) on MongoDB query.
106+
107+
108+
SUM:
109+
110+
db.employees.aggregate([ { $group: { _id: "$phone", total: { $sum: "$salary" } } } ])
111+
112+
MAX:
113+
114+
db.employees.aggregate([ { $group: { _id: "$phone", total: { $max: "$salary" } } } ])
115+
116+
MIN:
117+
118+
db.employees.aggregate([ { $group: { _id: "$phone", total: { $min: "$salary" } } } ])
119+
120+
AVG:
121+
122+
db.employees.aggregate([ { $group: { _id: "$phone", total: { $avg: "$salary" } } } ])
123+
124+
PUSH:
125+
126+
db.employees.aggregate([ { $group: {
127+
128+
_id: "$phone", total: { $push: "$salary" } } } ])
129+
130+
addToSet:
131+
132+
db.employees.aggregate([ { $group: { _id: "$phone", total: { $addToSet: "$salary" } } } ])
133+
134+
First:
135+
136+
db.employees.aggregate([ { $group: { _id: "$phone", total: { $first: "$salary" } } } ])
137+
138+
Last:
139+
140+
db.employees.aggregate([ { $group: { _id: "$phone", total: { $last: "$salary" } } } ])
141+
142+
--------------------------------------------------------------------------------------------------
143+
144+
145+
146+
147+
148+
149+
150+
151+
152+
153+
154+
155+
156+
157+
158+
159+
160+
161+
162+
MongoDB Aggregation - SUM, MAX, MIN, AVG
163+
164+
165+
166+
167+
168+
169+
--------------------------------------------------------

‎sqlphp/developer-notes/databases/mongodb/mongodb.joins.txt‎

Lines changed: 130 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,130 @@
1+
>>>>> MongoDB Joins <<<<<<<<<<<<
2+
3+
----------------------------------------------------------------------------
4+
5+
----------------------------------------------------------------------------
6+
Key: Joins Using view,
7+
8+
----------------------------------------------------------------------------
9+
10+
----------------------------------------------------------------------------
11+
12+
----------------------------------------------------------------------------'
13+
14+
Joins with view
15+
https://www.mongodb.com/docs/manual/core/views/join-collections-with-view/
16+
17+
18+
19+
----------------------------------------------------------------------------
20+
21+
----------------------------------------------------------------------------
22+
23+
----------------------------------------------------------------------------
24+
25+
26+
27+
----> Join Using View <------------
28+
29+
30+
31+
32+
----> Use a View to Join Two Collections <-----
33+
34+
You can use $lookup to create a view over two collections and then run queries against the view. Applications can query the view without having to construct or maintain complex pipelines.
35+
36+
37+
Example
38+
39+
Create two sample collections, inventory and orders:
40+
41+
db.inventory.insertMany( [
42+
{ prodId: 100, price: 20, quantity: 125 },
43+
{ prodId: 101, price: 10, quantity: 234 },
44+
{ prodId: 102, price: 15, quantity: 432 },
45+
{ prodId: 103, price: 17, quantity: 320 }
46+
] )
47+
db.orders.insertMany( [
48+
{ orderId: 201, custid: 301, prodId: 100, numPurchased: 20 },
49+
{ orderId: 202, custid: 302, prodId: 101, numPurchased: 10 },
50+
{ orderId: 203, custid: 303, prodId: 102, numPurchased: 5 },
51+
{ orderId: 204, custid: 303, prodId: 103, numPurchased: 15 },
52+
{ orderId: 205, custid: 303, prodId: 103, numPurchased: 20 },
53+
{ orderId: 206, custid: 302, prodId: 102, numPurchased: 1 },
54+
{ orderId: 207, custid: 302, prodId: 101, numPurchased: 5 },
55+
{ orderId: 208, custid: 301, prodId: 100, numPurchased: 10 },
56+
{ orderId: 209, custid: 303, prodId: 103, numPurchased: 30 }
57+
] )
58+
59+
60+
61+
62+
Create a Joined View:
63+
--------------------
64+
65+
This command uses db.createView() to create a new view named sales based on the orders collection:
66+
67+
68+
db.createView( "sales", "orders", [
69+
{
70+
$lookup:
71+
{
72+
from: "inventory",
73+
localField: "prodId",
74+
foreignField: "prodId",
75+
as: "inventoryDocs"
76+
}
77+
},
78+
{
79+
$project:
80+
{
81+
_id: 0,
82+
prodId: 1,
83+
orderId: 1,
84+
numPurchased: 1,
85+
price: "$inventoryDocs.price"
86+
}
87+
},
88+
{ $unwind: "$price" }
89+
] )
90+
91+
92+
93+
In the example:
94+
95+
The $lookup stage uses the prodId field in the orders collection to "join" documents in the inventory collection that have matching prodId fields.
96+
97+
The matching documents are added as an array in the inventoryDocs field.
98+
99+
The $project stage selects a subset of the available fields.
100+
101+
The $unwind stage converts the price field from an array to a scalar value.
102+
103+
104+
105+
Query the View:
106+
107+
To find the total amount sold of each product, query the view:
108+
109+
db.sales.aggregate( [
110+
{
111+
$group:
112+
{
113+
_id: "$prodId",
114+
amountSold: { $sum: { $multiply: [ "$price", "$numPurchased" ] } }
115+
}
116+
}
117+
] )
118+
119+
120+
121+
122+
123+
124+
125+
126+
127+
128+
----------------------------------------------------------------------------
129+
130+

0 commit comments

Comments
 (0)
Back | FazBrowse Home | New Git URL