How to filter/reduce a QuerySet to every nth row?

Question

I am storing time-series data from a number of sensors in a MySQL db. Each sensor is associated with a device, and each device can have multiple sensors.

The sensors poll every 10 seconds, so for long periods (day/week/month/year), fetching the unnecessarily large dataset becomes problematic.

I would like to resample the QuerySet prior to evaluation so that it only fetches every nth row. Is this possible?

If not, is there a smarter approach I can take? I suppose I could figure out a where clause that matches 1/n of the possible values for the timestamp's microseconds?

device_name = request.GET['device']
   device = Datalogger.objects.get(device_name=device_name)

   sensors = Sensor.objects.filter(datalogger=device).order_by('pk').select_related('type')
   sensor_models = sensors.values_list('type', flat=True)  # get all models of sensor used by this controller
   sensor_datum_types = list(SensorModelDatumType.objects.filter(sensor__in=sensor_models).order_by('sensor',
                                                                                                    'datum_type'))  # get all datatypes relating to all models of sensor used

# assign each trace (sensor/datum_type combination) an indice for the tuples (zero is used for time/x-axis)
   bulk_queryset = SensorDatum.objects.filter(sensor__datalogger__device_name=device_name,
                                              timestamp__gte=get_filter_start_time(request),
                                              timestamp__lte=get_filter_end_time(request))
   chart_traces = []
   chart_trace_indices = {}
   chart_trace_data = [None]
   chart_trace_queryset = SensorDatum.objects.none()
   next_free_idx = 1
   for sensor in sensors:
       for datum_type in sensor_datum_types:
           if datum_type.sensor == sensor.type:
               chart_trace_name = get_chart_trace_name(sensor.sensor_name, datum_type.datum_type.description)
               chart_traces.append({'sensor': sensor.sensor_name, 'datum_type': datum_type.datum_type.description,
                                    'chart_trace_name': chart_trace_name})
               chart_trace_indices.update({chart_trace_name: next_free_idx})
               chart_trace_queryset = chart_trace_queryset | bulk_queryset.filter(sensor_id=sensor.id,
                                                                                  type_id=datum_type.datum_type.id)
               next_free_idx += 1

   # process data into timestamp-grouped tuples accessible by chart_trace_index ([0] is timestamp)
   raw_data = list(chart_trace_queryset.order_by('timestamp', 'sensor_id', 'type_id'))
   row_count = len(raw_data)

Answer 1

You could perhaps use .annotate() and a modulus to only retrieve every Nth row. I'm using this answer as my reference.

Foo.objects.annotate(idmod4=F('id') % 4).filter(idmod4=0)

This should return approximately every 4th row, though if you're using some other filters as well then you might not get an exact subsample, the filter might exclude a bunch of rows that would fit the modulus, so you get unlucky that the scanner your filtering for doesn't have as many id's that are a multiple of 4. Though you mentioned you are generating a lot of rows, and for a subsample this may be sufficient.