[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] I need to fill up a sparse table in an view
From: Victor Yegorov <vyegorov () gmail ! com>
Date: 2013-03-02 20:46:18
Message-ID: CAGnEbogrmO22=FCAzhBXqk4sK=+POrHccvSxJ43Vm0eZM5Px8Q () mail ! gmail ! com
[Download RAW message or body]
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
[Attachment #3 (text/html)]
2013/3/2 Andreas <span dir="ltr"><<a href="mailto:maps.on@gmx.net" \
target="_blank">maps.on@gmx.net</a>></span><br><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"> So the table looks like.<br>
my_numbers ( object_id int, month int, some_nr int )<br>
<br>
( 17, 201301, 123 ),<br>
( 42, 201301, 456 ),<br>
( 42, 201303, 789 ),<br>
<br>
Now I need a view that fills the gaps up till the current month.<br>
<br>
<br>
( 17, 201301, 123 ),<br>
( 17, 201302, 123 ), <-- filled gap<br>
( 17, 201303, 123 ), <-- filled gap<br>
( 42, 201301, 456 ),<br>
( 42, 201302, 456 ), <-- filled gap<br>
( 42, 201303, 789 ),<br>
<br>
<br>
Is this possible?<span class="HOEnZb"><font \
color="#888888"><br></font></span></blockquote></div><div><br></div>Possible. \
Slightly different object identifiers used:<div><br></div><div><div>CREATE TABLE \
t(</div><div> id int,</div> <div> mon int,</div><div> val \
int</div><div>);</div><div>INSERT INTO t \
VALUES</div><div>(17,201301,123),</div><div>(42,201301,456),</div><div>(42,201303,789);</div><div><br></div><div><br></div><div>Then \
the query (check results here <a \
href="http://sqlfiddle.com/#!12/ce8fa/1">http://sqlfiddle.com/#!12/ce8fa/1</a> \
):</div> <div><br></div><div><div>WITH dr AS (</div><div> SELECT \
to_char(generate_series(to_date(min(mon)::text, 'YYYYMM'),</div><div> \
greatest(to_date(max(mon)::text,'YYYYMM'),</div> <div> \
date(date_trunc('mon', now()))), '1 mon'::interval),</div><div> \
'YYYYMM')::numeric mon</div><div> FROM t</div><div> )</div> \
<div>, x AS (</div><div> SELECT <a href="http://s.id">s.id</a>, \
dr.mon</div><div> FROM dr</div><div> CROSS JOIN (SELECT DISTINCT id \
FROM t) s</div><div> )</div><div>, g AS (</div><div> SELECT <a \
href="http://x.id">x.id</a>, x.mon, t.val, CASE WHEN t.val IS NOT NULL THEN 1 ELSE \
NULL END grp</div> <div> FROM x</div><div> LEFT JOIN t USING (id, \
mon)</div><div> )</div><div>, nr AS (</div><div> SELECT <a \
href="http://g.id">g.id</a>, g.mon, g.val, g.grp, sum(g.grp) OVER (ORDER BY id,mon) \
gnr</div><div> FROM g</div> <div> )</div><div>SELECT *,</div><div> \
coalesce(val, min(val) OVER (PARTITION BY gnr)) the_one</div><div> FROM \
nr</div><div> ORDER BY 1,2;</div></div><div><br></div><div>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;</div> <div>2) "x" will \
create a CROSS join of all the "id" with all the months;</div><div>3) "g" will create \
almost ready result with all the gaps in place, new service column is introduced to \
create groups;</div><div>4) within "nr" group identifiers are being summed, thus \
forming a unique group number for each entry and gap rows that follows it;</div> \
<div>5) finally, NULL entries are replaced with the correct \
ones.</div><div><br></div><div>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.</div> <div><br></div><div>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.</div><div><br></div><div><br></div>
-- <br>Victor Y. Yegorov
</div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic