PostgreSQL 10 – Getting Difference of Column Between First and Last Row

postgresql-10

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

Best Answer

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
)

Related Solutions

Conditional INSERT with Nested CTE in PostgreSQL

For the purpose of this question, I'll assume employee_details.name to be defined UNIQUE. Else, the whole operation wouldn't make sense.

You cannot nest a data-modifying CTE like you tried (as you already found out the hard way) - and you don't need to. This query would achieve your objective:

WITH e AS (
   SELECT name, employee_id
   FROM   employee_details
   WHERE  name = 'jack bauer'
   )
 , i1 AS (
   INSERT INTO employee             -- no target columns!
   SELECT                           -- empty SELECT list!
   WHERE NOT EXISTS (SELECT FROM e)
   RETURNING id
   )
 , i2 AS (
   INSERT INTO employee_details (name, employee_id) 
   SELECT 'jack bauer', id
   FROM   i1
   RETURNING name, employee_id
   )
SELECT employee_id, name FROM e
UNION ALL 
SELECT employee_id, name FROM i2;

The core feature is the INSERT with no target columns and an empty SELECT. Postgres fills all columns not listed in the SELECT with default values. This way we can replace the unconditional VALUES (default) with a conditional INSERT. The CTE i1 only inserts a row if the given name was not found.

The manual:

If no list of [target] column names is given at all, the default is all the columns of the table in their declared order; [...]

Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none.

This is a Postgres specific extension of the standard:

Also, the case in which a column name list is omitted, but not all the columns are filled from the VALUES clause or query, is disallowed by the standard.

The final CTE i2 only inserts a row if i1 returned a row. Voilá.

This is subject to race conditions under concurrent write load to the same tables. If you need to rule that out, you need to do more. Related:

How to use RETURNING with ON CONFLICT in PostgreSQL?

Without the complications from the conditional INSERT in the 2nd table, this would boil down to a common case of SELECT or INSERT:

Is SELECT or INSERT in a function prone to race conditions?

Aside

"id" text DEFAULT gen_random_uuid()

I'd strongly advise to use the data type uuid to store UUIDs.

PostgreSQL – Efficient Pagination for Large Tables

The key to performance is a matching multicolumn index of the form:

CREATE UNIQUE INDEX ON people (firstname, id);

UNIQUE, since sort order can be ambiguous without it, and you can get arbitrary results from peers.

A UNIQUE or PRIMARY KEY constraint serves as well.

While the first column is checked for equality like in your example (or sorted in the same direction as the query), this index is good for paging up and down, though it's a bit better for paging up.

With the index in place (and after running ANALYZE on the table), you won't see sequential scans any more (unless your table is small). The database is not "going through almost all your rows" any more.

Read the fine presentation by Markus Winand you are linking to.

If you want paging across more than one firstname, use ROW values. Example for paging down:

SELECT *
FROM  (
   SELECT id, firstname, lastname 
   FROM   people
   WHERE  (firstname, id) < ('John', 6)  -- ROW values
   ORDER  BY firstname DESC, id DESC 
   LIMIT  3
   ) d 
ORDER BY firstname, id;

Optimize query with OFFSET on large table

If the SELECT list only adds lastname like in your example, you might try to add that column to the index to get index-only scans out of it:

CREATE UNIQUE INDEX ON people (firstname, id, lastname);

Index expressions in this order.

The upcoming Postgres 11 allows indexes to INCLUDE columns, which results in smaller index size, better performance and is applicable in more situations. Like:

CREATE UNIQUE INDEX ON people (firstname, id) INCLUDE (lastname);

Best Answer

Related Solutions

Conditional INSERT with Nested CTE in PostgreSQL

Aside

PostgreSQL – Efficient Pagination for Large Tables

Related Question