SELECT p.country_id,
q.country_name,
p.dt,
p.deaths,
q.population,
(p.deaths / q.population)*100000 as m_rate
FROM ( SELECT country_id,
reported_date,
deaths,ROW_NUMBER() OVER (PARTITION BY country_id ORDER BY reported_date DESC ) as r
FROM STATISTICS
) p, Demographics d
WHERE r=1 AND p.country_id=q.country_id
ORDER BY m_rate DESC LIMIT 25
Hi @Jalal_Hossain ,
Can you share how ducuments is devided between collections and provide some sample data for better understanding.
Thanks
Pavel
Just take a look at .aggregate method
Docs are here: link
I have two collections: Demographics which contains country_id, country_name, and some other fields and another is statistics which consists of country_id, reported date, confirmed_cases and number of deaths. I want to retrieve highest number of deaths with reporting date countrywise. the result should be like this:
|Germany| 2022-01-29| 118335||
|Bangladesh| 2022-01-30| 34|
I have done the sql query as following:
SELECT
d.country_name, s.dt, MAX(s.deaths)
FROM
Demographics d
inner JOIN statistics s
where d.country_id=s.country_id
GROUP BY country_name
ORDER BY MAX(s.deaths) DESC;
Now i want the same result also in mongodb. Anyone can help please?
Please provide sample documents in well formatted JSON so that we can cut-n-paste into our test bed.
Make sure you read Formatting code and log snippets in posts before posting.
1 Like
{
"type":"database",
"name":"covid-19"
},
{
"type":"table",
"name":"demographics",
"database":"covid-19",
"data":[
{
"country_id":"BGD",
"country_name":"Bangladesh",
"population":"164700000",
"area":"148460",
"density":"1265"
},
{
"country_id":"CHN",
"country_name":"China",
"population":"1402000000",
"area":"9597000",
"density":"153"
},
{
"country_id":"DEU",
"country_name":"Germany",
"population":"83200000",
"area":"357386",
"density":"232"
},
{
"country_id":"ITA",
"country_name":"Italy",
"population":"59550000",
"area":"301340",
"density":"206"
},
{
"country_id":"USA",
"country_name":"United_States_of_Ame",
"population":"329500000",
"area":"9834000",
"density":"36"
}
]
},
{
"type":"table",
"name":"statistics",
"database":"covid-19",
"data":[
{
"country_id":"DEU",
"dt":"2022-01-29",
"confirmed_cases":"2016684",
"deaths":"118335"
},
{
"country_id":"DEU",
"dt":"2022-01-28",
"confirmed_cases":"1911657",
"deaths":"118244"
},
{
"country_id":"DEU",
"dt":"2022-01-30",
"confirmed_cases":"92921",
"deaths":"42"
},
{
"country_id":"DEU",
"dt":"2022-01-29",
"confirmed_cases":"143518",
"deaths":"91"
},
{
"country_id":"DEU",
"dt":"2022-01-28",
"confirmed_cases":"189464",
"deaths":"179"
},
{
"country_id":"DEU",
"dt":"2022-01-27",
"confirmed_cases":"189363",
"deaths":"182"
},
{
"country_id":"DEU",
"dt":"2022-01-26",
"confirmed_cases":"188759",
"deaths":"184"
},
{
"country_id":"DEU",
"dt":"2022-01-25",
"confirmed_cases":"148408",
"deaths":"184"
},
{
"country_id":"DEU",
"dt":"2022-01-24",
"confirmed_cases":"90962",
"deaths":"161"
},
{
"country_id":"DEU",
"dt":"2022-01-23",
"confirmed_cases":"75280",
"deaths":"31"
},
{
"country_id":"DEU",
"dt":"2022-01-22",
"confirmed_cases":"105903",
"deaths":"104"
},
{
"country_id":"DEU",
"dt":"2022-01-23",
"confirmed_cases":"75280",
"deaths":"31"
},
{
"country_id":"DEU",
"dt":"2022-01-22",
"confirmed_cases":"105903",
"deaths":"104"
},
{
"country_id":"DEU",
"dt":"2022-01-21",
"confirmed_cases":"138634",
"deaths":"175"
},
{
"country_id":"DEU",
"dt":"2022-01-20",
"confirmed_cases":"134930",
"deaths":"176"
},
{
"country_id":"DEU",
"dt":"2022-01-19",
"confirmed_cases":"121952",
"deaths":"258"
},
{
"country_id":"DEU",
"dt":"2022-01-18",
"confirmed_cases":"95098",
"deaths":"199"
},
{
"country_id":"DEU",
"dt":"2022-01-17",
"confirmed_cases":"53916",
"deaths":"143"
},
{
"country_id":"BGD",
"dt":"2022-01-30",
"confirmed_cases":"12183",
"deaths":"34"
},
{
"country_id":"BGD",
"dt":"2022-01-29",
"confirmed_cases":"10378",
"deaths":"21"
},
{
"country_id":"BGD",
"dt":"2022-01-30",
"confirmed_cases":"12183",
"deaths":"34"
},
{
"country_id":"BGD",
"dt":"2022-01-29",
"confirmed_cases":"10378",
"deaths":"21"
},
{
"country_id":"BGD",
"dt":"2022-01-28",
"confirmed_cases":"15440",
"deaths":"20"
},
{
"country_id":"BGD",
"dt":"2022-01-27",
"confirmed_cases":"15807",
"deaths":"15"
},
{
"country_id":"BGD",
"dt":"2022-01-26",
"confirmed_cases":"15527",
"deaths":"17"
},
{
"country_id":"BGD",
"dt":"2022-01-25",
"confirmed_cases":"16033",
"deaths":"18"
},
{
"country_id":"BGD",
"dt":"2022-01-24",
"confirmed_cases":"14828",
"deaths":"15"
},
{
"country_id":"BGD",
"dt":"2022-01-23",
"confirmed_cases":"10906",
"deaths":"14"
},
{
"country_id":"BGD",
"dt":"2022-01-22",
"confirmed_cases":"9614",
"deaths":"17"
},
{
"country_id":"BGD",
"dt":"2022-01-21",
"confirmed_cases":"11434",
"deaths":"21"
},
{
"country_id":"BGD",
"dt":"2022-01-20",
"confirmed_cases":"656",
"deaths":"8"
},
{
"country_id":"BGD",
"dt":"2022-01-19",
"confirmed_cases":"9500",
"deaths":"12"
},
{
"country_id":"BGD",
"dt":"2022-01-18",
"confirmed_cases":"8407",
"deaths":"10"
},
{
"country_id":"BGD",
"dt":"2022-01-17",
"confirmed_cases":"6676",
"deaths":"10"
}
]
}
]
1 Like
Hi @Jalal_Hossain ,
I hope that your numbers are not in string quotas whithin mongodb, as then there will be sorted as strings and not as numbers.
Here is the aggregation to run this type of query. It is based on 5.0+ ranking and window functions:
db.demographics.aggregate([{$lookup: {
from: 'statistics',
localField: 'country_id',
foreignField: 'country_id',
as: 'statistics'
}}, {$unwind: {
path: '$statistics'
}}, {$setWindowFields: {
partitionBy: '$country_id',
sortBy: {
'statistics.deaths': -1
},
output: {
rankDeaths: {
$denseRank: {}
}
}
}}, {$match: {
rankDeaths: 1
}}, {$sort: {
'statistics.deaths': -1
}}, {$project: {
country_name: 1,
dt: 1,
deaths: '$statistics.deaths'
}}])
Thanks
Pavel