[sqlite] Good way for CEIL, or is there a better way

[Cecil Westerhof]

2016-05-10 2:31 GMT+02:00 Darren Duncan <darren at darrenduncan.net>:

> On 2016-05-09 4:24 AM, Cecil Westerhof wrote:
>
>> I need to have a CEIL function in SQLite. This is the way I implemented
>> it:
>> WITH percentage AS (
>>      SELECT date
>>      ,           100.0 * rank / outOf         AS percentage
>>      ,
>> ??
>> CAST(100.0 * rank / outOf AS int) AS castedPercentage
>>      FROM ranking
>> )
>> SELECT date
>> ,      (CASE WHEN percentage = castedPercentage
>>             THEN castedPercentage
>>             ELSE castedPercentage + 1
>>         END) AS percentage
>> FROM percentage
>>
>> Is this a good way, or is there a better way?
>>
>
> The Ceiling function is not that simple, unless you know that your rank
> and outOf are always non-negative numbers.  If they might be negative, you
> would -1 rather than +1 when the result is negative.


?I did not give all information. Both rank and outOf are at least 1 and
rank is <= outOf. So that should not be a problem.

But it looks like that the following is also acceptable:
   ?
CAST(ROUND(100.0 * rank / outOf + 0.4999999) AS int) AS percentage
and it is a lot simpler. So probably I will go for this.


For the curious, this is how I defined the table:
CREATE TABLE linkedinRanking (
    date    TEXT    NOT NULL DEFAULT CURRENT_DATE,
    rank    INTEGER NOT NULL,
    outOf    INTEGER NOT NULL,

    CONSTRAINT formatDate  CHECK(date = date(strftime('%s', date),
'unixepoch')),
    CONSTRAINT notInFuture CHECK(date <= date()),
    CONSTRAINT rankIsInt   CHECK(TYPEOF(rank)  = 'integer'),
    CONSTRAINT outOfIsInt  CHECK(TYPEOF(outOf) = 'integer'),
    CONSTRAINT rankGEOne   CHECK(rank >= 1),
    CONSTRAINT rankLEOutOf CHECK(rank <= outOf),
    CONSTRAINT outOfGEOne  CHECK(outOf >= 1),

    PRIMARY KEY(date)
);

?Maybe I should rename the date field. ;-)?

-- 
Cecil Westerhof