I currently have a dataset that contains transactional information, including dates, an amount field, and descriptive fields. I am looking to do simple statistical analysis (mean, median, etc.), but need to remove outliers first. The issue I'm having is removing the outliers while grouping the data by Region, City, and Date. I'm sure this could be done more easily using a robust programming language (R, Python, etc.), but I have to use SQL (SSMS) for this task.
Simple dataset example:
| Region | City | Date | Amount |
| ------ | ------- | ---------- | ------ |
| SW | Phoenix | 2021-10-01 | 400 |
| NE | Boston | 2021-10-03 | 20 |
| SW | Phoenix | 2021-10-03 | 800 |
| SW | Phoenix | 2021-10-02 | 425 |
| NE | Boston | 2021-10-01 | 500 |
| SW | Phoenix | 2021-10-02 | 15 |
| SW | Phoenix | 2021-10-04 | 100 |
| NE | Boston | 2021-10-04 | 35 |
| SE | Orlando | 2021-10-02 | 300 |
Initially I wanted to use IQR method, but I'm thinking using standard deviation (STDEV) will be easier. However, I'm running into issues with the grouping once I include the STDEV code.
Here is the current code as I have it:
WITH CTE_data AS (
SELECT
Region
,City
,Date
,Amount
FROM OrderTable
)
SELECT
Region
,City
,MAX(Date) AS MaxDate
,MIN(Date) AS MinDate
,AVG(Amount) AS AvgAmt
,STDEV(Amount) AS StedvAmt
FROM CTE_data
GROUP BY Region, City
Given the sample dataset, I'd like to ignore the amounts that fall outside of ((Amount > (AvgAmt - StedvAmt) AND Amount < (AvgAmt StedvAmt)). My end goal is to have an average amount for each Region/City, as well as an average amount (excluding the outliers). My desired output would be:
| Region | City | MinDate | MaxDate | AvgAmt | AvgAmt_rem |
| ------ | ------- | ---------- | ---------- | ------ | ---------- |
| SW | Phoenix | 2021-10-01 | 2021-10-04 | 348 | 308.33 |
| NE | Boston | 2021-10-01 | 2021-10-04 | 185 | 27.5 |
| SE | Orlando | 2021-10-02 | 2021-10-02 | 300 | 300 |
Please note: for simplicity I didn't do the actual standard deviation calculation for my desired output, I just removed the outliers based on looking at the values (800 & 15 for Phoenix and 500 for Boston).
CodePudding user response：
You can do that by doing your calculation twice, the first time only to use as a filter, the second time to exclude outliers and recalculate the new average
with CTE_data as ( --Your original data
SELECT * FROM ( VALUES
('SW', 'Phoenix', CONVERT(DATE,'2021-10-01'), CONVERT(DECIMAL(18,6),'400'))
, ('NE', 'Boston', '2021-10-03', '20')
, ('SW', 'Phoenix', '2021-10-03', '800')
, ('SW', 'Phoenix', '2021-10-02', '425')
, ('NE', 'Boston', '2021-10-01', '500')
, ('SW', 'Phoenix', '2021-10-02', '15')
, ('SW', 'Phoenix', '2021-10-04', '100')
, ('NE', 'Boston', '2021-10-04', '35')
, ('SE', 'Orlando', '2021-10-02', '300')
) as TabA(Region, City, ReadingDate, Amount)
), cteStats as ( --Calculate the Avg & Std of the raw data
SELECT
Region
, City
, AVG(Amount) AS AvgRaw --ADD BELOW CASE to keep singleton from filtering itself
, CASE WHEN STDEV(Amount) > 0.01 THEN STDEV(Amount) ELSE 0.01 END AS StedRaw
FROM CTE_data
GROUP BY Region, City
), ctFiltered as ( --do it again but this time filter out the outliers
SELECT
D.Region
,D.City
,MIN(ReadingDate) AS MinDate
,MAX(ReadingDate) AS MaxDate
,AVG(Amount) AS AvgFiltered
,STDEV(Amount) AS StedvFiltered
FROM CTE_data as D --Your original data again, but tied to the stats and filtered on the stats
INNER JOIN cteStats as S on D.Region = S.Region AND D.City = S.City
WHERE D.Amount BETWEEN S.AvgRaw - S.StedRaw AND S.AvgRaw S.StedRaw
GROUP BY D.Region, D.City
)
SELECT D.Region, D.City , D.MinDate , D.MaxDate
, CONVERT(decimal(18,2),S.AvgRaw) as AvgAmt
, CONVERT(decimal(18,2),D.AvgFiltered) as AvgAmt_rem
--, S.StedRaw as SDevPreFilter, D.StedvFiltered
FROM ctFiltered as D --Tie the filtered back to the stats so you can see the filter criteria
INNER JOIN cteStats as S on D.Region = S.Region AND D.City = S.City
EDIT: Add a CASE statement to test if the standard deviation is zero (a single value for that city) and replace it with a small StdDev, which won't matter because there can't be outliers to begin with if there is a single value!
CodePudding user response：
It's not necessary to use a self-join. You can do this with a single scane of the base table, using window functions
WITH cteStats as ( --Calculate the Avg & Std of the raw data
SELECT
*
,AVG(Amount) OVER (PARTITION BY Region, City) AS AvgRaw
,STDEV(Amount) OVER (PARTITION BY Region, City) AS StedRaw
FROM CTE_data
)
SELECT
S.Region
,S.City
,MIN(S.ReadingDate) AS MinDate
,MAX(S.ReadingDate) AS MaxDate
,AVG(S.Amount) AS AvgFiltered
,STDEV(S.Amount) AS StedvFiltered
FROM cteStats as S
WHERE s.Amount BETWEEN S.AvgRaw - S.StedRaw AND S.AvgRaw S.StedRaw
GROUP BY s.Region, S.City;
You can also show both with and without the filtered rows, by using conditional aggregation
WITH cteStats as ( --Calculate the Avg & Std of the raw data
SELECT
*
,AVG(Amount) OVER (PARTITION BY Region, City) AS AvgRaw
,STDEV(Amount) OVER (PARTITION BY Region, City) AS StedRaw
FROM CTE_data
)
SELECT
S.Region
,S.City
,MIN(CASE WHEN s.Amount BETWEEN S.AvgRaw - S.StedRaw AND S.AvgRaw S.StedRaw
THEN S.ReadingDate END) AS MinDate
,MAX(CASE WHEN s.Amount BETWEEN S.AvgRaw - S.StedRaw AND S.AvgRaw S.StedRaw
THEN S.ReadingDate END) AS MaxDate
,AVG(CASE WHEN s.Amount BETWEEN S.AvgRaw - S.StedRaw AND S.AvgRaw S.StedRaw
THEN S.Amount END) AS AvgFiltered
,AVG(S.Amount) AS AvgAll
FROM cteStats as S
GROUP BY s.Region, S.City;