List:Internals« Previous MessageNext Message »
From:John Murtari Date:January 20 2009 6:14pm
Subject:Proposed SYNTAX for adding Row Pattern Recognition to SQL/MySQL
View as plain text  
Folks,  (also posted on comp.databases.mysql

The company I work for is on the U.S. standards committee for SQL
(it's called H2, for more info see http://www.incits.org/).  An
interesting proposal has been made to extend SQL syntax to support Row
Pattern Recognition using a regular expression type syntax (truly a
mouthful!).  We have an interest in MySQL development and wanted to
get some community feedback to hopefully make sure a good standard
moves forward.

The basic idea is to extend SQL to allow a query that can look for
patterns in data spanning many rows.  A simple example would be
looking for patterns in stock market data, e.g. you have a table with
date and stock value columns -- you'd like to do a query to report on
inflection points in the values, e.g.  when did it go up for two days,
followed by a down day, followed by at least two more up days?  You
could even add criteria like, tell me all the down days (> 50 points)
that were preceded by two or MORE updays of > 20 points.

As you can see, the possibilities are endless and could be very useful
in doing analysis!  Obviously, right now this type of stuff is done
by 'data mining' applications -- but it may be a good idea to introduce
a capability into the standard.

The other part of this is using 'regular expression' type syntax to
allows a flexible search (like many of you, I really wondered how
UNIX regexp syntax was going to be turned into SQL!).

I have some concerns, but don't want to taint your review.  I'd also
encourage you to think about the basic problem, row pattern
recognition, and what syntax you might use to query for potential
patterns -- BEFORE reading the proposal.  We don't want to be
lemmings!

Below I include a reference to the paper, please take mind of the
copyright restrictions.  Thoughts should be posted to the group, you
are also welcome to cc the author, Jim Melton, jim.melton@stripped,
who represents Oracle Corporation on the committee.

http://www.softwareworkshop.com/h2/SQL-RPR-review-paper.pdf

Best regards and thanks for your inputs!
-- 
                                          John
___________________________________________________________________
John Murtari                              Software Workshop Inc.
jmurtari@stripped 315.635.1968(x-211)  "TheBook.Com" (TM)
http://www.thebook.com/
Thread
Proposed SYNTAX for adding Row Pattern Recognition to SQL/MySQLJohn Murtari20 Jan
  • Re: Proposed SYNTAX for adding Row Pattern Recognition to SQL/MySQLKonstantin Osipov22 Jan