如何根据BigQuery中的最新日期为每个客户返回一行记录?

huangapple go评论98阅读模式
英文:

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)

huangapple
  • 本文由 发表于 2020年1月3日 19:26:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/59577784.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定