Обсуждение: Inline count on a query
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
portsa 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 ?
Laura
On Wednesday, August 19, 2020, Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.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.
On Wednesday, 19 August 2020 15:09, David G. Johnston <david.g.johnston@gmail.com> wrote:
> On Wednesday, August 19, 2020, Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.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
ofports 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
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.
On Wed, Aug 19, 2020 at 8:19 AM Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch> wrote:
On Wednesday, 19 August 2020 15:09, David G. Johnston <david.g.johnston@gmail.com> wrote:
> On Wednesday, August 19, 2020, Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.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.