How come MySQL does not support window function, specifically lag in dataflows?

user084060
user084060 ⚪️
in Dataflows

Hi,

 

I am currently building a dataflow and trying to get the best out of both sql dialect. I use Mysql to order my data (because redshift doesnt order properly) and then use another datflow in the redshift dialect to be able to use window function. What I am curious about is to know why Mysql does not support window function, specifically lag in dataflows? I have been googling and it seems that you are suppose to be able to use window functions with mysql: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

but why is it not possible in Domo?

 

Thank you

Comments

  • GrantSmith
    GrantSmith Indiana 🥷

    Hi @user084060

    This is because the MySQL backend in Domo is based on MySQL 5.6 which doesn't have window functions. They were later added with MySQL 8.0.

    I'd recommend you look into Magic ETL 2.0 which has a Rank & Window tile which you can use LAG functions and is typically much more performant than MySQL / Redshift if at all possible.



    **Was this post helpful? Click the heart icon**

    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    +1 on @GrantSmith, use Magic 2.0 it'll be much easier and faster over large datasets.

    But if you're hell bent on it, you can use variables.

    SELECT col, day
       @running_total:[email protected]_total + amount as running_total
FROM
table t, (SELECT @running_total:=0) r
ORDER BY t.day;


    https://popsql.com/learn-sql/mysql/how-to-calculate-cumulative-sum-running-total-in-mysql



    Jae Wilson
    Check out my Domo Training YouTube Channel

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories