google-bigquery - 使用 Bigquery 根据另一列 min(date) 选择列的值

Question

假设我有下表:

AccountID   Email              status_update       date (dd/mm/yyyy) 
123456      foo@gmail.com      state1              02/02/2016
123456      foo@gmail.com      state2              10/010/2018
456123      bar@gmail.com      state2              05/04/2017
789123      foobar@gmail.com   state2              22/04/2016
789123      foobar@gmail.com   state1              17/06/2018
456345      cool@gmail.com     state1              13/08/2017
456345      cool@gmail.com     state2              09/07/2015
456345      cool@gmail.com     state2              09/07/2014

这是我想要的输出:

UniqueID    Email              Most_recent_status_Update  CountUniqueID                    
123456      foo@gmail.com      state2                     2
456123      bar@gmail.com      state2                     1    
789123      foobar@gmail.com   state1                     2              
456345      cool@gmail.com     state1                     3

所以基本上我希望能够按电子邮件分组，执行unique(AccountID) 并根据 status_update 选择MIN(日期)

对我来说困难的部分是能够选择基于MIN(date) 的status_update。我希望能够做这样的事情:

当 date = min(date) 时选择 status_update

我正在考虑使用 CASE 公式来得到这样的东西:

选择 CASE (WHEN date = min(date) Then status_update else null END

我总是以各种错误告终。我无法构建整个查询。

谢谢

Answer 1

请参见下面的示例 - 适用于 BigQuery 标准 SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 123456 accountID, 'foo@gmail.com' email, 'state1' status_update, '02/02/2016' dt UNION ALL
  SELECT 123456, 'foo@gmail.com', 'state2', '10/10/2018' UNION ALL
  SELECT 456123, 'bar@gmail.com', 'state2', '05/04/2017' UNION ALL
  SELECT 789123, 'foobar@gmail.com', 'state2', '22/04/2016' UNION ALL
  SELECT 789123, 'foobar@gmail.com', 'state1', '17/06/2018' UNION ALL
  SELECT 456345, 'cool@gmail.com', 'state1', '13/08/2017' UNION ALL
  SELECT 456345, 'cool@gmail.com', 'state2', '09/07/2015' UNION ALL
  SELECT 456345, 'cool@gmail.com', 'state2', '09/07/2014' 
)
SELECT 
  accountID, 
  email, 
  ARRAY_AGG(status_update ORDER BY PARSE_DATE('%d/%m/%Y', dt) DESC LIMIT 1)[OFFSET (0)] most_recent_status_update,
  COUNT(1) AS cnt
FROM `project.dataset.table`
GROUP BY accountID, email   

结果为

Row accountID   email               most_recent_status_update   cnt  
1   456123      bar@gmail.com       state2                      1    
2   123456      foo@gmail.com       state2                      2    
3   789123      foobar@gmail.com    state1                      2    
4   456345      cool@gmail.com      state1                      3     

对于您的实际用例，您应该使用如下所示的内容

#standardSQL
SELECT 
  accountID, 
  email, 
  ARRAY_AGG(status_update ORDER BY PARSE_DATE('%d/%m/%Y', dt) DESC LIMIT 1)[OFFSET (0)] most_recent_status_update,
  COUNT(1) AS cnt
FROM `project.dataset.table`
GROUP BY accountID, email    

注意:此示例假定特定的架构/数据类型(基于您的示例)-因此，如果您的实际数据类型不同-您需要稍微调整一下:o)