时间戳

首页 > 解决方案 > 根据两行之间的差异选择列

database - 根据两行之间的差异选择列

问题描述

我正在研究一个看起来像这样的 PostgreSQL 数据库：

   +--------------+---------------+---------------+---------------+
   | avg_speed_p0 | avg_speed_p25 | avg_speed_p50 | avg_speed_p75 |
   +--------------+---------------+---------------+---------------+
1  |     85.15    |     87.23     |     84.16     |     85.44     |
2  |     78.63    |     82.76     |     78.01     |     83.15     |
   +--------------+---------------+---------------+---------------+

我计算了许多汽车在白天（1）和晚上（2）的平均速度，中间有 25 米。

我使用以下方法制作了表格：

create table avg_speeds as
   select * from avg_speed_day
      union
   select * from avg_speed_night

我试图通过以下方式制作主day键night：

alter table avg_speed_day add column time varchar

接着：

insert into avg_speed_day (time) values ('day')

但这不起作用，但只给了我：

   +--------------+---------------+---------------+---------------+--------+
   | avg_speed_p0 | avg_speed_p25 | avg_speed_p50 | avg_speed_p75 |  time  |
   +--------------+---------------+---------------+---------------+--------+
1  |     85.15    |     87.23     |     84.16     |     85.44     | [null] |
2  |     [null]   |     [null]    |     [null]    |     [null]    |   day  |
   +--------------+---------------+---------------+---------------+--------+

我想最终得到一个表格，计算白天（1）和夜晚（2）之间的差异，并且只显示差异大于 5 的一次。

像这样：

   +---------------+----------------+
   | diff_speed_p0 | diff_speed_p50 |
   +---------------+----------------+
1  |      6.52     |      6.15      |
   +---------------+----------------+

标签： databasepostgresql

解决方案

您的评论表明您从其他一些表或视图中收到这两行。

像这样直接使用这些：

WITH day   AS (SELECT avg_speed_p0, avg_speed_p25, avg_speed_p50, avg_speed_p75
               FROM avg_speed_day),
     night AS (SELECT avg_speed_p0, avg_speed_p25, avg_speed_p50, avg_speed_p75
               FROM avg_speed_night)
SELECT CASE WHEN abs(day.avg_speed_p0 - night.avg_speed_p0) > 5
            THEN day.avg_speed_p0 - night.avg_speed_p0
       END AS avg_speed_p0,
       CASE WHEN abs(day.avg_speed_p25 - night.avg_speed_p25) > 5
            THEN day.avg_speed_p25 - night.avg_speed_p25
       END AS avg_speed_p25,
       CASE WHEN abs(day.avg_speed_p50 - night.avg_speed_p50) > 5
            THEN day.avg_speed_p50 - night.avg_speed_p50
       END AS avg_speed_p50,
       CASE WHEN abs(day.avg_speed_p75 - night.avg_speed_p75) > 5
            THEN day.avg_speed_p75 - night.avg_speed_p75
       END AS avg_speed_p75
FROM day CROSS JOIN night;

SQL 中无法动态省略列。

推荐阅读