从MongoDB中为每个关联查询一个文档 [英] Query one document per association from MongoDB

问题描述

我正在调查MongoDB如何为我们工作.最常用的查询之一用于获取每个站点的最新(或从给定时间开始)测量值.有数千个站点，每个站点都有数以万计的测量值.

I'm investigating how MongoDB would work for us. One of the most used queries is used to get latest (or from a given time) measurements for each station. There is thousands of stations and each station has tens of thousands of measurements.

因此，我们计划为一个站点收集一个，为测量收集另一个.

So we plan to have one collection for stations and another for measurements.

在SQL中，我们将使用

In SQL we would do the query with

SELECT * FROM measurements 
INNER JOIN ( 
  SELECT max(meas_time) station_id 
  FROM measurements 
  WHERE meas_time <= 'time_to_query' 
  GROUP BY station_id 
) t2 ON t2.station_id = measurements.station_id 
    AND t2.meas_time = measurements.meas_time

这将为每个站点返回一个测量值，并且该测量值是"time_to_query"之前的最新测量值.

This returns one measurement for each station, and the measurement is the newest one before the 'time_to_query'.

在MongoDB中应该使用哪个查询来产生相同的结果?我们确实在使用Rails和MongoId，但这没关系.

What query should be used in MongoDB to produce the same result? We are really using Rails and MongoId, but it should not matter.

更新: 这个问题不是关于如何在MongoDB中执行JOIN.在SQL中从表中获取正确的数据需要联接这一事实并不一定意味着在MongoDB中我们也需要联接.查询中只使用一张表.

update: This question is not about how to perform a JOIN in MongoDB. The fact that in SQL getting the right data out of the table requires a join doesn't necessary mean that in MongoDB we would also need a join. There is only one table used in the query.

推荐答案

我猜最坏的解决方案是这样的(超出我的脑海):

I guess worst case solution would be something like this (out of my head):

meassures = []
StationId.all.each do |station|
  meassurement = Meassurment.where(station_id: station.id, meas_time <= 'time_to_query').order_by(meas_time: -1).limit(1)
  meassures << [station.name, meassurement.measure, ....]
end

这取决于查询可以花费多少时间.无论如何，应该通过station_id和meas_time为数据建立索引.

It depends on how much time query can take. Data should anyway be indexed by station_id and meas_time.

SQL查询需要多少时间?

How much time does the SQL query take?

这篇关于从MongoDB中为每个关联查询一个文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！