Proper syntax for a windowing function
Hi, all. I'm trying to figure out the proper syntax for queries such as a moving average over a window in MonetDB. THis works: sql>select avg(energy) from TABLENAME; +--------------------------+ | L3 | +==========================+ | 658.5049567388747 | +--------------------------+ This fails: select avg(energy) OVER () from TABLENAME; SELECT: function 'avg' not found (Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP1) Database: MonetDB v11.27.5 (Jul2017-SP1), 'mapi:monetdb://SERVER2:50000/scada' ) How do I write such a query? Many thanks.
https://www.monetdb.org/Documentation/Manuals/SQLreference/WindowFunctions From: users-list [mailto:users-list-bounces+dann.corbit=softwareag.com@monetdb.org] On Behalf Of Omri Schwarz Sent: Friday, October 20, 2017 1:46 PM To: users-list@monetdb.org Subject: Proper syntax for a windowing function Hi, all. I'm trying to figure out the proper syntax for queries such as a moving average over a window in MonetDB. THis works: sql>select avg(energy) from TABLENAME; +--------------------------+ | L3 | +==========================+ | 658.5049567388747 | +--------------------------+ This fails: select avg(energy) OVER () from TABLENAME; SELECT: function 'avg' not found (Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP1) Database: MonetDB v11.27.5 (Jul2017-SP1), 'mapi:monetdb://SERVER2:50000/scada' ) How do I write such a query? Many thanks.
Thank you. I've been trying all sorts of queries, but notice the avg function becoming unavailable the moment I try a windowing operation? ________________________________ From: users-list [users-list-bounces+ocschwar=mit.edu@monetdb.org] on behalf of Corbit, Dann [Dann.Corbit@softwareag.com] Sent: Friday, October 20, 2017 5:07 PM To: Communication channel for MonetDB users Subject: RE: Proper syntax for a windowing function https://www.monetdb.org/Documentation/Manuals/SQLreference/WindowFunctions From: users-list [mailto:users-list-bounces+dann.corbit=softwareag.com@monetdb.org] On Behalf Of Omri Schwarz Sent: Friday, October 20, 2017 1:46 PM To: users-list@monetdb.org Subject: Proper syntax for a windowing function Hi, all. I'm trying to figure out the proper syntax for queries such as a moving average over a window in MonetDB. THis works: sql>select avg(energy) from TABLENAME; +--------------------------+ | L3 | +==========================+ | 658.5049567388747 | +--------------------------+ This fails: select avg(energy) OVER () from TABLENAME; SELECT: function 'avg' not found (Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP1) Database: MonetDB v11.27.5 (Jul2017-SP1), 'mapi:monetdb://SERVER2:50000/scada' ) How do I write such a query? Many thanks.
Omri, Were you ever able to get this to work? From: users-list [mailto:users-list-bounces+david.b.anderson=citi.com@monetdb.org] On Behalf Of Omri Schwarz Sent: Friday, October 20, 2017 5:45 PM To: Communication channel for MonetDB users Subject: RE: Proper syntax for a windowing function Thank you. I've been trying all sorts of queries, but notice the avg function becoming unavailable the moment I try a windowing operation? ________________________________ From: users-list [users-list-bounces+ocschwar=mit.edu@monetdb.org] on behalf of Corbit, Dann [Dann.Corbit@softwareag.com] Sent: Friday, October 20, 2017 5:07 PM To: Communication channel for MonetDB users Subject: RE: Proper syntax for a windowing function https://www.monetdb.org/Documentation/Manuals/SQLreference/WindowFunctionshttps://urldefense.proofpoint.com/v2/url?u=https-3A__www.monetdb.org_Documentation_Manuals_SQLreference_WindowFunctions&d=DwMFAw&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=8_GYjk1edsyLJlNaxMxYxBJsviF3JXYvwDK42uy5KWU&m=EJH0dRe60FXa2hVIFF-UyYpyZU-LmfuNY1rLr_zmkv0&s=zgU_fNcSZzDJcaICO97cRmawm2k0WfdqqsLeT8JqqTo&e= From: users-list [mailto:users-list-bounces+dann.corbit=softwareag.com@monetdb.org] On Behalf Of Omri Schwarz Sent: Friday, October 20, 2017 1:46 PM To: users-list@monetdb.orgmailto:users-list@monetdb.org Subject: Proper syntax for a windowing function Hi, all. I'm trying to figure out the proper syntax for queries such as a moving average over a window in MonetDB. THis works: sql>select avg(energy) from TABLENAME; +--------------------------+ | L3 | +==========================+ | 658.5049567388747 | +--------------------------+ This fails: select avg(energy) OVER () from TABLENAME; SELECT: function 'avg' not found (Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP1) Database: MonetDB v11.27.5 (Jul2017-SP1), 'mapi:monetdb://SERVER2:50000/scada' ) How do I write such a query? Many thanks.
I figured out the proper syntax, but wound up filing two bug reports in Bugzilla, one for a typo in the parser and one for a segfault, which makes it clear nobody as yet has been putting MOnetDB through its paces with things like a moving average. ________________________________ From: users-list [users-list-bounces+ocschwar=mit.edu@monetdb.org] on behalf of Anderson, David B [david.b.anderson@citi.com] Sent: Wednesday, October 25, 2017 9:03 AM To: 'Communication channel for MonetDB users' Subject: RE: Proper syntax for a windowing function Omri, Were you ever able to get this to work? From: users-list [mailto:users-list-bounces+david.b.anderson=citi.com@monetdb.org] On Behalf Of Omri Schwarz Sent: Friday, October 20, 2017 5:45 PM To: Communication channel for MonetDB users Subject: RE: Proper syntax for a windowing function Thank you. I've been trying all sorts of queries, but notice the avg function becoming unavailable the moment I try a windowing operation? ________________________________ From: users-list [users-list-bounces+ocschwar=mit.edu@monetdb.org] on behalf of Corbit, Dann [Dann.Corbit@softwareag.com] Sent: Friday, October 20, 2017 5:07 PM To: Communication channel for MonetDB users Subject: RE: Proper syntax for a windowing function https://www.monetdb.org/Documentation/Manuals/SQLreference/WindowFunctionshttps://urldefense.proofpoint.com/v2/url?u=https-3A__www.monetdb.org_Documentation_Manuals_SQLreference_WindowFunctions&d=DwMFAw&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=8_GYjk1edsyLJlNaxMxYxBJsviF3JXYvwDK42uy5KWU&m=EJH0dRe60FXa2hVIFF-UyYpyZU-LmfuNY1rLr_zmkv0&s=zgU_fNcSZzDJcaICO97cRmawm2k0WfdqqsLeT8JqqTo&e= From: users-list [mailto:users-list-bounces+dann.corbit=softwareag.com@monetdb.org] On Behalf Of Omri Schwarz Sent: Friday, October 20, 2017 1:46 PM To: users-list@monetdb.orgmailto:users-list@monetdb.org Subject: Proper syntax for a windowing function Hi, all. I'm trying to figure out the proper syntax for queries such as a moving average over a window in MonetDB. THis works: sql>select avg(energy) from TABLENAME; +--------------------------+ | L3 | +==========================+ | 658.5049567388747 | +--------------------------+ This fails: select avg(energy) OVER () from TABLENAME; SELECT: function 'avg' not found (Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP1) Database: MonetDB v11.27.5 (Jul2017-SP1), 'mapi:monetdb://SERVER2:50000/scada' ) How do I write such a query? Many thanks.
On Wed, Oct 25, 2017 at 02:50:55PM +0000, Omri Schwarz wrote:
I figured out the proper syntax, but wound up filing two bug reports in Bugzilla, one for a typo in the parser and one for a segfault, which makes it clear nobody as yet has been putting MOnetDB through its paces with things like a moving average. Indeed the windowing functions and moving average/aggregation implementation are missing.
niels
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ From: users-list [users-list-bounces+ocschwar=mit.edu@monetdb.org] on behalf of Anderson, David B [david.b.anderson@citi.com] Sent: Wednesday, October 25, 2017 9:03 AM To: 'Communication channel for MonetDB users' Subject: RE: Proper syntax for a windowing function
Omri,
Were you ever able to get this to work?
From: users-list [mailto:users-list-bounces+david.b.anderson= citi.com@monetdb.org] On Behalf Of Omri Schwarz Sent: Friday, October 20, 2017 5:45 PM To: Communication channel for MonetDB users Subject: RE: Proper syntax for a windowing function
Thank you. I've been trying all sorts of queries, but notice the avg function becoming unavailable the moment I try a windowing operation?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
From: users-list [users-list-bounces+ocschwar=mit.edu@monetdb.org] on behalf of Corbit, Dann [Dann.Corbit@softwareag.com] Sent: Friday, October 20, 2017 5:07 PM To: Communication channel for MonetDB users Subject: RE: Proper syntax for a windowing function
https://www.monetdb.org/Documentation/Manuals/SQLreference/ WindowFunctions
From: users-list [mailto:users-list-bounces+dann.corbit= softwareag.com@monetdb.org] On Behalf Of Omri Schwarz Sent: Friday, October 20, 2017 1:46 PM To: users-list@monetdb.org Subject: Proper syntax for a windowing function
Hi, all. I'm trying to figure out the proper syntax for queries such as a moving average over a window in MonetDB.
THis works:
sql>select avg(energy) from TABLENAME; +--------------------------+ | L3 | +==========================+ | 658.5049567388747 | +--------------------------+
This fails:
select avg(energy) OVER () from TABLENAME; SELECT: function 'avg' not found
(Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP1) Database: MonetDB v11.27.5 (Jul2017-SP1), 'mapi:monetdb://SERVER2:50000 /scada' )
How do I write such a query?
Many thanks.
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- Niels Nes, Manager ITF, 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: https://www.cwi.nl/people/716 e-mail: Niels.Nes@cwi.nl
participants (4)
-
Anderson, David B -
Corbit, Dann -
Niels Nes -
Omri Schwarz