[FEATURE] Add support for MATCH_RECOGNIZE clause (BigQuery) to JSQLParser #2350
Description
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.