Help with SQL query
My first post so apologies if I have dropped this in the wrong forum.
I have a slightly more complex dataflow than Magic ETL will handle and I am hoping I can manage this with SQL, however my SQL is a little rusty (read metal falling apart).
The requirement is possibly quite common in that I need to measure how many concurrent events occur at any one second of the day.
I have an 'Events' dataset that has an 'Event ID', 'Start Second' and 'End Second' column. The Start and End Second columns are integers and represent the number of second into the day, where 0 is 12:00:00AM (midnight) and 86399 is 11:59:59PM.
I have a second dataset, 'Daily Seconds' with one column 'Second Number' that contains 86400 rows with the integers 0 to 86399 in them.
The logic would be to iterate over all 86400 rows in the 'Daily Seconds' table and for each 'Second Number', count how many Event rows match the conditions: 'Start Second' <= 'Second Number' and 'End Second' >= 'Second Number'.
The count would be written to a new column next to the 'Second Number'.
I'm hoping that someone with a good handle on SQL could provide some guidance.
Thanks in advance.
Regards,
Rod
Best Answer
-
Domo did announce at Domopalooza a new Beta for an ON clause in Magic ETL join tiles you might find helpful and valuable. You can read up on it here:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
You can do this in SQL and Magic ETL without much trouble. I will try and lay out both options for you, but I can pretty much guarantee that the Magic ETL option will perform better for you.
SQL
Start by getting your counts in your events table like this and call it EventCounts
SELECT StartSecond, EndSecond, COUNT(EventID) ct FROM Events GROUP BY StartSecond, EndSecond
This helps by developing an initial count and reducing the number of rows that you will be incorporating in your next join.
SELECT SecondNumber, SUM(ct) Events FROM DailySeconds as s LEFT JOIN EventCounts as e on e.StartSecond < = s.SecondNumber and e.EndSecond > = s.SecondNumber GROUP BY SecondNumber
Using the left join will give you every row from the DailySeconds table. Counting specifically the ct column that was created in the first table in conjunction with the on criteria, you should get amounts for the events that meet that criteria and 0 for all the other ones.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
In Magic ETL, it will look something like this. I had to add some tiles to get my data where I could make something like yours. I will explain the key tiles that you would want to focus on configuring in your environment.
Join Tile
Do a LEFT JOIN and have your DailySeconds table on the left and your Events on the right. Join on SecondNumber and StartSecond
Formula Tile
In the formula tile create a field that has a formula like this:
CASE WHEN StartSecond < = DailySecond and EndSecond > = DailySecond THEN 1 ELSE 0 END
Group By Tile
In the group by tile, group by DailySecond and then choose the aggregation type of Sum and sum the field you made in the formula tile.
This should get you your totals.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Domo did announce at Domopalooza a new Beta for an ON clause in Magic ETL join tiles you might find helpful and valuable. You can read up on it here:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
MarkSnodgrass, Thanks so much for the effort in providing two options; and for such a prompt reply. I have taken a look at the ETL option and I think it is only reporting how many events start on the same second. This however only captures a small subset of cases as I need to measure the number of overlapping events on each and every second of the day.
I suspect the ON clause mentioned by GrantSmith will do the trick… now to sign up for Beta :-)
In any case, thanks both for your responses - much appreciated.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 298 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 680 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 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
- 33 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive