title: presto function to check if array contains subarray
body: <p>I have a Presto database with a column array which contains for example:</p>
  
  <ol>
  <li>id1,[1,2,3,4]</li>
  <li>id2,[3,4,5,6]</li>
  <li>id3,[3,4,7,8] </li>
  <li>id4,[5,4,3,6]</li>
  </ol>
  
  <p>I need a to search which rows contains the array [3,4,5] in the correct order.
  So for instance the result should return only id2 but not id4.</p>
  
  <p>I can use array_intersect in combination with cardinality to find id2,id4 but I don't know how can I verify that id2 or id4 are in the correct order.</p>
  
  <p>The only ugly solution I can think of is to convert the two arrays into a string and then do a string like operation.</p>
  
  <p>Any better ideas?</p>
  
  <p>Following the suggestion below and using AWS Athena:</p>
  
  <pre><code>WITH dataset AS (
      (values array[1,2,3,4], 
      array[3,4,5,6], 
      array[3,4,7,8], 
      array[5,4,3,6])
  )
  SELECT ngrams FROM dataset t(ngrams) where reduce(
      transform(array[3,4,5], a -&gt; array_position(ngrams, a)),
      0, 
      (s, n) -&gt; if( s &lt; 0, -1, if ( n &gt; s, n, -1)),
      s -&gt; s &gt;= 0) ;
  </code></pre>
  
  <p>The error I get is:</p>
  
  <blockquote>
    <p>SYNTAX_ERROR: line 7:44: Unexpected parameters (array(bigint),
    integer,
    com.facebook.presto.sql.analyzer.TypeSignatureProvider@1d8b3792,
    com.facebook.presto.sql.analyzer.TypeSignatureProvider@563900c2) for
    function reduce. Expected: reduce(array(T), S, function(S,T,S),
    function(S,R)) T, S, R</p>
  </blockquote>