Can you have optional measurements with max_by()?

Question

Can you do a max_by() and not throw an error if the column doesn't exist?

```
select customer, site, system, max(time),
	max_by("loads", time) as loads,
	max_by("weight", time) as lbs,
	max_by("turns", time) as turns
from "x"."x-test" 
where time > ago(12h)
 AND customer='TEST'
 AND site='TEST'
 AND system='TEST'
 AND measure_name = 'system-status'
GROUP BY customer, site, system
```

The probably really is that I want to return max_by("*", time) but that's not possible.  The problem is that max_by() won't return null if the column doesn't exist; it will just throw an exception.

I am working off of [this doc](https://aws.amazon.com/blogs/database/effective-queries-for-common-query-patterns-in-amazon-timestream/) section 1 (last value queries) as my template.  I can use the first example (WITH ... SELECT ...) but as the document points out this is more expensive.  It'd be better to use max_by() but not have it flip out of 'weight' or 'turns' is not in that particular MULTI record.

Kyle_B · Answer

Hello,

In Timestream, before executing a query it verifies that all the columns specified in the query actually exists in the corresponding tables. If not, the database will return a “column does not exist error.”
Timestream will not assume an empty/null value for a non-existent column.
So in this case you would have to pass an existing column in max_by() function.
Please let us know if you have more questions.

Can you have optional measurements with max_by()?

Contenuto pertinente