sql - sql中的指数衰减

Question

考虑下表:

Person | 1/1/13 | 1/2/13  | 1/3/13 | 1/4/13 | 1/5/13
Bill   | 4      | 2       | 1      | .5     | .25
Jane   | 0      | 0       | 2      | 1      | .5
Mary   | 0      | 8       | 4      | 2      | 1
-------------------------------------------------
Total  | 4      | 10      | 7      | 3.5    | 1.75

这是从下表中得出的:

Bill | 1/1/13 | 4
Jane | 1/3/13 | 2
Mary | 1/2/13 | 8

基本上，我们知道第一天，然后我们假设该值每隔一天减少一半。我想从第一个表中获取“总计”行。

有没有办法在(T-)SQL 中做到这一点？我已经在 R 中实现了它，但是我完全不知道如何在 SQL 中实现它。 (日期是实际日期，而不仅仅是星期几。)

Answer 1

如果您的表如下所示:

CREATE TABLE t (person VARCHAR(7), day_of_week_name VARCHAR(7), value NUMERIC);
INSERT INTO t VALUES ('Bill', 'Monday', 4);
INSERT INTO t values ('Jane', 'Weds', 2);
INSERT INTO t VALUES ('Mary', 'Tuesday', 8);

你有某种 day_of_week天的相对位置表:

CREATE TABLE day_of_week (name VARCHAR(7), position INT);
INSERT INTO day_of_week VALUES ('Monday', 1);
INSERT INTO day_of_week values ('Tuesday', 2);
INSERT INTO day_of_week VALUES ('Weds', 3);
INSERT INTO day_of_week VALUES ('Thurs', 4);
INSERT INTO day_of_week VALUES ('Friday', 5);

那么用 PIVOT 做这件事并不太难看:

SELECT Monday, Tuesday, Weds, Thurs, Friday
  FROM ( SELECT dow2.name AS day_of_week_name,
                t.value / power(2, dow2.position - dow1.position) AS decayed_value
           FROM t
           JOIN day_of_week AS dow1
             ON t.day_of_week_name = dow1.name
           JOIN day_of_week AS dow2
             ON dow1.position <= dow2.position
       ) AS b
 PIVOT ( SUM(decayed_value)
           FOR day_of_week_name
             IN (Monday, Tuesday, Weds, Thurs, Friday)
       ) AS pvt
;

( SQL Fiddle here. )