Google Big Query SQL - 获取最新的列值 [英] Google Big Query SQL - Get Most Recent Column Value
问题描述
我有一个 Google Big Query Table，其中有一个 email 列.基本上每一行都显示了具有该电子邮件地址的用户所在的状态.我想要做的是查询表以获取显示每个电子邮件地址的最新行的结果.我尝试了各种 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.
非常感谢任何帮助！
样本数据
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屋！
