Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pxibC-0008Ai-0T for pgsql-general@arkaria.postgresql.org; Sat, 13 May 2023 06:19:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pxiaC-0003SV-3n for pgsql-general@arkaria.postgresql.org; Sat, 13 May 2023 06:18:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pxiaB-0003SK-PT for pgsql-general@lists.postgresql.org; Sat, 13 May 2023 06:18:19 +0000 Received: from lungold.riddles.org.uk ([82.68.208.19]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pxia4-002Mzj-VV for pgsql-general@postgresql.org; Sat, 13 May 2023 06:18:19 +0000 Received: from [192.168.127.4] (port=29831 helo=erlenstar.riddles.org.uk) by lungold.riddles.org.uk with esmtp (Exim 4.92.3 (FreeBSD)) (envelope-from ) id 1pxia1-0007Ey-VO; Sat, 13 May 2023 06:18:09 +0000 Received: from localhost ([127.0.0.1]:45317 helo=erlenstar.riddles.org.uk) by erlenstar.riddles.org.uk with esmtp (Exim 4.94 (FreeBSD)) (envelope-from ) id 1pxia3-00092D-1N; Sat, 13 May 2023 06:18:09 +0000 From: Andrew Gierth To: Durumdara Cc: Postgres General Subject: Re: Window function for get the last value to extend missing rows In-Reply-To: (durumdara@gmail.com's message of "Fri, 12 May 2023 13:04:32 +0200") Message-ID: <87ilcwg4gm.fsf@news-spur.riddles.org.uk> References: User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (berkeley-unix) Date: Sat, 13 May 2023 07:18:09 +0100 MIME-Version: 1.0 Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk >>>>> "Durumdara" == Durumdara writes: Durumdara> I have to make a virtual table which is minute based. Durumdara> I thought I would make a generated temp table Durumdara> (generate_series) and then join these values based on minue. Durumdara> 03:00 10 Durumdara> 03:01 NULL Durumdara> 03:02 12 Durumdara> 03:03 11 Durumdara> 03:04 NULL Durumdara> 03:05 13 Durumdara> I need a code to replace the value to the last value on Durumdara> NULL. It is possible to do this, even without the IGNORE NULLS option for window functions, but it's somewhat awkward. Someone else already mentioned using generate_series in lateral position to fill in values; in most cases this is probably the best approach, though it requires a little care. There is also a window function approach based on using the non-null values to delimit partitions: create table tmp_test_table(mmin,val) as select o, v from unnest(array[1,5,NULL,3,NULL,NULL,10,7,NULL,NULL,NULL,4]) with ordinality as u(v,o); select * from tmp_test_table order by mmin; mmin | val ------+----- 1 | 1 2 | 5 3 | 4 | 3 5 | 6 | 7 | 10 8 | 7 9 | 10 | 11 | 12 | 4 (12 rows) First we take advantage of the fact that mmin is increasing to generate a distinguishing value for each block of nulls: select *, max(case when val is not null then mmin end) over (order by mmin) as grp from tmp_test_table order by mmin; mmin | val | grp ------+-----+----- 1 | 1 | 1 2 | 5 | 2 3 | | 2 4 | 3 | 4 5 | | 4 6 | | 4 7 | 10 | 7 8 | 7 | 8 9 | | 8 10 | | 8 11 | | 8 12 | 4 | 12 (12 rows) Then we can fill in the missing vals by using the fact that there is at most one non-null val in each group: select *, max(val) over (partition by grp) as val2 from (select *, max(case when val is not null then mmin end) over (order by mmin) as grp from tmp_test_table) s order by mmin; mmin | val | grp | val2 ------+-----+-----+------ 1 | 1 | 1 | 1 2 | 5 | 2 | 5 3 | | 2 | 5 4 | 3 | 4 | 3 5 | | 4 | 3 6 | | 4 | 3 7 | 10 | 7 | 10 8 | 7 | 8 | 7 9 | | 8 | 7 10 | | 8 | 7 11 | | 8 | 7 12 | 4 | 12 | 4 (12 rows) This _really_ isn't efficient, though; you end up with typically three sorts of the data. For a one-off operation or for generating a materialized view it might be acceptable. -- Andrew (irc:RhodiumToad)