如何在MongoDB中按季度统计数据数量

Hey there! Let's figure out how to count your MongoDB documents by those four 3-month periods you mentioned. Here's a step-by-step solution using the aggregation framework:

Approach #

We'll use MongoDB's aggregation pipeline to:

1. Tag each document with its corresponding 3-month period based on the date field.
2. Group the documents by these periods and calculate the total count for each group.
3. Format the output to be clean and readable.

Solution Code #

This query will group your documents into the exact periods you specified (1月-3月, 3月-6月, 6月-9月, 9月-12月):

db.your_collection_name.aggregate([
  // Step 1: Add a field to mark which period each document belongs to
  {
    $addFields: {
      quarter_period: {
        $switch: {
          branches: [
            { 
              case: { $and: [{ $gte: [{ $month: "$date" }, 1] }, { $lte: [{ $month: "$date" }, 3] }] }, 
              then: "1月-3月" 
            },
            { 
              case: { $and: [{ $gte: [{ $month: "$date" }, 3] }, { $lte: [{ $month: "$date" }, 6] }] }, 
              then: "3月-6月" 
            },
            { 
              case: { $and: [{ $gte: [{ $month: "$date" }, 6] }, { $lte: [{ $month: "$date" }, 9] }] }, 
              then: "6月-9月" 
            },
            { 
              case: { $and: [{ $gte: [{ $month: "$date" }, 9] }, { $lte: [{ $month: "$date" }, 12] }] }, 
              then: "9月-12月" 
            }
          ],
          default: "其他" // Catch-all for any unexpected dates
        }
      }
    }
  },
  // Step 2: Group by period and count documents
  {
    $group: {
      _id: "$quarter_period",
      total_count: { $sum: 1 }
    }
  },
  // Step 3: Format output for clarity
  {
    $project: {
      _id: 0,
      period: "$_id",
      total_count: 1
    }
  }
])

Don't forget to replace your_collection_name with the actual name of your collection!

Example Output #

Using your sample documents, running this query will return:

[
  { "period" : "1月-3月", "total_count" : 1 },
  { "period" : "3月-6月", "total_count" : 1 },
  { "period" : "9月-12月", "total_count" : 1 }
]

Bonus: Group by Year + Period #

If you need to distinguish between periods across different years (e.g., 2021's 3月-6月 vs 2022's 3月-6月), modify the query to include the year in the grouping:

db.your_collection_name.aggregate([
  {
    $addFields: {
      year: { $year: "$date" },
      quarter_period: {
        $switch: {
          branches: [
            { case: { $and: [{ $gte: [{ $month: "$date" }, 1] }, { $lte: [{ $month: "$date" }, 3] }] }, then: "1月-3月" },
            { case: { $and: [{ $gte: [{ $month: "$date" }, 3] }, { $lte: [{ $month: "$date" }, 6] }] }, then: "3月-6月" },
            { case: { $and: [{ $gte: [{ $month: "$date" }, 6] }, { $lte: [{ $month: "$date" }, 9] }] }, then: "6月-9月" },
            { case: { $and: [{ $gte: [{ $month: "$date" }, 9] }, { $lte: [{ $month: "$date" }, 12] }] }, then: "9月-12月" }
          ],
          default: "其他"
        }
      }
    }
  },
  {
    $group: {
      _id: { year: "$year", period: "$quarter_period" },
      total_count: { $sum: 1 }
    }
  },
  {
    $project: {
      _id: 0,
      year: "$_id.year",
      period: "$_id.period",
      total_count: 1
    }
  }
])

This will give you results like:

[
  { "year" : 2022, "period" : "1月-3月", "total_count" : 1 },
  { "year" : 2021, "period" : "3月-6月", "total_count" : 1 },
  { "year" : 2022, "period" : "9月-12月", "total_count" : 1 }
]

Note on Non-Overlapping Periods #

Your original period definitions overlap (e.g., March is included in both 1月-3月 and 3月-6月). If you want strictly non-overlapping standard quarters, adjust the $switch conditions to:

branches: [
  { case: { $lte: [{ $month: "$date" }, 3] }, then: "1月-3月" },
  { case: { $lte: [{ $month: "$date" }, 6] }, then: "4月-6月" },
  { case: { $lte: [{ $month: "$date" }, 9] }, then: "7月-9月" },
  { case: { $lte: [{ $month: "$date" }, 12] }, then: "10月-12月" }
]

This way, each month belongs to exactly one period.

内容的提问来源于stack exchange，提问作者skodret solutions