feat: support range expr as window frame in snowflake

taozhi8833998 · taozhi8833998 · commit 048aba496e83 · 2024-12-03T20:21:27.000+08:00
diff --git a/pegjs/snowflake.pegjs b/pegjs/snowflake.pegjs
@@ -2687,6 +2687,13 @@ window_frame_clause
     // => string
     return `rows between ${p.value} and ${f.value}`
   }
+  / 'RANGE'i __ KW_BETWEEN __ i:interval_expr __ 'PRECEDING'i __ KW_AND __ f:interval_expr __ 'PRECEDING'i {
+    return {
+      type: 'range',
+      between: i,
+      and: f
+    }
+  }
 
 window_frame_following
   = s:window_frame_value __ 'FOLLOWING'i  {
diff --git a/src/window.js b/src/window.js
@@ -1,7 +1,15 @@
 import { hasVal, toUpper } from './util'
 import { exprToSQL, orderOrPartitionByToSQL } from './expr'
+import { intervalToSQL } from './interval'
 import { overToSQL } from './over'
 
+function rangeExprToSQL(rangeExpr) {
+  if (!rangeExpr) return
+  if (typeof rangeExpr === 'string') return toUpper(rangeExpr)
+  const { type, between, and } = rangeExpr
+  const result = [toUpper(type), 'BETWEEN', intervalToSQL(between), 'PRECEDING', 'AND', intervalToSQL(and), 'PRECEDING']
+  return result.filter(hasVal).join(' ')
+}
 function windowSpecificationToSQL(windowSpec) {
   const {
     name,
@@ -13,7 +21,7 @@ function windowSpecificationToSQL(windowSpec) {
     name,
     orderOrPartitionByToSQL(partitionby, 'partition by'),
     orderOrPartitionByToSQL(orderby, 'order by'),
-    toUpper(windowFrame),
+    rangeExprToSQL(windowFrame),
   ]
   return result.filter(hasVal).join(' ')
 }
diff --git a/test/snowflake.spec.js b/test/snowflake.spec.js
@@ -467,6 +467,23 @@ describe('snowflake', () => {
       'SELECT "my_column"::FLOAT AS "my_number", "my_column"::FLOAT4 AS "my_number2", "my_column"::FLOAT8 AS "my_number3" FROM "my_table"'
       ]
     },
+    {
+      title: 'over window frame',
+      sql: [
+        `SELECT
+    user_id,
+    date(derived_tstamp) AS event_date,
+    price_point,
+    MAX(price_point) OVER (
+        PARTITION BY user_id
+        ORDER BY date(derived_tstamp)
+        RANGE BETWEEN INTERVAL '29 DAYS' PRECEDING AND INTERVAL '1 DAY' PRECEDING
+    ) AS max_price_point_last_30_days
+    FROM
+        some_table;`,
+        `SELECT "user_id", date("derived_tstamp") AS "event_date", "price_point", MAX("price_point") OVER (PARTITION BY "user_id" ORDER BY date("derived_tstamp") ASC RANGE BETWEEN INTERVAL '29 DAYS' PRECEDING AND INTERVAL '1 DAY' PRECEDING) AS "max_price_point_last_30_days" FROM "some_table"`
+      ]
+    },
   ]
   SQL_LIST.forEach(sqlInfo => {
     const { title, sql } = sqlInfo
