Postgres数据库中两个表的列平均值的差异

Question

我已经根据一列的平均值将一个表拆分为2个，现在我想计算新表中其他列的平均值的差值。

例如(简化的例子，我的数据有5列)

t1 =      'id', 'price', 'size'

           1 ,  10.0,     3    

          ...,   ...,    ...   

           10,   6.50,    8       

t2 =      'id', 'price', 'size'

           11 ,  12.12,   3    

          ...,   ...,    ...  

           20,   3.50,    5    '

diff_table  =  'diff_price'               ,'diff_size'

               t1.avg_price - t2.avg_price, t1.avg_size - t2.avg_size

Answer 1

嗯，非常简单，比如：

SELECT
    avg(t1.price) - avg(t2.price) AS diff_price,
    avg(t1.size) - avg(t2.size) AS diff_size
FROM t1, t2;

示例：https://www.db-fiddle.com/f/eXuc1bUyxAHhWo2Yu91GMt/0

如果需要将数据放入新表中，只需执行以下操作(使用显式列类型)：

CREATE TABLE diff_table AS
SELECT
    (avg(t1.price) - avg(t2.price))::decimal(10,2) AS diff_price,
    (avg(t1.size) - avg(t2.size))::decimal(10,2) AS diff_size
FROM t1, t2;

...result：

                  Table "public.diff_table"
   Column   |     Type      | Collation | Nullable | Default 
------------+---------------+-----------+----------+---------
 diff_price | numeric(10,2) |           |          | 
 diff_size  | numeric(10,2) |           |          | 

Answer 2

好吧我解决了我自己的问题。

CREATE TABLE diff AS
SELECT AVG(bathroomcnt) AS avg_bathroomcnt,
       AVG(bedroomcnt) AS avg_bedroomcnt,
       AVG(calculatedfinishedsquarefeet) AS avg_calculatedfinishedsqrft,
       AVG(garagecarcnt) AS avg_garagecarcnt,
       AVG(lotsizesquarefeet) AS avg_lotsizesqft,
       AVG(roomcnt) AS avg_roomcnt
FROM higher_values;

CREATE TABLE diff2 AS
SELECT AVG(bathroomcnt) AS avg_bathroomcnt,
       AVG(bedroomcnt) AS avg_bedroomcnt,
       AVG(calculatedfinishedsquarefeet) AS avg_calculatedfinishedsqrft,
       AVG(garagecarcnt) AS avg_garagecarcnt,
       AVG(lotsizesquarefeet) AS avg_lotsizesqft,
       AVG(roomcnt) AS avg_roomcnt
FROM lower_values;


SELECT h.avg_bathroomcnt - l.avg_bathroomcnt AS bathroomcnt_diff,
       h.avg_bedroomcnt - l.avg_bedroomcnt AS bedroomcnt_diff,
       h.avg_calculatedfinishedsqrft - l.avg_calculatedfinishedsqrft AS calculatedfinished_diff,
       h.avg_garagecarcnt - l.avg_garagecarcnt AS garagecarcnt_diff,
       h.avg_lotsizesqft - l.avg_lotsizesqft AS lotsizesqft_diff,
       h.avg_roomcnt - l.avg_roomcnt AS roomcnt_diff
from diff as h, diff2 as l;