根据BigQuery的另一列最小(日期)选择列的值

Question

让我有下表：

AccountID   Email              status_update       date (dd/mm/yyyy) 
123456      [e-mail]      state1              02/02/2016
123456      [e-mail]      state2              10/010/2018
456123      [e-mail]      state2              05/04/2017
789123      [e-mail]   state2              22/04/2016
789123      [e-mail]   state1              17/06/2018
456345      [e-mail]     state1              13/08/2017
456345      [e-mail]     state2              09/07/2015
456345      [e-mail]     state2              09/07/2014

这是我想要的输出：

UniqueID    Email              Most_recent_status_Update  CountUniqueID                    
123456      [e-mail]      state2                     2
456123      [e-mail]      state2                     1    
789123      [e-mail]   state1                     2              
456345      [e-mail]     state1                     3

因此，我基本上希望能够group by Email，进行unique(AccountID)并选择基于MIN(date)

的status_update

我困难的部分是能够在MIN(date)中选择status_update。我希望能够做这样的事情：

Select status_update when date = min(date)

我正在考虑使用CASE公式以使其具有类似的东西：

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

我总是遇到各种错误。我无法构建整个查询。

谢谢

Answer 1

请参见下面的示例 - 有关BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 123456 accountID, '[e-mail]' email, 'state1' status_update, '02/02/2016' dt UNION ALL
  SELECT 123456, '[e-mail]', 'state2', '10/10/2018' UNION ALL
  SELECT 456123, '[e-mail]', 'state2', '05/04/2017' UNION ALL
  SELECT 789123, '[e-mail]', 'state2', '22/04/2016' UNION ALL
  SELECT 789123, '[e-mail]', 'state1', '17/06/2018' UNION ALL
  SELECT 456345, '[e-mail]', 'state1', '13/08/2017' UNION ALL
  SELECT 456345, '[e-mail]', 'state2', '09/07/2015' UNION ALL
  SELECT 456345, '[e-mail]', '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      [e-mail]       state2                      1    
2   123456      [e-mail]       state2                      2    
3   789123      [e-mail]    state1                      2    
4   456345      [e-mail]      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(