Major Description
When writing a query, LET clauses are an excellent way to make the query easier to read. If the same formula is used multiple places, you can write once and then reference it. However, there is not currently a way to use the result of one clause in the LET statement as input to a second clause in the LET statement.
Example:
SELECT NumberDocs.locationKey, MILLIS_TO_STR(createDateBySixHours) AS createDate, items.name as name,
|
{
|
"sum": SUM(items.`value`),
|
"avg": AVG(items.`value`),
|
"min": MIN(items.`value`),
|
"max": MAX(items.`value`),
|
"count": COUNT(items.`value`)
|
} as aggregates
|
FROM SiteInfo NumberDocs
|
UNNEST OBJECT_PAIRS(NumberDocs.item) items
|
LET
|
createDateByHour = DATE_TRUNC_MILLIS(STR_TO_MILLIS(NumberDocs.createDate, 'hour'),
|
createDateBySixHours = DATE_ADD_MILLIS(createDateByHour, -(DATE_PART_MILLIS(createDateByHour, 'hour') % 6), 'hour')
|
WHERE NumberDocs.type = 'LocationNumberDataDoc'
|
AND STR_TO_MILLIS(NumberDocs.createDate) >= STR_TO_MILLIS('2016-05-31T00:00:00Z')
|
AND STR_TO_MILLIS(NumberDocs.createDate) < DATE_ADD_MILLIS(STR_TO_MILLIS('2016-05-31T00:00:00Z'), 1, 'day')
|
GROUP BY
|
NumberDocs.locationKey,
|
createDateBySixHours,
|
items.name
|
HAVING COUNT(items.`value`) > 0
|
ORDER BY
|
NumberDocs.locationKey,
|
createDateBySixHours,
|
items.name
|
In the second clause of the LET statement above, DATE_TRUNC_MILLIS(STR_TO_MILLIS(NumberDocs.createDate, 'hour')) is needed twice. So here, we attempt to define this as "createDateByHour" and then reference it in the second clause. This is currently not accepted by the query engine.
