SQL Absolute value across columns

Question

I have a table that looks something like this:

word        big   expensive   smart   fast

dog         9     -10         -20     4
professor   2     4           40      -7
ferrari     7     50          0       48
alaska      10    0           1       0
gnat        -3    0           0       0

The + and - values are associated with the word, so professor is smart and dog is not smart. Alaska is big, as a proportion of the total value associated with its entries, and the opposite is true of gnat.

Is there a good way to get the absolute value of the number farthest from zero, and some token whether absolute value =/= value? Relatedly, how might I calculate whether the results for a given value are proportionately large with respect to the other values? I would write something to format the output to the effect of: "dog: not smart, probably not expensive; professor smart; ferrari: fast, expensive; alaska: big; gnat: probably small." (The formatting is not a question, just an illustration, I am stuck on the underlying queries.)

Also, the rest of the program is python, so if there is any python solution with normal dbapi modules or a more abstract module, any help appreciated.

Answer 1

Can you use the built-in database aggregate functions like MAX(column)?

Answer 2

Words listed by absolute value of big:

select word, big from myTable order by abs(big)

totals for each category:

select sum(abs(big)) as sumbig, 
       sum(abs(expensive)) as sumexpensive,   
       sum(abs(smart)) as sumsmart,
       sum(abs(fast)) as sumfast
  from MyTable;

Answer 3

abs value fartherest from zero:

select max(abs(mycol)) from mytbl

will be zero if the value is negative:

select n+abs(mycol)
  from zzz
 where abs(mycol)=(select max(abs(mycol)) from mytbl);

Answer 4

Asking the question helped clarify the issue; here is a function that gets more at what I am trying to do. Is there a way to represent some of the stuff in ¶2 above, or a more efficient way to do in SQL or python what I am trying to accomplish in show_distinct?

#!/usr/bin/env python

import sqlite3

conn = sqlite3.connect('so_question.sqlite')
cur = conn.cursor()

cur.execute('create table soquestion (word, big, expensive, smart, fast)')
cur.execute("insert into soquestion values ('dog', 9, -10, -20, 4)")
cur.execute("insert into soquestion values ('professor', 2, 4, 40, -7)")
cur.execute("insert into soquestion values ('ferrari', 7, 50, 0, 48)")
cur.execute("insert into soquestion values ('alaska', 10, 0, 1, 0)")
cur.execute("insert into soquestion values ('gnat', -3, 0, 0, 0)")

cur.execute("select * from soquestion")
all = cur.fetchall()

definition_list = ['word', 'big', 'expensive', 'smart', 'fast']

def show_distinct(db_tuple, def_list=definition_list):
    minimum = min(db_tuple[1:])
    maximum = max(db_tuple[1:])
    if abs(minimum) > maximum:
     print db_tuple[0], 'is not', def_list[list(db_tuple).index(minimum)]
    elif maximum > abs(minimum):
     print db_tuple[0], 'is', def_list[list(db_tuple).index(maximum)]
    else:
     print 'no distinct value'

for item in all:
    show_distinct(item)

Running this gives:

    dog is not smart
    professor is smart
    ferrari is expensive
    alaska is big
    gnat is not big
    >>> 

Answer 5

The problem seems to be that you mainly want to work within one row, and these type of questions are hard to answer in SQL.

I'd try to turn the structure you mentioned into a more "atomic" fact table like

word property value

either by redesigning the underlying table (if possible and if that makes sense regarding the rest of the application), or by defining a view that does this for you, like

select word, 'big' as property, big as value from soquestion
UNION ALLL
select word, 'expensive', expensive from soquestion
UNION ALL
...

This allows you to ask for the max value for each word:

select word, max(value), 
    (select property from soquestion t2 
     where t1.word = t2.word and t2.value = max(t1.value))
from soquestion t1
group by word

Still a little awkward, but most logic will be in SQL, not in your programming language of choice.