使用計數獲取第一行和最後一行之間的列差異

我有mytable如下所示的表格

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 );

我想得到第一個和最後一個之間的 total_number 差異

我得到了這個查詢的第一行和最後一行

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

我的查詢應該返回這個結果。我怎樣才能實現它？

difference        number_of_rows   avg
(26972-26909=63)  4                63/4 

另一種變體。這裡該函式first_value用於獲取第一個和最後一個total_number（不同方向）：

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;

另一種方法是指定視窗框架unbounded following並使用函式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;

或許值得注意的是，預設的視窗框架是range between unbounded preceding and current row，所以在最後一個例子中，我們需要覆蓋它才能真正看到最後一行。子選擇中的所有行都是相同的，因此我們可以隨機選擇一個（在兩個範例中都使用）。

我相信現在限制 1 是標準的一部分，但我已經習慣了，fetch first ...所以我會堅持下去

在這種情況下，可以使用 distinct 而不是 limit 或 fetch …

from (
   select distinct count(1) over () as number_of_rows
        , ...
   from tbl
)

引用自：https://dba.stackexchange.com/questions/207976