如果你想要纯粹的速度，使用横向扁平化比array_contains方法快10%，在XS仓库上超过500，000，000条记录。您可以直接将下面的代码复制粘贴到snowflake中进行测试。
为什么侧向平台接近更快？如果你看看查询计划，优化器在第一步过滤（立即剔除记录），而array_contains在做同样的事情之前一直等到第四步。过滤器是max（max_date）的限定符...
创建随机数据集：

create or replace table stack_overflow_68132958 as
SELECT
    seq4() col_1,
    UNIFORM (1, 500, random()) col_2,
    DATEADD(day, UNIFORM (-40, -0, random()), current_date()) random_date_1,
    DATEADD(day, UNIFORM (-40, -0, random()), current_date()) random_date_2,
    DATEADD(day, UNIFORM (-40, -0, random()), current_date()) random_date_3,
    ARRAY_CONSTRUCT(random_date_1, random_date_2, random_date_3) date_array,
    greatest(random_date_1, random_date_2, random_date_3) max_date,
    to_array(greatest(random_date_1, random_date_2, random_date_3)) max_date_array
FROM
TABLE (GENERATOR (ROWCOUNT => 500000000))  ;

字符串
测试Felipe/Mike进近-> 51秒

select 
 distinct 
 col_1
,col_2  
from 
  stack_overflow_68132958 
qualify 
 array_contains(max(max_date) over () :: variant, date_array);

型
测试Adrian方法-> 47秒

select 
  distinct    
  col_1
, col_2 
from 
   stack_overflow_68132958
 , lateral flatten(input => date_array) g 
qualify 
    max(max_date) over () = g.value;

型
x1c 0d1x的数据

的

我可能会使用CTE，例如：

WITH x AS (
SELECT max(max_date) as max_max_date
FROM t1
)
select col1, col2, maxdate
from t1 
cross join x
where array_contains(x.max_max_date::variant,alldate);

字符串
我还没有完全测试语法，数据类型可能会有所不同，但这里的概念是CTE将非常快，并返回一个具有单个值的单个记录。MAX（）函数利用Snowflake中的元数据，因此它甚至不使用仓库来获取元数据。
也就是说，Snowflake分析器非常智能，因此您的查询实际上可能会创建与此语句完全相同的查询配置文件。测试他们两个，看看什么样的配置文件看起来像，看看它是否真的有所作为。

为了建立在Mike的答案上，我们可以在QUALIFY中执行所有操作，而不需要CTE：

with t1 as (
select 'a' col1, 'b' col2, '2020-01-01'::date maxdate, array_construct('2020-01-01'::date, '2018-01-01', '2017-01-01') alldate
)

select col1, col2, alldate, maxdate
from t1 
qualify array_contains((max(maxdate) over())::variant, alldate)
;

字符串
请注意，您应该小心使用类型。这两个都是真的：

select array_contains('2020-01-01'::date::string::variant, array_construct('2020-01-01', '2019-01-01'));
select array_contains('2020-01-01'::date::variant, array_construct('2020-01-01'::date, '2019-01-01'));

型
但这是假的：

select array_contains('2020-01-01'::date::variant, array_construct('2020-01-01', '2019-01-01'));

型

你有一些很棒的答案，我只看到，在我写了我的。如果你的数据类型，匹配，你应该是好的去，复制粘贴直接成 snowflake .这个应该能用

create or replace schema abc;
use schema abc;

create or replace table myarraytable(col1 number, col2 number, alldates variant, max_date timestamp_ltz);

insert into myarraytable
select 1,2,array_construct('2021-02-12'::timestamp_ltz,'2021-02-13'::timestamp_ltz), '2021-02-13'
union
select 2,3,array_construct('2021-01-12'::timestamp_ltz,'2021-02-13'::timestamp_ltz),'2021-02-13'
union
select 4,4,array_construct('2021-01-12'::timestamp_ltz)              ,  '2021-01-12'
union
select 5,3,array_construct('2021-01-11'::timestamp_ltz,'2021-02-12'::timestamp_ltz) ,  '2021-02-12'
union
select 6,7,array_construct('2021-02-13'::timestamp_ltz)              ,  '2021-02-13';

select * from myarraytable
order by 1 ;


WITH cte_max AS (
SELECT max(max_date) as max_date
FROM myarraytable
)

select myarraytable.* 
from myarraytable, cte_max  
where array_contains(cte_max.max_date::variant, alldates)
order by 1 ;

字符串