pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedI need to fill up a sparse table in an view
2+ messages / 2 participants
[nested] [flat]
* I need to fill up a sparse table in an view
@ 2013-03-02 19:30 Andreas <maps.on@gmx.net>
2013-03-02 20:46 ` Re: I need to fill up a sparse table in an view Victor Yegorov <vyegorov@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Andreas @ 2013-03-02 19:30 UTC (permalink / raw)
To: pgsql-sql
Hi,
I need to fill up a sparse table in an view.
The table holds some numbers relating months and objects.
The month is an integer in the format YYYYMM.
To make it more convenient to manage this table I decidet to let a value
be good till the next entry.
E.g. if there is an entry in january and march, the january entry is
good in february, too.
So the table looks like.
my_numbers ( object_id int, month int, some_nr int )
( 17, 201301, 123 ),
( 42, 201301, 456 ),
( 42, 201303, 789 ),
Now I need a view that fills the gaps up till the current month.
( 17, 201301, 123 ),
( 17, 201302, 123 ), <-- filled gap
( 17, 201303, 123 ), <-- filled gap
( 42, 201301, 456 ),
( 42, 201302, 456 ), <-- filled gap
( 42, 201303, 789 ),
Is this possible?
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: I need to fill up a sparse table in an view
2013-03-02 19:30 I need to fill up a sparse table in an view Andreas <maps.on@gmx.net>
@ 2013-03-02 20:46 ` Victor Yegorov <vyegorov@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Victor Yegorov @ 2013-03-02 20:46 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
2013/3/2 Andreas <maps.on@gmx.net>
> So the table looks like.
> my_numbers ( object_id int, month int, some_nr int )
>
> ( 17, 201301, 123 ),
> ( 42, 201301, 456 ),
> ( 42, 201303, 789 ),
>
> Now I need a view that fills the gaps up till the current month.
>
>
> ( 17, 201301, 123 ),
> ( 17, 201302, 123 ), <-- filled gap
> ( 17, 201303, 123 ), <-- filled gap
> ( 42, 201301, 456 ),
> ( 42, 201302, 456 ), <-- filled gap
> ( 42, 201303, 789 ),
>
>
> Is this possible?
>
Possible. Slightly different object identifiers used:
CREATE TABLE t(
id int,
mon int,
val int
);
INSERT INTO t VALUES
(17,201301,123),
(42,201301,456),
(42,201303,789);
Then the query (check results here http://sqlfiddle.com/#!12/ce8fa/1 ):
WITH dr AS (
SELECT to_char(generate_series(to_date(min(mon)::text, 'YYYYMM'),
greatest(to_date(max(mon)::text,'YYYYMM'),
date(date_trunc('mon',
now()))), '1 mon'::interval),
'YYYYMM')::numeric mon
FROM t
)
, x AS (
SELECT s.id, dr.mon
FROM dr
CROSS JOIN (SELECT DISTINCT id FROM t) s
)
, g AS (
SELECT x.id, x.mon, t.val, CASE WHEN t.val IS NOT NULL THEN 1 ELSE NULL
END grp
FROM x
LEFT JOIN t USING (id, mon)
)
, nr AS (
SELECT g.id, g.mon, g.val, g.grp, sum(g.grp) OVER (ORDER BY id,mon) gnr
FROM g
)
SELECT *,
coalesce(val, min(val) OVER (PARTITION BY gnr)) the_one
FROM nr
ORDER BY 1,2;
1) “dr” is used to generate a range of months from the minimal found in the
“t” table up to either current or the max one found in the “t”, whichever
is bigger. A bit tricky query, if you can get the series of month some
other way — feel free;
2) “x” will create a CROSS join of all the “id” with all the months;
3) “g” will create almost ready result with all the gaps in place, new
service column is introduced to create groups;
4) within “nr” group identifiers are being summed, thus forming a unique
group number for each entry and gap rows that follows it;
5) finally, NULL entries are replaced with the correct ones.
To obtain the desired output, you should “SELECT id, mon, the_one” in the
last query. Feel free to query each of the intermediate steps to see how
data transforms.
You might want to get rid of the CTEs and write a bunch of subqueries to
avoid optimization fences of CTEs, as for bigger tables this construct will
be performing badly.
--
Victor Y. Yegorov
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-03-02 20:46 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-03-02 19:30 I need to fill up a sparse table in an view Andreas <maps.on@gmx.net>
2013-03-02 20:46 ` Victor Yegorov <vyegorov@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox