Major Description
[ONE ROW | ALL ROWS] PER MATCH: Choosing Summaries or Details for Each Match
You will sometimes want summary data about the matches and other times need details. You can do that with the following SQL:
- ONE ROW PER MATCH
Each match produces one summary row. This is the default. - ALL ROWS PER MATCH
A match spanning multiple rows will produce one output row for each row in the match.
The output is explained in "Row Pattern Output".
The MATCH_RECOGNIZE clause may find a match with zero rows. For an empty match, ONE ROW PER MATCH returns a summary row: the PARTITION BY columns take the values from the row where the empty match occurs, and the measure columns are evaluated over an empty set of rows.
ALL ROWS PER MATCH has three suboptions:
- ALL ROWS PER MATCH SHOW EMPTY MATCHES
- ALL ROWS PER MATCH OMIT EMPTY MATCHES
- ALL ROWS PER MATCH WITH UNMATCHED ROWS