3

I have table mytable as shown below

CREATE TABLE mytable( id, created_at, total_number )
AS VALUES
    ( 17330  , '2018-05-24 19:25:29'::timestamp, 26909 ),
    ( 46331  , '2018-05-25 00:57:34',            26914 ),
    ( 72131  , '2018-05-26 00:48:12',            26944 ),
    ( 102583 , '2018-05-27 00:53:50',            26972 );

I would like to get total_number difference between first and the last

I got my first and last row with this query

(SELECT * FROM mytable 
    ORDER BY created_at ASC LIMIT 1)
    UNION
    (SELECT * FROM mytable 
    ORDER BY created_at DESC LIMIT 1)  

My query should return this results. How can I achieve it?

difference        number_of_rows   avg
(26972-26909=63)  4                63/4 
  • You should divide by 3, not 4 because the column is titled "difference". With 4 values, you get 3 differences. If we have a value every hour (at xx:00), would you divide the diff from midnight to midnight with 25 or 24, to get the hourly average difference? – ypercubeᵀᴹ May 27 '18 at 18:03
1

Another variation. Here the function first_value is used to get both the first and last total_number (different directions):

select abs(lst - fst) as diff, number_of_rows
     , abs(lst - fst) / number_of_rows as average 
from (
    select count(1) over () as number_of_rows
         , first_value(total_number) over (order by created_at) fst
         , first_value(total_number) over (order by created_at desc) lst
    from tbl
    fetch first 1 rows only
) as t;

An alternative is to specify the window frame with unbounded following and use the function last_value:

select abs(lst - fst) as diff, number_of_rows
     , abs(lst - fst) / number_of_rows as average 
from (
    select count(1) over () as number_of_rows
         , first_value(total_number) over (order by created_at) fst
         , last_value(total_number) over (order by created_at 
                                          rows between current row 
                                                and unbounded following
                                         ) lst
    from tbl
    fetch first 1 rows only
) as t;

Perhaps it is worth noting that the default window frame is range between unbounded preceding and current row, so in the last example, we need to override that to truly see the last row. All rows in the sub-select are identical, so we can pick one randomly (used in both examples).

I believe limit 1 now is part of the standard, but I'm used to fetch first ... so I'll stick to that

In this situation, distinct could have been used instead of limit or fetch ...

from (
    select distinct count(1) over () as number_of_rows
         , ...
    from tbl
)
| improve this answer | |
1

I would like to get total_number difference between first and the last

My assumption is that there is no faster way to do this if you have a sufficiently large data set and an index on created_at

SELECT tmax.max - tmin.min AS diff,
  tcount.cnt AS number_of_rows,
  (tmax.max - tmin.min)::double precision / tcount.cnt AS avg
FROM ( SELECT total_number FROM mytable ORDER BY created_at ASC FETCH FIRST ROW ONLY ) 
  AS tmin(min)
CROSS JOIN ( SELECT total_number FROM mytable ORDER BY created_at DESC FETCH FIRST ROW ONLY )
  AS tmax(max)
CROSS JOIN ( SELECT count(*) FROM mytable ) AS tcount(cnt);

I don't know of a more terse way to tell the planner find the highest and lowest by X, retrieving value Y -- which seems to be what you want.

AFAIK, only this way can the database plan this as one index scan.

| improve this answer | |
0

Select the total_number where created_at is the maximum or the minimum, respectively and subtract them to get the difference. For number_of_rows use count(). And avg just uses the previously built expressions in the arithmetic expression. It should look something like:

SELECT (SELECT mi.total_number
               FROM mytable mi
               WHERE mi.created_at = (SELECT max(mii.created_at)
                                             FROM mytable mii))
       -
       (SELECT mi.total_number
               FROM mytable mi
               WHERE mi.created_at = (SELECT min(mii.created_at)
                                             FROM mytable mii)) difference,
       (SELECT count(*)
               FROM mytable mi) number_of_rows,
       ((SELECT mi.total_number
                FROM mytable mi
                WHERE mi.created_at = (SELECT max(mii.created_at)
                                              FROM mytable mii))
        -
        (SELECT mi.total_number
                FROM mytable mi
                WHERE mi.created_at = (SELECT min(mii.created_at)
                                              FROM mytable mii)))
       /
       (SELECT count(*)
               FROM mytable mi) avg;

But you will get a problem if there is more than one row in mytable having the minimum (or maximum) value for created_at. To handle that case you need to define which one of the possible total_number values should be chosen then. The IDs don't look serial to me, so they cannot be used to solve such a conflict I guess.

| improve this answer | |
0

I assume "first" and "last" value are defined by the created_at column. Your approach can be extended to use both sub-query and combine them into a single one:

with fvalue as (
  -- get the first value and count the total number of rows
  select id, 
         created_at,
         total_number, 
         count(*) over () as number_of_rows
  from mytable
  order by created_at
  limit 1
), lvalue as (
  select id, 
         created_at,
         total_number
  from mytable
  order by created_at desc
  limit 1
)
select lv.total_number - fv.total_number as diff, 
       fv.number_of_rows, 
       (lv.total_number - fv.total_number) / fv.number_of_rows::decimal as avg
from fvalue fv
  cross join lvalue lv

The window function count(*) over () counts all rows in the table and is evaluated before the limit clause.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.