按日/月间隔汇总交易金额：近12个月数据聚合需求

Alright, let's break down how to solve this aggregation task for your transaction data. You're looking to sum up payment_amount values over either monthly or daily intervals, and get exactly 12 results covering the most recent 12 months—here's a step-by-step solution tailored to your data structure:

First, Let's Clarify the Core Requirements #

We need to:

• Filter transactions from only the last 12 months
• Group transactions by either month or day (month makes the most sense for 12 results, but I'll cover both)
• Calculate the total payment_amount for each group
• Ensure we end up with exactly 12 aggregated entries

Solution 1: Monthly Aggregation (Perfect for 12 Results) #

This is the ideal approach since it directly maps to 12 months of data. Replace your_transaction_collection with your actual collection name:

db.your_transaction_collection.aggregate([
  // Step 1: Filter only transactions from the last 12 months
  {
    $match: {
      payment_time: {
        $gte: new Date(new Date().setMonth(new Date().getMonth() - 12))
      },
      // Optional: Add this if you only want to count deposit transactions
      payment_type: "deposit"
    }
  },
  // Step 2: Format the payment date into a YYYY-MM string for grouping
  {
    $project: {
      payment_amount: 1,
      yearMonth: {
        $dateToString: {
          format: "%Y-%m",
          date: "$payment_time"
        }
      }
    }
  },
  // Step 3: Group by month and calculate total amount
  {
    $group: {
      _id: "$yearMonth",
      total_transaction_amount: { $sum: "$payment_amount" }
    }
  },
  // Step 4: Sort results chronologically (oldest to newest)
  {
    $sort: { _id: 1 }
  },
  // Step 5: Ensure we only get 12 results (covers last 12 months)
  {
    $limit: 12
  }
])

Key Stage Explanations #

• $match: Uses a dynamic date calculation to grab the last 12 months of data—this automatically handles varying month lengths and leap years, so you don't have to hardcode dates.
• $project: Converts the payment_time timestamp into a clean YYYY-MM string, which makes grouping by month straightforward.
• $group: Sums up all payment_amount values per month, using the formatted year-month string as the group ID.
• $sort: Orders results so you can easily see trends over time.
• $limit: Guarantees you only get 12 entries, which aligns with your requirement.

Solution 2: Daily Aggregation (If You Need 12 Days of Data) #

If you actually meant daily intervals and 12 results (instead of 12 months), adjust the query to target the last 12 days instead:

db.your_transaction_collection.aggregate([
  {
    $match: {
      payment_time: {
        $gte: new Date(new Date().setDate(new Date().getDate() - 12))
      },
      payment_type: "deposit"
    }
  },
  {
    $project: {
      payment_amount: 1,
      transactionDate: {
        $dateToString: {
          format: "%Y-%m-%d",
          date: "$payment_time"
        }
      }
    }
  },
  {
    $group: {
      _id: "$transactionDate",
      total_transaction_amount: { $sum: "$payment_amount" }
    }
  },
  {
    $sort: { _id: 1 }
  },
  {
    $limit: 12
  }
])

Pro Tips #

• Index Optimization: Add an index on payment_time (and payment_type if you're filtering by it) to speed up the $match stage—critical if you have a large dataset.
• Edge Cases: If some months/days have no transactions, the query won't return those groups. If you need to include empty periods with a total of 0, you'll need to use a $lookup with a generated date range, but that's a more advanced scenario.
• Time Zones: If your payment_time is stored in UTC but you need local time grouping, add a timezone parameter to $dateToString (e.g., timezone: "Asia/Shanghai").

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