SQL Queries

In addition to simple filter expressions, Seq provides an SQL syntax for more advanced queries. SQL queries in Seq permit:

• Tabular queries - select event properties as columns
• Aggregate operators like count, mean, distinct, percentile and sum
• Time groupings to apply an aggregation over individual time slices

SQL queries enable charting through the view selector that will appear above and to the left of a SQL result set.

Basic Syntax

The syntax of an SQL query in Seq is:

select [<column> [as <label>],]
[from stream
  [where <predicate>]
  [group by [time(<d>)|<grouping>,]]
  [having <predicate>]
  [order by [time|<label>] [asc|desc]]
  [limit <n>]
  [for refresh]]

The from stream clause indicates the query will run over the event stream currently being viewed, with any active signals used to filter results.

👍

Expressions in a SQL query use SQL-style 'single quoted' strings and familiar SQL operators such as and, or, not and like.

Tabular Queries

The simplest SQL queries pluck properties out of events into columns.

select ContactId, Eligibility.Category
from stream
where Application = 'Patient Portal' and Eligibility is not null

This produces a rowset:

In all queries, when SQL syntax is used, Seq requires that a time range is specified using the date range picker in the Seq web interface. By default the last 24 hours will be included; use the calendar drop down to change the time range and refresh the query.

Tabular queries are great for exporting comma-separated text files from Seq. Use the drop-down beside the refresh button to download results in CSV format.

Limits

Seq automatically limits the size of rowsets that can be computed using SQL queries, and returns an error if a query would exceed the default limit.

To override the limit, specifying a limit clause will take a subset of results:

select ContactId, Eligibility.Category
from stream
where Application like '%Portal' and Eligibility is not null
limit 100

The limit clause is specified last, after any conditions or groupings.

👍

Instead of specifying a limit directly, it's usually possible to select a shorter time range using the date picker controls, or group the query at a coarser level.

When a tabular query is executed, options to display the data as a a bar or pie chart will appear to the bottom left of the filter bar. Selecting one of these will display the rowset in a chart of that kind.

Aggregate Operators

Most uses for SQL queries in Seq involve aggregate operators. These provide familiar computations like count, sum, min/max/mean/percentile and distinct.

select count(*)
from stream
where Application = 'Patient Portal'
group by Eligibility.Category

This produces a rowset:

View the full list of aggregate operators.

Time-Slicing Queries

Rather than compute aggregates across the entire time range, a time slice grouping can be used.

select count(*)
from stream
where Application = 'Patient Portal'
group by time(1h), Eligibility.Category

Time slicing expressions can group events by a number of days (d), hours (h), minutes (m), seconds (s) or milliseconds (ms).

To retrieve the most-recent results first, specify order by time desc in the query.

📘

Timeseries Result

The time() grouping may be specified last to render the result in timeseries format. Note that in this configuration, limit clauses will apply to the raw (un-filled) result set, so limit 5 may return more than five rows.

Selecting the timeseries icon to the bottom-left of the filter bar will display the results in a timeseries chart.

Cheat Sheet

Need a handy syntax reference to keep by your desk? We've put together a simple cheat sheet with SQL syntax and operator basics. Download the PDF here.