LAST_VALUE returns the entire partition

From: Дмитрий Иванов <firstdismay(at)gmail(dot)com>
To: pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org>
Subject: LAST_VALUE returns the entire partition
Date: 2021-07-02 12:04:47
Message-ID: CAPL5KHp2AfXb_xi+-gpffaS1-idNarvxFuUOqkCs+rq-yWz1Lw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 строки)

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2021-07-02 14:04:10 Re: LAST_VALUE returns the entire partition
Previous Message Dean Gibson (DB Administrator) 2021-06-21 21:57:00 Re: Help to design relations between some 5 tables with possible many-to-many relations