ANSI SQL 2003 Extension OVER() appears to be missing in MonetDB Click to flag this post

Niels Nes Niels.Nes at cwi.nl
Mon Oct 22 19:20:07 CEST 2012


On Thu, Oct 18, 2012 at 09:32:45PM +0000, James Becker III wrote:
> Niels…
> 
> > The FIRST_VALUE isn't implemented, but shouldn't be all that difficult
> > to add. The dense rank is and should be directly usable.
> 
> Yes, I have tweaked that out of what I am working with currently.
> 
> > Currently indeed the OVER isn't fully described in the documenation.
> 
> Is how this is implemented in Monet documented at all? I can't find any
> reference to it.
> 
> 
> I have been testing things that work against other vendors DB's in Monet
> to get a feel for it with mixed results.
> 
> If not, in which section of the ANSI documents might I find this bit of
> information?

OVER is in the windowed function section of the SQL standaard (section 6.10 in
the 2008 standard, ISO_9075-02-Foundation_20081)

Niels
> 
> Thx.
> 
> ~jj
> 
> On 10/18/12 4:10 PM, "Niels Nes" <Niels.Nes at cwi.nl> wrote:
> 
> >On Thu, Oct 18, 2012 at 04:25:46PM +0000, James Becker III wrote:
> >>Hi Niels,
> >>Thanks for the fast reply.
> >>The reason I thought that it wasn't supported is that I didn't find over
> >>in sys.functions.
> >>sql>select name from sys.functions where name like 'o%';
> >>+-----------------+
> >>| name            |
> >>+=================+
> >>| or              |
> >>| octet_length    |
> >>| octet_length    |
> >>| octet_length    |
> >>| optimizer_stats |
> >>| optimizers      |
> >>+-----------------+
> >>Is this the wrong place to look for this type of function?
> >
> >Well OVER isn't implemented as function more as a language feature. So
> >no you won't find the 'over' functions in the functions table.
> >>Before I inundate you fine people with my need to understand, is there
> >>any
> >>documentation for Monet's OVER implementation? This would save me from
> >>prematurely posting potentially naïve questions like this to this list.
> >>Here is a sample of the code in vertica that needs to be ported. There
> >>are
> >>more complex examples, but for the spike, I simply need a fair sample
> >>query set to take Monet into a full POC phase of testing.
> >>SELECT
> >>col1
> >>,col2
> >>, FIRST_VALUE(school_name) over (partition by school_loc_code order by
> >>academic_year_code::integer desc)
> >>                                     as school_name
> >>,blahŠ
> >>-- large array of nested subqueries
> >>-- then the dense_rank()over(
> >>, dense_rank()over(
> >>             order by account_code
> >>                    , school_group_inst_code
> >>                    , school_loc_code
> >>                    , grade_code
> >>                    , subject_code
> >>                    , course_code
> >>                    , staff_sid
> >>                    , section_sid
> >>                    , student_sid
> >>                    , attribute_category_code
> >>                    , attribute_value_code) entity_num
> >>I have attached the entire query in it's unmodified vertica form for
> >>deeper analysis, if you are willing to give it a gander. It's kind of
> >>hairy, but the OVER() bits are rather discreet.
> >
> >The FIRST_VALUE isn't implemented, but shouldn't be all that difficult
> >to add. The dense rank is and should be directly usable.
> >
> >Currently indeed the OVER isn't fully described in the documenation.
> >
> >Niels
> >>Thanks again.
> >>~jj
> >>On 10/15/12 3:13 PM, "Niels Nes" <Niels.Nes at cwi.nl> wrote:
> >>>On Mon, Oct 15, 2012 at 05:55:10PM +0000, James Becker III wrote:
> >>>> Hello MonetDB Community.
> >>>>
> >>>> My name is James Becker, my friends call me Jaimi, and I am new to
> >>>> posting messages here, so I hope you will bear with me.
> >>>>
> >>>> I am conducting a POC on MonetDB to see if we can use it to replace
> >>>> Vertica for a large scale read mostly educational reporting
> >>>> application. So far things are showing promise, but the legacy reports
> >>>> lean heavily on OVER(), which doesn't appear to be part of the
> >>>>11.11.11
> >>>> release. Am I missing something, or is this the case?
> >>>>
> >>>> If anyone who has run into a similar limitations has any insights as
> >>>>to
> >>>> where I have made a wrong turn, or how to either implement OVER() in
> >>>> MonetDB using either MAL/C, or any suggestions for query work-arounds
> >>>> that would yield similar results, this would be greatly appreciated.
> >>>>
> >>>> Thx.
> >>>>
> >>>> ~jj
> >>>
> >>>
> >>>James
> >>>
> >>>OVER is supported, but no all of its functionality. Could specify which
> >>>functions you would like to use with over? Also which windowing
> >>>limitations would be required?
> >>>We sofar focused on the row_number and rank functions.
> >>>
> >>>Niels
> >>>
> >>>> _______________________________________________
> >>>> developers-list mailing list
> >>>> developers-list at monetdb.org
> >>>> http://mail.monetdb.org/mailman/listinfo/developers-list
> >>>
> >>>
> >>>--
> >>>Niels Nes, Centrum Wiskunde & Informatica (CWI)
> >>>Science Park 123, 1098 XG Amsterdam, The Netherlands
> >>>room L3.14,  phone ++31 20 592-4098 	sip:4098 at sip.cwi.nl
> >>>url: http://www.cwi.nl/~niels   e-mail: Niels.Nes at cwi.nl
> >
> >
> >>_______________________________________________
> >>developers-list mailing list
> >>developers-list at monetdb.org
> >>http://mail.monetdb.org/mailman/listinfo/developers-list
> >
> >
> >--
> >Niels Nes, Centrum Wiskunde & Informatica (CWI)
> >Science Park 123, 1098 XG Amsterdam, The Netherlands
> >room L3.14,  phone ++31 20 592-4098 	sip:4098 at sip.cwi.nl
> >url: http://www.cwi.nl/~niels   e-mail: Niels.Nes at cwi.nl
> >
> 
> _______________________________________________
> developers-list mailing list
> developers-list at monetdb.org
> http://mail.monetdb.org/mailman/listinfo/developers-list

-- 
Niels Nes, Centrum Wiskunde & Informatica (CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands
room L3.14,  phone ++31 20 592-4098 	sip:4098 at sip.cwi.nl
url: http://www.cwi.nl/~niels   e-mail: Niels.Nes at cwi.nl
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 198 bytes
Desc: not available
URL: <http://www.monetdb.org/pipermail/developers-list/attachments/20121022/8da1c777/attachment.sig>
-------------- next part --------------
_______________________________________________
developers-list mailing list
developers-list at monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list


More information about the developers-list mailing list