The full script for this post can be downloaded here.

The following post was written primarily in Postgres 14 and tested in both Postgres and MySQL.

While the data generation and use of EXCEPT are not supported natively in MySQL, the principles of row comparisons are equivalent.

Setup

--create a table to hold some random integers
drop table if exists random_numbers;
create temp table random_numbers (a int, b int);

--create 100 rows of random integers
--between 0 and 50
insert into random_numbers (a, b)
select floor(random()*50), ceiling(random()*50)
from generate_series(1,10000);

Comparisons

PostgreSQL allows us to compare two sets of values using what is known as a row constructor comparison. 

A row can be constructed with parentheses, like this:

--with columns in a query
(a_column, b_column)

--or with literals
(10, 40)

We can create complex rows in this way and then compare them with each other.

For example:

(a_column, b_column) = (10, 40)

This can be incredibly powerful and is a useful shortcut for complex logic. 

However, the usual Spiderman caveat applies. 

Comparing two row constructors is not always intuitive. 

= operator

Consider this:

select 'This: "where (a,b) = (10,40)"' as message, count(*) 
from (
    select a, b
    from random_numbers 
    where (a,b) = (10,40)
) x
union all 
select 'Is the same as: "where a = 10 and b = 40"' as message, count(*) 
from (
    select a, b
    from random_numbers 
    where a = 10 and b = 40
) x
union all 
select 'Subtracting one from the other using "except" gives us' as message, count(*)
from (
    select a, b
    from random_numbers 
    where a = 10 and b = 40
    except
    select a, b
    from random_numbers 
    where (a,b) = (10,40)
) x;

Which for the random data created when I wrote this post, returns this:

This is straightforward enough. Put simply: is the left side equal to the right side?

Intuitively, we expect a comparison between each “column” from the left side with the corresponding “column” of the right side.  For the equals operator, it works how we expect.

<> operator

Consider this:

select 'This: "where (a,b) <> (10,40)"' as message, count(*) 
from (
    select a, b
    from random_numbers 
    where (a,b) <> (10,40)
) x
union all 
select 'Is not the same as: "where a <> 10 and b <> 40"' as message, count(*) 
from (
    select a, b
    from random_numbers 
    where a <> 10 and b <> 40
) x
union all 
select 'It''s the same as: "where a <> 10 or (a = 10 and b <> 40)"' as message, count(*) 
from (
    select a, b
    from random_numbers 
    where a <> 10 or (a = 10 and b <> 40)
) x
union all 
select 'Subtracting row 1 from row 3 using "except" gives us' as message, count(*)
from (
    select a, b
    from random_numbers 
    where a <> 10 or (a = 10 and b <> 40)
    except
    select a, b
    from random_numbers 
    where (a,b) <> (10,40)
) x;

Which gives us:

This may not appear as intuitive. But think about it this way: as long as one of the two conditions is met, then we consider the entire condition met.  

When comparing two rows with the <> operator, if even one column is different, then the rows are different

We can either have a <> 10 and b = anything, or we can have a = 10 and b <> 40. Since both of those conditions together are equivalent to b <> 40 and a = anything, it doesn’t need to be specified. 

Pretty straightforward when you think about if for a second.

Things are less intuitive when we move to other operators.

< operator

Consider:

select 'This: "where (a,b) < (10,40)"' as where_clause, count(*) 
from (
    select a, b
    from random_numbers 
    where (a,b) < (10,40)
) x
union all 
select 'Is not the same as: "where a < 10 and b < 40"' as where_clause, count(*) 
from (
    select a, b
    from random_numbers 
    where a < 10 and b < 40
) x
union all 
select 'It''s the same as: "where a < 10 or (a = 10 and b < 40)"' as where_clause, count(*) 
from (
    select a, b
    from random_numbers 
    where a < 10 or (a = 10 and b < 40)
) x
union all 
select 'Subtracting row 1 from row 3 using "except" gives us' as message, count(*)
from (
    select a, b
    from random_numbers 
    where a < 10 or (a = 10 and b < 40)
    except
    select a, b
    from random_numbers 
    where (a,b) < (10,40)
) x;

We get this:

According to the documentation:

For the <, <=, > and >= cases, the row elements are compared left-to-right, stopping as soon as an unequal or null pair of elements is found. If either of this pair of elements is null, the result of the row comparison is unknown (null); otherwise comparison of this pair of elements determines the result

So, we are comparing one row with another, column-wise from left to right.

1. Is a less than 10? If yes, include the row in the output. If not:
2. Is a equal to 10 and b less than 40? If yes, include the row in the output. 

The notable omission here is where b is less than 40 but a is greater than 10. Why? 

Well, read the quote above again. 

Specifically:

stopping as soon as an unequal or null pair of elements is found

The implication here is that each subsequent comparison assumes equality in the prior comparison(s). So, the “2nd” comparison of b with 40 assumes equality between a and 10. 

And if that wasn’t confusing enough.

<= operator

Consider:

select 'This: "where (a,b) <= (10,40)"' as where_clause, count(*) 
from (
    select a, b
    from random_numbers 
    where (a,b) <= (10,40)
) x
union all 
select 'Is not the same as: "where a <= 10 and b <= 40"' as where_clause, count(*) 
from (
    select a, b
    from random_numbers 
    where a <= 10 and b <= 40
) x
union all 
select 'It''s the same as: "where a < 10 or (a = 10 and b <= 40)"' as where_clause, count(*) 
from (
    select a, b
    from random_numbers 
    where a < 10 or (a = 10 and b <= 40)
) x
union all 
select 'Subtracting row 1 from row 3 using "except" gives us' as message, count(*)
from (
    select a, b
    from random_numbers 
    where a < 10 or (a = 10 and b <= 40)
    except
    select a, b
    from random_numbers 
    where (a,b) <= (10,40)
) x;

That code gives us:

• If a is less than 10, then include the row in the output, otherwise:
• If a is equal to 10 and b is less than or equal to 40, then include the row in the output

We might have expected the first condition to be:

• If a is less than or equal to 10

But again:

stopping as soon as an unequal or null pair of elements is found

So, if the first comparison was actually a<=10, those rows where a=10 would not cause the evaluation to stop, the implication being that it would be including rows where a < 10 and b = anything as well as a = 10 and b = anything, after which rows where a = 10 and b <= 40 would be evaluated. This latter evaluation is a subset of (a = 10 and b = anything), and so becomes entirely redundant. 

This behavior is similarly expressed with >= and >, but I won’t elaborate here, though they are included in the example script.

In summary

PostgreSQL allows us to use row constructors to compare sets of values.

The operators supported for such comparisons are =, <>, <, <=, > or >=

= and <> work as we might expect. 

The other operators are evaluated from left-to-right and stop evaluating when the first unequal non-null comparison is encountered. Subsequent comparisons assume equality of the prior comparisons.

What is Advanced SQL, anyway?

I think about this sometimes. Is it window functions? CTEs? I don’t know. Some of it is trickier than others. I think it’s relative to how much practice you’ve had and depends a great deal on the data in front of you.

Anyway, I saw this post on LinkedIn by Ankit Bansal. It struck me as an interesting problem.

I wanted to come up with a solution before watching the author’s video. It’s only 8 minutes, so probably worth watching.

I was happy to find that my solution was close but not identical.

The goal

This is the create script:

CREATE TABLE event_status (event_time varchar(10),[status] varchar(10));

INSERT INTO event_status (event_time, [status])
VALUES 
	('10:01','on'),('10:02','on'),('10:03','on'),
	('10:04','off'),('10:07','on'),('10:08','on'),
	('10:09','off'),('10:11','on'),('10:12','off');

A solution

With a problem like this, it’s useful to formulate a sentence or two that describes what we’re trying to do. So:

Identify groups of rows that begin with 1:n rows with status=’on’ and end with 1 row with status=’off’. Then, identify the minimum event_time, the maximum event_time and the count of ‘on’ rows within each group.

There are many ways to solve this and some of the detail can depend on which variant of SQL you’re using. This time, I used SQL Server. This is what I came up with:

WITH dat 
AS 
(
SELECT
	e.event_time,
	e.[status],
	LAG(CASE WHEN e.[status] = 'off' THEN 1 ELSE 0 END,1,0) OVER (ORDER BY e.event_time) AS new_group
FROM event_status e
),
grp 
AS 
(
SELECT event_time, [status], SUM(new_group) OVER (ORDER BY event_time) AS grp_id
FROM dat
)
SELECT MIN(event_time) AS login, MAX(event_time) AS logout, SUM(CASE WHEN [status] = 'on' THEN 1 ELSE 0 END) AS on_count
FROM grp
GROUP BY grp_id;

We’ll break it down in a moment, but first let’s get some definitions out there.

Important concepts

1. CTE – is a common table expression. It let’s you define a named temporary result that can be used by other queries. Read more here.
2. Window function – is a way to apply an aggregation or calculation over a partition of the query result. In this post, I’m using LAG to return a value from a row before the current row (here, one row before). I’m also using SUM to calculate a running total. If you’re not familiar with window functions, you can read more here. You can tell something is a window function by the presence of the OVER clause.

The detail

Here I’m using two CTEs to:

1. Add an indicator to show where a new group has started, and
2. Assign a group id to each group of rows defined in the problem statement above

The first CTE is:

SELECT
	e.event_time,
	e.[status],
	LAG(CASE WHEN e.[status] = 'off' THEN 1 ELSE 0 END,1,0) OVER (ORDER BY e.event_time) AS new_group
FROM event_status e

It produces this result:

You can see that whenever the previous row’s status is ‘off’, the new column shows 1. All other rows show 0. The assumption here is that there are never two ‘off’ rows adjacent to each other. This 1 indicates the beginning of a new group.

LAG is a very useful function. The first parameter is the column, or calculated column, from which we want to return a value. The second parameter is the number of rows prior to the current row we want to return the value from and the third parameter is a default value to use in case LAG cannot find a row before the current row. We determine what constitutes “before” by using the required ORDER BY statement in the OVER clause.

SELECT event_time, [status], SUM(new_group) OVER (ORDER BY event_time) AS grp_id
FROM dat

When we use SUM over an ordered field, it has the effect of creating a running total. So, the result is then:

Now that we have a group ID, calculating simple aggregates is easy:

SELECT MIN(event_time) AS login, MAX(event_time) AS logout, SUM(CASE WHEN [status] = 'on' THEN 1 ELSE 0 END) AS on_count
FROM grp
GROUP BY grp_id

Min and Max should be simple enough. Counting the number of ‘on’ rows in each group is just a matter of nesting a CASE statement inside the SUM function. Because each ‘on’ row has a value of 1 according to this CASE, then, SUM of that CASE is equivalent to COUNT WHERE status=’on’.

Note that we achieve the right segmentation of the rows by using GROUP BY grp_id. It’s not necessary to have the grouping field in the SELECT clause.

In summary

This post showed how to use LAG and SUM window functions to group rows.

The LAG window function allows us to return values from rows before the current row.

SUM OVER ORDER BY allows us to create a running total on a numeric field. By using this on a field where a 1 indicates the change of a group from one group to the next, we can increment the ID by one each time we encounter a new group.