snowflake-cloud-data-platform - 在 Snowflake 中使用 `DISTINCT ON`

Question

我有一个下面的查询，我需要从联合结果中对 allowed_id 列执行 DISTINCT ON，这在 PostgreSQL 中是可能的。我读过 Snowflake 使用类似的 PostgreSQL，但 DISTINCT ON 不起作用。

select distinct on (allowed_id), *  from (
  select listagg(distinct id) as allowed_id,   count (people) as totalpeople ,max(score) as maxscore , min(score) as minscore, 'n' as type from tableA
         where userid = 123  

  union
  select listagg(distinct id) as allowed_id,  count (people) as totalpeople, max(elscore) as maxscore , min(elscore) as minscore, 'o' as type from tableB
         where userid = 123 
   union
   select listagg(distinct id) as allowed_id, null, null , null , 'j' as type from tableC
         where userid = 123 
    union 
    select listagg(distinct id) as allowed_id, null, null , null , 'a' as type from tableD
         where userid = 123 
   )

Answer 1

Snowflake 不支持“DISTINCT ON”，但您可以使用 QUALIFY 和 ROW_NUMBER 来产生相同的结果:

SELECT * from (
select * from values (123,11,12,'a' ) as tableA (allowed_id, col2, col3, table_name)
union all
select * from values (123,21,22,'b' ) as tableA (allowed_id, col2, col3, table_name)
union all
select * from values (123,31,32,'c' ) as tableA (allowed_id, col2, col3, table_name)
union all
select * from values (123,41,42,'d' ) as tableA (allowed_id, col2, col3, table_name)
) 
where allowed_id = 123
QUALIFY ROW_NUMBER() OVER (PARTITION BY allowed_id ORDER BY allowed_id) = 1 ;

请检查:

https://docs.snowflake.net/manuals/sql-reference/constructs/qualify.html

https://docs.snowflake.net/manuals/sql-reference/functions/row_number.html