Обсуждение: Q: using generate_series to fill in the blanks

I've got a desired output which looks something like this..

 vdt        | count
------------+-------
          1 |   514
          2 |    27
          3 |    15
      4 |  <NULL>
          5 |    12
          6 |    15


the query in psql is something like this..

select vdt, count(*) from footable where c_id = '71' group by vdt order
by vdt

problem is.. since there's not data whatsoever on vdt=4 I get this..

 vdt        | count
------------+-------
          1 |   514
          2 |    27
          3 |    15
          5 |    12
          6 |    15

I tried to use generate_series


select generate_series(1,7,1), count(*) from footable where c_id = '71'
group by generate_series(1,7,1),vdt order by generate_series(1,7,1);

(note : the vdt are numbered from 1 to 7 sequence)

 generate_series | count
-----------------+-------
               1 |   514
               1 |    27
               1 |    15
               1 |    12
               1 |    15
               2 |   514
               2 |    27
               2 |    15
               2 |    12
               2 |    15
               3 |   514
               3 |    27
               3 |    15
               3 |    12
               3 |    15
               4 |   514
               4 |    27
               4 |    15
               4 |    12
               4 |    15
.....
[snip]
.....

On Dec 6, 2007 10:44 PM, Ow Mun Heng <Ow.Mun.Heng@wdc.com> wrote:
> I've got a desired output which looks something like this..
>
>  vdt        | count
> ------------+-------
>           1 |   514
>           2 |    27
>           3 |    15
>           4 |  <NULL>
>           5 |    12
>           6 |    15

SELECT i.i AS vdt,
       CASE
         WHEN COUNT(vdt)=0 THEN NULL
         ELSE COUNT(vdt)
       END AS COUNT
FROM generate_series (1, 7) i
     LEFT JOIN footable f ON i.i = f.vdt AND c_id = '71'
GROUP BY i.i
ORDER BY i.i;

On Thu, 2007-12-06 at 23:06 -0500, Rodrigo De León wrote:
> On Dec 6, 2007 10:44 PM, Ow Mun Heng <Ow.Mun.Heng@wdc.com> wrote:
> > I've got a desired output which looks something like this..
> >
> >  vdt        | count
> > ------------+-------
> >           1 |   514
> >           2 |    27
> >           3 |    15
> >           4 |  <NULL>
> >           5 |    12
> >           6 |    15
>
> SELECT i.i AS vdt,
>        CASE
>          WHEN COUNT(vdt)=0 THEN NULL
>          ELSE COUNT(vdt)
>        END AS COUNT
> FROM generate_series (1, 7) i
>      LEFT JOIN footable f ON i.i = f.vdt AND c_id = '71'
> GROUP BY i.i
> ORDER BY i.i;

This is _way_ cool. Thanks. However I still have some additional
questions.

as individual c_ids:
 vdt | c_id  | count
-----+-------+-------
   1 | 71    |   533
   2 | 71    |    30
   3 | 71    |    15
   4 | 71    |    10
   5 | 71    |    12
   6 | 71    |    15
   7 |       |

 vdt |c_id| count
-----+-------+-------
   1 | 48 |   217
   2 | 48 |    86
   3 | 48 |    46
   4 | 48 |    50
   5 | 48 |     4
   6 |    |
   7 |    |

select i.i as vdt,dcm_evaluation_code as c_id
, case when count(vdt_format) = 0 then NULL else count(vdt_format) end
as count
from generate_series(1,7) i
left join footable f
on i.i = f.vdt_format
and c_id in ('71','48')
group by c_id, i.i
order by c_id,i.i;


When Joined into 1 query
 vdt | c_id  | count
-----+-------+-------
   1 | HMK71 |   533
   2 | HMK71 |    30
   3 | HMK71 |    15
   4 | HMK71 |    10
   5 | HMK71 |    12
   6 | HMK71 |    15 << What happened to 7?
   1 | HML48 |   217
   2 | HML48 |    86
   3 | HML48 |    46
   4 | HML48 |    50
   5 | HML48 |     4
   7 |       |

additionally, if you don't mind, when I substitute

-->and c_id = '71'

with

--> where c_id = '71'

the nulls also disappears.

In any case, it seems to be working for _single_ c_id clauses..

On Fri, Dec 07, 2007 at 01:18:13PM +0800, Ow Mun Heng wrote:
> select i.i as vdt,dcm_evaluation_code as c_id
> , case when count(vdt_format) = 0 then NULL else count(vdt_format) end
> as count
> from generate_series(1,7) i
> left join footable f
> on i.i = f.vdt_format
> and c_id in ('71','48')
> group by c_id, i.i
> order by c_id,i.i;
>
> When Joined into 1 query
>  vdt | c_id  | count
> -----+-------+-------
>    1 | HMK71 |   533
>    2 | HMK71 |    30
>    3 | HMK71 |    15
>    4 | HMK71 |    10
>    5 | HMK71 |    12
>    6 | HMK71 |    15 << What happened to 7?
>    1 | HML48 |   217
>    2 | HML48 |    86
>    3 | HML48 |    46
>    4 | HML48 |    50
>    5 | HML48 |     4
>    7 |       |

You need to start by generating all of the values you consider you
want.  In the previous example this was easy as all you wanted was a
set of numbers.  Now you want the cartesian product of this series and
something else.  So you need to be doing something like:

  SELECT x.i, x.j, COUNT(t.k)
  FROM (SELECT DISTINCT t.i,s.j FROM table t, generate_series(1,7) s(j)) x
    LEFT JOIN table t ON (x.i,x.j) = (t.i,t.j)
  GROUP BY x.i, x.j
  ORDER BY x.i, x.j;


  Sam