IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Another SQL question
I am baaack :)

I have a table that has following columns:

box, paramName, paramValue, timestamp

This table is the result of polling of around 30000 boxes for different values, 10 to 20 per box.

I want to do something with different parameters' values. So, my question is, how do I turn rows into columns? How do I write a statement that would have the following schema its output:

box, valueOfParamFoo, valueOfParamBar, timestamp

For the first approximation, let's assume that timestamps for 1 and 2 are always the same (timestamp turns out to be redundant). For the second approximation, let's say I want to look at last values.

I was thinking about subqueries in FROM clause, something like this:

select box
fooValue,
barValue,
time
from (
select box
\t time,
\t value as barValue
\t NULL as fooValue
\t where name = 'bar'
UNION
select box
\t time,
\t NULL as barValue
\t value as fooValue
\t where name = 'foo'
)

But that is obviously not a good solution.

So, generally speaking, how do you create a record from 2 rows?
--

We have only 2 things to worry about: That
things will never get back to normal, and that they already have.
New quickie postgres hack
'seq' is in lieu of a timestamp; I didn't do the max() testing. Gotta git to bed. :-)

\nforum=> create table atest (box integer, pname text, pval text, seq integer);\nCREATE\nforum=> insert into atest values (1, 'foo', 'fooval', 1);\nINSERT 16989 1\nforum=> insert into atest values (1, 'bar', 'barval', 1);\nINSERT 16990 1\nforum=> insert into atest values (2, 'foo', 'fooval 2', 1);\nINSERT 16991 1\nforum=> insert into atest values (2, 'bar', 'barval 2', 1);\nINSERT 16992 1\n\nforum=> select atest.box, atest.pval, a.pval \nfrom atest, (select box, pval from atest where pname = 'bar') as a \nwhere atest.box = a.box and atest.pname = 'foo';\n\n box |   pval   |   pval   \n-----+----------+----------\n   1 | fooval   | barval\n   2 | fooval 2 | barval 2\n(2 rows)\n
Regards,

-scott anderson

"Welcome to Rivendell, Mr. Anderson..."
New Depends on what you're trying to determine
Depending on what decision I'm trying to make using the query, I often end up using a TRANSFORM for these kinds of things, for the only reason that I can catch wierd entries that are NOT 'foo' or 'bar', but something else that snuck into my nice clean dataset when I wasn't looking.

Example: if you just wanted to know the lowest value of 'foo' and compare it to the lowest value for 'bar', you might write (MSAccess-style):

TRANSFORM Min(paramValue) AS [The Value] SELECT box FROM myTable GROUP BY box PIVOT paramName;

This would give you:

box | foo | bar | grok\n----+-----+-----+-----\n  1 |  0  |  1  |  0\n  2 |  1  | 13  |  0\n  3 |  4  |  6  |  1


BUT you have to be careful with this sort of thing--careful that the number of possible paramNames doesn't exceed the number of columns your table (or brain, or eyes) can handle.

Many fears are born of stupidity and ignorance -
Which you should be feeding with rumour and generalisation.
BOfH, 2002 "Episode" 10
New Re: Another SQL question
In the simple case where each box/timestamp pairing is guaranteed to have both foo and bar parameters:

\nselect a.box,\n       a.param_value foo_value,\n       b.param_value bar_value,\n       a.timestamp\n  from some_table a,\n       some_table b\n where a.box = b.box\n       and a.timestamp = b.timestamp\n       and a.param_name = 'foo'\n       and b.param_name = 'bar'\n


In the case where foo is guaranteed to be there and bar is optional:

\n   select a.box,\n          a.param_value foo_value,\n          b.param_value bar_value,\n          a.timestamp\n     from some_table a\nleft join some_table b\n       on a.box = b.box\n      and a.timestamp = b.timestamp\n      and b.param_name = 'bar'\n    where a.param_name = 'foo'\n


or, Oracle-style:

\nselect a.box,\n       a.param_value foo_value,\n       b.param_value bar_value,\n       a.timestamp\n  from some_table a,\n       some_table b\n where a.box = b.box (+)\n       and a.timestamp = b.timestamp (+)\n       and a.param_name = 'foo'\n       and b.param_name (+) = 'bar';\n


Where neither is guaranteed, the "approved solution" would be to bring UNION into it. I don't particularly care for that approach (I do know that outer joins effectively are unions). This is what I would do in this case:

\ncreate view boxes_timestamps as\n  select box, timestamp\n    from some_table\n   group by box, timestamp\n\n   select a.box,\n          b.param_value foo_value,\n          c.param_value bar_value,\n          a.timestamp\n     from boxes_timestamps a,\nleft join some_table b\n       on a.box = b.box\n      and a.timestamp = b.timestamp\n      and b.param_name = 'foo'\nleft join some_table c\n       on a.box = c.box\n      and a.timestamp = c.timestamp\n      and c.param_name = 'bar'\n


Or, Oracle-style:

\nselect a.box,\n       b.param_value foo_value,\n       c.param_value bar_value,\n       a.timestamp\n  from boxes_timestamps a,\n       some_table b,\n       some_table c\n where a.box = b.box (+)\n       and a.timestamp = b.timestamp (+)\n       and b.param_name (+) = 'foo'\n       and a.box = c.box (+)\n       and a.timestamp = c.timestamp (+)\n       and c.param_name (+) = 'bar';\n


The other case that you describe, that of taking only the most recent occurence, is tricky. You cannot use a MAX on the timestamp because SQL aggregate functions apply to individual fields. That is, once you take MAX(timestamp), the value that is returned has lost its relation to the row from which it was pulled.

In the straighforward first case of all box/timestamp combinations having both a foo and bar value, the most easily understood way of getting the most recent values of foo and bar is:

\ncreate view most_recent_poll as\n  select box, max(timestamp)\n    from some_table\n   group by box\n\nselect a.box,\n       b.param_value foo,\n       c.param_value bar,\n       a.timestamp\n  from most_recent_poll a,\n       some_table b,\n       some_table c\n where a.box = b.box\n   and b.param_name = 'foo'\n   and a.timestamp = b.timestamp\n   and a.box = c.box\n   and c.param_name = 'bar'\n   and a.timestamp = c.timetamp\n


The other cases are more complex.

If you want to be slick and give yourself a headache, here's something I've done in Oracle to save a join or two:

\nselect a.box,\n       substr(\n              max(\n                  to_char(a.timestamp,'YYYYMMDDHH24MISS')||\n                  to_char(a.param_value)\n              ), 14\n       ) foo_value,\n       substr(\n              max(\n                  to_char(a.timestamp,'YYYYMMDDHH24MISS')||\n                  to_char(b.param_value)\n              ), 14\n       ) bar_value,\n       max(a.timestamp) timestamp\n  from some_table a,\n       some_table b\n where a.box = b.box\n       and a.timestamp = b.timestamp\n       and a.param_name = 'foo'\n       and b.param_name = 'bar';\n


Please note that all these examples will likely take a while to run and may have a nasty impact on the database server.

Also, duplication of foo or bar parameters in any box/timestamp pairing will lead to exponential duplicates in your result sets.

Next time, denormalize a little. Live it up. :-)


Hope this helps,

Mike

Edited one of the queries where I had left-out the timestamp info.
Expand Edited by morganek Nov. 14, 2002, 03:33:31 AM EST
     Another SQL question - (Arkadiy) - (3)
         quickie postgres hack - (admin)
         Depends on what you're trying to determine - (tseliot)
         Re: Another SQL question - (morganek)

It's hard to be religious when certain people are never incinerated by bolts of lightning.
42 ms