sql - 如何在 SQL 中过滤每个组中前 1% 和后 1% 的数据

Question

我有一个包含 PRICE、SUBTYPE 等的数据集。我想在使用数据集之前进行一些异常值去除。我想删除每个 SUBTYPE 中价格高得离谱或低得离谱的行。

对于每个 SUBTYPE 查看 PRICE 的范围并删除或过滤掉行。
保留介于以下之间的行: PRICErange * .01 |KEEP|价格范围 * .99

这是由 Martin Smith 在 stackoverflow 上提供给我的，我编辑了这个问题，所以让我们从这里开始。

;WITH CTE       
AS (SELECT *,                   
ROW_NUMBER() OVER (PARTITION BY SUBTYPE ORDER BY PRICE) AS RN,                    
COUNT(*) OVER(PARTITION BY SUBTYPE) AS Cnt             
FROM    all_resale)    
SELECT *    
FROM   CTE    
WHERE (CASE WHEN Cnt > 1 THEN 100.0 * (RN -1)/(Cnt -1) END) BETWEEN 1 AND 99

我不确定这是我需要做的。我不知道会从末端移除多少行。

Answer 1

您没有具体说明如何定义 1% 以及应如何处理关系。

一种方法如下

;WITH CTE
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY SUBTYPE ORDER BY PRICE) AS RN,
                COUNT(*) OVER(PARTITION BY SUBTYPE) AS Cnt
         FROM    all_resale)
SELECT *
FROM   CTE
WHERE (CASE WHEN Cnt > 1 THEN 100.0 * (RN -1)/(Cnt -1) END) BETWEEN 1 AND 99

假设最高价格的项目是 100% ，最低价一位 0%和所有其他人在不考虑关系之间平均缩放。如果您需要考虑关系，请查看 RANK而不是 ROW_NUMBER
注意:如果所有子类型都有相对较多的行，您可以使用 NTILE(100) 相反，如果行数相对于桶数较小，则它不会在桶之间很好地分布。