Re: Row pattern recognition

On 6/28/23 14:17, Tatsuo Ishii wrote:
> Small question.
> 
>> This query (with all the defaults made explicit):
>>
>> SELECT s.company, s.tdate, s.price,
>>         FIRST_VALUE(s.tdate) OVER w,
>>         LAST_VALUE(s.tdate) OVER w,
>>         lowest OVER w
>> FROM stock AS s
>> WINDOW w AS (
>>    PARTITION BY s.company
>>    ORDER BY s.tdate
>>    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
>>    EXCLUDE NO OTHERS
>>    MEASURES
>>      LAST(DOWN) AS lowest
>>    AFTER MATCH SKIP PAST LAST ROW
>>    INITIAL PATTERN (START DOWN+ UP+)
>>    DEFINE
>>      START AS TRUE,
>>      UP AS price > PREV(price),
>>      DOWN AS price < PREV(price)
>> );
> 
>>      LAST(DOWN) AS lowest
> 
> should be "LAST(DOWN.price) AS lowest"?

Yes, it should be.  And the tdate='07-08-2023' row in the first 
resultset should have '07-08-2023' and '07-10-2023' as its 4th and 5th 
columns.

Since my brain is doing the processing instead of postgres, I made some 
human errors. :-)
-- 
Vik Fearing