Snowflake中match_recognize如何将匹配价格收集为单个数组列

在Snowflake中使用MATCH_RECOGNIZE编写SQL时，希望在ONE ROW PER MATCH的输出模式下，将匹配模式对应的所有价格收集为一个数组并返回至单个列中，当前代码中measures子句的price as all_price无法实现该需求，其余部分运行正常。

无需切换到ALL ROWS PER MATCH模式，只需在measures子句中使用Snowflake的数组聚合函数**COLLECT_LIST()**即可。该函数会遍历整个匹配序列，将所有符合条件的price值聚合为一个数组，完美适配ONE ROW PER MATCH的输出要求。

原代码中直接引用price只会返回匹配序列中某一行（默认是最后一行）的价格值，而COLLECT_LIST(price)能完整收集匹配范围内的所有价格。

create or replace temporary table stock_price_history (company text, price_date date, price int);
insert into stock_price_history values
    ('ABCD', '2020-10-01', 50),
    ('ABCD', '2020-10-02', 50),
    ('ABCD', '2020-10-03', 51),
    ('ABCD', '2020-10-04', 51),
    ('ABCD', '2020-10-05', 51),
    ('ABCD', '2020-10-06', 52),
    ('ABCD', '2020-10-07', 71),
    ('ABCD', '2020-10-08', 80),
    ('ABCD', '2020-10-09', 90),
    ('ABCD', '2020-10-10', 63),
    ('XYZ' , '2020-10-01', 24),
    ('XYZ' , '2020-10-02', 24),
    ('XYZ' , '2020-10-03', 37),
    ('XYZ' , '2020-10-04', 63),
    ('XYZ' , '2020-10-05', 65),
    ('XYZ' , '2020-10-06', 66),
    ('XYZ' , '2020-10-07', 50),
    ('XYZ' , '2020-10-08', 54),
    ('XYZ' , '2020-10-09', 30),
    ('XYZ' , '2020-10-10', 32);
    
select * from stock_price_history
  match_recognize(
    partition by company
    order by price_date
    measures
      match_number() as match_number,
      COLLECT_LIST(price) as all_prices, -- 修改此处，用COLLECT_LIST收集所有价格为数组
      first(price_date) as start_date,
      last(price_date) as end_date,
      count(*) as rows_in_sequence,
      count(row_with_price_stationary.*) as num_stationary,
      count(row_with_price_increase.*) as num_increases
    one row per match
    after match skip to last row_with_price_increase
    pattern(row_before_increase row_with_price_increase{1} row_with_price_stationary* row_with_price_increase{1})
    define
      row_with_price_increase as price > lag(price),
      row_with_price_stationary as price = lag(price)
  )
order by company, match_number;

内容的提问来源于stack exchange，提问作者MoneyBall