Re: Inline count on a query

On Wed, Aug 19, 2020 at 8:19 AM Laura Smith <
n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch> wrote:

> On Wednesday, 19 August 2020 15:09, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> > On Wednesday, August 19, 2020, Laura Smith <
> n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch> wrote:
> >
> > > Hi,
> > >
> > > Let's say we've got a fairly basic table :
> > >
> > > create table networks (
> > > lan_id text not null,
> > > net_id text not null,
> > > port_id text not null
> > > );
> > > create index net_uniq on networks(lan_id,port_id);
> > >
> > > The query conundrum I am facing is that I need to add metadata to the
> output of the query that indicates the count of ports a given net has on a
> lan.
> > >
> > > So, for example, given :
> > > insert into networks(lan_id,net_id,port_id) values('L1','N1,'P1');
> > > insert into networks(lan_id,net_id,port_id) values('L1','N1,'P2');
> > >
> > > The metadata count would be 1, 2 (because 'N1' has 'P1' and 'P2' on
> 'L1').
> > >
> > > Is there a sensible way to query this without stressing out Postgres
> too much ? I'm guessing a CTE of some sort ?
> >
> > Suggest you provide your desired output in table format, and show “the
> query” that you mention.
> >
> > David J.
>
>
> If I knew what "the query" was, I wouldn't be posting here. ;-p
>

You implied that there was some existing query to which you are trying to
add metadata.

The only thing I've managed to come up with so far is adding count(*) over
> (partition by digest(lan_id||net_id,'sha256')) to my query, but that
> obviously gives the total count, not the ongoing incremental count.
>

If you want order to matter you need to add an ORDER BY to the window
specification, probably will the ROW * PRECEDING * FOLLOWING modifier as
well. Though there is nothing in your original formulation that suggests
you cared about an "ongoing incremental count" so we're back to my
insistence you better formulate your problem statement and/or actually
provide the output needed for a given set of inputs even if you cannot put
together a working query that at least gets you close to that output.

David J.