主页 / dba / 问题 / 227736
Accepted
Nrj
Asked: 2019-01-22 23:54:17 +0800 CST

根据每个用户 id mysql 选择第一行和最后一行

  • 772

我想选择每个用户 ID 的每个第一个和最后一个事件，如果不存在第一个事件，则只选择最后一个事件。尝试使用 partition over ，但我得到的是前 2 个事件。

输入：

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

预期输出：

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

尝试这样做但没有得到我想要的：

SELECT * FROM (
SELECT 
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) as RowNum, 
    id, 
    timestamp,
    event
FROM
    `table`
) MyData
WHERE RowNum < 3
mysql greatest-n-per-group

2 个回答

  1. SELECT id, 
       MIN(`timestamp`) first_timestamp, 
       FIRST_VALUE(event) OVER (PARTITION BY id ORDER BY timestamp ASC) first_event,
       MAX(`timestamp`) last_timestamp, 
       LAST_VALUE(event) OVER (PARTITION BY id ORDER BY timestamp ASC) last_event
 -- or FIRST_VALUE(event) OVER (PARTITION BY id ORDER BY timestamp DESC) last_event
FROM `table`
GROUP BY id
    • 1
  2. Best Answer

    这是一种方法：

    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)

    这个想法是按时间戳以升序和降序对每个 id 进行排序，然后为每个排序选择第一个。

    • 0

相关问题