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

James Becker III jbecker at wgen.net
Thu Oct 18 23:32:45 CEST 2012


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?

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


More information about the developers-list mailing list