last() function for AWS Timestream db to return last non-null value of a column

Question

i am currently using a AWS Timestream Database in combination with grafana. If i want to get the last non-null record of a column i am querying a timeinterval and using the limit funtions to only display the last value.

Select DevEUI, time, measure_value::boolean as "Bewegung" from $__database.$__table Where measure_name = 'inTrip' and measure_value::boolean = true and $__timeFilter  order by time desc limit 1

Influx db has an last() function which selects the last non-null record from an input table as a selector function.

Is there any equivalent function in AWS Timestream which i am missing? Because AWS has Pay per Use of the byte scanned by a query the last function would reduce the cost.

Thank you for your advice

Answer 1

You can use MAX_BY(#col_name, time) , it will return the latest value of #col_name.

e.g select MAX_BY(#col_name, time) AS #anyname from #dbname.#tableName where #condition

if you have multi measure value data model then you can fetch all latest measure using below query:

select MAX_BY(#col_name1, time) AS #anyname,MAX_BY(#col_name2, time) AS #anyname,MAX_BY(#col_name3, time) AS #anyname .... from #dbname.#tableName where #condition

If you are looking for say latest n value, then you can use MAX_BY(#col_name, time,n). It will return n latest value in descending order of time.

e.g select MAX_BY(#col_name, time,10) AS #anyname from #dbname.#tableName where #condition