Back to DocumentationMQL Documentation

$derivative

Calculates the average rate of change of a field between the first and last documents within a specified window, useful for trend analysis.

Syntax

{ $derivative: { input: <expression>, unit: <timeWindow> } }

Parameters

inputobjectrequired

Expression or field to calculate the rate of change for

unitstringrequired

Time window unit for the rate of change calculation

Examples

Calculate derivative for total sales

Compute average rate of sales change within company partition over time

Query:

db.stores.aggregate([{ $match: { "company": { $in: ["First Up Consultants"] }, "$and": [{ "lastUpdated": { "$gt": ISODate("2024-12-01T03:06:24.180Z") } }, { "lastUpdated": { "$lt": ISODate("2025-12-01T03:55:17.557Z") } }] } }, { $setWindowFields: { "partitionBy": "$company", "sortBy": { "lastUpdated": 1 }, "output": { "storeAverageSales": { "$derivative": { "input": "$sales.totalSales", "unit": "week" }, "window": { "range": [-1, 0], "unit": "week" } } } } }])

Output:

Documents with calculated derivative showing average sales rate of change per week

Price change derivative

Calculate rate of price change over monthly periods

Query:

db.products.aggregate([{ $setWindowFields: { "partitionBy": "$productId", "sortBy": { "priceDate": 1 }, "output": { "priceChangeRate": { "$derivative": { "input": "$price", "unit": "month" } } } } }])

Output:

Products with price change rates calculated over monthly intervals

Related