Message-ID: <d5e469b6-2486-7569-e4da-1528b3b41d8c@postgresfriends.org>
Date: Thu, 29 Jun 2023 00:30:43 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.0
Subject: Re: Row pattern recognition
Content-Language: en-US
To: Tatsuo Ishii <ishii@sraoss.co.jp>
Cc: pgsql-hackers@postgresql.org
References: <fdf57a8d-65d5-e8a5-361f-3095d6899099@postgresfriends.org>
 <20230626.100520.2022209340496571373.t-ishii@sranhm.sra.co.jp>
 <90bff6fa-01b7-cf60-f4f4-3412514a7cd2@postgresfriends.org>
 <20230628.211700.191321983330956034.t-ishii@sranhm.sra.co.jp>
From: Vik Fearing <vik@postgresfriends.org>
In-Reply-To: <20230628.211700.191321983330956034.t-ishii@sranhm.sra.co.jp>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/d5e469b6-2486-7569-e4da-1528b3b41d8c%40postgresfriends.org>
Precedence: bulk

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