SQL Server How do I calculate the ADI and COV of time series data using SQL?

7eumitmz 于 2023-04-19 发布在其他

关注(0)|答案(2)|浏览(148)

I'm trying to classify time series data using SQL. I have data for a reference data point that occurs over 3 years. So the reference occurs 36 times, one for each month. Sometimes the quantity is 0, other times it may be 25 or even higher for each row. What I want to know is how to calculate these equations using SQL (MSSQL in particular).

Then, similarly, I want to classify the data into Erratic , Smooth , Lumpy , and/or Intermittentas seen here .
Smooth demand (ADI < 1.32 and CV² < 0.49). The demand is very regular in time and in quantity. It is therefore easy to forecast and you won’t have trouble reaching a low forecasting error level.

Intermittent demand (ADI >= 1.32 and CV² < 0.49). The demand history shows very little variation in demand quantity but a high variation in the interval between two demands. Though specific forecasting methods tackle intermittent demands, the forecast error margin is considerably higher.

Erratic demand (ADI < 1.32 and CV² >= 0.49). The demand has regular occurrences in time with high quantity variations. Your forecast accuracy remains shaky.

Lumpy demand (ADI >= 1.32 and CV² >= 0.49). The demand is characterized by a large variation in quantity and in time. It is actually impossible to produce a reliable forecast, no matter which forecasting tools you use. This particular type of demand pattern is unforecastable.

Here is the query that produces the table that I am working with.

SELECT
distinct
CHAN_ID
,PROD_CD
,CP_REF
,PARENT
,ORDERED_DATE
,QUANTITY
FROM DF_ALL_DEMAND_BY_ROW_V
where parent is not null

CP_REF is the ID that I am focusing on.

Here is an example of the top 12 rows.

Please ask if you need more clarity. My SQL skills are barely basic.

sql-server

来源：https://stackoverflow.com/questions/71055377/how-do-i-calculate-the-adi-and-cov-of-time-series-data-using-sql

2条答案

按热度按时间

bwntbbo31#

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 <  0.49 then 'Smooth'
        when ADI >= 1.32 and COV <  0.49 then 'Intermittent'
        when ADI <  1.32 and COV >= 0.49 then 'Erratic'
        when ADI >= 1.32 and COV >= 0.49 then 'Lumpy'
        else 'Smooth'
    end as DEMAND
from data;

Double check that you want to use stdevp() and not stdev . I wish I were more knowledgeable about statistics.

赞(0）回复(0）举报 2023-04-19

mi7gmzs62#

As per the link, COV should only include the non-zero periods. And we'll need to square it before comparing. So the corrected COV should be computed as: pow(stddev(nullif(QUANTITY, 0)) / avg(nullif(QUANTITY, 0)), 2) as COV

赞(0）回复(0）举报 2023-04-19

我来回答

SQL Server How do I calculate the ADI and COV of time series data using SQL?

2条答案

相关问题

热门标签

最新问答