MySql Set Column on First Appearance of Value

Question

I have a select that returns something that looks like the following:

PeopleId	Timestamp	FavoriteNumber
1	2020-11-04 17:40:33	14
2	2020-12-06 21:19:41	59
3	2020-12-10 18:46:11	14
4	2020-12-15 00:01:53	7

Given this information, I want to, for every row, check if the person's favorite number is the first appearance of that number and if so return 0, if not, return That would make a table that looks like:

PeopleId	Timestamp	FavoriteNumber	HasOriginalNumber
1	2020-11-04 17:40:33	14	1
2	2020-12-06 21:19:41	59	1
3	2020-12-10 18:46:11	14	0
4	2020-12-15 00:01:53	7	1

Assume the query up to this point has been:

Select p.PeopleId, p.Timestamp, p.FavoriteNumber From people p

The second table could be achieved by doing:

Select p.PeopleId, p.Timestamp, p.FavoriteNumber, 
    ((Select Min(x.Timestamp) from people x where x.FavoriteNumber = p.FavoriteNumber) = p.TimeStamp) as 
    HasOriginalColor)
    From people p

However, I think that query will be very inefficient for large tables and seems like overkill. Is there an easier and faster way to do this?

Answer 1

This is a classic example of where window functions come in handy! In this case, we shall use the ROW_NUMBER() function.

To answer your question, I did the following (see fiddle here):

The table:

CREATE TABLE people 
(
  people_id INT, 
  t_timestamp TIMESTAMP, 
  favourite_number INT
);

Just a word of advice, you should NEVER call your fields (or your tables) using SQL keywords - it makes debugging more complex and error-prone and your apps less portable and it's bad practice generally.

Populate it:

INSERT INTO people VALUES 
(1, '2020-11-04 17:40:33',  14),
(2, '2020-12-06 21:19:41',  59),
(3, '2020-12-10 18:46:11',  14),
(4, '2020-12-15 00:01:53',  7);

Then run the query (I'll go through the logic step-by-step):

SELECT 
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn,
  people_id,
  t_timestamp,
  favourite_number
FROM people
ORDER BY people_id, t_timestamp, favourite_number;

Result:

rn  people_id   t_timestamp favourite_number
1   1   2020-11-04 17:40:33 14
1   2   2020-12-06 21:19:41 59
2   3   2020-12-10 18:46:11 14
1   4   2020-12-15 00:01:53 7

So, we can see by inspection that we want all of the records where rn = 1.

We now have to do a SUBSELECT (you can't put window functions in WHERE clauses):

SELECT * FROM
(
  SELECT 
    ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn,
    people_id,
    t_timestamp,
    favourite_number
  FROM people
--  ORDER BY people_id, t_timestamp, favourite_number
) AS tab
WHERE rn = 1
ORDER BY favourite_number, t_timestamp, favourite_number;

Result:

rn  people_id   t_timestamp          favourite_number
1   4           2020-12-15 00:01:53                 7
1   1           2020-11-04 17:40:33                14
1   2           2020-12-06 21:19:41                59

Now, these are the records we're interested in, but it's not quite the result you want.

We obtain this by using a CASE function, and since we're SELECTing all records, we can eliminate the SUBSELECT:

SELECT 
  people_id,
  t_timestamp,
  favourite_number,
    CASE
      WHEN 
        ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) = 1
        THEN 1
      ELSE 0
    END AS has_original_number,
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn
FROM people
ORDER BY people_id;

Result:

people_id          t_timestamp favourite_number has_original_number rn
        1  2020-11-04 17:40:33               14                   1  1
        2  2020-12-06 21:19:41               59                   1  1
        3  2020-12-10 18:46:11               14                   0  2
        4  2020-12-15 00:01:53                7                   1  1

Which, excepting rn is the result you want!

This is an example of a class of problems known as greatest-n-per-group - it's a dba.stackexchange tag - look at some of the questions in there and I would strongly urge you to get to know window functions - they will repay any effort you put into them many times over!

I modified your query in this way:

SELECT 
  p.people_id, p.t_timestamp, p.favourite_number,
  CASE
  WHEN
    (
      SELECT 
        MIN(x.t_timestamp) FROM people x WHERE x.favourite_number = p.favourite_number
    ) = p.t_timestamp
    THEN 1
    ELSE 0
  END AS has_original_number
FROM people p;

The result is the same!

Performance analysis: (MySQL)

I then looked at the performance of both queries using the new EXPLAIN ANALYZE <followed by query text> functionality available in MySQL version >= 8.18 (as explained here and here). For brevity, I'm only showing the plans - see the revised fiddle for the whole thing. I also make use of the MySQL profiling capability as explained here.

(caveat: I am not an expert on query plans - but I think I know a bit - any corrections, references, URLs appreciated):

I turned on SET PROFILING = 1;

Then, after running the two queries, I looked at the profiles (some of the query text is truncated):

SHOW PROFILES; 

=================================

Query_ID    Duration    Query

1           0.00078575  

SELECT 
  p.people_id, p.t_timestamp, p.favourite_number,
  CASE
  WHEN
    (
      SELECT 
        MIN(x.t_timestamp) FROM people x WHERE x.favourite_number = p.favourite_number
    ) = p.t_timestamp
    THEN 1
    ELSE 0
  END AS has_original_number
FROM people p
ORDER BY p.people_id

=======================================

2           0.00062000  

SELECT 
  p.people_id,
  p.t_timestamp,
  p.favourite_number,
    CASE
      WHEN 
        ROW_NUMBER() OVER (PARTITION BY p.favourite_number ORDER BY p.t_timestamp) = 1
      THEN 1
      ELSE 0
    END AS has_original_number,
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp)

_

I ran this several times (typical run shown) and the result consistently shows that the ROW_NUMBER() query is faster (by ~ 20% - frequently more) than the MIN() one - this seems logical (to me) since the MIN() query is SELECTing from the people table twice! The difference is larger if I run the MIN() query second. This effect is also seen if the queries are run in the opposite order.

However, when I ran the query plans, I got a surprise:

-> Sort: people.people_id  (actual time=0.064..0.065 rows=4 loops=1)
    -> Table scan on <temporary>  (actual time=0.000..0.001 rows=4 loops=1)
        -> Temporary table  (actual time=0.057..0.058 rows=4 loops=1)
            -> Window aggregate: row_number() OVER (PARTITION BY people.favourite_number ORDER BY people.t_timestamp )   (actual time=0.043..0.049 rows=4 loops=1)
                -> Table scan on <temporary>  (actual time=0.000..0.001 rows=4 loops=1)
                    -> Temporary table  (actual time=0.040..0.042 rows=4 loops=1)
                        -> Window aggregate: row_number() OVER (PARTITION BY people.favourite_number ORDER BY people.t_timestamp )   (actual time=0.031..0.036 rows=4 loops=1)
                            -> Sort: people.favourite_number, people.t_timestamp  (cost=0.65 rows=4) (actual time=0.027..0.027 rows=4 loops=1)
                                -> Table scan on people  (cost=0.65 rows=4) (actual time=0.013..0.016 rows=4 loops=1)

2nd Query (yours modified):

-> Table scan on p  (cost=0.65 rows=4) (actual time=0.013..0.015 rows=4 loops=1)
-> Select #2 (subquery in projection; dependent)
    -> Aggregate: min(x.t_timestamp)  (actual time=0.036..0.037 rows=1 loops=4)
        -> Filter: (x.favourite_number = p.favourite_number)  (cost=0.35 rows=1) (actual time=0.009..0.033 rows=2 loops=4)
            -> Table scan on x  (cost=0.35 rows=4) (actual time=0.008..0.031 rows=4 loops=4)

The performances are roughly comparable - a bit of variability from run to run.

However, because you're returning the whole table, the optimiser will ignore indexes and scan the whole table - Table Scan on people - note that for your query, it performs 2 Table Scans whereas for mine it only performs 1 for mine (temporary tables excluded - in memory?).

IMHO, this means that as the row-count of the people table grows, the query which performs 2 table scans will slow down as a function of O(n²) but the other query will only slow down as a function of O(n). So, long-term, I think you'll be better off with my query - but, I am open to correction on this!