Range support over time

Question

Read this and try experimenting with it.

Below is my diagram.

SCHEMA:

{
    "s"  : "CB",
    "c"  : "REQ_RCV",
    "e"  : "sms_click",
    "st" : "i",
    "b"  : "2",
    "a"  : "1",
    "u"  : "b1_h1_d1_m1_user_2",
    "c#" : "b1_h1_d1_m1_cr-2",
    "@"  : ISODate("2016-10-01T06:03:00.000Z"), //Indexed
    "@h" : "16100106", //hourly bucket
    "@d" : "161001",                            //Indexed
    "@m" : "1610"
}

And below is a plan of explanation:

> 2017-01-22T13:43:47.764+0530 I COMMAND  [conn34] command test-analytics.template3 appName: "MongoDB Shell" command: aggregate {
> aggregate: "template3", pipeline: [ { $match: { @: { $gte: new
> Date(1483228800000), $lte: new Date(1483315199000) } } }, { $group: {
> _id: { b: "$b", HOURLY: "$@h", s: "$s" }, count: { $sum: 1.0 } } }, { $project: { _id: 0.0, BUCKET: "$_id.b", SERVICE: "$_id.s", TIME:
> "$_id.HOURLY", count: 1.0 } }, { $sort: { SERVICE: 1.0, BUCKET: 1.0,
> TIME: 1.0 } } ], cursor: {} } planSummary: IXSCAN { @: 1.0 }
> keysExamined:106888 docsExamined:106888 hasSortStage:1
> cursorExhausted:1 numYields:925 nreturned:96 reslen:7095 locks:{
> Global: { acquireCount: { r: 1860 } }, Database: { acquireCount: { r:
> 930 } }, Collection: { acquireCount: { r: 929 } } }
> protocol:op_command **3499ms**


> 2017-01-22T13:44:24.825+0530 I COMMAND  [conn34] command test-analytics.template3 appName: "MongoDB Shell" command: aggregate {
> aggregate: "template3", pipeline: [ { $match: { @d: "170101" } }, {
> $group: { _id: { b: "$b", HOURLY: "$@h", s: "$s" }, count: { $sum: 1.0
> } } }, { $project: { _id: 0.0, BUCKET: "$_id.b", SERVICE: "$_id.s",
> TIME: "$_id.HOURLY", count: 1.0 } }, { $sort: { SERVICE: 1.0, BUCKET:
> 1.0, TIME: 1.0 } } ], cursor: {} } planSummary: IXSCAN { @d: 1.0 } keysExamined:106888 docsExamined:106888 hasSortStage:1
> cursorExhausted:1 numYields:865 nreturned:96 reslen:7095 locks:{
> Global: { acquireCount: { r: 1740 } }, Database: { acquireCount: { r:
> 870 } }, Collection: { acquireCount: { r: 869 } } }
> protocol:op_command **1294ms**

Questions

• Although the same number of documents is considered in both requests, why is there a time difference in the output?
• Operators $lte, $gteslowing down only in the range of dates or even slower when compared to digital?
• Since bucketing gives a faster answer, how to use bucketing for range queries? I can make multiple cumulative time calls to support range queries, but it will do more round-trip time, any suggestions?
• $and $match ? $match, $match $match, / $match .

Q4:

db.template3.aggregate([ 
    {
        $match: {
            $or: [
                {"@d":"170301"},
                {"@d":"170306"}, 
                {"@d":"170202"}, 
                {"@d":"170303"},
                {"@d":"170304"}, 
                {"@d":"170305"}
            ]
        }
    },
    { $project: { _id: 0, "b": 1, "s": 1, "@h": 1 } }, 
    {
        $group: {
            _id: {"b": "$b", "HOURLY": "$@h", "s": "$s" }, 
            count: { $sum: 1 } 
        }
    },  
    { 
        $project: {
            _id: 0, 
            "BUCKET": "$_id.b",
            "SERVICE": "$_id.s",
            "TIME": "$_id.HOURLY", 
            count: 1
        }
    },         
    { $sort: { "SERVICE": 1, "BUCKET": 1, "TIME": 1 } } 
]);

( ) , . https://docs.mongodb.com/manual/reference/operator/query/or/#or-clauses-and-indexes.

:

db.template3.aggregate([ 

    {$match:{"@h":{$gte : 17020511, $lte : 17030511}, "st":"i"}}, 

    {$project : {"_id":0,  "@h":1,"c":1, "@m":1}}, 

    {$group:{_id:{ "HOURLY":"$@h", "c":"$c"}, count:{$sum:1}}},

    {$project : {_id:0, "COUNTER":"$_id.c","TIME":"$_id.HOURLY", count:1}}, 

    {$sort:{"COUNTER":1,"TIME":1}}
]);

{ "count" : 2255, "COUNTER" : "REQ_RCVD", "TIME" : 17020511 }
{ "count" : 28888, "COUNTER" : "REQ_RCVD", "TIME" : 17020600 }
{ "count" : 37613, "COUNTER" : "REQ_RCVD", "TIME" : 17020601 }
{ "count" : 6723, "COUNTER" : "REQ_RCVD", "TIME" : 17020602 }
{ "count" : 14057, "COUNTER" : "REQ_RCVD", "TIME" : 17020603 }
{ "count" : 12405, "COUNTER" : "REQ_RCVD", "TIME" : 17020604 }
{ "count" : 2392, "COUNTER" : "REQ_RCVD", "TIME" : 17020611 }
{ "count" : 28784, "COUNTER" : "REQ_RCVD", "TIME" : 17020700 }
{ "count" : 37494, "COUNTER" : "REQ_RCVD", "TIME" : 17020701 }
{ "count" : 6697, "COUNTER" : "REQ_RCVD", "TIME" : 17020702 }
{ "count" : 13930, "COUNTER" : "REQ_RCVD", "TIME" : 17020703 }
{ "count" : 12493, "COUNTER" : "REQ_RCVD", "TIME" : 17020704 }
{ "count" : 2225, "COUNTER" : "REQ_RCVD", "TIME" : 17020711 }
{ "count" : 28821, "COUNTER" : "REQ_RCVD", "TIME" : 17020800 }
{ "count" : 37949, "COUNTER" : "REQ_RCVD", "TIME" : 17020801 }
{ "count" : 6676, "COUNTER" : "REQ_RCVD", "TIME" : 17020802 }
{ "count" : 14039, "COUNTER" : "REQ_RCVD", "TIME" : 17020803 }
{ "count" : 12349, "COUNTER" : "REQ_RCVD", "TIME" : 17020804 }
{ "count" : 2332, "COUNTER" : "REQ_RCVD", "TIME" : 17020811 }
{ "count" : 28379, "COUNTER" : "REQ_RCVD", "TIME" : 17020900 }

• , . , docsExamined: 106888

• - , $project $group

• "@h" String Integer (NumberInt), , .

Answer 1

:

?

, , . , . MongoDB , - . , , .

MongoDB , . , $match $sort . $match - , .

$lte, $gte .?

MongoDB BSON, - , . .

bucketing , bucketing ? , , ?

, , time_bucket . created_at , time_bucket. , , . .

time_bucket , - . , .

, , .

, $and $match . Aggregate $match, $match $, / $match .

, . $and, , , $match. $or $or.

$macth MongoDB . .

, . docsExamined: 106888

, .

, $project $group

$group $project, , .

@h dataType int (NumberInt), , .

, . .