How do I create a view, select from it, and join it with an existing table-CodePudding

So here's the code producing the data that I want for table 1.

with data as 
(
    select
        CP_REF,
        count(*) * 1.0 /
          nullif(count(case when QUANTITY > 0 then 1 end), 0) as ADI,
          stdevp(QUANTITY) / nullif(avg(QUANTITY), 0) as COV
    from 
        DF_ALL_DEMAND_BY_ROW_V
    where 
        parent is not null
    group by 
        CP_REF
)
select
    CP_REF, ADI, COV
    case
        when ADI <  1.32 and COV * COV <  0.49 then 'Smooth'
        when ADI >= 1.32 and COV * COV <  0.49 then 'Intermittent'
        when ADI <  1.32 and COV * COV >= 0.49 then 'Erratic'
        when ADI >= 1.32 and COV * COV <= 0.49 then 'Lumpy'
        else 'No Quantity Measured'
    end
from data;

This provides a very elegant solution like this...

This is what I'm looking for, however, now I need to take this data and join it with the original table DF_ALL_DEMAND_BY_ROW_V. I want it to have everything that exists in DF_ALL_DEMAND_BY_ROW_V but joining the data above on CP_REF, which I think is a LEFT JOIN but I can't figure it out.

CodePudding user response：

Not sure if this is what you mean however why not create a second CTE and join to that, eg

with data as (
    select
    ...
), Q2 as (
  select
    CP_REF, ADI,
    case
        when ADI <  1.32 and COV * COV <  0.49 then 'Smooth'
        when ADI >= 1.32 and COV * COV <  0.49 then 'Intermittent'
        when ADI <  1.32 and COV * COV >= 0.49 then 'Erratic'
        when ADI >= 1.32 and COV * COV <= 0.49 then 'Lumpy'
        else 'Smooth'
    end as StatusOrWhatever
  from data
)
select stuff
from DF_ALL_DEMAND_BY_ROW_V v
left join Q2 on D2.CF_REF = v.CP_REF