pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
Cc: pgsql-sql@postgresql.org
Subject: Re: difficult query
Date: Sun, 05 May 2002 14:43:16 +0900
Message-ID: <20020505061938.1E8A.RK73@sea.plala.or.jp> (raw)
In-Reply-To: <Pine.LNX.4.33.0205041921020.18453-100000@u-pl2>
References: <200205041744.48054.barwick@gmx.net>
	<Pine.LNX.4.33.0205041921020.18453-100000@u-pl2>

On Sat, 4 May 2002 19:34:03 +0200 (CEST)
Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz> wrote:

> > Basically, no. (You might be able to do it with the help
> > of a user-defined function though).

I also think so.


> yesterday when I faced this problem (which can be solved perfectly with
> the outer join) I wasn't able to remember the solution... If there is
> any...  Just out of curiosity...

 I wouldn't think that that can be always solved. Whether it can be or not
 depends on the elements of  "day" column.  But until its aggregation 
 will lack two successive numbers or more, the following query is useful,
 I guess. 


CREATE VIEW v_table1 AS 
  SELECT tt.day , COUNT(tt.data) AS data
     FROM (SELECT t.day, t.data FROM table1 AS t
           UNION ALL
           SELECT 1, NULL -- indispensable if there's no row of day=1.
          )AS tt
    GROUP BY tt.day
; 
SELECT t0.day, t0.data
       FROM v_table1 AS t0
UNION
SELECT t1.day - 1 , 0
      FROM v_table1 AS t1, v_table1 AS t2
     WHERE t1.day > t2.day
     GROUP BY t1.day
    HAVING t1.day > MAX(t2.day) + 1
;


In addition, until its aggregation will lack THREE ones or more, ...

SELECT t0.day, t0.data
       FROM v_table1 AS t0
UNION
SELECT t1.day - 1, 0
       FROM  ...
UNION
SELECT t3.day - 2, 0
       FROM  ...



Regards,
Masaru Sugawara





view thread (6+ messages)  latest in thread

Message-ID: <20020505061938.1E8A.RK73@sea.plala.or.jp>
Permalink:  ../20020505061938.1E8A.RK73@sea.plala.or.jp/
Also on:    postgresql.org/message-id/20020505061938.1E8A.RK73@sea.plala.or.jp

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: rk73@sea.plala.or.jp, jouh8664@ss1000.ms.mff.cuni.cz
  Subject: Re: difficult query
  In-Reply-To: <20020505061938.1E8A.RK73@sea.plala.or.jp>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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