关系数据库背景用户如何实现MongoDB遥测数据按小时分组聚合查询

修正后的MongoDB聚合查询 #

你可以直接使用下方的查询实现和目标PostgreSQL语句完全一致的效果：

db.telemetry.aggregate([
    // 过滤匹配条件，对应SQL的WHERE逻辑
    {
        $match: {
            system_code: 22,
            date: {
                $gte: ISODate("2021-08-01T00:00:00.000Z"),
                $lte: ISODate("2021-08-01T02:00:00.000Z")
            }
        }
    },
    // 按格式化后的小时维度分组，同时计算两个字段的平均值
    {
        $group: {
            _id: {
                $dateToString: {
                    format: "%Y-%m-%d %H:00:00",
                    date: "$date",
                    timezone: "Europe/Rome" // 需要意大利时区转换时保留该配置，不需要可直接删除
                }
            },
            avgValue1: { $avg: "$value1" },
            avgValue2: { $avg: "$value2" }
        }
    },
    // 调整字段返回格式，和SQL返回的字段名对齐
    {
        $project: {
            _id: 0,
            italianDate: "$_id",
            avgValue1: 1,
            avgValue2: 1
        }
    },
    // 按时间升序排序，对应SQL的ORDER BY逻辑
    {
        $sort: {
            italianDate: 1
        }
    }
])

调整说明 #

• 原查询先排序再分组属于无效操作，分组操作会打乱之前的排序结果，因此把排序步骤移到分组之后
• 原$group阶段只定义了分组规则，缺少聚合计算逻辑，补充了$avg算子实现两个指标的平均值统计
• 直接把格式化后的日期作为分组键，和PostgreSQL的GROUP BY TO_CHAR(...)逻辑完全对齐，不需要单独拆分年/月/日/小时维度再分组
• 补充$project阶段将字段名调整为你需要的italianDate，同时隐藏默认返回的_id字段
• 如果需要使用意大利时区做日期格式化，保留timezone配置即可，不需要的话直接删除该行就行

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