如何用列中的NULL值交换最后的非NULL值？ [英] How to trade NULL value from a column for last non-NULL value?

问题描述

我正在实现一个不支持SQLite和PostgreSQL的查询。我的数据库有2个表，我想按日期排序，对传感器的值进行插值。我的表位于以下位置：

I'm implementing a query that should work no SQLite and PostgreSQL. My database have 2 tables that I want to order by date, interpolating the values of sensors. My tables ares de following:

Valor_Sensor_Pressão：

Valor_Sensor_Pressão:

ID_Sensor_Pressão |    Data_Medição    | Valor_Medido
1                  2016-01-01 10:20:05   13
1                  2016-01-01 10:20:06   11
1                  2016-01-01 10:20:12   20
1                  2016-01-01 10:20:13   0
1                  2016-01-01 10:21:05   100

Valor_Sensor_Temperatura：

Valor_Sensor_Temperatura:

ID_Sensor_Temperatura |    Data_Medição    | Valor_Medido
1                      2016-01-01 10:20:05   1
1                      2016-01-01 10:20:08   3
1                      2016-01-01 10:20:12   20
1                      2016-01-01 10:21:05   1
1                      2016-01-01 10:21:13   31

我使用以下SQL来按日期汇总和插值数据：

I've made the following SQL to aggregate and interpolate the data by dates:

WITH Resultado (ValorPressão, ValorTemperatura, DataMedida) AS (
    SELECT
        p.Valor_Medido AS pValue,
        t.Valor_Medido AS tValue,
        COALESCE(p.Data_Medição, t.Data_Medição) AS DataMedida
    FROM Valor_Sensor_Pressão AS p
        LEFT JOIN Valor_Sensor_Temperatura AS t USING (Data_Medição)
    WHERE p.ID_Sensor_Pressão = 1
    UNION ALL
    SELECT
        p.Valor_Medido AS pValue,
        t.Valor_Medido AS tValue,
        COALESCE(p.Data_Medição, t.Data_Medição) AS DataMedida
    FROM Valor_Sensor_Temperatura AS t
        LEFT JOIN Valor_Sensor_Pressão AS p USING (Data_Medição)
    WHERE
        p.Data_Medição IS NULL
        AND t.ID_Sensor_Temperatura = 1
    ORDER BY COALESCE(p.Data_Medição, t.Data_Medição)
)

SELECT DataMedida, ValorPressão, ValorTemperatura FROM Resultado

结果如下：

"2016-01-01 10:20:05"   "13"    "1"
"2016-01-01 10:20:06"   "11"    NULL
"2016-01-01 10:20:08"   NULL    "3"
"2016-01-01 10:20:12"   "20"    "20"
"2016-01-01 10:20:13"   "0"     NULL
"2016-01-01 10:21:05"   "100"   "1"
"2016-01-01 10:21:13"   NULL    "31"

我已阅读以下页面，并为将在SQL中使用的查询调整了解决方案ite和PostgreSQL：

I've read the following page and adapted a solution to a query that would be used in SQLite and PostgreSQL:

WITH Resultado (ValorPressão, ValorTemperatura, DataMedida) AS (
    SELECT
        p.Valor_Medido AS pValue,
        t.Valor_Medido AS tValue,
        COALESCE(p.Data_Medição, t.Data_Medição) AS DataMedida
    FROM Valor_Sensor_Pressão AS p
        left join Valor_Sensor_Temperatura t USING (Data_Medição)
    WHERE p.ID_Sensor_Pressão = 1
    UNION ALL
    SELECT
        p.Valor_Medido AS pValue,
        t.Valor_Medido AS tValue,
        COALESCE(p.Data_Medição, t.Data_Medição) AS DataMedida
    FROM Valor_Sensor_Temperatura AS t
        LEFT JOIN Valor_Sensor_Pressão AS p USING (Data_Medição)
    WHERE
        p.Data_Medição IS NULL
        AND t.ID_Sensor_Temperatura = 1
    ORDER BY COALESCE(p.Data_Medição, t.Data_Medição)
)

SELECT
    DataMedida,
    COALESCE(last_value(ValorPressão) OVER (order by DataMedida), 0) AS ValorPressão,
    COALESCE(last_value(ValorTemperatura) over (order by DataMedida), 0) AS ValorTemperatura
FROM Resultado

我不知道出了什么错误，因为LAST_VALUE并没有给我上一行的值，所以COALESCE将0代替了上一个值，而我找不到SQLite的IGNORE NULL的替代品。

I couldn't figure out what is the error, because the LAST_VALUE didn't give me the previous row value, so COALESCE put 0 instead the previous value, and I couldn't find a substitute to IGNORE NULLS for SQLite.

哪些修改将返回列的先前值而不是NULL？给我这样的东西：

What modifications will return the previous value of a column instead of NULL? Giving me something like this:

"2016-01-01 10:20:05"   "13"    "1"
"2016-01-01 10:20:06"   "11"    "1"
"2016-01-01 10:20:08"   "11"    "3"
"2016-01-01 10:20:12"   "20"    "20"
"2016-01-01 10:20:13"   "0"     "20"
"2016-01-01 10:21:05"   "100"   "1"
"2016-01-01 10:21:13"   "100    "31"

编辑：我将从其他表中获得的ID，因此我将WHERE选择为 1进行测试。

The IDs I will get from others table, so I put the WHERE selecting "1" just to test.

推荐答案

我已经解决了这个问题，我在LAG和LAST_VALUE上犯了一个错误。解决方案是将两者合并到最后一个COALESCE中，并添加FIRST_VALUE。

I've figure out the problem, I was making a mistake with LAG and LAST_VALUE. The solution was to merge the two inside the last COALESCE and adding FIRST_VALUE.

我在Valor_Sensor_Pressão中添加了新行，以检查第一行是否为NULL：

I added a new row in "Valor_Sensor_Pressão" to check when I got a NULL at the first row:

"1" "2016-01-01 10:20:00"   "5.5"

查询是：

WITH Resultado (ValorPressão, ValorTemperatura, DataMedida) AS (
    SELECT
        p.Valor_Medido AS pValue,
        t.Valor_Medido AS tValue,
        COALESCE(p.Data_Medição, t.Data_Medição) AS DataMedida
    FROM Valor_Sensor_Pressão AS p
        LEFT JOIN Valor_Sensor_Temperatura t USING (Data_Medição)
    WHERE p.ID_Sensor_Pressão = 1
    UNION ALL
    SELECT
        p.Valor_Medido AS pValue,
        t.Valor_Medido AS tValue,
        COALESCE(p.Data_Medição, t.Data_Medição) AS DataMedida
    FROM Valor_Sensor_Temperatura AS t
        LEFT JOIN Valor_Sensor_Pressão AS p USING (Data_Medição)
    WHERE
        p.Data_Medição IS NULL
        AND t.ID_Sensor_Temperatura = 1
    ORDER BY COALESCE(p.Data_Medição, t.Data_Medição)
)

SELECT
    DataMedida,
    COALESCE(LAST_VALUE(ValorPressão) OVER (ORDER BY DataMedida),LAG(ValorPressão) OVER (ORDER BY DataMedida), FIRST_VALUE(ValorPressão) OVER (ORDER BY DataMedida),0) AS ValorPressão,
    COALESCE(LAST_VALUE(ValorTemperatura) OVER (ORDER BY DataMedida), LAG(ValorTemperatura) OVER (ORDER BY DataMedida), FIRST_VALUE(ValorTemperatura) OVER (ORDER BY DataMedida),0) AS ValorTemperatura
FROM Resultado

我的结果是：

"2016-01-01 10:20:00"   "5.5"   "0"
"2016-01-01 10:20:05"   "13"    "1"
"2016-01-01 10:20:06"   "11"    "1"
"2016-01-01 10:20:08"   "11"    "3"
"2016-01-01 10:20:12"   "20"    "20"
"2016-01-01 10:20:13"   "0"     "20"
"2016-01-01 10:21:05"   "100"   "1"
"2016-01-01 10:21:13"   "100"   "31"

这篇关于如何用列中的NULL值交换最后的非NULL值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！