英文:
How can I return one single row for each customer based on the latest date in BigQuery?
问题
我有一个包含以下结构数据的表格。
customer_id; status; date
1; 1; 01-01-2019
1; 3; 01-02-2019
2; 4; 01-02-2019
2; 3; 01-03-2019
我想要返回的是:
customer_id; status; date
1; 3; 01-02-2019
2; 3; 01-03-2019
到目前为止,我已经在选择子句中使用了max语句来处理日期。
select distinct customer_id,
status,
max(date) as date_max
from *table*
group by customer_id
这会返回错误:意外的关键字GROUP。
希望能帮助你!
祝好!
英文:
I have a table which contains data in the following structure.
customer_id; status; date
1; 1; 01-01-2019
1; 3; 01-02-2019
2; 4; 01-02-2019
2; 3; 01-03-2019
What I want to return is:
customer_id; status; date
1; 3; 01-02-2019
2; 3; 01-03-2019
So far I have worked with a max statement in the select clause for the date.
select distinct customer_id,
status,
max(date) as date_max
from *table*
group by customer_id
This returns the error: Unexpected keyword GROUP
Hopefully you can help!
Kind regards.
答案1
得分: 3
以下是翻译好的内容:
要筛选数据而不是汇总数据。以下是一种可以使用的方法:
select t.*
from t
where t.date = (select max(t2.date)
from t t2
where t2.customer_id = t.customer_id
);
在BigQuery中,您还可以使用以下方法:
select t.* except (seqnum)
from (select t.*, row_number() over (partition by customer_id order by date desc) as seqnum
from t
) t;
或者,如果您想要使用聚合:
select as value array_agg(t order by date desc limit 1)[offset(1)]
from t
group by customer_id;
英文:
You want to filter the data, not aggregate it. Here is one method you can use:
select t.*
from t
where t.date = (select max(t2.date)
from t t2
where t2.customer_id = t.customer_id
);
In BigQuery, you can also do:
select t.* except (seqnum)
from (select t.*, row_number() over (partition by customer_id order by date desc) as seqnum
from t
) t;
Or, if you want to use aggregation:
select as value array_agg(t order by date desc limit 1)[offset(1)]
from t
group by customer_id;
答案2
得分: 1
这个查询应该可以完成任务:
select t.customer_id, t.status, t.date
from YourTable t
inner join (select customer_id, max(date) as date
from YourTable
group by customer_id) a
on (t.customer_id= a.customer_id and t.date = a.date)
英文:
This query should do it:
select t.customer_id, t.status, t.date
from YourTable t
inner join (select customer_id, max(date) as date
from YourTable
group by customer_id) a
on (t.customer_id= a.customer_id and t.date = a.date)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论