"OVER" function not there in SQL Dataflows - Running totals calculations

user03161

I am not able to perform "window" functions (specifically "over" function ) in a SQL Dataflow that I have created. Also - there were no dropdowns that suggest "OVER" exists as a function within Domo for SQL dataflows.

It is related to "Partition" and is used in Running Totals. Partition is there, but there is no "OVER" function available. I keep getting a syntax error in my dataflow because of this - I have tested out SQLFiddle and in other platforms where it works properly.

 

Very important function for calculating running totals as described here

SQL Fiddle - http://sqlfiddle.com/#!15/c9e0d/5

This is urgent and needs to be fixed asap

PodiumMason

Hi there!

 

MySQL does not support OVER() along with other window functions. 

 

Redshift however, does support these. If you don't currently have access to Redshift dataflows, please contact Domo Support to have those turned on inside your Domo instance.

 

This link may be helpful in emulating some windowed functionalities in mySQL: https://explainextended.com/2009/03/10/analytic-functions-first_value-last_value-lead-lag/

 

Hope this is helpful.  

 

AS

Domo has a couple of SQL platforms.  Are you using mySQL?  

 

We are on the other one, the RedShift platform, and use window functions pretty frequently.  For example, one of our revenue to goal calculations looks like:

SELECT

...

SUM(r.amount_usd) OVER (PARTITION BY r.type, r.salesrep_id, r.driver_category, DATE_TRUNC('yr',r.trx_month)) AS amount_yr_usd

FROM

...

 

You can have Domo turn on Redshift in your instance if you like.  I think you just have to contact your account or success manager.

 

Let me know if you have other questions.

DanHoughton

Announced at domopalooza ‘18 is the support for window functions in beast mode. Sum(sum(x)) over () etc