search

macenv / pgdecibel

decibel data type to display data in decibels while storing and managing in pascals
GNU General Public License v3.0
1 stars 2 forks source link

Finish up Aggregate functions #1

Closed MACNoise closed 8 years ago

MACNoise commented 8 years ago

So far it appears to work great. We just need to finish up the aggregate functions.

Derek-Anderson commented 8 years ago

From: Anderson, Derek Sent: Tuesday, March 08, 2016 3:22 PM To: David Bitner Subject: FW: Decibel Datatype

Here was the cheat sheet for functionality sent out last March. I think most of it was implemented but there were a few remaining such Avg, Min, Max.

Aggregate functions should all be done native in pascals and only use the in/out for output (display, comparison, etc…)

-Derek

matt-baker commented 8 years ago

@Derek-Anderson - please break these out into specific issues for each piece of functionality that is missing or not working as expected.

Derek-Anderson commented 8 years ago

Division and Multiplication do not produce correct results using any data_type.

WITH VALUES AS ( 
    --SELECT 80::integer AS val_1, 70::integer AS val_2 
    --SELECT 80::numeric AS val_1, 75::numeric AS val_2
    SELECT 80::double precision AS val_1, 75::double precision AS val_2
)
 SELECT val_1, val_2, val_1 * val_2 AS direct_operation, 
    val_1::decibel * val_2::decibel as decibel_result, 
    10*LOG(10,(power(10, val_1/10) * power(10,val_2/10))::numeric) AS _expected_result
FROM values

In the SQL above using division, we're getting a result 98.75 when ~155 (154.00000000000000009 or something like that) is expected. Similar for multiplication.

bitner commented 8 years ago

Derek,

I can't quite remember, but I don't believe that I actually implemented the

In any case, I should make sure that it errors out when you try if nothing else, but are those operators that you actually want/need?

David

On Thu, Apr 28, 2016 at 3:04 PM, Derek Anderson notifications@github.com wrote:

Division and Multiplication do not produce correct results using any data_type.

WITH VALUES AS ( --SELECT 80::integer AS val_1, 70::integer AS val_2 --SELECT 80::numeric AS val_1, 75::numeric AS val_2 SELECT 80::double precision AS val_1, 75::double precision AS val_2 ) SELECT val_1, val_2, val_1 * val_2 AS direct_operation, val_1::decibel * val_2::decibel as decibel_result, 10LOG(10,(power(10, val_1/10) \ power(10,val_2/10))::numeric) AS _expected_result FROM values

In the SQL above using division, we're getting a result 98.75 when ~155 (154.00000000000000009 or something like that) is expected. Similar for multiplication.

— You are receiving this because you are subscribed to this thread. Reply to this email directly or view it on GitHub https://github.com/macenv/pgdecibel/issues/1#issuecomment-215545875

David William Bitner dbSpatial LLC 612-424-9932

Derek-Anderson commented 8 years ago

I don't see it as a primary function but could definitely see it being used at some point.

FYI - I'm going through all the use cases and I'll be logging any issues/gaps that I see.

Derek-Anderson commented 8 years ago

Has the "Pascal" cast type been implemented. I can't cast 40::pascal or 40::psc (which would return 10,000). This was part of the original casting operations and I thought you said you implemented it.

bitner commented 8 years ago

I don't remember, I haven't looked at this stuff for a year or so. I'll need to check when I get back into this.

On Thu, Apr 28, 2016 at 3:19 PM, Derek Anderson notifications@github.com wrote:

Has the "Pascal" cast type been implemented. I can't cast 40::pascal or 40::psc (which would return 10,000). This was part of the original casting operations and I thought you said you implemented it.

— You are receiving this because you commented. Reply to this email directly or view it on GitHub https://github.com/macenv/pgdecibel/issues/1#issuecomment-215549897

David William Bitner dbSpatial LLC 612-424-9932

bitner commented 8 years ago

Just looked. There is no pascal data type as pascals are just straight normal float8 values. There is a convenience pascal(decibel) function as well as casts to go between decibel and float8 or numeric in pascals.

On Thu, Apr 28, 2016 at 3:33 PM, David William Bitner bitner@dbspatial.com wrote:

I don't remember, I haven't looked at this stuff for a year or so. I'll need to check when I get back into this.

On Thu, Apr 28, 2016 at 3:19 PM, Derek Anderson notifications@github.com wrote:

Has the "Pascal" cast type been implemented. I can't cast 40::pascal or 40::psc (which would return 10,000). This was part of the original casting operations and I thought you said you implemented it.

— You are receiving this because you commented. Reply to this email directly or view it on GitHub https://github.com/macenv/pgdecibel/issues/1#issuecomment-215549897

David William Bitner dbSpatial LLC 612-424-9932

David William Bitner dbSpatial LLC 612-424-9932

Derek-Anderson commented 8 years ago

We will need a psc cast for adnl type calculations.

e.g. SELECT (SUM(SEL)::pascal/uptime)::decibel

bitner commented 8 years ago

pascal is not special, it's just float8 -- for that select (sum(sel)::float8/uptime)::decibel will work

On Thu, Apr 28, 2016 at 3:55 PM, Derek Anderson notifications@github.com wrote:

We will need a psc cast for adnl type calculations.

e.g. SELECT (SUM(SEL)::pascal/uptime)::decibel

— You are receiving this because you commented. Reply to this email directly or view it on GitHub https://github.com/macenv/pgdecibel/issues/1#issuecomment-215559173

David William Bitner dbSpatial LLC 612-424-9932

Derek-Anderson commented 8 years ago

The point is to put it into business context.

bitner commented 8 years ago

I'm happy to do that, but adding a full additional type is going to take take a fair bit of time as I'd have to map out all the operators and casts for the new type.

Pascals follow all the same rules and operations as float8. Adding a new type for pascals is akin to adding a special type just for meters.

Do you want me to spend the time to do this?

On Fri, Apr 29, 2016 at 7:38 AM, Derek Anderson notifications@github.com wrote:

The point is to put it into business context.

— You are receiving this because you commented. Reply to this email directly or view it on GitHub https://github.com/macenv/pgdecibel/issues/1#issuecomment-215700086

David William Bitner dbSpatial LLC 612-424-9932