SQL to get some groups and keep the order

问题: I have an example data as below. +---------+------------+--------+ | user id | sequence | Action | |---------|------------|--------| | 12345 | 1 | Run | |...

问题:

I have an example data as below.

+---------+------------+--------+
| user id |   sequence | Action |
|---------|------------|--------|
| 12345   |    1       | Run    |
| 12345   |    2       | Sit    |
| 12345   |    3       | Sit    |
| 12345   |    4       | Run    |
| 12345   |    5       | Run    |
| 12345   |    6       | Sit    |
+---------+------------+--------+

Now I'd like the result should be like this:

+---------+---------+
| user id |  Action |
|---------|---------|
| 12345   |  Run    |
| 12345   |  Sit    |
| 12345   |  Run    |
| 12345   |  Sit    |
+---------+---------+

The row with sequence #2 and #3 should be merged, #4 and #5 should be merged. I use 'group by Action' will get answer like the following table, but it isn't what I want:

+---------+---------+
| user id |  Action |
|---------|---------|
| 12345   |  Run    |
| 12345   |  Sit    |
+---------+---------+

How can I implement SQL(I use Google Bigquery)?

Thanks a million!


回答1:

Below for BigQuery Standard SQL

#standardSQL
SELECT * EXCEPT(dup) FROM (
  SELECT *, action = LAG(action, 1, '') OVER(PARTITION BY user_id ORDER BY sequence) AS dup
  FROM `project.dataset.table`
)
WHERE NOT dup

If to apply to sample data from your question - output is

Row user_id sequence    action   
1   12345   1           Run  
2   12345   2           Sit  
3   12345   4           Run  
4   12345   6           Sit  

回答2:

You can use window functions: the idea is to compare the action on each row to the "previous" action, and filter on the rows where the value changes:

select *
from (
    select t.*, lag(action) over(partition by user_id order by sequence) lag_action
    from mytable t
) t
where action <> lag_action or lag_action is null
  • 发表于 2020-06-27 19:13
  • 阅读 ( 96 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除