sql - 雪花-sql : case when min vs first_value windows function

Question

我希望有人能提供帮助。假设我有这张表

<表类="s-表"><头>sto_idpro_ido_dated_date<正文>381112021-04-01301112021-04-102021-04-10302222021-04-15302222021-04-182021-04-19303332021-04-20

我想看到的结果是

<表类="s-表"><头>first_sto_idpro_idmin_o_date第一个日期<正文>381112021-04-01302222021-04-152021-04-19303332021-04-20

因此，如果 sto_id 为 30 且 d_date 为 null，我希望查看此 pro_id 的下一个 d_date(如果可用)。如果 sto_id 不是 30，则 d_date 即使它为 null。

我试过这个:

select 
first_value(sto_id) OVER (PARTITION BY pro_id ORDER BY o_date ASC) as first_sto_id,
pro_id,
min(o_date) OVER (PARTITION BY pro_id ORDER BY o_date ASC) as min_o_date,
case when sto_id='30' then min(d_date) OVER (PARTITION BY pro_id ORDER BY o_date ASC)
else first_value(d_date) OVER (PARTITION BY pro_id ORDER BY o_date ASC) end as first_d_date
from tab1

但我没有得到我需要的结果。这里还有fiddle

有什么建议吗？非常感谢。

Answer 1

使用数组:

-- PostgreSQL
SELECT
 (ARRAY_AGG(sto_id ORDER BY o_date))[1] AS first_sto_id
,pro_id
,MIN(o_date) AS min_o_date
,CASE WHEN (ARRAY_AGG(sto_id ORDER BY o_date))[1] != '30'
           THEN (ARRAY_AGG(d_date ORDER BY o_date))[1]
      ELSE (ARRAY_REMOVE(ARRAY_AGG(d_date ORDER BY o_date),NULL))[1]
      END
FROM tab1
GROUP BY pro_id
ORDER BY pro_id;

db<>fiddle demo

雪花版:

SELECT
 (ARRAY_AGG(sto_id) WITHIN GROUP(ORDER BY o_date))[0]::int AS first_sto_id
,pro_id
,MIN(o_date) AS min_o_date
,CASE WHEN (ARRAY_AGG(sto_id) WITHIN GROUP(ORDER BY o_date))[0] != '30'
           THEN (ARRAY_AGG(d_date) WITHIN GROUP (ORDER BY o_date))[0]::date
      ELSE (ARRAY_COMPACT(ARRAY_AGG(d_date) WITHIN GROUP(ORDER BY o_date)))[0]::date
      END
FROM tab1
GROUP BY pro_id
ORDER BY pro_id;

相关:Equivalent for Keep in Snowflake