按行最大值（n列）-优雅的方法 [英] rowwise maximum value (of n columns) - Elegant approach

问题描述

我有一个如下所示的表

我正在尝试下面的方法，但是它肯定不是优雅而有效的。

I was trying the below but it certainly isn't elegant and efficient. Is this the only way to do?

我正在寻找同时在 Bigquery和Postgresql

SELECT
CASE
    WHEN date_1 >= date_2 AND date_1 >= date_3 AND date_1 >= date_4 AND date_1 >= date_5 AND date_1 >= date_6 THEN date_1
    WHEN date_2 >= date_1 AND date_2 >= date_3 AND date_2 >= date_4 AND date_2 >= date_5 AND date_2 >= date_6 end AS max_date
from table_1

我希望我的输出如下所示

And I expect my output to be like as shown below

推荐答案

这应该很好并且可行在两种BigQuery / PGSQL环境中：

This should be good and work in both BigQuery/PGSQL environments:

select 
   subject_id,
   hadm_id,
   icustay_id,
   greatest(
      coalesce(date_1, '1900/01/01'), 
      coalesce(date_2, '1900/01/01'), 
      coalesce(date_3, '1900/01/01')
      coalesce(date_4, '1900/01/01'),
      coalesce(date_5, '1900/01/01'),
      coalesce(date_6, '1900/01/01')
   ) as max_date
from `dataset.table`

希望它会有所帮助。

这篇关于按行最大值（n列）-优雅的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！