Re: Row pattern recognition
| От | Tatsuo Ishii |
|---|---|
| Тема | Re: Row pattern recognition |
| Дата | |
| Msg-id | 20230912.174457.1842965393591953201.t-ishii@sranhm.sra.co.jp обсуждение исходный текст |
| Ответ на | Re: Row pattern recognition (Tatsuo Ishii <ishii@sraoss.co.jp>) |
| Ответы |
Re: Row pattern recognition
|
| Список | pgsql-hackers |
Regarding v6 patch: > SELECT company, tdate, price, > first_value(price) OVER w, > last_value(price) OVER w, > max(price) OVER w, > min(price) OVER w, > sum(price) OVER w, > avg(price) OVER w, > count(price) OVER w > FROM stock > WINDOW w AS ( > PARTITION BY company > ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING > AFTER MATCH SKIP PAST LAST ROW > INITIAL > PATTERN (START UP+ DOWN+) > DEFINE > START AS TRUE, > UP AS price > PREV(price), > DOWN AS price < PREV(price) > ); > company | tdate | price | first_value | last_value | max | min | sum | avg | count > ----------+------------+-------+-------------+------------+------+-----+------+-----------------------+------- > company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4 > company1 | 07-02-2023 | 200 | | | | | | | > company1 | 07-03-2023 | 150 | | | | | | | > company1 | 07-04-2023 | 140 | | | | | | | > company1 | 07-05-2023 | 150 | | | | | | | > company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4 > company1 | 07-07-2023 | 110 | | | | | | | > company1 | 07-08-2023 | 130 | | | | | | | > company1 | 07-09-2023 | 120 | | | | | | | > company1 | 07-10-2023 | 130 | | | | | | | > company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4 > company2 | 07-02-2023 | 2000 | | | | | | | > company2 | 07-03-2023 | 1500 | | | | | | | > company2 | 07-04-2023 | 1400 | | | | | | | > company2 | 07-05-2023 | 1500 | | | | | | | > company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4 > company2 | 07-07-2023 | 1100 | | | | | | | > company2 | 07-08-2023 | 1300 | | | | | | | > company2 | 07-09-2023 | 1200 | | | | | | | > company2 | 07-10-2023 | 1300 | | | | | | | > (20 rows) count column for unmatched rows should have been 0, rather than NULL. i.e. company | tdate | price | first_value | last_value | max | min | sum | avg | count ----------+------------+-------+-------------+------------+------+-----+------+-----------------------+------- company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4 company1 | 07-02-2023 | 200 | | | | | | | company1 | 07-03-2023 | 150 | | | | | | | company1 | 07-04-2023 | 140 | | | | | | | company1 | 07-05-2023 | 150 | | | | | | | 0 company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4 company1 | 07-07-2023 | 110 | | | | | | | company1 | 07-08-2023 | 130 | | | | | | | company1 | 07-09-2023 | 120 | | | | | | | company1 | 07-10-2023 | 130 | | | | | | | 0 company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4 company2 | 07-02-2023 | 2000 | | | | | | | company2 | 07-03-2023 | 1500 | | | | | | | company2 | 07-04-2023 | 1400 | | | | | | | company2 | 07-05-2023 | 1500 | | | | | | | 0 company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4 company2 | 07-07-2023 | 1100 | | | | | | | company2 | 07-08-2023 | 1300 | | | | | | | company2 | 07-09-2023 | 1200 | | | | | | | company2 | 07-10-2023 | 1300 | | | | | | | 0 (20 rows) Attached is the fix against v6 patch. I will include this in upcoming v7 patch. Best reagards, -- Tatsuo Ishii SRA OSS LLC English: http://www.sraoss.co.jp/index_en/ Japanese:http://www.sraoss.co.jp diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c index 32270d051a..2b78cb6722 100644 --- a/src/backend/executor/nodeWindowAgg.c +++ b/src/backend/executor/nodeWindowAgg.c @@ -968,12 +968,12 @@ eval_windowaggregates(WindowAggState *winstate) { /* * If the skip mode is SKIP TO PAST LAST ROW and we already know that - * current row is a skipped row or an unmatched row, we don't need to - * accumulate rows, just return NULL. + * current row is a skipped row, we don't need to accumulate rows, + * just return NULL. Note that for unamtched row, we need to do + * aggregation so that count(*) shows 0, rather than NULL. */ if (winstate->rpSkipTo == ST_PAST_LAST_ROW && - (get_reduced_frame_map(winstate, winstate->currentpos) == RF_SKIPPED || - get_reduced_frame_map(winstate, winstate->currentpos) == RF_UNMATCHED)) + get_reduced_frame_map(winstate, winstate->currentpos) == RF_SKIPPED) agg_result_isnull = true; } @@ -1080,8 +1080,8 @@ next_tuple: result, isnull); /* - * RPR is enabled and we just return NULL. because skip mode is SKIP - * TO PAST LAST ROW and current row is skipped row or unmatched row. + * RPR is defined and we just return NULL because skip mode is SKIP + * TO PAST LAST ROW and current row is skipped row. */ if (agg_result_isnull) { diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out index 63bed05f05..97bdc630d1 100644 --- a/src/test/regress/expected/rpr.out +++ b/src/test/regress/expected/rpr.out @@ -457,22 +457,22 @@ DOWN AS price < PREV(price) company1 | 07-02-2023 | 200 | | | | | | | company1 | 07-03-2023 | 150 | | | | | | | company1 | 07-04-2023 | 140 | | | | | | | - company1 | 07-05-2023 | 150 | | | | | | | + company1 | 07-05-2023 | 150 | | | | | | | 0 company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4 company1 | 07-07-2023 | 110 | | | | | | | company1 | 07-08-2023 | 130 | | | | | | | company1 | 07-09-2023 | 120 | | | | | | | - company1 | 07-10-2023 | 130 | | | | | | | + company1 | 07-10-2023 | 130 | | | | | | | 0 company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4 company2 | 07-02-2023 | 2000 | | | | | | | company2 | 07-03-2023 | 1500 | | | | | | | company2 | 07-04-2023 | 1400 | | | | | | | - company2 | 07-05-2023 | 1500 | | | | | | | + company2 | 07-05-2023 | 1500 | | | | | | | 0 company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4 company2 | 07-07-2023 | 1100 | | | | | | | company2 | 07-08-2023 | 1300 | | | | | | | company2 | 07-09-2023 | 1200 | | | | | | | - company2 | 07-10-2023 | 1300 | | | | | | | + company2 | 07-10-2023 | 1300 | | | | | | | 0 (20 rows) -- using AFTER MATCH SKIP TO NEXT ROW
В списке pgsql-hackers по дате отправления:
Предыдущее
От: "Hayato Kuroda (Fujitsu)"Дата:
Сообщение: RE: [PoC] pg_upgrade: allow to upgrade publisher node