RESULTS ALL ROWS

[dh37]

I'm using Vertica's MATCH clause, which the Vertica SQL Reference Manual describes as "A SQL extension that lets you screen large amounts of historical data in search of event patterns, the MATCH clause provides subclasses for analytic partitioning and ordering and matches rows from the result table based on a pattern you define."

In the examples in the Programmer's Guide, there's a phrase RESULTS ALL ROWS that appears in examples, like this:

Code: Select all

SELECT uid,
       sid,
       ts,
       refurl,
       pageurl,
       action,
       event_name(),
       pattern_id(),
       match_id()
FROM clickstream_log
MATCH
  (PARTITION BY uid, sid ORDER BY ts
   DEFINE
     Entry AS RefURL NOT ILIKE '%website2.com%' AND PageURL ILIKE '%website2.com%',
     Onsite AS PageURL ILIKE '%website2.com%' AND Action='V',
     Purchase AS PageURL ILIKE '%website2.com%' AND Action = 'P'
   PATTERN
     P AS (Entry Onsite* Purchase)
   RESULTS ALL ROWS);

However, I cannot find a defition of what RESULTS ALL ROWS does. Disturbingly, it affects the output, but in some subtle way. (Meaning, it takes quite a few rows to hit a difference.)

Anyone knows what this phrase actually does?

[id10t]

Hi!

It isn't clear from documentation but its a default value for number of matches: all or first only
Look here for parameter "ROWS MATCH ( ALL EVENTS | FIRST EVENT )".

[Julie]

Thanks, sKwa. That was helpful to me too!

[dh37]

Thanks for the info---very helpful.