[FEATURE] Add support for MATCH_RECOGNIZE clause (BigQuery) to JSQLParser

[zedach]

Grammar or Syntax Description

• JSQLParser version tested: 5.4-SNAPSHOT. The query above throws > Encountered: <K_BY> / "BY", at line 14, column 12, in lexical state DEFAULT.
• Error encountred using Starlake.ai | SQL PLayground on DuckDB Transpiller tab.
• BigQuery MATCH_RECOGNIZE syntax – https://cloud.google.com/bigquery/docs/match-recognize
• Blog announcement – https://cloud.google.com/blog/products/data-analytics/introducing-match_recognize-in-bigquery

SQL Example

WITH Operations AS (
  SELECT 1 AS OperationID, 120.00 AS Amount, 'C001' AS CatalogID,
         DATE '2025-01-03' AS OperationDate UNION ALL
  SELECT 2, 20.00, 'C001', DATE '2025-01-04' UNION ALL
  SELECT 3, 175.00, 'C001', DATE '2025-01-05' UNION ALL
  SELECT 4,  30.00, 'C001', DATE '2025-01-10' UNION ALL
  SELECT 5, 190.00, 'C001', DATE '2025-01-11' UNION ALL
  SELECT 6, 250.00, 'C001', DATE '2025-01-12'
)

SELECT *
FROM Operations
MATCH_RECOGNIZE (
  PARTITION BY CatalogID
  ORDER BY OperationDate ASC
  MEASURES
    FIRST(OperationDate) AS START_DT,
    LAST(OperationDate)  AS END_DT,
    SUM(Amount)          AS TOTAL_AMOUNT,
    COUNT(*)             AS ROW_COUNT
  AFTER MATCH SKIP  PAST LAST ROW   
  PATTERN (low mid+ high+)                      
  DEFINE
    low AS Amount < 50,
    mid AS Amount between 100 and 200,
    high AS Amount > 200
  OPTIONS ( use_longest_match = FALSE )
)
ORDER BY CatalogID, START_DT;

This query sgould return this result

CatalogID	START_DT	END_DT	         TOTAL_AMOUNT	 ROW_COUNT
C001	        2025-01-10	2025-01-12	  470.0	                 3

Thank you for considering this feature.

[manticore-projects]

Greetings!

MATCH_RECOGNIZE(...) is not supported by JSQLParser and I am not sure if implementing makes any sense since DuckDB does also not support it.