I have a Google Big Query Table that has an email column in it. Basically each rows shows a state the user with that email address existed in. What I want to do is query the table to get a result showing the most recent row per email address. I've tried all sorts of GROUP BY's, JOINing the table against itself and the usual fun stuff that I would use in MySQL, but I keep getting duplicate emails returned if the entire row isn't a match.
我有一个Google Big Query Table，里面有一个电子邮件列。基本上每行都显示存在该电子邮件地址的用户的状态。我想要查询表格以获得显示每个电子邮件地址的最新行的结果。我已经尝试过各种各样的GROUP BY，将表格加入到自身以及我将在MySQL中使用的通常有趣的东西，但如果整行不匹配，我会不断收到重复的电子邮件。
Any help is much appreciated!
任何帮助深表感谢！
Sample Data
样本数据
user_email | user_first_name | user_last_name | time | is_deleted
test@test.com | Joe | John | 123456790 | 1
test@test.com | Joe | John | 123456789 | 0
test2@test.com | Jill | John | 123456789 | 0
So if sampling that data I would want to return:
因此，如果我想要返回的数据采样：
user_email | user_first_name | user_last_name | time | is_deleted
test@test.com | Joe | John | 123456790 | 1
test2@test.com | Jill | John | 123456789 | 0
2 个解决方案
#1
9
SELECT user_email, user_first_name, user_last_name, time, is_deleted
FROM (
SELECT user_email, user_first_name, user_last_name, time, is_deleted
, RANK() OVER(PARTITION BY user_email ORDER BY time DESC) rank
FROM table
)
WHERE rank=1
#2
1
Solved!
解决了！
SELECT l.* FROM [mytable.list] l JOIN (
SELECT user_email, MAX(time) as time FROM [mytable.list] GROUP EACH BY user_email
) j ON j.user_email = l.user_email WHERE j.time = l.time;
#1
9
SELECT user_email, user_first_name, user_last_name, time, is_deleted
FROM (
SELECT user_email, user_first_name, user_last_name, time, is_deleted
, RANK() OVER(PARTITION BY user_email ORDER BY time DESC) rank
FROM table
)
WHERE rank=1
#2
1
Solved!
解决了！
SELECT l.* FROM [mytable.list] l JOIN (
SELECT user_email, MAX(time) as time FROM [mytable.list] GROUP EACH BY user_email
) j ON j.user_email = l.user_email WHERE j.time = l.time;