使用SQL插值大数据值

Question

我对SQL和大数据的了解还比较浅显，所以请耐心等待。我有一张包含一整年天气数据的表格，其中我关注每小时的温度和降水量（大约9000个值），但问题在于气象站只记录6小时的一次降水量。因此，我想插值得到其他时间点的数值，但我不确定如何做。任何帮助都将不胜感激！以下是表格的一部分：

年份 月份 日 小时 温度 降水量 2014 1 1 0 -16.5444 0 2014 1 1 1 -10.3455 ***** 2014 1 1 2 -5.34855 ***** 2014 1 1 3 -15.4569 ***** 2014 1 1 4 -4.45666 ***** 2014 1 1 5 -11.2344 ***** 2014 1 1 6 -13.3454 0 2014 1 1 7 -20.2334 ***** 2014 1 1 8 -2.48555 ***** 2014 1 1 9 -5.43554 ***** 2014 1 1 10 -1.34555 ***** 2014 1 1 11 -9.00333 ***** 2014 1 1 12 -6.43555 0.04 2014 1 1 13 -12.3423 ***** 2014 1 1 14 -8.56664 ***** 2014 1 1 15 -15.3498 ***** 2014 1 1 16 -19.2384 ***** 2014 1 1 17 -21.2348 ***** 2014 1 1 18 -23.8778 0.31 2014 1 1 19 -20.8374 ***** 2014 1 1 20 -25.3874 ***** 2014 1 1 21 -21.3445 ***** 2014 1 1 22 -27.4599 ***** 2014 1 1 23 -30.9384 ***** 2014 1 2 0 -25.9085 0.15

- paulkariya

---

你有机会尝试我的答案了吗？ - jpw

Answer 1

如果你想要的输出是：

Year        Month       Day         hour        Precipitation_interpolated
----------  ----------  ----------  ----------  --------------------------
2014        1           1           0           0.0
2014        1           1           1           0.0
2014        1           1           2           0.0
2014        1           1           3           0.0
2014        1           1           4           0.0
2014        1           1           5           0.0
2014        1           1           6           0.0
2014        1           1           7           0.00666666666666667
2014        1           1           8           0.0133333333333333
2014        1           1           9           0.02
2014        1           1           10          0.0266666666666667
2014        1           1           11          0.0333333333333333
2014        1           1           12          0.04
2014        1           1           13          0.085
2014        1           1           14          0.13
2014        1           1           15          0.175
2014        1           1           16          0.22
2014        1           1           17          0.265
2014        1           1           18          0.31
2014        1           1           19          0.283333333333333
2014        1           1           20          0.256666666666667
2014        1           1           21          0.23
2014        1           1           22          0.203333333333333
2014        1           1           23          0.176666666666667
2014        1           2           0           0.15

然后下面的查询将会生成相应结果。由于依赖许多子查询进行行编号/分区（SQLite没有原生支持），并且使用了通用表达式，因此它可能不是很高效。通用表达式从3.8.3版本开始引入（发布于2014年2月3日）。如果您使用的是旧版本，则需要升级或重写查询以不使用通用表达式 - 这应该是可能的，但我没有尝试过。

查询使用一个名为t的表作为源；它仅在第一个select语句的from子句中被引用，并且我假设你示例数据中的*****是null值的占位符：

with src as (
    select year, month, day, hour, precipitation, strftime('%Y-%m-%d %H:%M:%S', year ||'-'|| case when length(month) = 1 then 0||month else month end || '-' ||case when length(day) = 1 then 0||day else day end || case when length(hour) = 1 then 0||hour else hour end ||':00:00') as dt from t
), sample_data as (
        select year, month, day, hour, dt, Precipitation, (select count(*) from src b where a.dt >= dt) as id from src a 
), value_rows as (
    select id, Precipitation,
    (select count(*) from src b where val.dt >= dt and Precipitation is not null ) rn
    from (
        select id, Precipitation, dt
        from sample_data
        where Precipitation is not null
    ) val
), step_change as (
    select c1.id id_Start
        , c2.id - 1 id_End
        , c1.Precipitation
        , (c2.Precipitation - c1.Precipitation)/(c2.id - c1.id) change
    From value_rows c1
    inner join value_rows c2
    on c1.rn = c2.rn - 1
), interpolated_values as (
    select s.id
        , s.Year
        , s.Month
        , s.Day
        , s.Hour
        , s.Precipitation as value1
        , coalesce(sc.Precipitation, s.Precipitation) Precipitation
        , coalesce(sc.change, 0) change
        , coalesce((select count(*) from src b where s.dt >= dt) - id_Start, 0) coeff
        , sc.id_Start ,sc.id_End
    from sample_data s
    left outer join step_change sc
    on s.id between sc.id_Start and sc.id_End
) 

select Year, Month, Day, hour, Precipitation + coeff * change Precipitation_interpolated
from interpolated_values
order by id;

我从 SQL Server MSDN 论坛上找到了一个 T-SQL 查询，然后将其拼凑成了 SQLite 版本（在这里）。

- jpw

Answer 2

我没有看到快速的方法，但我看到了一种方法。

以下是您需要的内容：

2个新表，2个填充表格的过程和1个在实际表格中进行插值的过程。

按照以下3个步骤执行：

1：在第一个过程中，您需要浏览天气表并收集所有降水量（仅每6小时一次的降水量而不是 *****），并将它们插入到第一个表中

2：在第二个过程中，您需要填充插值值的第二个表格。在这里，您将不得不做一些数学来计算要插入的数字

3：在最后一个过程中，您需要浏览您的天气表，并用第2个表格中相应的数字替换每个 ***** 值（在步骤2中填充）。

要执行此操作，您需要在您的过程中使用游标。完成插值后，您可以删除第1步和第2步创建的2个表。

我希望这足够清晰。可能有更简单的方法，但我不知道。如果有任何不清楚的地方，请在评论中要求精度，我会编辑我的答案。

- micbobo

---

如果您无法访问存储过程但可以访问PHP，则可以在步骤1和2中使用列表或数组代替表格。@paulkariya - micbobo

这可能确实可行，但它并没有解决我的问题，即计算数千个插值值。一定有另一种方法... - paulkariya

我认为在线性插值的情况下是可以的。假设你需要计算降水量13到17的值。第一步，你收集了降水12和18的值，分别为0.04和0.31。如果你做0.31-0.04，你会得到0.27，然后你将其除以6（得到中间的5个降水量），你会得到0.045。因此，降水量13是0.04 + 0.045 = 0.085。这些计算可以很容易地在循环中完成，在思考一段时间后@paulkariya。 - micbobo

先前评论提到的值，如0.085，表示步骤2所需的值。 - micbobo

我之前提到了PHP，因为这是我看到Sqlite主要被用于的语言，但它也可以在任何其他编程语言中完成。 - micbobo