sql - LEAST/GREATEST 列的源标签

Question

我创建了一个显示最早时间戳的列，现在我想创建另一个显示该时间戳来源的列。这就是我正在使用的，但当原始数据中的时间戳值之一为 NULL 时，它不起作用。

SELECT LEAST (timestamp_1, timestamp_2, timestamp_3)
       ,CASE WHEN timestamp_1 is null AND timestamp_2 is null AND timestamp_3 is null THEN NULL
        ELSE CASE WHEN timestamp_1 <= timestamp_2 AND timestamp_1 <= timestamp_3 THEN 'timestamp_1'
                  WHEN timestamp_2 <= timestamp_3 THEN 'timestamp_2'
                  ELSE 'timestamp_3' END
       END timestamp_source

FROM (

    SELECT timestamp_1
           ,timestamp_2
           ,timestamp_3
           ,timestamp_4
    FROM time
)

我希望结果看起来像这样:

timestamp_1 | timestamp_2 | timestamp_3 | timestamp_4 | MIN_time    | MIN_source

8/5/18 7:00 | 8/5/18 5:00 | NULL        | 8/5/18 9:00 | 8/5/18 5:00 | timestamp_2
9/5/18 9:00 | 8/5/18 7:00 | 8/5/18 3:00 | NULL        | 8/5/18 3:00 | timestamp_3

是否有更直接的方法来实现结果？

Answer 1

这很痛苦。一种很好概括的方法是使用聚合。性能不如使用 CASE 并假定行具有唯一 id:

SELECT DISTINCT id, MIN(ts) OVER (PARTITION BY id) as min_ts,
       FIRST_VALUE(which) OVER (PARTITION BY id ORDER BY ts)
FROM ((SELECT id, 'timestamp_1' as which, timestamp_1 as ts
       FROM time
      ) UNION ALL
      (SELECT id, 'timestamp_2' as which, timestamp_2 as ts
       FROM time
      ) UNION ALL
      (SELECT id, 'timestamp_3' as which, timestamp_3 as ts
       FROM time
      ) UNION ALL
      (SELECT id, 'timestamp_4' as which, timestamp_4 as ts
       FROM time
      ) 
     ) t;

我不喜欢 select distinct 但 Redshift 不支持 first_value() 作为聚合函数。

另一种方法是使用一些 future 值来避免 NULL:

select nullif(least(timestamp_1, timestamp_2, timestamp_3, timestamp_4), '9999-01-01') as least_ts,
       (case when timestamp_1 = least(timestamp_1, timestamp_2, timestamp_3, timestamp_4) then 'timestamp_1'
             when timestamp_2 = least(timestamp_1, timestamp_2, timestamp_3, timestamp_4) then 'timestamp_2'
             when timestamp_3 = least(timestamp_1, timestamp_2, timestamp_3, timestamp_4) then 'timestamp_3'
             else 'timestamp_4'
        end) as which
from (select coalesce(timestamp_1, '9999-01-01') as timestamp_1,
             coalesce(timestamp_2, '9999-01-01') as timestamp_2,
             coalesce(timestamp_3, '9999-01-01') as timestamp_3,
             coalesce(timestamp_4, '9999-01-01') as timestamp_4
      from time
     ) t;

注意:我假设您使用的是 Redshift。 Postgres 有更简单的方法来做到这一点。