Блог

Представьте, что у меня есть таблица с некоторым полем, одно из которых является устаревшим массивом. как показано ниже

 col1 col2 alldate                       Max_date
1    2    ["2021-02-12","2021-02-13"]   "2021-02-13"
2    3    ["2021-01-12","2021-02-13"]   "2021-02-13"
4    4    ["2021-01-12"]                "2021-01-12"
5    3    ["2021-01-11","2021-02-12"]   "2021-02-12"
6    7    ["2021-02-13"]                "2021-02-13"
 

Мне нужно написать запрос таким образом, чтобы выбрать только тот, у которого в массиве есть максимальная дата. И есть столбец, в котором также указана максимальная дата.

Как должно отображаться в инструкции select

 col1 col2 alldate                       Max_date
1    2    ["2021-02-12","2021-02-13"]   "2021-02-13"
2    3    ["2021-01-12","2021-02-13"]   "2021-02-13"
6    7    ["2021-02-13"]                "2021-02-13"
 

Таблица огромна, поэтому необходим оптимизированный запрос.
До сих пор я думал о

 select col1, col2, maxdate
       from t1 where array_contains((select max(max_date) from t1)::variant,date));
 

Но мне кажется, что запускать инструкцию select для каждого запроса-плохая идея.

Любые Предложения

Если вам нужна чистая скорость, использование бокового выравнивания на 10% быстрее, чем подход array_contains для более чем 500 000 000 записей на складе XS. Вы можете скопировать приведенный ниже код прямо в snowflake, чтобы проверить его самостоятельно.

Почему боковой плоский подход быстрее? Хорошо, если вы посмотрите на планы запросов, оптимизатор фильтрует на первом шаге (сразу отбраковывает записи), где, поскольку array_contains ожидает до 4-го шага, прежде чем сделать то же самое. Фильтр является классификатором 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))  ;
 

Тестовый подход Фелипе/Майка -> 51 сек

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

Тестовый подход Адриана -> 47 секунд

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

Я бы, скорее всего, использовал 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 довольно умен, поэтому ваш запрос может фактически создать тот же профиль запроса, что и этот оператор. Проверьте их обоих и посмотрите, как выглядит профиль, чтобы понять, действительно ли это имеет значение.

Чтобы развить ответ Майка, мы можем сделать все в квалификации, без необходимости в 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 ;