Help with SQL query

Rod

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

GrantSmith

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

MarkSnodgrass

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.

MarkSnodgrass

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.

GrantSmith

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

Rod

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.