Mongo查询多个日期范围 [英] Mongo query for multiple date ranges

问题描述

我的付款收集中包含从Stripe Charges返回的数据.创建的"属性是时间戳记(unix epoch以秒为单位).我有一个查询，该查询可在一个日期范围内找到给定成员ID的成功收费并求和:

My payments collection contains data returned from Stripe Charges. The "created" attribute is a timestamp (unix epoch in seconds). I have a query that finds successful charges for a given member id within a date range and sums the payment amounts:

// start of the day for Jan 1, 2017 (unix epoch) 
var jan1 = 1483250400

// end of the day for May 1, 2017 (unix epoch)
var may1 = 1502000000

var pipeline = [
  {
    $match: {
      // Member id
      _id: ObjectId("597ceea6122ccfda71d011be"),
    }
  },
  {
    $project: {
      _id: 0,
      payments: {
        $filter: {
          input: "$payments",
          as: "payment",
          cond: {
            $and: [
              { $gte: ["$$payment.created", jan1] },
              { $lte: ["$$payment.created", may1] },
              { $eq: ["$$payment.status", "succeeded"] }
            ]
          }
        }
      }
    }
  },
  {
    $project: {
      paid: {
        $sum: "$payments.amount"
      }
    }
  }
]
db.members.aggregate(pipeline).pretty()

它以以下格式返回数据:

It returns data in the following format:

{
  "paid" : 190000
}

问题是我还要查询其他几个日期范围:

• 1月1日-7月15日
• 1月1日-9月15日
• 1月1日-12月31日

我总是可以单独执行每个查询，但我希望一次执行所有查询.我尝试使用 $ bucket ，但这并不能支持使用与1月1日相同的下限阈值.

I can always do each query individually, but I'd rather do all of them at once. I've tried using $bucket, but that doesn't support using the same lower end threshold of Jan 1.

我想让每个日期范围的付款号码相关联.理想情况下，输出看起来像这样:

I would like to have the paid number associated with each date range. Ideally, the output would look like this:

{
  "May 1": 190000,
  "July 15": 240000,
  "Sept 15": 250000,
  "Dec 31": 255000
}

推荐答案

一种方法如下:

var jan1 = 1483250400;
var may2 = 1493683200;
var jul16 = 1500163200;
var sep16 = 1505520000;
var jan1NextYear = 1514764800;

db.collection.aggregate([
  {
    // I omit your match stage here but you will need it, obviously
    $project: {
      payments: {
        $map: {
          input: "$payments",
          as: "payment",
          in: {
            janToMay: {
              $cond: {
                if: {
                  $and: [
                    { $gte: ["$$payment.created", jan1] },
                    { $lt: ["$$payment.created", may2] }
                  ]
                },
                then: "$$payment.amount",
                else: null
              }
            },
            janToJul: {
              $cond: {
                if: {
                  $and: [
                    { $gte: ["$$payment.created", jan1] },
                    { $lt: ["$$payment.created", jul16] }
                  ]
                },
                then: "$$payment.amount",
                else: null
              }
            },
            janToSep: {
              $cond: {
                if: {
                  $and: [
                    { $gte: ["$$payment.created", jan1] },
                    { $lt: ["$$payment.created", sep16] }
                  ]
                },
                then: "$$payment.amount",
                else: null
              }
            },
            janToDec: {
              $cond: {
                if: {
                  $and: [
                    { $gte: ["$$payment.created", jan1] },
                    { $lt: ["$$payment.created", jan1NextYear] }
                  ]
                },
                then: "$$payment.amount",
                else: null
              }
            }
          }
        }
      }
    }
  },
  {
    $project: {
      "May 1": {
        $sum: "$payments.janToMay"
      },
      "Jul 15": {
        $sum: "$payments.janToJul"
      },
      "Sep 15": {
        $sum: "$payments.janToSep"
      },
      "Dec 31": {
        $sum: "$payments.janToDec"
      },
    }
  }
])

这是一个非常通用的解决方案.但是，在您的特定情况下，您可能希望将所有过滤器的公用部分提取到一个单独的过滤器步骤中，如下所示:

This is a pretty generic solution. In your particular case, however, you might want to extract the common part of all your filters to a separate filter step like so:

var jan1 = 1483250400;
var may1 = 1493596800;
var jul16 = 1500163200;
var sep16 = 1505520000;
var jan1NextYear = 1514764800;

db.collection.aggregate([
  {
    // I omit your match stage here but you will need it, obviously
    $project: {
      payments: {
        $map: {
          input: {
              $filter: { // here we drop all the elements that all of the below filters would drop anyway
                  input: "$payments",
                  as: "payment",
                  cond: {
                    $gte: ["$$payment.created", jan1],
                  }
              }
          },
          as: "payment",
          in: {
            janToMay: {
              $cond: {
                if: {
                  $lt: ["$$payment.created", may1]
                },
                then: "$$payment.amount",
                else: null
              }
            },
            janToJul: {
              $cond: {
                if: {
                  $lt: ["$$payment.created", jul16]
                },
                then: "$$payment.amount",
                else: null
              }
            },
            janToSep: {
              $cond: {
                if: {
                  $lt: ["$$payment.created", sep16]
                },
                then: "$$payment.amount",
                else: null
              }
            },
            janToDec: {
              $cond: {
                if: {
                  $lt: ["$$payment.created", jan1NextYear]
                },
                then: "$$payment.amount",
                else: null
              }
            }
          }
        }
      }
    }
  }
  // the final project stage stays identical to the one in the above example
])

这篇关于Mongo查询多个日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！