How come MySQL does not support window function, specifically lag 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
-
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 Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
+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:=@running_total + amount as running_total FROM table t, (SELECT @running_total:=0) r ORDER BY t.day;
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Is there any word that DOMO has updated their mysql to 8.0? I'd like to use it in an adrenaline sql statement.
0 -
@mfed303 it probably won't happen. Domo are pushing more SQL functionality into MagicETL, which will be a much stronger push to making MySQL ETLs obsolete
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive