Databases
StackOverflow2013
Postsdbo
POAggregating timeseries from sensors
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAggregating timeseries from sensors
    primarykey
    data
    text
    <p>I have about 500 sensors which emit a value about once a minute each. It can be assumed that the value for a sensor remains constant until the next value is emitted, thus creating a time series. The sensors are not synchronized in terms of when they emit data (so the observation timestamps vary), but it's all collected centrally and stored per sensor (to allow filtering by subset of sensors).</p> <p>How can I produce an aggregate time series that gives the sum of the data from the sensors? n (need to create a time series over 1 day's set of observations - so will need to take into account 24x60x500 observations per day). The calculations also need to be fast, preferrably run in in &lt; 1s. </p> <p>Example - raw input:</p> <pre><code>q)n:10 q)tbl:([]time:n?.z.t;sensor:n?3;val:n?100.0) q)select from tbl time sensor val ---------------------------- 01:43:58.525 0 33.32978 04:35:12.181 0 78.75249 04:35:31.388 0 1.898088 02:31:11.594 1 16.63539 07:16:40.320 1 52.34027 00:49:55.557 2 45.47007 01:18:57.918 2 42.46532 02:37:14.070 2 91.98683 03:48:43.055 2 41.855 06:34:32.414 2 9.840246 </code></pre> <p>The output I'm looking for should show the same timestamps, and the sum across sensors. If a sensor doesn't have a record defined at a matching timestamp, then it's previous value should be used (the records only imply times when the output from the sensor changes). </p> <p>Expected output, sorted by time</p> <pre><code>time aggregatedvalue ---------------------------- 00:49:55.557 45.47007 / 0 (sensor 0) + 0 (sensor 1) + 45.47007 (sensor 2) 01:18:57.918 42.46532 / 0 (sensor 0) + 0 (sensor 1) + 42.46532 (new value on sensor 2) 01:43:58.525 75.7951 / 33.32978 + 0 + 42.46532 02:31:11.594 92.43049 / 33.32978 + 16.63539 + 42.46532 02:37:14.070 141.952 / 33.32978 + 16.63539 + 91.98683 03:48:43.055 91.82017 / 33.32978 + 16.63539 + 41.855 04:35:12.181 137.24288 / 78.75249 + 16.63539 + 41.855 04:35:31.388 60.388478 / 1.898088 + 16.63539 + 41.855 06:34:32.414 28.373724 / 1.898088 + 16.63539 + 9.840246 07:16:40.320 64.078604 / 1.898088 + 52.34027 + 9.840246 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USf.carlsen
    1. USf.carlsen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAggregating timeseries from sensors
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload