agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedLAST_VALUE returns the entire partition
3+ messages / 2 participants
[nested] [flat]
* LAST_VALUE returns the entire partition
@ 2021-07-02 12:04 Дмитрий Иванов <firstdismay@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Дмитрий Иванов @ 2021-07-02 12:04 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Hi!
I cannot understand the behavior of the last_value function. Conditions
opposite to first_value conditions. Why such a result?
first_value(rg.spath) OVER (PARTITION BY rg.id_path ORDER BY rg.level DESC)
AS first_value <> last_value(rg.spath) OVER (PARTITION BY rg.id_path ORDER
BY rg.level) AS last_value?
**************************************************************
SELECT * FROM VERSION();
--
version
PostgreSQL 12.7, compiled by Visual C++ build 1914, 64-bit
**************************************************************
CREATE TABLE "public"."group" (
"id" BigInt NOT NULL,
"id_parent" BigInt NOT NULL,
"name" Character Varying( 100 ) NOT NULL,
PRIMARY KEY ( "id" ) );
**************************************************************
SELECT * FROM public.group;
--
id | id_parent | name
-----+-----------+---------------
386 | 385 | group_level_3
385 | 384 | group_level_2
384 | 383 | group_level_1
383 | 0 | group_root
(4 строки)
**************************************************************
CREATE OR REPLACE VIEW "public"."group_path_first" AS WITH RECURSIVE
rgroup(id_path, id, id_parent, level, path, spath, cycle) AS (
SELECT rg.id,
rg.id,
rg.id_parent,
0,
ARRAY[rg.id] AS "array",
concat(rg.name) AS concat,
false AS bool
FROM "group" rg
WHERE (rg.id = 386)
UNION ALL
SELECT rgroup_1.id_path,
rgc.id,
rgc.id_parent,
(rgroup_1.level + 1),
(ARRAY[rgc.id] || rgroup_1.path),
concat(rgc.name, $$\$$, rgroup_1.spath) AS concat,
(rgc.id = ANY (rgroup_1.path))
FROM ("group" rgc
JOIN rgroup rgroup_1 ON ((rgroup_1.id_parent = rgc.id)))
WHERE (NOT rgroup_1.cycle)
), grouppath(id_path, spath) AS (
SELECT DISTINCT rg.id_path,
first_value(rg.spath) OVER (PARTITION BY rg.id_path ORDER BY
rg.level DESC) AS first_value
FROM rgroup rg
)
SELECT grouppath.id_path,
grouppath.spath
FROM grouppath;
-- -------------------------------------------------------------
SELECT * FROM "public"."group_path_first";
--
id_path | spath
---------+------------------------------------------------------
386 | group_root\group_level_1\group_level_2\group_level_3
(1 строка)
**************************************************************
CREATE OR REPLACE VIEW "public"."group_path_last" AS WITH RECURSIVE
rgroup(id_path, id, id_parent, level, path, spath, cycle) AS (
SELECT rg.id,
rg.id,
rg.id_parent,
0,
ARRAY[rg.id] AS "array",
concat(rg.name) AS concat,
false AS bool
FROM public."group" rg
WHERE rg.id = 386
UNION ALL
SELECT rgroup_1.id_path,
rgc.id,
rgc.id_parent,
(rgroup_1.level + 1),
(ARRAY[rgc.id] || rgroup_1.path),
concat(rgc.name, $$\$$, rgroup_1.spath) AS concat,
(rgc.id = ANY (rgroup_1.path))
FROM (public."group" rgc
JOIN rgroup rgroup_1 ON ((rgroup_1.id_parent = rgc.id)))
WHERE (NOT rgroup_1.cycle)
),
grouppath(id_path, spath) AS (
SELECT DISTINCT rg.id_path,
last_value(rg.spath) OVER (PARTITION BY rg.id_path ORDER BY
rg.level) AS last_value
FROM rgroup rg
)
SELECT * FROM grouppath;
-- -------------------------------------------------------------
SELECT * FROM "public"."group_path_last";
--
id_path | spath
---------+------------------------------------------------------
386 | group_root\group_level_1\group_level_2\group_level_3
386 | group_level_3
386 | group_level_2\group_level_3
386 | group_level_1\group_level_2\group_level_3
(4 строки)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: LAST_VALUE returns the entire partition
@ 2021-07-02 14:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Дмитрий Иванов <firstdismay@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2021-07-02 14:04 UTC (permalink / raw)
To: Дмитрий Иванов <firstdismay@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:
> I cannot understand the behavior of the last_value function. Conditions
> opposite to first_value conditions. Why such a result?
The default window frame isn't symmetrical is why. Per the manual [1]:
Note that first_value, last_value, and nth_value consider only the
rows within the “window frame”, which by default contains the rows
from the start of the partition through the last peer of the current
row. This is likely to give unhelpful results for last_value and
sometimes also nth_value. You can redefine the frame by adding a
suitable frame specification (RANGE, ROWS or GROUPS) to the OVER
clause. See Section 4.2.8 for more information about frame
specifications.
That is, if you do nothing to change it then first_value returns the
value at the first row of the partition, while last_value returns the
value at the last peer of the current row (which isn't even very
well-defined when the current row has some peers).
regards, tom lane
[1] https://www.postgresql.org/docs/current/functions-window.html
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: LAST_VALUE returns the entire partition
@ 2021-07-02 15:30 Дмитрий Иванов <firstdismay@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Дмитрий Иванов @ 2021-07-02 15:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Thank you for such a quick response.
Why did last_value return ALL records to me and not one.
first_value(rg.spath) OVER (*PARTITION BY rg.id_path* *ORDER BY* rg.level
*DESC*) AS first_value
last_value(rg.spath) OVER (*PARTITION BY rg.id_path* *ORDER BY* rg.level)
AS last_value
last_value returns all rows, first_value one.? I don't understand this
behavior.
id_path | spath
---------+------------------------------------------------------
386 | group_root\group_level_1\group_level_2\group_level_3
386 | group_level_3
386 | group_level_2\group_level_3
386 | group_level_1\group_level_2\group_level_3
Thank you for your time.
пт, 2 июл. 2021 г. в 19:04, Tom Lane <tgl@sss.pgh.pa.us>:
> =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com>
> writes:
> > I cannot understand the behavior of the last_value function. Conditions
> > opposite to first_value conditions. Why such a result?
>
> The default window frame isn't symmetrical is why. Per the manual [1]:
>
> Note that first_value, last_value, and nth_value consider only the
> rows within the “window frame”, which by default contains the rows
> from the start of the partition through the last peer of the current
> row. This is likely to give unhelpful results for last_value and
> sometimes also nth_value. You can redefine the frame by adding a
> suitable frame specification (RANGE, ROWS or GROUPS) to the OVER
> clause. See Section 4.2.8 for more information about frame
> specifications.
>
> That is, if you do nothing to change it then first_value returns the
> value at the first row of the partition, while last_value returns the
> value at the last peer of the current row (which isn't even very
> well-defined when the current row has some peers).
>
> regards, tom lane
>
> [1] https://www.postgresql.org/docs/current/functions-window.html
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-07-02 15:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-02 12:04 LAST_VALUE returns the entire partition Дмитрий Иванов <firstdismay@gmail.com>
2021-07-02 14:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-07-02 15:30 ` Дмитрий Иванов <firstdismay@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox