时间戳

首页 > 解决方案 > BQ：从多列中选择最新日期

sql - BQ：从多列中选择最新日期

问题描述

大家好。我之前写了一个与此相关的问题，但现在我遇到了另一个问题。

我必须计算 install_time 和contributor_time 列之间的时间戳差异。但是，我有三个contributor_time 列，我需要先从这些列中选择最新时间，然后从安装时间中减去它。

样本数据

用户 安装时间 贡献者时间_1 贡献者时间_2 贡献者时间_3
1 8:00 7:45 7:50 7:55
2 10:00 9:15 9:45 9:30
3 11:00 10:30 无效的 无效的

例如，在上表中，我需要选择contributor_time_3 并从用户1 的install_time 中减去它。对于用户2，我会这样做，但使用contributor_time_2。

样本结果

用户 安装时间 time_diff_min
1 8:00 5
2 10:00 15
3 11:00 30

我面临的问题是 1）contributor_time 列是字符串格式，2）其中一些具有“空”字符串值（这意味着我不能将其转换为时间戳。）

我创建了一个查询，但我遇到了一个错误，指出我不能从时间戳中减去一个字符串。所以我添加了 safe_cast，但是 time_diff_min 结果仅在我将所有三个contributor_time 列作为时间戳时才显示。例如，在上面的示例表中，只有前两行会拉取。

我到目前为止的查询如下：

SELECT
  users,
  install_time,
  TIMESTAMP_DIFF(install_time, greatest(contributor_time_1, contributor_time_2, contributor_time_3), MINUTE) as ctct_min 
FROM
  (SELECT 
  users,
  install_time,
  safe_cast(contributor_time_1 as timestamp) as contributor_time_1,
  safe_cast(contributor_time_2 as timestamp) as contributor_time_2,
  safe_cast(contributor_time_3 as timestamp) as contributor_time_3,
  FROM 
  (SELECT 
  users,
  install_time,
  case when contributor_time_1 = 'null' then '0' else contributor_time_1 end as contributor_time_1,
  ....
  FROM datasource

感谢您为我指明正确方向的任何帮助！先感谢您！

标签： sqlgoogle-bigquery

解决方案

您可以使用greatest()：

select t.*,
       time_diff(install_time, greatest(contributor_time_1, contributor_time_2, contributor_time_3), minute) as diff_min
from t;

注意：这假设值是 never NULL，根据您的样本数据，这似乎是合理的。

推荐阅读