Re: Row pattern recognition

> On Tue, Oct 22, 2024 at 6:12 AM Vik Fearing <vik(at)postgresfriends(dot)org> wrote:
>
>>
>> On 22/10/2024 12:19, Tatsuo Ishii wrote:
>>
>> Acording to ISO/IEC 9075-2:2016 "4.21.2 Row pattern navigation operations",
>>
>> <row pattern navigation operation> evaluates a <value expression> VE
>> in a row NR, which may be different than current row CR.
>>
>> From this I think PREV(col + 1) should be interpreted as:
>>
>> 1. go to the previous row.
>> 2. evaluate "col + 1" at the current row (that was previous row).
>> 3. return the result.
>>
>> If my understanding is correct, prev(price + 1) has the same meaning
>> as prev(price) + 1.
>>
>>
>>
>> This is how I read the specification also.
>>
>>
>>
> That makes sense. Definitely much nicer to only have to write PREV once if
> the expression you are evaluating involves multiple columns. And is also
> consistent with window function "value" behavior.

Thanks to all who joined the discussion. I decided to support PREV and
NEXT in my RPR patches to allow to have multiple columns and other
expressions in their argument. e.g.

CREATE TEMP TABLE rpr1 (id INTEGER, i SERIAL, j INTEGER);
INSERT INTO rpr1(id, j) SELECT 1, g*2 FROM generate_series(1, 10) AS g;
SELECT id, i, j, count(*) OVER w
FROM rpr1
WINDOW w AS (
PARTITION BY id
ORDER BY i
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
AFTER MATCH SKIP PAST LAST ROW
INITIAL
PATTERN (START COND+)
DEFINE
START AS TRUE,
COND AS PREV(i + j + 1) < 10
);
id | i | j | count
----+----+----+-------
1 | 1 | 2 | 3
1 | 2 | 4 | 0
1 | 3 | 6 | 0
1 | 4 | 8 | 0
1 | 5 | 10 | 0
1 | 6 | 12 | 0
1 | 7 | 14 | 0
1 | 8 | 16 | 0
1 | 9 | 18 | 0
1 | 10 | 20 | 0
(10 rows)

Attached are the v23 patches. V23 also includes the fix for the problem
pointed out by Jacob Champion and test cases from him. Thank you, Jacob.
https://www.postgresql.org/message-id/CAOYmi%2Bns3kHjC83ap_BCfJCL0wfO5BJ_sEByOEpgNOrsPhqQTg%40mail.gmail.com

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp