时间戳

首页 > 解决方案 > 过滤分区内的值

sql - 过滤分区内的值

问题描述

我有一个表收入的收入值数据集。

我需要计算当前日期前 60 天的每一天的标准偏差。当计算标准偏差时，我按如下方式运行它：

SELECT
    [Date],Brand,[Country],[Marketing Channel],
    STDEV(Revenue) OVER (PARTITION BY Brand, Country,[Marketing Channel] 
        ORDER BY [Date] ROWS BETWEEN 2 PRECEDING and 1 PRECEDING) 
    as STD
FROM RevTable

问题是分区内的极值会使标准偏差向上或向下倾斜。我希望过滤分区本身内的极值。

如何过滤掉这种结构中的极值（我想到的是基于百分位的过滤）？

日期 牌 国家 营销渠道 收入
2021 年 1 月 1 日 有趣的游戏 加拿大 搜索引擎优化  3,578,834 美元
2021 年 1 月 1 日 有趣的游戏 加拿大 社交网络  90,000 美元
2021 年 1 月 1 日 有趣的游戏 荷兰 搜索引擎优化  682,943 美元
2021 年 1 月 2 日 有趣的游戏 加拿大 搜索引擎优化  3,731,849 美元
2021 年 1 月 2 日 有趣的游戏 加拿大 社交网络  257 美元
2021 年 1 月 2 日 有趣的游戏 荷兰 搜索引擎优化  627,272 美元
2021 年 1 月 3 日 有趣的游戏 加拿大 搜索引擎优化  2,418,136 美元
2021 年 1 月 3 日 有趣的游戏 加拿大 社交网络  40 美元
2021 年 1 月 3 日 有趣的游戏 荷兰 搜索引擎优化  479,642 美元
2021 年 1 月 4 日 有趣的游戏 加拿大 搜索引擎优化  2,231,254 美元
2021 年 1 月 4 日 有趣的游戏 加拿大 社交网络  $ -
2021 年 1 月 4 日 有趣的游戏 荷兰 搜索引擎优化  635,715 美元
2021 年 1 月 5 日 有趣的游戏 加拿大 搜索引擎优化  2,686,366 美元
2021 年 1 月 5 日 有趣的游戏 加拿大 社交网络  177 美元
2021 年 1 月 5 日 有趣的游戏 荷兰 搜索引擎优化  499,026 美元
2021 年 1 月 5 日 有趣的游戏 荷兰 社交网络  $ -
2021 年 1 月 6 日 有趣的游戏 加拿大 搜索引擎优化  2,096,472 美元
2021 年 1 月 6 日 有趣的游戏 加拿大 社交网络  465 美元
2021 年 1 月 6 日 有趣的游戏 荷兰 搜索引擎优化  653,359 美元
2021 年 1 月 6 日 有趣的游戏 荷兰 社交网络  $ -
2021 年 1 月 7 日 有趣的游戏 加拿大 搜索引擎优化  2,962,476 美元
2021 年 1 月 7 日 有趣的游戏 加拿大 社交网络  663 美元
2021 年 1 月 7 日 有趣的游戏 荷兰 搜索引擎优化  747,990 美元
2021 年 1 月 8 日 有趣的游戏 加拿大 搜索引擎优化  3,092,163 美元
2021 年 1 月 8 日 有趣的游戏 加拿大 社交网络  156 美元
2021 年 1 月 8 日 有趣的游戏 荷兰 搜索引擎优化  655,688 美元
2021 年 1 月 8 日 有趣的游戏 荷兰 社交网络  $ -
2021 年 1 月 9 日 有趣的游戏 加拿大 搜索引擎优化  3,110,117 美元
2021 年 1 月 9 日 有趣的游戏 加拿大 社交网络  153 美元
2021 年 1 月 9 日 有趣的游戏 荷兰 搜索引擎优化  571,313 美元
2021 年 1 月 9 日 有趣的游戏 荷兰 社交网络  $ -
2021 年 1 月 10 日 有趣的游戏 加拿大 搜索引擎优化  3,024,675 美元
2021 年 1 月 10 日 有趣的游戏 加拿大 社交网络  68 美元
2021 年 1 月 10 日 有趣的游戏 荷兰 搜索引擎优化  462,699 美元
2021 年 1 月 10 日 有趣的游戏 荷兰 社交网络  563 美元
2021 年 1 月 11 日 有趣的游戏 加拿大 搜索引擎优化  2,552,153 美元
2021 年 1 月 11 日 有趣的游戏 加拿大 社交网络  275 美元
2021 年 1 月 11 日 有趣的游戏 荷兰 搜索引擎优化  725,954 美元

期望的输出：

日期 牌 国家 营销渠道 性病
2021 年 1 月 1 日 有趣的游戏 加拿大 搜索引擎优化 522429
2021 年 1 月 1 日 有趣的游戏 荷兰 搜索引擎优化 97543
2021 年 1 月 2 日 有趣的游戏 加拿大 社交网络 27069
2021 年 1 月 2 日 有趣的游戏 荷兰 社交网络 251.66

但分区内没有异常值

标签： sqlsql-servertsqlwindow-functions

解决方案

您没有解释什么是“极端值”。但是你可以只使用一个CASE表达式。例如，如果您只想要 10 到 100 之间的值：

SELECT rt.*
       STDEV(CASE WHEN Revenue >= 10 AND Revenue <= 100 THEN Revenue END) OVER
             (PARTITION BY Brand, Country, [Marketing Channel] 
              ORDER BY [Date]
              ROWS BETWEEN 2 PRECEDING and 1 PRECEDING
             ) as STD
FROM RevTable rt ;

STDEV()-- 与大多数聚合和窗口函数一样 -- 忽略NULL值。

推荐阅读