Using partitions (window functions) in combination with aggregations in MongoDB

Question

In MongoDB I have documents like below (I cross the names of calls for confidentiality):

Now I need to build a query to return results grouped by the name of the call and for each type of call I need to get the number of calls by month, day and hour. Also, in this query I need to indicate a range between two dates (including time).

In SQL server this is done using window functions (partitions) in combination with aggregations but how can I do the same in Mongo?

I am using MongoDB compass as mongo client.

I need to obtain something as below:

call name      month    day  hour  #ByMonth  #ByDay   #ByHour
GetEmployee    January  1    14    10         6       1    
GetEnployee    January  1    18    10         6       5
GetEmployee    January  3    12    10         4       4
GetEmployee    March    5    20    8          8       8
GetEmployee    April    12   17    45         35      35
GetEmployee    April    20   10    45         10      10

For example, for GetEmployee call the distribution is as below:

10 calls done in January
8 calls done in March
45 calls done in April

For the January, the 10 calls are being distributed as below:

6 calls done on 1st January(these 6 calls are distributed as follows: 1 call at 14h and 5 calls at 18h)
4 calls done on 3rd January(these 4 calls are all done at 12h)

and so on for the rest of months.

For example, in SQL Server, if I have below table:

processName   initDateTime           
processA      2020-06-15 13:31:15.330
processB      2020-06-20 10:00:30.000
processA      2020-06-20 13:31:15.330
...
and so on

The SQL query is:

select 
    processName,
    month(initDateTime),
    day(initDateTime),
    datepart(hour, initDateTime),
    sum(count(*)) over(partition by processName, year(initDateTime), month(initDateTime)) byMonth,
    sum(count(*)) over(partition by processName, year(initDateTime), month(initDateTime), day(initDateTime)) byDay,
    count(*) byHour
from mytable
group by 
    processName,
    year(initDateTime),
    month(initDateTime),
    day(initDateTime),
    datepart(hour, initDateTime)

So How to do the same in Mongo? above processName and initDateTime fields would be "call" and "created" attributes respectively in mongodb.

@Valijon It is not necessary, only by taking the created and call attributes shown in the image is enough. No need to query any fields in the json. I have updated the post with an example — Willy, Jun 16 '20 at 08:58
You could use [`$group`](https://docs.mongodb.com/manual/reference/operator/aggregation/group/) within [`$facet`](https://docs.mongodb.com/manual/reference/operator/aggregation/facet/) and [date expression operators](https://docs.mongodb.com/manual/reference/operator/aggregation/#date-expression-operators) — styvane, Jun 16 '20 at 10:03

Using partitions (window functions) in combination with aggregations in MongoDB

0 Answers0