sql server 迭代计算方差

问题描述：
1. Sql server版本：Sql Server 2008 R2 
2.假充有一个表：
Date Amount
2017/1/1 205.00
2017/1/2 208.00
2017/1/3 182.00
2017/1/4 193.00
2017/1/5 226.00
2017/1/6 218.00
2017/1/7 185.00
2017/1/8 205.00
2017/1/9 250.00
2017/1/10 300.00
2017/1/11 258.27
2017/1/12 265.73
2017/1/13 273.20
2017/1/14 280.67
2017/1/15 288.13
2017/1/16 295.60
2017/1/17 303.07
2017/1/18 310.53
2017/1/19 318.00
2017/1/20 325.47
这里的日期有可能不是连续的，例如某天放假就没数据。

需要实现的效果：
1. 如果过去3天的平均值低于过去10天的平均值20%，则在Amount后面增加一列，列名叫Mark，写上 “过去3天平均值低于过去10天平均值的20%”
2.这里的过去3天包含当天，但过去10天则不包含当天。
3.如果当前日期前面的数据小于或等于3天，则不需要比较，例如上表的：2017-1-4之前的则不需要比较，而2017-1-4及之后的每天都需要比较。
4.如果当前日期之前的天数不足10天，则以实际天数为准。例如2017-1-4~2017-1-9

请问如何通过一条sql语句写出来，谢谢！

7 个解决方案

#1

同样的问题发了两遍

#2

都是比平均值还差不多，如果加一个20%，基本没有符合条件的：

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([Date] Date,[Amount] decimal(18,8))
Insert #T
select '2017/1/1',205.00 union all
select '2017/1/2',208.00 union all
select '2017/1/3',182.00 union all
select '2017/1/4',193.00 union all
select '2017/1/5',226.00 union all
select '2017/1/6',218.00 union all
select '2017/1/7',185.00 union all
select '2017/1/8',205.00 union all
select '2017/1/9',250.00 union all
select '2017/1/10',300.00 union all
select '2017/1/11',258.27 union all
select '2017/1/12',265.73 union all
select '2017/1/13',273.20 union all
select '2017/1/14',280.67 union all
select '2017/1/15',288.13 union all
select '2017/1/16',295.60 union all
select '2017/1/17',303.07 union all
select '2017/1/18',310.53 union all
select '2017/1/19',318.00 union all
select '2017/1/20',325.47
Go
--测试数据结束
;WITH temp AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY Date) AS num from #T
)
SELECT  * ,
        CASE WHEN a.num > 4
             THEN ( CASE WHEN ( SELECT  AVG(b.Amount)
                                FROM    temp b
                                WHERE   b.num BETWEEN a.num - 2 AND a.num
                              ) < ( SELECT  AVG(b.Amount)
                                    FROM    temp b
                                    WHERE   b.num BETWEEN a.num - 9 AND a.num
                                  ) * 0.2 THEN '过去3天平均值低于过去10天平均值的20%'
                         ELSE NULL
                    END )
             ELSE NULL
        END
FROM    temp a;

#3

是不是每天都针对当天为基准进行计算，你看看下面

if not object_id(N'Tempdb..#t') is null drop table #t
Go
Create table #t([Date] Date,[Amount] decimal(18,8))
Insert #t
select '2017/1/1',205.00 union all
select '2017/1/2',208.00 union all
select '2017/1/3',182.00 union all
select '2017/1/4',193.00 union all
select '2017/1/5',226.00 union all
select '2017/1/6',218.00 union all
select '2017/1/7',185.00 union all
select '2017/1/8',205.00 union all
select '2017/1/9',250.00 union all
select '2017/1/10',300.00 union all
select '2017/1/11',258.27 union all
select '2017/1/12',265.73 union all
select '2017/1/13',273.20 union all
select '2017/1/14',280.67 union all
select '2017/1/15',288.13 union all
select '2017/1/16',295.60 union all
select '2017/1/17',303.07 union all
select '2017/1/18',310.53 union all
select '2017/1/19',318.00 union all
select '2017/1/20',325.47
Go
select *,CASE WHEN o.av_three<o.av_ten THEN N'过去3天平均值低于过去10天平均值的20%' END 
from #t as t
outer apply(
    SELECT SUM(CASE WHEN x.ID BETWEEN 0 AND 2 THEN x.Amount ELSE 0 END )/NULLIF(SUM(CASE WHEN x.ID BETWEEN 0 AND 2 THEN 1 ELSE 0 END),0) AS av_three
    ,SUM(CASE WHEN x.ID BETWEEN 1 AND 10 THEN x.Amount ELSE 0 END )/NULLIF(SUM(CASE WHEN x.ID BETWEEN 1 AND 10 THEN 1 ELSE 0 END ),0) AS av_ten
    FROM (
        SELECT datediff(d,tt.date,t.date) AS ID,tt.Amount
        FROM #t as tt where datediff(d,tt.date,t.date) BETWEEN 0 AND 10 
     ) AS x
)o

+------------+--------+--------------+--------------+-----------------------+
| Date       | Amount | av_three     | av_ten       |                       |
+------------+--------+--------------+--------------+-----------------------+
| 2017-01-01 | 205    | 205          | NULL         | NULL                  |
| 2017-01-02 | 208    | 206.5        | 205          | NULL                  |
| 2017-01-03 | 182    | 198.33333333 | 206.5        | 过去3天平均值低于过去10天平均值的20% |
| 2017-01-04 | 193    | 194.33333333 | 198.33333333 | 过去3天平均值低于过去10天平均值的20% |
| 2017-01-05 | 226    | 200.33333333 | 197          | NULL                  |
| 2017-01-06 | 218    | 212.33333333 | 202.8        | NULL                  |
| 2017-01-07 | 185    | 209.66666666 | 205.33333333 | NULL                  |
| 2017-01-08 | 205    | 202.66666666 | 202.42857142 | NULL                  |
| 2017-01-09 | 250    | 213.33333333 | 202.75       | NULL                  |
| 2017-01-10 | 300    | 251.66666666 | 208          | NULL                  |
| 2017-01-11 | 258.27 | 269.42333333 | 217.2        | NULL                  |
| 2017-01-12 | 265.73 | 274.66666666 | 222.527      | NULL                  |
| 2017-01-13 | 273.2  | 265.73333333 | 228.3        | NULL                  |
| 2017-01-14 | 280.67 | 273.2        | 237.42       | NULL                  |
| 2017-01-15 | 288.13 | 280.66666666 | 246.187      | NULL                  |
| 2017-01-16 | 295.6  | 288.13333333 | 252.4        | NULL                  |
| 2017-01-17 | 303.07 | 295.6        | 260.16       | NULL                  |
| 2017-01-18 | 310.53 | 303.06666666 | 271.967      | NULL                  |
| 2017-01-19 | 318    | 310.53333333 | 282.52       | NULL                  |
| 2017-01-20 | 325.47 | 318          | 289.32       | NULL                  |
+------------+--------+--------------+--------------+-----------------------+

#4

上面漏了第3个条件，另外如果过去三天包含当天，应该是3号前的不用考虑吧，到了三号时不是已经有三天了吗？

select *,CASE WHEN o.av_three<o.av_ten and pasteddays>2 THEN N'过去3天平均值低于过去10天平均值的20%' END 
from #t as t
outer apply(
    SELECT SUM(CASE WHEN x.ID BETWEEN 0 AND 2 THEN x.Amount ELSE 0 END )/NULLIF(SUM(CASE WHEN x.ID BETWEEN 0 AND 2 THEN 1 ELSE 0 END),0) AS av_three
    ,SUM(CASE WHEN x.ID BETWEEN 1 AND 10 THEN x.Amount ELSE 0 END )/NULLIF(SUM(CASE WHEN x.ID BETWEEN 1 AND 10 THEN 1 ELSE 0 END ),0) AS av_ten
    ,SUM(CASE WHEN x.ID BETWEEN 0 AND 2 THEN 1 ELSE 0 END) pasteddays
    FROM (
        SELECT datediff(d,tt.date,t.date) AS ID,tt.Amount
        FROM #t as tt where datediff(d,tt.date,t.date) BETWEEN 0 AND 10 
     ) AS x
)o

#5

晕，漏了低于20% 这个条件了,加上这个条件你的测试数据就没有满足的条件了，我将其中一条改的很小就有了

if not object_id(N'Tempdb..#t') is null drop table #t
Go
Create table #t([Date] Date,[Amount] decimal(18,8))
Insert #t
select '2017/1/1',205.00 union all
select '2017/1/2',208.00 union all
select '2017/1/3',182.00 union all
select '2017/1/4',193.00 union all
select '2017/1/5',226.00 union all
select '2017/1/6',218.00 union all
select '2017/1/7',185.00 union all
select '2017/1/8',205.00 union all
select '2017/1/9',20.00 union all
select '2017/1/10',300.00 union all
select '2017/1/11',258.27 union all
select '2017/1/12',265.73 union all
select '2017/1/13',273.20 union all
select '2017/1/14',280.67 union all
select '2017/1/15',288.13 union all
select '2017/1/16',295.60 union all
select '2017/1/17',303.07 union all
select '2017/1/18',310.53 union all
select '2017/1/19',318.00 union all
select '2017/1/20',325.47
Go


select *,ROUND((o.av_ten-o.av_three)/o.av_ten*100,2),
CASE WHEN o.av_three*1.2<o.av_ten and pasteddays>2 THEN N'过去3天平均值低于过去10天平均值的20%' END 
from #t as t
outer apply(
    SELECT SUM(CASE WHEN x.ID BETWEEN 0 AND 2 THEN x.Amount ELSE 0 END )/NULLIF(SUM(CASE WHEN x.ID BETWEEN 0 AND 2 THEN 1 ELSE 0 END),0) AS av_three
    ,SUM(CASE WHEN x.ID BETWEEN 1 AND 10 THEN x.Amount ELSE 0 END )/NULLIF(SUM(CASE WHEN x.ID BETWEEN 1 AND 10 THEN 1 ELSE 0 END ),0) AS av_ten
    ,SUM(CASE WHEN x.ID BETWEEN 0 AND 2 THEN 1 ELSE 0 END) pasteddays
    FROM (
        SELECT datediff(d,tt.date,t.date) AS ID,tt.Amount
        FROM #t as tt where datediff(d,tt.date,t.date) BETWEEN 0 AND 10 
     ) AS x
)o

+------------+--------+--------------+--------------+------------+--------+-----------------------+
| Date       | Amount | av_three     | av_ten       | pasteddays |        |                       |
+------------+--------+--------------+--------------+------------+--------+-----------------------+
| 2017-01-01 | 205    | 205          | NULL         | 1          | NULL   | NULL                  |
| 2017-01-02 | 208    | 206.5        | 205          | 2          | -0.73  | NULL                  |
| 2017-01-03 | 182    | 198.33333333 | 206.5        | 3          | 3.95   | NULL                  |
| 2017-01-04 | 193    | 194.33333333 | 198.33333333 | 3          | 2.02   | NULL                  |
| 2017-01-05 | 226    | 200.33333333 | 197          | 3          | -1.69  | NULL                  |
| 2017-01-06 | 218    | 212.33333333 | 202.8        | 3          | -4.7   | NULL                  |
| 2017-01-07 | 185    | 209.66666666 | 205.33333333 | 3          | -2.11  | NULL                  |
| 2017-01-08 | 205    | 202.66666666 | 202.42857142 | 3          | -0.12  | NULL                  |
| 2017-01-09 | 20     | 136.66666666 | 202.75       | 3          | 32.59  | 过去3天平均值低于过去10天平均值的20% |
| 2017-01-10 | 300    | 175          | 182.44444444 | 3          | 4.08   | NULL                  |
| 2017-01-11 | 258.27 | 192.75666666 | 194.2        | 3          | 0.74   | NULL                  |
| 2017-01-12 | 265.73 | 274.66666666 | 199.527      | 3          | -37.66 | NULL                  |
| 2017-01-13 | 273.2  | 265.73333333 | 205.3        | 3          | -29.44 | NULL                  |
| 2017-01-14 | 280.67 | 273.2        | 214.42       | 3          | -27.41 | NULL                  |
| 2017-01-15 | 288.13 | 280.66666666 | 223.187      | 3          | -25.75 | NULL                  |
| 2017-01-16 | 295.6  | 288.13333333 | 229.4        | 3          | -25.6  | NULL                  |
| 2017-01-17 | 303.07 | 295.6        | 237.16       | 3          | -24.64 | NULL                  |
| 2017-01-18 | 310.53 | 303.06666666 | 248.967      | 3          | -21.73 | NULL                  |
| 2017-01-19 | 318    | 310.53333333 | 259.52       | 3          | -19.66 | NULL                  |
| 2017-01-20 | 325.47 | 318          | 289.32       | 3          | -9.91  | NULL                  |
+------------+--------+--------------+--------------+------------+--------+-----------------------+

#6

非常感谢各位的回复，我星期一验证一下。

#7

@ch21st：你的代码写得非常漂亮