LAST_VALUE returns the entire partition - Mailing list pgsql-sql

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 строки)