主页 / user-166988

Nrj's questions

Martin Hope
Nrj
Asked: 2019-01-22 23:54:17 +0800 CST
  • 0

我想选择每个用户 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
Martin Hope
Nrj
Asked: 2019-01-03 22:08:08 +0800 CST
  • 0

我目前正在研究 bigquery（标准 sql），其中有一个timestamp字段，我想将其转换为date and time2 个单独的列。我试过这样做select EXTRACT(DATE FROM timestamp) from table。

我也试过投射timestamp到date，但它给了我一个错误。任何建议将不胜感激

Input

timestamp             
1539167364779001       
1542242226310005
1544881556726002

Expected output

date         time
10/10/2018   3:59:24 PM 
11/15/2018   6:07:06 AM
12/15/2018   7:15:56 PM
mysql google-bigquery
Martin Hope
Nrj
Asked: 2018-12-20 22:17:49 +0800 CST
  • 0

以下是儿童日常活动的示例。我想根据用户 ID 选择最后一个“播放”活动之后的下一行。

id          timestamp               activity
567     1541300537817000            Play
567     1541300584959000            Games
567     1541300711884001            Play
567     1541300749548000            Study
567     1541300804865000            Movie
678     1542073550481000            Computer
678     1542073551422000            Play
678     1542073551478000            Play
678     1542073561885000            Play
678     1542073567663000            Sleep     
908     1543580126943000            Tv     
908     1543580212409000            Sleep     
908     1543580245227000            Play

Expected output

id          timestamp               activity
567     1541300749548000            Study
567     1541300804865000            Movie 
678     1542073567663000            Sleep

我试过这样做：

SELECT DISTINCT t1.*
FROM `table` t1, `table` t2
WHERE t1.id = t2.id
  AND t1.timestamp > t2.timestamp
  AND t2.activity = 'Play';

但它在第一次播放活动后给了我活动。

mysql greatest-n-per-group
Martin Hope
Nrj
Asked: 2018-12-13 21:15:46 +0800 CST
  • 0

以下是儿童活动表的示例。我只想查看每个用户 ID 的 1st Tv 活动之后的所有行。我试过分组，但它对我不起作用

桌子

ID          Timestamp               Activity
567     1541300537817000            Games
567     1541300584959000            Tv
567     1541300711884001            Play
567     1541300749548000            Tv
567     1541300804865000            Cartoon
678     1542073550481000            Computer
678     1542073551422000            Tv
678     1542073551478000            Study
678     1542073561885000            Tv
678     1542073567663000            Sleep     
908     1543580126943000            Tv     
908     1543580212409000            Sleep     
908     1543580245227000            Play

预期产出 

567  Play  
567  Tv  
567  Cartoon    
678  Study  
678  Tv  
678  Sleep   
908  Sleep       
908  Play
mysql greatest-n-per-group