On 6/26/23 03:05, Tatsuo Ishii wrote:
I don't understand this. RPR in a window specification limits the
window to the matched rows, so this looks like your rpr() function is
just the regular first_value() window function that we already have?
No, rpr() is different from first_value(). rpr() returns the argument
value at the first row in a frame only when matched rows found. On the
other hand first_value() returns the argument value at the first row
in a frame unconditionally.
company | tdate | price | rpr | first_value
----------+------------+-------+------+-------------
company1 | 2023-07-01 | 100 | | 100
company1 | 2023-07-02 | 200 | 200 | 200
company1 | 2023-07-03 | 150 | 150 | 150
company1 | 2023-07-04 | 140 | | 140
company1 | 2023-07-05 | 150 | 150 | 150
company1 | 2023-07-06 | 90 | | 90
company1 | 2023-07-07 | 110 | | 110
company1 | 2023-07-08 | 130 | | 130
company1 | 2023-07-09 | 120 | | 120
company1 | 2023-07-10 | 130 | | 130
For example, a frame starting with (tdate = 2023-07-02, price = 200)
consists of rows (price = 200, 150, 140, 150) satisfying the pattern,
thus rpr returns 200. Since in this example frame option "ROWS BETWEEN
CURRENT ROW AND UNBOUNDED FOLLOWING" is specified, next frame starts
with (tdate = 2023-07-03, price = 150). This frame satisfies the
pattern too (price = 150, 140, 150), and rpr retus 150... and so on.
Okay, I see the problem now, and why you need the rpr() function.
You are doing this as something that happens over a window frame, but it
is actually something that *reduces* the window frame. The pattern
matching needs to be done when the frame is calculated and not when any
particular function is applied over it.
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)
);
Should produce this result:
company | tdate | price | first_value | last_value | lowest
----------+------------+-------+-------------+------------+--------
company1 | 07-01-2023 | 100 | | |
company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023 | 140
company1 | 07-03-2023 | 150 | | |
company1 | 07-04-2023 | 140 | | |
company1 | 07-05-2023 | 150 | | |
company1 | 07-06-2023 | 90 | | |
company1 | 07-07-2023 | 110 | | |
company1 | 07-08-2023 | 130 | 07-05-2023 | 07-05-2023 | 120
company1 | 07-09-2023 | 120 | | |
company1 | 07-10-2023 | 130 | | |
(10 rows)
Or if we switch to AFTER MATCH SKIP TO NEXT ROW, then we get:
company | tdate | price | first_value | last_value | lowest
----------+------------+-------+-------------+------------+--------
company1 | 07-01-2023 | 100 | | |
company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023 | 140
company1 | 07-03-2023 | 150 | 07-03-2023 | 07-05-2023 | 140
company1 | 07-04-2023 | 140 | | |
company1 | 07-05-2023 | 150 | 07-05-2023 | 07-08-2023 | 90
company1 | 07-06-2023 | 90 | | |
company1 | 07-07-2023 | 110 | | |
company1 | 07-08-2023 | 130 | 07-08-2023 | 07-10-2023 | 120
company1 | 07-09-2023 | 120 | | |
company1 | 07-10-2023 | 130 | | |
(10 rows)
And then if we change INITIAL to SEEK:
company | tdate | price | first_value | last_value | lowest
----------+------------+-------+-------------+------------+--------
company1 | 07-01-2023 | 100 | 07-02-2023 | 07-05-2023 | 140
company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023 | 140
company1 | 07-03-2023 | 150 | 07-03-2023 | 07-05-2023 | 140
company1 | 07-04-2023 | 140 | 07-05-2023 | 07-08-2023 | 90
company1 | 07-05-2023 | 150 | 07-05-2023 | 07-08-2023 | 90
company1 | 07-06-2023 | 90 | 07-08-2023 | 07-10-2023 | 120
company1 | 07-07-2023 | 110 | 07-08-2023 | 07-10-2023 | 120
company1 | 07-08-2023 | 130 | 07-08-2023 | 07-10-2023 | 120
company1 | 07-09-2023 | 120 | | |
company1 | 07-10-2023 | 130 | | |
(10 rows)
Since the pattern recognition is part of the frame, the window
aggregates should Just Work.
o SUBSET is not supported
Is this because you haven't done it yet, or because you ran into
problems trying to do it?
Because it seems SUBSET is not useful without MEASURES support. Thus
my plan is, firstly implement MEASURES, then SUBSET. What do you
think?
SUBSET elements can be used in DEFINE clauses, but I do not think this
is important compared to other features.
Comments and suggestions are welcome.
I have not looked at the patch yet, but is the reason for doing R020
before R010 because you haven't done the MEASURES clause yet?
One of the reasons is, implementing MATCH_RECOGNIZE (R010) looked
harder for me because modifying main SELECT clause could be a hard
work. Another reason is, I had no idea how to implement PREV/NEXT in
other than in WINDOW clause. Other people might feel differently
though.
I think we could do this with a single tuplesort if we use backtracking
(which might be really slow for some patterns). I have not looked into
it in any detail.
We would need to be able to remove tuples from the end (even if only
logically), and be able to update tuples inside the store. Both of
those needs come from backtracking and possibly changing the classifier.
Without backtracking, I don't see how we could do it without have a
separate tuplestore for every current possible match.
In any case, I will be watching this with a close eye, and I am eager
to help in any way I can.
Thank you! I am looking forward to comments on my patch. Also any
idea how to implement MEASURES clause is welcome.
I looked at your v2 patches a little bit and the only comment that I
currently have on the code is you spelled PERMUTE as PREMUTE.
Everything else is hopefully explained above.
--
Vik Fearing
