从mongodb中的星期数获取星期的第一天 [英] Getting first day of week from week number in mongodb

问题描述

我有包含日期​​字段的集合.我正在按星期和其他相关字段对记录进行分组.

I have collection containing date field. I'm Grouping records by week and other related fields.

这是我的汇总查询:

db.raw.aggregate([
    { "$match" : {
        "Timestamp":{
            "$gte": new Date("2012-05-30"), 
            "$lt": new Date("2014-07-31")
        }
    }},
    { "$group" : {
        "_id":{ 
            "ApplicationId": "$ApplicationId",
            "Country": "$Country",
            "week":{ "$week": "$Timestamp" }
        },
        "Date":{ "$first": "$Timestamp" },
        "Visits": { "$sum": 1 }
    }}
])

我要投影:从星期几开始的访问和星期几.

I want to Project : Visits and Start Date of week from week number.

推荐答案

您似乎想要一个日期值"来表示一周开始时的日期.最好的方法是在聚合运算符 $dayOfWeek :

You seem to want a "date value" representing the date at the start of the week. Your best approach is "date math" with a little help from the aggregation operator $dayOfWeek:

db.raw.aggregate([
  { "$match" : {
    "Timestamp":{
      "$gte": new Date("2012-05-30"), 
      "$lt": new Date("2014-07-31")
    }
  }},
  { "$group" : {
    "_id":{ 
      "ApplicationId": "$ApplicationId",
      "Country": "$Country",
      "weekStart":{ 
        "$subtract": [
          { "$subtract": [
            { "$subtract": [ "$Timestamp", new Date("1970-01-01") ] },
            { "$cond": [
              { "$eq": [{ "$dayOfWeek": "$Timestamp" }, 1 ] },
              0,
              { "$multiply": [
                1000 * 60 * 60 * 24,
                { "$subtract": [{ "$dayOfWeek": "$Timestamp" }, 1 ] }
              ]}
            ]}
          ]},
          { "$mod": [
            { "$subtract": [
              { "$subtract": [ "$Timestamp", new Date("1970-01-01") ] },
              { "$cond": [
                { "$eq": [{ "$dayOfWeek": "$Timestamp" }, 1 ] },
                0,
                { "$multiply": [
                  1000 * 60 * 60 * 24,
                  { "$subtract": [{ "$dayOfWeek": "$Timestamp" }, 1 ] }
                ]}
              ]}
            ]},
            1000 * 60 * 60 * 24
          ]}
        ]
      }
    },
    "Date":{ "$first": "$Timestamp" },
    "Visits": { "$sum": 1 }
  }}
])

或者使用 $let 从MongoDB 2.6及更高版本开始:

Or a little cleaner with $let from MongoDB 2.6 and upwards:

db.raw.aggregate([
  { "$match" : {
    "Timestamp":{
      "$gte": new Date("2012-05-30"), 
      "$lt": new Date("2014-07-31")
    }
  }},
  { "$group" : {
    "_id":{ 
      "ApplicationId": "$ApplicationId",
      "Country": "$Country",
      "weekStart":{ 
        "$let": {
          "vars": {
            "dayMillis": 1000 * 60 * 60 * 24,
            "beginWeek": {
              "$subtract": [
                { "$subtract": [ "$Timestamp", new Date("1970-01-01") ] },
                { "$cond": [
                  { "$eq": [{ "$dayOfWeek": "$Timestamp" }, 1 ] },
                  0,
                  { "$multiply": [
                    1000 * 60 * 60 * 24,
                    { "$subtract": [{ "$dayOfWeek": "$Timestamp" }, 1 ] }
                  ]}
                ]} 
              ]
            }
          },
          "in": {
            "$subtract": [
              "$$beginWeek",
              { "$mod": [ "$$beginWeek", "$$dayMillis" ]}
            ]
          }
        }
      }
    },
    "Date":{ "$first": "$Timestamp" },
    "Visits": { "$sum": 1 }
  }}
])

分组"中的结果值是纪元毫秒，代表星期初的一天的开始. 一周的开始"通常被认为是星期日"，因此，如果您打算改天，则需要进行适当的调整. $add 运算符， $dayMillis变量值可在此处用于应用星期一".

The resulting value in the "grouping" is the epoch milliseconds that represents the start of the day at the start of the week. The "start of the week" is generally considered to be "Sunday", so if you intend another day then you would need to adjust by the appropriate amount. The $add operator with the $dayMillis variable value can be used here to apply "Monday" for example.

它不是日期对象，但是您可以在后处理中轻松地将其输入另一种方法来构造日期对象.

It's not a date object, but something that you can easily feed to another method to construct a date object in post processing.

还要注意，您正在使用其他内容，例如 通常要求文档以特定顺序或通常通过时间戳"值进行排序.如果尚未订购这些文档，则您 $sort 或使用诸如 以获得该范围内的第一个实际时间戳.

Also note that other things you are using such as $first usually require that the documents are sorted in a particular order, or generally by your "Timestamp" values. If those documents are not already ordered then you either $sort first or use an operator such as $min to get the first actual timestamp in the range.

这篇关于从mongodb中的星期数获取星期的第一天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！