MongoDB cherry pick data from dates-CodePudding

I have a bunch of hourly captured price data that im bucketing in mongodb and I've got a requirement that I need to pick out the value/price of an item for a set range of intervals such as 1 day, 1 week, 1 month, 3 months and so on.

The data is pretty simple it's just in a collection under the format of

"history" : [ 
   {
     "value" : 3600,
     "date" : ISODate("2021-10-19T11:48:19.811Z"),
   },
   {...},
   {...}
]

Currently I've made a solution to do this but I don't think its the right most performant solution and I'm sure there's a better way to do it. This is my current implementation.

exports.getCollectibleChangeSummary = (req,res) => {
    const slug = req.params.slug
    MarketPriceHistoric.find({ collectibleId: slug })
        .exec((err, data) => {
            if (err){
                return res.status(400).json({
                    error: errorHandler(err)
                })
            }
            const currentPrice = data[0].history[0] ? data[0].history[0].value : 0
            const calcThis = (endDay) => {
                return (currentPrice - endDay) / endDay * 100
            }
            const results = {
                "currentPrice": currentPrice,
                "one_day_change": data[0].history[23] ? calcThis(data[0].history[23].value) : null,
                "one_week_change": data[0].history[161] ? calcThis(data[0].history[161].value) : null,
                "one_month_change": data[0].history[644] ? calcThis(data[0].history[961].value) : null,
                "three_month_change": data[0].history[1932] ? calcThis(data[0].history[1932].value) : null,
                "six_month_change": data[0].history[3864] ? calcThis(data[0].history[3864].value) : null,
                "one_year_change": data[0].history[7728] ? calcThis(data[0].history[7728].value) : null
            }
            res.json(results)
        })
}

Is there a better way to achieve this ? Possibly with the aggregate function?

Thanks,

CodePudding user response：

Query

its big but its exactly the same code 6x only difference is the $multiply arguments
takes one target-date, you can put any date and replace the $$NOW, that is the current date of the server
for example if target-date is ISODate("2021-10-19T11:48:19.811Z") i will calculate
- price range target-date - 24 hours (day range)
- price range target-date - 7 * 24 hours (week range)
- price range target-date - 30 * 24 hours (month range)
- price range target-date - 3 * 30 * 24 hours (3 month range)
- price range target-date - 6 * 30 * 24 hours (6 month range)
- price range target-date - 12 * 30 * 24 hours (12 month range)

*it calculates the max-price and min-price, for each period, you can subtract them or take percentage etc to get the price range in the way you want it

*we dont have data or expected output to be sure, but i think this is what you need

aggregate(
[{"$set": {"target-date": "$$NOW"}},
  {"$facet": 
    {"one-day": 
      [{"$match": 
          {"$expr": 
            {"$lte": 
              [{"$subtract": ["$target-date", "$date"]},
                {"$multiply": [24, 60, 60, 1000]}]}}},
        {"$group": 
          {"_id": null,
            "max-price": {"$max": "$value"},
            "min-price": {"$min": "$value"}}},
        {"$unset": ["_id"]}],
      "one-week": 
      [{"$match": 
          {"$expr": 
            {"$lte": 
              [{"$subtract": ["$target-date", "$date"]},
                {"$multiply": [7, 24, 60, 60, 1000]}]}}},
        {"$group": 
          {"_id": null,
            "max-price": {"$max": "$value"},
            "min-price": {"$min": "$value"}}},
        {"$unset": ["_id"]}],
      "one-month": 
      [{"$match": 
          {"$expr": 
            {"$lte": 
              [{"$subtract": ["$target-date", "$date"]},
                {"$multiply": [30, 24, 60, 60, 1000]}]}}},
        {"$group": 
          {"_id": null,
            "max-price": {"$max": "$value"},
            "min-price": {"$min": "$value"}}},
        {"$unset": ["_id"]}],
      "three-months": 
      [{"$match": 
          {"$expr": 
            {"$lte": 
              [{"$subtract": ["$target-date", "$date"]},
                {"$multiply": [3, 30, 24, 60, 60, 1000]}]}}},
        {"$group": 
          {"_id": null,
            "max-price": {"$max": "$value"},
            "min-price": {"$min": "$value"}}},
        {"$unset": ["_id"]}],
      "six-months": 
      [{"$match": 
          {"$expr": 
            {"$lte": 
              [{"$subtract": ["$target-date", "$date"]},
                {"$multiply": [6, 30, 24, 60, 60, 1000]}]}}},
        {"$group": 
          {"_id": null,
            "max-price": {"$max": "$value"},
            "min-price": {"$min": "$value"}}},
        {"$unset": ["_id"]}],
      "one-year": 
      [{"$match": 
          {"$expr": 
            {"$lte": 
              [{"$subtract": ["$target-date", "$date"]},
                {"$multiply": [12, 30, 24, 60, 60, 1000]}]}}},
        {"$group": 
          {"_id": null,
            "max-price": {"$max": "$value"},
            "min-price": {"$min": "$value"}}}, {"$unset": ["_id"]}]}}])

CodePudding user response：

Maybe you get desired result with new $setWindowFields operator:

db.collection.aggregate([
   { $unwind: "$history" },
   { $replaceRoot: { newRoot: "$history" } },
   { $sort: { date: -1 } },
   {
      $setWindowFields: {
         sortBy: { date: 1 },
         output: {
            last_day_price: {
               $first: "$value",
               window: { range: [-1, "current"], unit: "day" }
            },
            last_week_price: {
               $first: "$value",
               window: { range: [-1, "current"], unit: "week" }
            },
            last_month_price: {
               $first: "$value",
               window: { range: [-1, "current"], unit: "month" }
            },
            three_month_price: {
               $first: "$value",
               window: { range: [-3, "current"], unit: "month" }
            },
            six_month_price: {
               $first: "$value",
               window: { range: [-6, "current"], unit: "month" }
            },
            last_year_price: {
               $first: "$value",
               window: { range: [-1, "current"], unit: "year" }
            }
         }
      }
   },
   {
      $set: {
         currentPrice: "$value",
         one_day_change: { $multiply: [100, { $divide: [{ $subtract: ["$last_day_price", "$value"] }, "$value"] }] },
         one_week_change: { $multiply: [100, { $divide: [{ $subtract: ["$last_week_price", "$value"] }, "$value"] }] },
         one_month_change: { $multiply: [100, { $divide: [{ $subtract: ["$last_month_price", "$value"] }, "$value"] }] },
         three_month_change: { $multiply: [100, { $divide: [{ $subtract: ["$three_month_price", "$value"] }, "$value"] }] },
         six_month_change: { $multiply: [100, { $divide: [{ $subtract: ["$six_month_price", "$value"] }, "$value"] }] },
         one_year_change: { $multiply: [100, { $divide: [{ $subtract: ["$last_year_price", "$value"] }, "$value"] }] }
      }
   },
   { $limit: 1 }
])