MySQL – How to Select First and Last Row Based on User ID

google-bigquerygreatest-n-per-groupMySQL

I want to select every first and last event of every user id, if no first event exists,then just the last event instead . tried using partition over , but i am getting first 2 events instead.

Input:

id  timestamp   event
10096   1545136190  songs
10096   1545136202  tv
10096   1545136239  book
10096   1545136244  eat
10096   1545136252  travel
10096   1545136271  tv
10096   1545136275  laptop
10096   1545313396  sleep
10105   1545464444  book
10105   1545464455  eat
10105   1545464457  travel
10105   1545464460  tv
10105   1545464605  book
10105   1546740388  sleep
10213   1545134176  tv
10213   1545134180  computer
10213   1545134221  play
10213   1545134238  book
10213   1545134267  eat
10213   1545146190  travel
10213   1545146212  play
10213   1545623861  sleep
1053    1545131279  sleep

Expected Output:

   id   timestamp   event
10096   1545136190  songs
10096   1545313396  sleep
10105   1545464444  book
10105   1546740388  sleep
10213   1545134176  tv
10213   1545623861  sleep
1053    1545131279  sleep

tried doing this but not getting exactly what i want:

SELECT * FROM (
SELECT 
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) as RowNum, 
    id, 
    timestamp,
    event
FROM
    `table`
) MyData
WHERE RowNum < 3

Best Answer

Here's one way:

SELECT id, timestamp, event 
FROM (
    SELECT id, timestamp, event  
         , ROW_NUMBER() OVER(PARTITION BY id ORDER BY timestamp asc) as r1
         , ROW_NUMBER() OVER(PARTITION BY id ORDER BY timestamp desc) as r2
    FROM table
) MyData
WHERE 1 IN (r1,r2)

The idea is to order each id by timestamp in both ascending and descending order, then pick the first one for each ordering.

Related Solutions

Oracle – How to Select the First Row of Each Group

This solution also uses keep, but val and kind can also be simply calculated for each group without a subquery:

select min(id) keep(dense_rank first order by kind) id
     , val
     , min(kind) kind
  from mytable
 group by val;

ID |  VAL | KIND
-: | ---: | ---:
 3 |    3 |    4
 2 | 1337 |    1

dbfiddle here

KEEP…FIRST and KEEP…LAST are an Oracle-specific feature of aggregates — you can read about then here in the Oracle docs, or on ORACLE_BASE:

The FIRST and LAST functions can be used to return the first or last value from an ordered sequence

Mysql – inner join on PK with extra criteria slow despite indices

The optimizer does not see that your conditions are correlated and picks the wrong access method.

Basically, it considers two options:

Scan the index on siteVisitId until the first match on site_visits and the first satisfied timestamp condition.
Scan the index on timestamp until the first match on site_visits.

Since timestamp is a part of the primary key and siteVisitId is not, the second plan would involve table lookups on product_views which is several times more slow than a pure index scan (note Using index in the plan).

The optimizer calculates the conditional probability of the timestamp condition being satisfied (given that a corresponding site_visit record exists) and compares it to the overhead of the table access.

Since your timestamp condition is quite wide (as seen on the index histograms), the optimizer prefers the first method.

However, since both siteVisitId and timestamp are incremental, they are correlated and the conditional probability of both matches is not a mere product of their independent probabilities.

In simple words, you have to filter through many low siteVisitId until you find the first matching timestamp, which is exactly what is happening to your query.

You should add ORDER BY timestamp to your query to make the timestamp index cheaper as it won't have to sort. It would also help to create an index on timestamp, siteVisitId (in this order) to avoid table lookups.

Best Answer

Related Solutions

Oracle – How to Select the First Row of Each Group

Mysql – inner join on PK with extra criteria slow despite indices

Related Question