Exponential decay in sql

Question

Consider the following table:

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 

This comes from the following table:

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

Basically, we know the first day, and then we assume that the cost is halved every next day. I want to get the "summary" row from the first table.

Is there a way to do this in (T-) SQL? I did this in R, but I am completely fixated on how to do this in SQL. (Dates are actual dates, not just the day of the week.)

Answer 1

If your table looks like this:

 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); 

and you have some kind of day_of_week table with the relative position of the days:

 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); 

then it is not too ugly to do this with 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 feed here. )

Answer 2

 with dow as ( select 'Monday' as dow, 1 as num union all select 'Tuesday', 2 union all select 'Wedneday', 3 union all select 'Thursday', 4 union all select 'Friday', 5 union all select 'Saturday', 6 union all select 'Sunday', 7 ) select dow.dow, t.num * power(cast(0.5 as float), dow.dow - t.dow)) from (select t.*, dow.dow as dow from t join dow on t.dow = dow.dow ) t join dow on t.dow >= dow.dow group by t.name, dow.dow 

Gets data in a normalized format. ,,

If you really need to flip it (i.e. move to columns), you can use the pivot keyword or make conditional aggregation amounts.

Answer 3

I created a monster, but it works.

This is mysql, but I'm sure it translates to tsql easily

 select sum(mon) , sum(tue) , sum(wed) , sum(thur) , sum(fri) from ( select person , if (1 < field(day, 'mon', 'tue', 'wed', 'thur', 'fri'), 0, val / pow(2, 1 - field(day, 'mon', 'tue', 'wed', 'thur', 'fri'))) as mon , if (2 < field(day, 'mon', 'tue', 'wed', 'thur', 'fri'), 0, val / pow(2, 2 - field(day, 'mon', 'tue', 'wed', 'thur', 'fri'))) as tue , if (3 < field(day, 'mon', 'tue', 'wed', 'thur', 'fri'), 0, val / pow(2, 3 - field(day, 'mon', 'tue', 'wed', 'thur', 'fri'))) as wed , if (4 < field(day, 'mon', 'tue', 'wed', 'thur', 'fri'), 0, val / pow(2, 4 - field(day, 'mon', 'tue', 'wed', 'thur', 'fri'))) as thur , if (5 < field(day, 'mon', 'tue', 'wed', 'thur', 'fri'), 0, val / pow(2, 5 - field(day, 'mon', 'tue', 'wed', 'thur', 'fri'))) as fri from ( (select 'bill' as person, 'mon' as day, 4 as val from dual) union (select 'jane', 'wed', 2 from dual) union (select 'mary', 'tue', 8 from dual) ) t ) tbl 

http://sqlfiddle.com/#!2/d41d8/5249

Answer 4

how it should work on sql server:

 with data as (select ppl.name, case when ppl.day_of_week = 'Monday' then val when 1 < day_val then 0 end monday, case when ppl.day_of_week = 'Tuesday' then val when 2 < day_val then 0 else val * Power(Cast(0.5 as FLOAT), ( 2 - day_val )) end Tuesday, case when ppl.day_of_week = 'Weds' then val when 3 < day_val then 0 else val * Power(Cast(0.5 as FLOAT), ( 3 - day_val )) end Weds, case when ppl.day_of_week = 'Thurs' then val when 4 < day_val then 0 else val * Power(Cast(0.5 as FLOAT), ( 4 - day_val )) end thurs, case when ppl.day_of_week = 'Friday' then val when 5 < day_val then 0 else val * Power(Cast(0.5 as FLOAT), ( 5 - day_val )) end friday from (select name, day_of_week, val, case day_of_week when 'Monday' then 1 when 'Tuesday' then 2 when 'Weds' then 3 when 'Thurs' then 4 when 'Friday' then 5 end day_val from person) ppl) select coalesce(name, 'Total') as "Name", Sum(monday) as "Monday", Sum(tuesday) as "Tuesday", Sum(weds) as "Weds", Sum(thurs) as "Thursday", Sum(friday) as "Friday" from data group by name with rollup 

http://sqlfiddle.com/#!6/c4c6e/1

Answer 5

The following queries assume that this table variable is present:

 DECLARE @data TABLE ( Person varchar(50), Date date, Value int ); INSERT @data VALUES ('Bill', '2013-01-01', 4), ('Jane', '2013-01-03', 2), ('Mary', '2013-01-02', 8); 

Here's the CTE recursive approach for getting raw decay data:

 WITH decay AS ( SELECT Person, Date, CAST(Value as float) AS Value FROM @data UNION ALL SELECT Person, DATEADD(day, 1, Date) AS Date, Value / 2 AS Value FROM decay WHERE Value / 2 >= 0.25 ) SELECT Person, Date, SUM(Value) AS Value FROM decay GROUP BY Person, Date; 

I would suggest that most people will probably solve this, but if you need it, you can specify dates for the columns:

 WITH decay AS ( SELECT Person, Date, CAST(Value as float) AS Value FROM @data UNION ALL SELECT Person, DATEADD(day, 1, Date) AS Date, Value / 2 AS Value FROM decay WHERE Value / 2 >= 0.25 ) SELECT pvt.Person, ISNULL(pvt.[2013-01-01], 0) AS [2013-01-01], ISNULL(pvt.[2013-01-02], 0) AS [2013-01-02], ISNULL(pvt.[2013-01-03], 0) AS [2013-01-03], ISNULL(pvt.[2013-01-04], 0) AS [2013-01-04], ISNULL(pvt.[2013-01-05], 0) AS [2013-01-05] FROM ( SELECT Person, Date, SUM(Value) AS Value FROM decay GROUP BY Person, Date ) AS q PIVOT ( SUM(Value) FOR Date IN ([2013-01-01], [2013-01-02], [2013-01-03], [2013-01-04], [2013-01-05]) ) AS pvt; 

This gives you a set of results:

 Person 2013-01-01 2013-01-02 2013-01-03 2013-01-04 2013-01-05 ------------------------------------------------------------------ Bill 4 2 1 0.5 0.25 Jane 0 0 2 1 0.5 Mary 0 8 4 2 1 

Unfortunately, PIVOT requires you to specify the values ​​/ names of the rows / columns that you rotate, so the query must be dynamically generated.