критерий соответствия первой строки, после первой строки, соответствующей другим критериям

Как найти первую строку, которая соответствует критериям и следует за первой строкой, чтобы соответствовать другим критериям? Я могу сделать это с помощью соединения достаточно легко, но хочу избежать соединения (и, таким образом, по-видимому, использовать оконные функции). Я использую Snowflake (но если вы знаете ответ на другом диалекте, я могу попробовать его перевести). Я не смог найти способ сделать это без соединения.

Для ясности предположим, что мои данные

create table t (col1 varchar, col2 varchar, col3 varchar, row_number int) as
      select 'a', 'd' ,'r', 1
union select 'a', 'c', 'r', 2
union select 'b', 'd', 'r', 3
union select 'b', 'c', 's', 4
union select 'a', 'd', 's', 5
union select 'a', 'd', 'r', 6

Среди строк с col3='r' первая с col2='c' - 2, а первая после этого с col1='a' - 6. Я хочу выбрать строку 6 на основе этих критериев.


snowflake-cloud-data-platform window-functions
person Tyro    schedule 05.11.2018    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Snowflake предлагает очень мощные пользовательские табличные функции JavaScript, который может быть легко использован здесь.

Вот код ...

Давайте сначала создадим данные

create or replace table t (col1 varchar, col2 varchar, col3 varchar, row_number int)
as select * from values
        ('a', 'd' ,'r', 1),
        ('a', 'c', 'r', 2),
        ('b', 'd', 'r', 3),
        ('b', 'c', 's', 4),
        ('a', 'd', 's', 5),
        ('a', 'd', 'r', 6);

Затем мы представляем табличную функцию, которая потребляет строки, содержащие col1 и col2, и для каждой строки возвращает столбец MATCH, содержащий true/false, в зависимости от того, соответствует ли он вашему предикату.

CREATE OR REPLACE FUNCTION myfunc (
        col1 varchar,
        col2 varchar)
RETURNS TABLE (MATCH boolean)
LANGUAGE JAVASCRIPT
AS $$
{
  seen: false,
  produced: false,
  processRow: function (row, rowWriter, context) {
        let match = false;
        if (!this.seen && row.COL2 == "c") {       
          this.seen = true;       
        } else if (this.seen && !this.produced && row.COL1 == "a") {          
          this.produced = true;   
          match = true;
        }
        rowWriter.writeRow({MATCH: match});
   },
   initialize: function (argumentInfo, context) {
     this.seen = this.produced = false;
   }
}   
$$;

Затем мы используем его, разделяя данные по col3 и убеждаясь, что строки потребляются по row_number:

select * from t,
        table(myfunc(col1, col2) over (partition by col3 order by row_number));

------+------+------+------------+-------+
 COL1 | COL2 | COL3 | ROW_NUMBER | MATCH |
------+------+------+------------+-------+
 b    | c    | s    | 4          | FALSE |
 a    | d    | s    | 5          | TRUE  |
 a    | d    | r    | 1          | FALSE |
 a    | c    | r    | 2          | FALSE |
 b    | d    | r    | 3          | FALSE |
 a    | d    | r    | 6          | TRUE  |
------+------+------+------------+-------+

Если вам нужно, вы можете просто отфильтровать MATCH, и все готово.

Естественно, в такой функции можно выразить сколь угодно сложную логику.

person Marcin Zukowski    schedule 06.11.2018
comment
Большое спасибо! Я бы +1, если бы мог, но у меня недостаточно репутации. - person Tyro; 07.11.2018
comment
Мы используем UDF таким образом для таких проблем с отслеживанием состояния ... так что я дам вам это +1 - person Simeon Pilgrim; 12.11.2018
comment
@SimeonPilgrim Хороший момент о UDF с отслеживанием состояния. Я не рекомендовал их, так как здесь у нас есть требование упорядочивания во входных данных, а также не гарантируется, что UDF сохранят состояние между вызовами. - person Marcin Zukowski; 13.11.2018
comment
@MarcinZukowski тот факт, что вы решаете ограничения с множественным порядком, имеет состояние .. Где, как я согласен, это сохранение состояния (которое можно использовать, чтобы избежать инициализации больших объектов данных для каждой строки), что значительно улучшает производительность, что является Я думаю, это то, что вы имели в виду, не упомянув. - person Simeon Pilgrim; 13.11.2018