Re-using computed columns in the same query for other calculation

Question

I have to use computed columns to yet calculate the value of another column. Because I work with a very large table and also doing complex computation for the two previous columns, it wouldn't be a good idea to repeat the same functions to derive the third column, so looking for a way to use sub-query.

My table:

SELECT * FROM my_table LIMIT 5;
 session_id |  seconds   | millis |  gpstime   | gpsmillis | nsats |    lat     |    lon     | alt | track |  speed  | acc 
------------+------------+--------+------------+-----------+-------+------------+------------+-----+-------+---------+-----
      14026 | 1460464791 |    264 | 1460464791 |       264 |    -1 |  41.178237 | -8.5947137 |   0 |     0 |       0 |  20
      14026 | 1460464983 |    970 | 1460464983 |       970 |    -1 |  41.177956 | -8.5953581 | 234 |    67 | 10.2583 |  25
      14026 | 1460464984 |    712 | 1460464984 |       712 |    -1 | 41.1780008 | -8.5952012 | 182 |    58 | 9.19696 |  31
      14026 | 1460464985 |    700 | 1460464985 |       700 |    -1 | 41.1779522 |  -8.595209 | 196 |    74 | 7.63053 |  19
      14026 | 1460464986 |    714 | 1460464986 |       714 |    -1 |  41.177981 | -8.5951491 | 196 |    74 | 5.51359 |  22
(5 rows)

Then I run the query below to compute a1 and a2 in a subquery and averaged them in the main query as acceleration.

SELECT session_id
    , gpstime
    , lat
    , lon
    , track AS heading
    , speed
    , AVG(a1, a2) AS acceleration
FROM (
        SELECT *
            ,((LEAD(speed) OVER (ORDER BY gpstime)) - speed) / 
               (((LEAD(gpstime) OVER (ORDER BY gpstime)) - gpstime) +0.001) AS a2
        , (speed - (LAG(speed) OVER (ORDER BY gpstime))) / 
                     (gpstime - (LAG(gpstime) OVER (ORDER BY gpstime)) + 0.001) AS a1
        FROM my_table
    );

Error:

ERROR:  subquery in FROM must have an alias
LINE 9:  (
         ^
HINT:  For example, FROM (SELECT ...) [AS] foo.
SQL state: 42601
Character: 111

I also added an alias to my subquery, yet that didn't work.

I found this answer, which seems to work for Oracle database, but this doesn't work in my case(using PostgreSQL). I reproduce a minimal working example in a fiddle here to illustrate the problem.

Can someone point out what I missed or did wrong here?

It works fine with a table alias: dbfiddle.uk/….

Gordon Linoff
– Gordon Linoff

2020-01-28 15:56:25 +00:00
Commented Jan 28, 2020 at 15:56 — Gordon Linoff
– Gordon Linoff, Commented Jan 28, 2020 at 15:56

user330315 · Accepted Answer · 2020-01-28 15:55:56Z

2

The hint tells you what to do: give the sub-query an alias:

SELECT session_id
    , gpstime
    , lat
    , lon
    , track AS heading
    , speed
    , AVG(a1) AS acceleration
FROM (
        SELECT *
            ,((LEAD(speed) OVER (ORDER BY gpstime)) - speed) / 
               (((LEAD(gpstime) OVER (ORDER BY gpstime)) - gpstime) +0.001) AS a2
        , (speed - (LAG(speed) OVER (ORDER BY gpstime))) / 
                     (gpstime - (LAG(gpstime) OVER (ORDER BY gpstime)) + 0.001) AS a1
        FROM my_table
    ) as sub; --<< HERE

But AVG(a1, a2) is also wrong.

avg() is an aggregate function that works on a single column and separate rows. So you either need to use avg(a1) or avg(a2) or maybe you want (a1 + a2) / 2 if you want the average between the two values.

Demo

answered Jan 28, 2020 at 15:55

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

arilwan Over a year ago

Ah, thank you for your answer. I was placing the subquery alias within the parenthesis.

Collectives™ on Stack Overflow

Re-using computed columns in the same query for other calculation

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related