所以這是生成表 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;
這提供了一個非常優雅的解決方案，像這樣......
這就是我正在尋找的，但是，現在我需要獲取這些資料并將其與原始表連接起來DF_ALL_DEMAND_BY_ROW_V。我希望它擁有所有存在DF_ALL_DEMAND_BY_ROW_V但加入上面的資料的資料CP_REF，我認為這是一個LEFT JOIN但我無法弄清楚。
uj5u.com熱心網友回復：
不確定這是否是您的意思，但是為什么不創建第二個 CTE 并加入其中，例如
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/428232.html
下一篇：訪問資料庫：連續兩個月的平均值