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

newer
Announcement: New Oct2012 Feature...

James Becker III

15 Oct 2012 15 Oct '12

5:55 p.m.

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 _______________________________________________ developers-list mailing list developers-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

Attachments:

attachment.html (text/html — 1.1 KB)

Show replies by date

Niels Nes

15 Oct 15 Oct

7:13 p.m.

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@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@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl _______________________________________________ developers-list mailing list developers-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

James Becker III

18 Oct 18 Oct

4:25 p.m.

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? 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. Thanks again. ~jj On 10/15/12 3:13 PM, "Niels Nes" 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@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@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

_______________________________________________ developers-list mailing list developers-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

Niels Nes

8:10 p.m.

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" 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@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@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

...

_______________________________________________ developers-list mailing list developers-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

James Becker III

9:32 p.m.

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" 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" 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@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@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

...
_______________________________________________ developers-list mailing list developers-list@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@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

_______________________________________________ developers-list mailing list developers-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

Niels Nes

22 Oct 22 Oct

5:20 p.m.

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" 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" 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@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@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

...
_______________________________________________ developers-list mailing list developers-list@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@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

_______________________________________________ developers-list mailing list developers-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

4398

Age (days ago)

4405

Last active (days ago)

List overview

Download

5 comments

2 participants

participants (2)

James Becker III
Niels Nes