1

I'm trying to implement a decay mechanism in SQL that's based on the amount of time that has gone by. So the item "decays" faster as time goes by (based on the previous product).

This is accomplished by having a "Decay" value, a date of Generation, and a periodicity AKA the amount of time between applications of the decay value.

So if an item has a decay of 25%, starts at 1, with a date of generation of today and 3.5 minutes ago, it will already have decayed 3x resulting in a 'weighted value':

1m - 1.25

2m - 1.5625

3m - 1.953125

etc

s * ((1+d) ^ n)
where
s = starting value
d = decay % as decimal
n = number of elapsed periods

With the relevant SQL being:

ORDER BY

    1.00 * POWER((1.00+[Decay]),CONVERT(float, DATEDIFF(minute,[GenerationDate],GETUTCDATE())))

However this causes arithmetic overflows, so now I have to determine something in the WHERE statement that would stop me from ordering by items that are too old and would cause an overflow, or at least maxing it out to the max value of a decimal.

C Bauer
  • 5,003
  • 4
  • 33
  • 62

2 Answers2

2

Assuming that s > 0 and d > 0, then you can take advantage of the fact that the ordered set of reals X has it's ordering preserved under the transformation Y = Log(X). Take the Log of your function and you'll get a real number that fits compactly within the IEEE floating point:

X = s * ((1+d) ^ n)
Log(X) = Log(s * ((1 + d) ^ n)))
Log(X) = Log(s) + Log((1 + d) ^ n)
Log(X) = Log(s) + n * Log(1 + d)

Then, taking advantage of the fact that Log(s) is a constant term you simplify down to the following ORDER BY:

ORDER BY
    CONVERT(float, DATEDIFF(minute, [GenerationDate], GETUTCDATE())) * Log(1.00+[Decay])

Be aware that you will ignore any index that you have defined on this table since you're ordering by a computed operation. If this is something you expect to do frequently, consider adding a computed column with an index on the above expression or a static column that you perform a (for example) hourly update on.

Mike Bailey
  • 12,479
  • 14
  • 66
  • 123
  • 1
    See the Wikipedia article on [Logarithmic Identities](https://en.wikipedia.org/wiki/Logarithm#Logarithmic_identities) – Mike Bailey Mar 15 '16 at 12:54
  • I like this. Though I remember something about log from school I never thought I will see real life example :) – kurin123 Mar 15 '16 at 22:00
1

Try this

SET ARITHABORT OFF;
SET ARITHIGNORE ON;
SET ANSI_WARNINGS OFF;

This will not hang on overflow error but will return null instead

EDIT Null as last

select MyDate
from MyTable
order by case when MyDate is null then 1 else 0 end, MyDate
kurin123
  • 363
  • 1
  • 6
  • This I had found but I was having trouble getting the nulls to order properly. Really I know not nulls mean they are smaller than the nulls, so I need numbers followed by the nulls. Any thoughts on that? – C Bauer Mar 14 '16 at 21:12
  • try this [null as last](http://stackoverflow.com/questions/1498648/sql-how-to-make-null-values-come-last-when-sorting-ascending) – kurin123 Mar 14 '16 at 21:43