Help with SQL query

Rod
Rod Member
in SQL DataFlows

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

Tagged:

Best Answer

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    image.png

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach
    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: https://domo-support.domo.com/s/article/Current-Release-Notes?language=en_US#on_property

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Rod
    Rod Member

    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.

Categories