Good way for CEIL, or is there a better way

classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

Good way for CEIL, or is there a better way

Cecil Westerhof-5
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?

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

OBones
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?
Isn't Ceil(Value) simply Round(Value + 0.5) ?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

Michele Pradella
In reply to this post by Cecil Westerhof-5
> 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?
>
Probably  you can create your own function

void sqlite3_ceilFunc(sqlite3_context* context, int argc,
sqlite3_value** values) {
     //yourcode
}

SQliteContext cContext; //any sqlite context
sqlite3* pDB; //your DB session
sqlite3_create_function(pDB, "CEIL", 1, SQLITE_UTF8, &cContext,
&sqlite3_ceilFunc, NULL, NULL);
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

Cecil Westerhof-5
In reply to this post by OBones
2016-05-09 13:36 GMT+02:00 OBones <[hidden email]>:

> 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?
>>
> Isn't Ceil(Value) simply Round(Value + 0.5) ?
>

​That was my first thought. But when playing with it, I ran into some edge
cases.

But maybe I should not worry to much about those. ;-)

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

Cecil Westerhof-5
In reply to this post by Michele Pradella
2016-05-09 13:40 GMT+02:00 Michele Pradella <[hidden email]>:

> 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?
>>
>> Probably  you can create your own function
>

​But I want it to be possible for ‘everyone’ to use the application. People
need to implement my function then. Or am I wrong about that?

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

Michele Pradella

> 2016-05-09 13:40 GMT+02:00 Michele Pradella <[hidden email]>:
>
>> 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?
>>>
>>> Probably  you can create your own function
> ​But I want it to be possible for ‘everyone’ to use the application. People
> need to implement my function then. Or am I wrong about that?
Just add CEIL function with (sqlite3_create_function) when you need in
your code. I do not understand what do you mean with "everyone" anyway
if your application has the definition of CEIL function everyone using
your application will have the function
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

Stephan Beal-3
In reply to this post by Cecil Westerhof-5
On Mon, May 9, 2016 at 1:52 PM, Cecil Westerhof <[hidden email]>
wrote:

> ​But I want it to be possible for ‘everyone’ to use the application. People
> need to implement my function then. Or am I wrong about that?
>

fyi, ceil(3) is c99, not c89, which is likely the (or a) reason it's not
included in sqlite by default.

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

Cecil Westerhof-5
In reply to this post by Michele Pradella
2016-05-09 13:57 GMT+02:00 Michele Pradella <[hidden email]>:

>
> 2016-05-09 13:40 GMT+02:00 Michele Pradella <[hidden email]>:
>>
>> 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?
>>>>
>>>> Probably  you can create your own function
>>>>
>>> ​But I want it to be possible for ‘everyone’ to use the application.
>> People
>> need to implement my function then. Or am I wrong about that?
>>
> Just add CEIL function with (sqlite3_create_function) when you need in
> your code. I do not understand what do you mean with "everyone" anyway if
> your application has the definition of CEIL function everyone using your
> application will have the function


​It is not a real application yet, just thought about it today. But it will
probably be a set of Bash scripts. If those Bash scripts depend on a
modified SQLite, then it would not be easy to use.


--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

E.Pasma
In reply to this post by OBones
09-05-2016, OBones:
> Isn't Ceil(Value) simply Round(Value + 0.5) ?
But Round(0.5) = 1
May be Round(Value+0.49999) is good enough?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

OBones
E.Pasma wrote:
> 09-05-2016, OBones:
>> Isn't Ceil(Value) simply Round(Value + 0.5) ?
> But Round(0.5) = 1
> May be Round(Value+0.49999) is good enough?
Well, yes, there's an issue at 0, but for anything else positive, it
should be good enough.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

Darren Duncan
In reply to this post by Cecil Westerhof-5
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. -- Darren Duncan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

Rowan Worth
On 10 May 2016 at 08:31, Darren Duncan <[hidden email]> wrote:

> 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. -- Darren Duncan
>

Yeah you can't always add one. But you never need to subtract one - ceil
always rounds towards positive infinity which is equivalent to integer
truncation over the range of negative reals.

I can't see a better way to implement ceil/floor in sqlite than using an
int cast to truncate.

-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

Darren Duncan
On 2016-05-09 7:54 PM, Rowan Worth wrote:

> On 10 May 2016 at 08:31, Darren Duncan <[hidden email]> wrote:
>
>> 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. -- Darren Duncan
>>
>
> Yeah you can't always add one. But you never need to subtract one - ceil
> always rounds towards positive infinity which is equivalent to integer
> truncation over the range of negative reals.
>
> I can't see a better way to implement ceil/floor in sqlite than using an
> int cast to truncate.

Yeah, actually.

So if we assume casting to an integer will always truncate aka round towards
zero, then the answer is to add 1 to the casted amount if and only if the real
percentage is positive and doesn't equal the cast amount.

-- Darren Duncan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

Cecil Westerhof-5
In reply to this post by Darren Duncan
2016-05-10 2:31 GMT+02:00 Darren Duncan <[hidden email]>:

> 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

Darren Duncan
On 2016-05-10 12:03 AM, Cecil Westerhof wrote:
> 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.

That might seem simpler but on edge cases it would fail.  Having an if-then
version is more reliable in general.  Also less ambiguous as ROUND doesn't
behave the same everywhere. -- Darren Duncan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

Cecil Westerhof-5
2016-05-10 22:06 GMT+02:00 Darren Duncan <[hidden email]>:

> On 2016-05-10 12:03 AM, Cecil Westerhof wrote:
>
>> 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.
>>
>
> That might seem simpler but on edge cases it would fail.  Having an
> if-then version is more reliable in general.  Also less ambiguous as ROUND
> doesn't behave the same everywhere. -- Darren Duncan


​OK, I am back to my original version. ;-)

But I made a view:
CREATE VIEW linkedinRankingPercentage AS
WITH percentage AS (
    SELECT date
    ,      rank
    ,      outOf
    ,           100.0 * rank / outOf         AS percentage
    ,      CAST(100.0 * rank / outOf AS int) AS castedPercentage
    FROM linkedinRanking
)
SELECT date
,      rank
,      outOf
,      (CASE WHEN percentage = castedPercentage
           THEN castedPercentage
           ELSE castedPercentage + 1
       END) AS percentage
FROM percentage
;

It is probably not possible, but just to be sure. ;-)
I like to have linkedinRankingPercentage the same fields as
linkedinRanking, but with the calculated field percentage added. I now name
those fields twice in the creating of the view. Can this be done better? In
the first SELECT I could change the three fields to a *, but I like the
symmetry more.

Would the view be a lot more expensive as the table, or if I do not need
the percentage, is it better to use the table instead of the view?

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

R Smith


On 2016/05/10 11:05 PM, Cecil Westerhof wrote:

> 2016-05-10 22:06 GMT+02:00 Darren Duncan <[hidden email]>:
>
>> On 2016-05-10 12:03 AM, Cecil Westerhof wrote:
>>
>>> 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.
>>>
>> That might seem simpler but on edge cases it would fail.  Having an
>> if-then version is more reliable in general.  Also less ambiguous as ROUND
>> doesn't behave the same everywhere. -- Darren Duncan
>
> ​OK, I am back to my original version. ;-)
>
> But I made a view:
> CREATE VIEW linkedinRankingPercentage AS
> WITH percentage AS (
>      SELECT date
>      ,      rank
>      ,      outOf
>      ,           100.0 * rank / outOf         AS percentage
>      ,      CAST(100.0 * rank / outOf AS int) AS castedPercentage
>      FROM linkedinRanking
> )
> SELECT date
> ,      rank
> ,      outOf
> ,      (CASE WHEN percentage = castedPercentage
>             THEN castedPercentage
>             ELSE castedPercentage + 1
>         END) AS percentage
> FROM percentage
> ;
>
> It is probably not possible, but just to be sure. ;-)
> I like to have linkedinRankingPercentage the same fields as
> linkedinRanking, but with the calculated field percentage added. I now name
> those fields twice in the creating of the view. Can this be done better? In
> the first SELECT I could change the three fields to a *, but I like the
> symmetry more.
>
> Would the view be a lot more expensive as the table, or if I do not need
> the percentage, is it better to use the table instead of the view?
>



Sorry I'm late to this thread, but why not use a proper CEIL function
inline? Any of the following two will work in all circumstances and are
simple enough to be done without the view:

(COALESCE(NULLIF(CAST(v AS int),v)+1,CAST(v AS int))

(CAST(v AS int) + CASE CAST(v AS int) WHEN v THEN 0 ELSE 1 END)


Translated to your Query:

SELECT date
,      rank
,      outOf
,      (COALESCE(NULLIF(CAST((100*rank/outof) AS int),(100*rank/outof))+1,CAST((100*rank/outof) AS int)) AS percentage
FROM linkedinRanking

OR

SELECT date
,      rank
,      outOf
,      (CAST((100*rank/outof) AS int) + CASE CAST((100*rank/outof) AS int) WHEN (100*rank/outof) THEN 0 ELSE 1 END) AS percentage
FROM linkedinRanking


If you'd like to do a view and avoid repeating the calculation, I'd
suggest a common table expression rather, like this perhaps:

WITH PC(date, rank, outOf, prcnt) AS (
   SELECT date, rank, outOf, (100*rank/outof)
     FROM linkedinRanking)SELECT date, rank, outOf, (COALESCE(NULLIF(CAST(prcnt AS
int),prcnt)+1,CAST(prcnt AS int)) AS percentage
   FROM PC


Although - I doubt this makes a significant improvement in the
efficiency, if any. Better yet for this kind of thing would be:

SELECT date, rank, outOf, (COALESCE(NULLIF(CAST(prcnt AS
int),prcnt)+1,CAST(prcnt AS int)) AS percentage
   FROM (SELECT date, rank, outOf, (100*rank/outof) AS prcnt FROM linkedinRanking)


HTH,
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Good way for CEIL, or is there a better way

Cecil Westerhof-5
2016-05-10 23:27 GMT+02:00 R Smith <[hidden email]>:

>
>
> On 2016/05/10 11:05 PM, Cecil Westerhof wrote:
>
>> 2016-05-10 22:06 GMT+02:00 Darren Duncan <[hidden email]>:
>>
>> On 2016-05-10 12:03 AM, Cecil Westerhof wrote:
>>>
>>> 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.
>>>>
>>>> That might seem simpler but on edge cases it would fail.  Having an
>>> if-then version is more reliable in general.  Also less ambiguous as
>>> ROUND
>>> doesn't behave the same everywhere. -- Darren Duncan
>>>
>>
>> ​OK, I am back to my original version. ;-)
>>
>> But I made a view:
>> CREATE VIEW linkedinRankingPercentage AS
>> WITH percentage AS (
>>      SELECT date
>>      ,      rank
>>      ,      outOf
>>      ,           100.0 * rank / outOf         AS percentage
>>      ,      CAST(100.0 * rank / outOf AS int) AS castedPercentage
>>      FROM linkedinRanking
>> )
>> SELECT date
>> ,      rank
>> ,      outOf
>> ,      (CASE WHEN percentage = castedPercentage
>>             THEN castedPercentage
>>             ELSE castedPercentage + 1
>>         END) AS percentage
>> FROM percentage
>> ;
>>
>> It is probably not possible, but just to be sure. ;-)
>> I like to have linkedinRankingPercentage the same fields as
>> linkedinRanking, but with the calculated field percentage added. I now
>> name
>> those fields twice in the creating of the view. Can this be done better?
>> In
>> the first SELECT I could change the three fields to a *, but I like the
>> symmetry more.
>>
>> Would the view be a lot more expensive as the table, or if I do not need
>> the percentage, is it better to use the table instead of the view?
>>
>>
>
>
> Sorry I'm late to this thread, but why not use a proper CEIL function
> inline?


​Better late as never. ;-)




> Any of the following two will work in all circumstances and are simple
> enough to be done without the view:
>
> (COALESCE(NULLIF(CAST(v AS int),v)+1,CAST(v AS int))
>
> (CAST(v AS int) + CASE CAST(v AS int) WHEN v THEN 0 ELSE 1 END)
>

​I liked the second more, but after I really understood the first I liked
that one more. By the way: in both the first ( does not belong there.




> Translated to your Query:
>
> SELECT date
> ,      rank
> ,      outOf
> ,      (COALESCE(NULLIF(CAST((100*rank/outof) AS
> int),(100*rank/outof))+1,CAST((100*rank/outof) AS int)) AS percentage
> FROM linkedinRanking
>
> OR
>
> SELECT date
> ,      rank
> ,      outOf
> ,      (CAST((100*rank/outof) AS int) + CASE CAST((100*rank/outof) AS int)
> WHEN (100*rank/outof) THEN 0 ELSE 1 END) AS percentage
> FROM linkedinRanking
>
>
> If you'd like to do a view and avoid repeating the calculation, I'd
> suggest a common table expression rather, like this perhaps:
>
> WITH PC(date, rank, outOf, prcnt) AS (
>   SELECT date, rank, outOf, (100*rank/outof)
>     FROM linkedinRanking)SELECT date, rank, outOf,
> (COALESCE(NULLIF(CAST(prcnt AS int),prcnt)+1,CAST(prcnt AS int)) AS
> percentage
>   FROM PC
>
>
> Although - I doubt this makes a significant improvement in the efficiency,
> if any.


​I do not do it for performance, but readability and maintenance.




> Better yet for this kind of thing would be:
>
> SELECT date, rank, outOf, (COALESCE(NULLIF(CAST(prcnt AS
> int),prcnt)+1,CAST(prcnt AS int)) AS percentage
>   FROM (SELECT date, rank, outOf, (100*rank/outof) AS prcnt FROM
> linkedinRanking)
>

​I went for:
CREATE VIEW linkedinRankingPercentage AS
SELECT date
,      rank
,      outOf
,      COALESCE(NULLIF(CAST(prcnt AS int), prcnt) + 1, CAST(prcnt AS int))
AS percentage
FROM (
    SELECT date
    ,      rank
    ,      outOf
    ,      (100.0 * rank / outof) AS prcnt
    FROM   linkedinRanking
);




> HTH,
>

​Certainly.

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users