You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Analytical SQL queries can sometimes rely on a window to aggregate data over, for example:
SELECTMetric.name,
Metric.value,
COUNT(*) OVER Bucket AS bucket_size,
NTILE(100) OVER Bucket AS percentile,
FROM
Metric
WINDOW Bucket AS (
PARTITION BY Metric.nameORDER BYMetric.name, Metric.value
)
But as far as I can tell, there is no API currently for creating named windows like Bucket in this example.
Instead you can do something like:
constbucket=sql`( PARTITION BY ${Metric.name} ORDER BY ${Metric.name}, ${Metric.value})`;constdata=awaitdb.select({name: Metric.name,value: Metric.value,bucket_size: sql`COUNT(*) OVER ${Bucket}`,percentile: sql`NTILE(*) OVER ${Bucket}`,}).from(Metric);
But this limits functionality like window chaining and other SQL features.
It would be nice to have a native API for using named windows, e.g. (drawing on the WITH clause syntax)
constBucket=db.$window('Bucket').partitionBy(Metric.name).orderBy(Metric.name,Metric.value);constdata=awaitdb.select({bucket_size: sql`COUNT(*) OVER ${Bucket}`,}).from(Metric).window(Bucket);
Or in more complex scenarios:
constBucket=db.$window('Bucket').partitionBy(Metric.name).orderBy(Metric.name,Metric.value);constSubBucket=db.$window('Bucket').partitionBy(Metric.name,Metric.sub_type).orderBy(Metric.name,Metric.sub_type,Metric.value);constdata=awaitdb.select({bucket_size: sql`COUNT(*) OVER ${Bucket}`,sub_bucket_count: sql`COUNT(*) OVER ${SubBucket}`,}).from(Metric).window(Bucket,SubBucket);
(A chaining .over() API would be nice too in this case I guess, e.g. using some of Drizzle’s aggregate helpers: count().over(Bucket))
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
Analytical SQL queries can sometimes rely on a window to aggregate data over, for example:
But as far as I can tell, there is no API currently for creating named windows like
Bucket
in this example.Instead you can do something like:
But this limits functionality like window chaining and other SQL features.
It would be nice to have a native API for using named windows, e.g. (drawing on the
WITH
clause syntax)Or in more complex scenarios:
(A chaining
.over()
API would be nice too in this case I guess, e.g. using some of Drizzle’s aggregate helpers:count().over(Bucket)
)Beta Was this translation helpful? Give feedback.
All reactions