Re: [SQL] CTEs and re-use - Mailing list pgsql-sql

On Tue, Apr 11, 2017 at 5:41 PM, Rob Sargent <robjsargent@gmail.com> wrote:

I have a lovely little CTE/select doing exactly what I need it to do.  Unfortunately I need its results in the next query.

Can't you just chain the CTEs? E.g.,

with segset as (
--...

)
, optmarkers as (

select m.id as mkrid
--... 

  group by m.id

)

select s.id, o.optval, min(m.basepos) as firstbase
  from optmarkers o 

--...
  order by firstbase;

No temp table to drop.

rls

--

:wq

In your chaining suggestion, are you thinking "optmarkers" uses "segset"?, as I have in [2] below?  I also tried it with optmarkers including segset [1]. 

Both have the same horrible performance as seen with an in-lining of the single CTE.  I haven't done the explains to see where the confusion is but clearly CTE fencing needs to be discrete.

[1] Nested CTE attempt
with final as(
     with segset as (
         select s.id
                , s.chrom
                , s.markerset_id
                , s.startbase
                , s.endbase
                , ((s.events_equal + s.events_greater)/(1.0 * (s.events_less + s.events_equal + s.events_greater))) as pval
         from seg.segment s
              join seg.probandset i on s.probandset_id = i.id
              join (select people_id, array_agg(person_id) as persons
                    from seg.people_member
                    group by people_id) as pa on i.probands <@ pa.persons
              join seg.people o on pa.people_id = o.id
         where
              s.markerset_id = 'b474655c-80d2-47e7-bcb5-c65245195888'
              and o.name = '709'
     )
     select m.id as mkrid
            , min(ss.pval) as optval
     from segset ss
          join seg.markerset_member mm on ss.markerset_id = mm.markerset_id
          join seg.marker m on mm.member_id = m.id
     where
          m.basepos between ss.startbase and ss.endbase
          and m.chrom = ss.chrom
          and mm.markerset_id = 'b474655c-80d2-47e7-bcb5-c65245195888' -- mkset
     group by m.id
)
select s.id, f.optval, min(m.basepos) as firstbase
from final f
     join seg.marker m on f.mkrid = m.id
     join seg.markerset_member mm on m.id = mm.member_id
     join seg.segment s on mm.markerset_id = s.markerset_id
where mm.markerset_id = 'b474655c-80d2-47e7-bcb5-c65245195888' -- mkset
      and m.basepos between s.startbase and s.endbase
      and ((s.events_equal + s.events_greater)/(1.0 * (s.events_less + s.events_equal + s.events_greater))) = f.optval
group by s.id, f.optval
order by firstbase;

[2] Chained attempt
with segset as (
    select s.id
           , s.chrom
           , s.markerset_id
           , s.startbase
           , s.endbase
           , ((s.events_equal + s.events_greater)/(1.0 * (s.events_less + s.events_equal + s.events_greater))) as pval
    from seg.segment s
         join seg.probandset i on s.probandset_id = i.id
         join (select people_id, array_agg(person_id) as persons
               from seg.people_member
               group by people_id) as pa on i.probands <@ pa.persons
         join seg.people o on pa.people_id = o.id
    where
         s.markerset_id = 'b474655c-80d2-47e7-bcb5-c65245195888'
         and o.name = '709'
),
final as(
select m.id as mkrid
       , min(ss.pval) as optval
from segset ss
     join seg.markerset_member mm on ss.markerset_id = mm.markerset_id
     join seg.marker m on mm.member_id = m.id
where
     m.basepos between ss.startbase and ss.endbase
     and m.chrom = ss.chrom
     and mm.markerset_id = 'b474655c-80d2-47e7-bcb5-c65245195888' -- mkset
group by m.id
)
select s.id, f.optval, min(m.basepos) as firstbase
from final f
     join seg.marker m on f.mkrid = m.id
     join seg.markerset_member mm on m.id = mm.member_id
     join seg.segment s on mm.markerset_id = s.markerset_id
where mm.markerset_id = 'b474655c-80d2-47e7-bcb5-c65245195888' -- mkset
      and m.basepos between s.startbase and s.endbase
      and ((s.events_equal + s.events_greater)/(1.0 * (s.events_less + s.events_equal + s.events_greater))) = f.optval
group by s.id, f.optval
order by firstbase;