Jump to content
  • How to do a binning query in LiveQL

    Question: I have a table with 2 columns (starttime, endtime). Is there a way to aggregate the number of rows for the difference between the endtime and starttime? For example, for how many rows is the difference is < 1 hour, how many between 1 hour and 2 hours, and how many > 2 hours. I would like to see the results on a single LiveView Web card.

    Answer: As of Live Datamart 2.2.2, you can use a group by expression to bin by things like Hour. As a simple example:

     select count() as Count, HourBin from MyTable group by get_hour(transactionTime) as HourBin

    This query will create a result set that one row per hour, where all rows are counted.

    You can use WHEN clause to restrict what rows you look at.

    Question: How can I bin in 3 groups? < 1 hour, <1 and < 2 and < 2?

    Answer: So, as a first cut, and assuming endtime and starttime are timestamp data types:

    select count() as MyCount, Buckets from MyTable
      group by (if ((endtime - starttime)/(60*60) > hours(2))
                    then "old" 
                    else if ((endtime - starttime)/(60*60) < hours(1))
                            then "new"
                            else "middling")
                as Buckets

    You should get three rows: "new", "middling", and "old".

    You can use a WHERE clause to further restrict the rows you look at.

    Question: Is there any other way to do this? Perhaps I don't have LDM 2.2.2 yet.

    Answer: Sure! Try something like:

    select countif(int(to_seconds(endtime-starttime)/3600)<1) as low,
             - countif(int(to_seconds(endtime-starttime)/3600)<1) as mid,
           countif(int(to_seconds(endtime-starttime)/3600)>2) as high
           from eventtimes

    This will give you the results in one row, sort of rotated 90 degrees.

    User Feedback

    Recommended Comments

    There are no comments to display.

  • Create New...