IFNULL和date

Question

我有以下select语句

FIRST_VALUE(date) OVER (PARTITION BY client_ID ORDER BY date ASC),0) AS first_conversion_date,

如果没有可用的日期，我希望它返回一个字符串或一个整数。

例如，我尝试过这样做：

ifnull(FIRST_VALUE(date) OVER (PARTITION BY client_ID ORDER BY date ASC),0) AS first_conversion_date,

但得到以下错误：

No matching signature for function IFNULL for argument types: DATE, INT64. Supported signature: IFNULL(ANY, ANY) at [26:9] Learn More about BigQuery SQL Functions.

我该怎么办？

Answer 1

看起来您希望在结果中避免空值

显然，您不能在同一个输出列中混合使用DATE类型和STRING或INT64，因此需要将所有类型转换为STRING，如下例所示

IFNULL(CAST(FIRST_VALUE(DATE) OVER (PARTITION BY client_ID ORDER BY DATE ASC) AS STRING), 'whatever default value you have in mind') AS first_conversion_date

Answer 2

你想要coalesce()吗

COALESCE(FIRST_VALUE(date) OVER (PARTITION BY client_ID ORDER BY date ASC),0)