Sql-server – Filling Empty Values when Calculating Standard Deviation

aggregatesql serversql-server-2008-r2stored-procedures

I am currently trying to calculate the standard deviation of the amount of products we have sold at our company over the past 12 months.

I have a table that contains every date over the past year and is updated moving forward. I use a left outer join on our sales table to come up with 4 columns:

Year | Month | Part ID | Value

The Year and Month columns come from the date table, and the Part ID and Value represent a summation from the sales table, where the summation is grouped based on Sales Year, Sales Month, and Part ID.

Now, I can calculate standard deviation fine using this.

A simple

SELECT
    PART_ID,
    STDEV(VALUE) STANDARD_DEV_VALUE
FROM QUERYX
GROUP BY PART_ID

This works fine when I have a sale for a given item each month, as it returns 12 months of data. But, if a part does not have a sale, I do not have data for that month.

For example, here is a possible output:

YEAR | MONTH | PART | VALUE
2016 | 1     |   X  | 100.0

when what I need is:

YEAR | MONTH | PART | VALUE
2016 | 1     |   X  | 100.0
2016 | 2     |   X  | 0
2016 | 3     |   X  | 0

 and so on upto a full 12 months...

If I don't have this, I am not accurately calculating the standard deviation.

So, can someone help explain to me how I could fill in the Part ID when there are no sales? I have no link between the Part and the Dates, so I can only link on the sales dates. Thus, my issue.

This needs to be done in a single query, as there is other data being pulled here. Cursors are not an option. A temporary table is possible but it would have to execute very quickly.

Best Answer

I was able to resolve the issue through the use of a CROSS JOIN.

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product.

I used this in a sub-query to generate a list of dates with every Part ID. This was joined to my other table, allowing me to substitute a value of 0 where null. The standard dev. then calculates correctly.

Related Solutions

Sql-server – Performing aggregations and calculations: SQL (CTE) vs MDX

Row-level YTD calculations on a database table are not very useful unless you have a unit of analysis for your YTD figures that can be processed additively (i.e. you're not going to find your reports trying to aggregate two YTD figures). This essentially limits their usefulness to summary tables.

This type of aggregate table is of limited use with a cube, as the cube gives you the aggregation for free. If you have a time dimension on the cube you can make calculated YTD measures that encapsulate the calculations. with an expression such as Aggregate (YTD([Date].[Date].[CurrentMember]), [Measures].[SomeMetric]). You don't even have to specify the date.

The calculations you describe can all be done relatively easily in MDX with hierarchy functions such as PrevMember and ParallelPeriod, and this is more flexible and much less effort to implement than working off an aggregate table generated in the underlying database. You're better off doing al the YTD and period-period comparisons in the cube.

Sql-server – Calculating Running Average using Over clause in SQL server

You can apply your final filter on @dekade after computing the running averages.

In order to reduce the number of rows that need to be processed for the running averages, you can apply an earlier filter on [dekade] IN (@dekade, (@dekade+1)%36, (@dekade+2)%36) to ensure that you are processing the minimal amount of rows but still including all the rows that are necessary to including the following 11 rows in the running average. (The only reason for the % 36 is to handle values at @dekade that fall at the end of the year.)

This will still result in a table scan given your current table structure, but at least the rows can be filtered out earlier on in the query plan.

DECLARE @dekade TINYINT = 1
SELECT *
FROM (
    SELECT 
        LOCID,
        [Year],
        [Date],
        [dekade],
        AVG(cast(RTT as float)) OVER 
            (PARTITION BY LOCID ORDER BY Date
                ROWS BETWEEN CURRENT ROW AND 11 FOLLOWING) AS avgRTT
    FROM TimeSeries
    -- If you want to limit the rows that are use when computing the running average,
    -- you can make sure that only the desired @dekade plus the following two @dekades
    -- (which may be needed to get the following 11 rows) are used for each year
    WHERE [dekade] (@dekade, (@dekade+1)%36, (@dekade+2)%36)
) x
-- Filter your results be @dekade after computing the running average
WHERE x.[dekade] = @dekade

It would be helpful to post some sample data so that we can actually run the query though.

Best Answer

Related Solutions

Sql-server – Performing aggregations and calculations: SQL (CTE) vs MDX

Sql-server – Calculating Running Average using Over clause in SQL server

Related Question