Google Big Query SQL - 获取最新的列值 [英] Google Big Query SQL - Get Most Recent Column Value
问题描述
我有一个Google Big Query Table，其中有一个电子邮件列。基本上每一行都显示了一个用户使用该电子邮件地址的状态。我想要做的是查询表以获得显示每个电子邮件地址最近一行的结果。我已经尝试了各种各样的 GROUP BY 的， JOIN 我会在MySQL中使用，但如果整行不匹配，我会一直收到重复的电子邮件。
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.
任何帮助都非常感谢！
示例数据
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
推荐答案
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
这篇关于Google Big Query SQL - 获取最新的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
