sql—在postgresql中划分两个查询

p4tfgftt 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(252)

我尝试在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来查看输出

sql postgresql

来源：https://stackoverflow.com/questions/63298342/dividing-two-querys-in-postgresql

2条答案

按热度按时间

vof42yt11#

如果我理解的很好，您需要将两个查询的结果合并到一个查询中，并将两个查询的平均值除以 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联接的可视化表示

赞(0）回复(0）举报 2021-07-24

fsi0uk1n2#

您可以尝试这样的查询

with cte as (
    SELECT dr.coil_id, max(dr.drawing_force) as mdrawing_force
         FROM drawing dr
        GROUP BY dr.coil_id
)
SELECT (case when sum(case when d1.pushing_force > d2.mdrawing_force then 1 else 0 end) > 0 
              then sum(case when d1.pushing_force > d2.mdrawing_force then d1.pushing_force else 0 end)
                 / sum(case when d1.pushing_force > d2.mdrawing_force then 1 else 0 end)
              else 0 -- to avoid divide by zero error
              end)
         / avg(d1.pushing_force)*0.1
  FROM drawing d1
  inner join cte d2 using (coil_id)
  GROUP BY d1.coil_id
  ORDER BY d1.coil_id

赞(0）回复(0）举报 2021-07-24

我来回答

sql—在postgresql中划分两个查询

2条答案

相关问题

热门标签

最新问答