Search code examples
sqlpostgresqllogarithm

Get the count of numbers doubling using SQL?

I am trying to get a count on the number of times a number (start_value) doubles until it reaches a particular value (end_value) in the cleanest way possible. Consider the following example: 

id   start_value    end_value

1     40              130
2     100             777
3     0.20            2.1


example 1: 40 * 2 = 80
           80 * 2 = 160
           160 = above value so therefore we had 2 doubles

example 2: 100 * 2 = 200
           200 * 2 = 400
           400 * 2 = 800
           800 = above value so we had 3 doubles

example 3: 0.20 * 2 = 0.4
           0.4 * 2  = 0.8
           0.8 * 2 = 1.6
           1.6 * 2 = 3.2
           3.2 = 4 doubles
Solution

  • You want logarithms for this. Specifically, the exact number of times is the log-of-base-2 of the ratio of the two values. You want the next higher integer, so you want to round this up.

    In Postgres:

    ceiling(log(2, end_value / start_value))