fix: over window frame current row in snwoflake

taozhi8833998 · taozhi8833998 · commit a7aa86a9370f · 2024-12-23T08:53:25.000+08:00
diff --git a/pegjs/snowflake.pegjs b/pegjs/snowflake.pegjs
@@ -2678,6 +2678,15 @@ window_specification_frameless
     }
   }
 
+interval_expr_preceding
+  = i:interval_expr __ 'PRECEDING'i {
+    const suffix = {
+      type: 'origin',
+      value: 'preceding',
+    }
+    i.suffix = suffix;
+    return i;
+  }
 window_frame_clause
   = kw:KW_ROWS __ s:(window_frame_following / window_frame_preceding) {
     return {
@@ -2696,17 +2705,11 @@ window_frame_clause
     }
     return createBinaryExpr(op, left, right)
   }
-  / 'RANGE'i __ op:KW_BETWEEN __ p:interval_expr __ 'PRECEDING'i __ KW_AND __ f:interval_expr __ 'PRECEDING'i {
+  / 'RANGE'i __ op:KW_BETWEEN __ p:interval_expr_preceding __ KW_AND __ f:(interval_expr_preceding / window_frame_current_row) {
     const left = {
       type: 'origin',
       value: 'range',
     }
-    const suffix = {
-      type: 'origin',
-      value: 'preceding',
-    }
-    p.suffix = suffix
-    f.suffix = suffix
     const right = {
       type: 'expr_list',
       value: [p, f]
diff --git a/test/snowflake.spec.js b/test/snowflake.spec.js
@@ -484,6 +484,22 @@ describe('snowflake', () => {
         `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"`
       ]
     },
+    {
+      title: 'window function with range',
+      sql: [
+        `SELECT
+          SUM(id) OVER (
+            PARTITION BY
+              name
+            ORDER BY
+              created_at ASC RANGE BETWEEN INTERVAL '9 DAYS' PRECEDING
+              AND CURRENT ROW 
+          ) last10Dmatches
+        FROM
+          model1`,
+        `SELECT SUM("id") OVER (PARTITION BY "name" ORDER BY "created_at" ASC RANGE BETWEEN INTERVAL '9 DAYS' PRECEDING AND CURRENT ROW) AS "last10Dmatches" FROM "model1"`
+      ]
+    },
   ]
   SQL_LIST.forEach(sqlInfo => {
     const { title, sql } = sqlInfo
