Search code examples
sqlsql-servertime-seriesclassification

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

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).

enter image description here

enter image description here

Then, similarly, I want to classify the data into Erratic, Smooth, Lumpy, and/or Intermittent as 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.

Sample data

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

Solution 
  • 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.