如何用列中的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屋!