时间戳

首页 > 解决方案 > 在PostgreSQL中划分两个查询

sql - 在PostgreSQL中划分两个查询

问题描述

我试图在 PostgreSQL 中划分两个查询：

我的样本数据是：

time                          coil_id    pushing_force  drawing_force

"2017-08-15 23:03:23.1"          0        855.125          0   
"2017-08-15 23:03:23.11"         0        822.12           0
"2017-08-15 23:03:23.12"         0         0              771.572
"2017-08-15 23:03:23.13"         0         0              772.572
"2017-08-15 23:03:23.14"         1        899.92           0
"2017-08-15 23:03:23.15"         1        821.12           0
"2017-08-15 23:03:23.16"         1         0              742.15
"2017-08-15 23:03:23.17"         2        892.1            0
"2017-08-15 23:03:23.18"         2         0              775.528
"2017-08-15 23:03:23.19"         2         0              771.572
"2017-08-15 23:03:23.2"          3        955.52           0
"2017-08-15 23:03:23.21"         3         0              768.866
"2017-08-15 23:03:23.22"         3         0              765.866

我的第一个查询是：

SELECT avg(d1.pushing_force)*0.1
  FROM drawing d1, (
       SELECT coil_id, max(drawing_force) as mdrawing_force
         FROM drawing
        GROUP BY coil_id
       ) d2 
 WHERE d1.pushing_force > d2.mdrawing_force
   AND d1.coil_id = d2.coil_id
GROUP BY d1.coil_id
ORDER BY d1.coil_id

输出：

96.0686237179488
98.2271765407554
98.3511043388427
99.0244
99.782412195122
98.9033064593301

我的第二个查询是：

SELECT AVG(drawing_force)*0.1 from drawing group by coil_id order by coil_id

输出 ：

67.4467013876606
34.7058347285139
45.304114594917
36.9346255571282
33.4687833398917
24.9778648102293
30.40511668208

我想划分query1/query2来查看输出

标签： sqlpostgresql

解决方案

如果我很了解您，您希望将两个查询的结果合并为一个，并且您希望将平均值除以平均值pushing_force以drawing_force获得每个查询的结果 coil_id。

尝试这个：

SELECT  t1.coil_id, t1.avg_pushingforce, t2.avg_drawing_force, t1.avg_pushingforce/t2.avg_drawing_force as result
FROM 
(
    SELECT coil_id, avg(d1.pushing_force)*0.1 as avg_pushingforce
    FROM drawing d1
        INNER JOIN
        (
            SELECT coil_id, max(drawing_force) as mdrawing_force
            FROM drawing
            GROUP BY coil_id
        ) d2 ON d1.pushing_force > d2.mdrawing_force AND d1.coil_id = d2.coil_id
    GROUP BY d1.coil_id
) t1 INNER JOIN
    (
        SELECT coil_id, avg(drawing_force)*0.1 as avg_drawing_force 
        FROM drawing
        GROUP BY coil_id
    ) t2 ON t1.coil_id = t2.coil_id
ORDER BY t1.coil_id

注意：我使用而不是在子句join中加入表。where请参阅：SQL 连接的可视化表示

推荐阅读