sql - 如果可用，从实时数据中获取温度，否则对历史数据取平均值

Question

我正在尝试获取旅行的实时温度，如果实时数据不可用，则从历史数据中获取平均温度。我用这些表格制作了我的问题的简单版本:

旅行

id  departure_time          arrival_time            location_id
1   2018-04-07 07:00:00     2018-04-14 17:00:00     1
2   2018-04-14 07:00:00     2018-04-21 17:00:00     1

位置

id      name
1       Location

天气

id  temperature date        location_id
1       20      2018-04-07  1
2       20      2018-04-08  1
3       20      2018-04-09  1
4       20      2018-04-10  1
5       20      2018-04-11  1
6       20      2018-04-12  1
7       20      2018-04-13  1
8       20      2018-04-14  1
9       15      2016-04-07  1
10      15      2016-04-08  1
11      15      2016-04-09  1
12      15      2016-04-10  1
13      15      2016-04-11  1
14      15      2016-04-12  1
15      15      2016-04-13  1
16      15      2016-04-14  1
17      19      2017-04-07  1
18      19      2017-04-08  1
19      19      2017-04-09  1
20      19      2017-04-10  1
21      19      2017-04-11  1
22      19      2017-04-12  1
23      19      2017-04-13  1
24      19      2017-04-14  1
25      15      2017-04-15  1
26      15      2017-04-16  1
27      15      2017-04-17  1
28      15      2017-04-18  1
29      15      2017-04-19  1
30      15      2017-04-20  1
31      15      2017-04-21  1
32      19      2016-04-15  1
33      19      2016-04-16  1
34      19      2016-04-17  1
35      19      2016-04-18  1
36      19      2016-04-19  1
37      19      2016-04-20  1
38      19      2016-04-21  1

我遇到的问题是，由于这些旅行是最后一分钟的旅行，我有下周内出发的旅行的“实时”数据。所以我想获得实时预报(如果有的话)，或者获得前几年温度的平均值。

http://sqlfiddle.com/#!17/bce59/3

这是我为尝试解决问题而采用的方法。

如果忘记了任何细节，请询问。

预期结果:

id  departure_time          arrival_time        location_id temperature
1   2018-04-07 07:00:00     2018-04-14 17:00:00         1           20
1   2018-04-07 07:00:00     2018-04-14 17:00:00         1           20
1   2018-04-07 07:00:00     2018-04-14 17:00:00         1           20
1   2018-04-07 07:00:00     2018-04-14 17:00:00         1           20
1   2018-04-07 07:00:00     2018-04-14 17:00:00         1           20
1   2018-04-07 07:00:00     2018-04-14 17:00:00         1           20
1   2018-04-07 07:00:00     2018-04-14 17:00:00         1           20
1   2018-04-07 07:00:00     2018-04-14 17:00:00         1           20
2   2018-04-14 07:00:00     2018-04-21 17:00:00         1           20
2   2018-04-14 07:00:00     2018-04-21 17:00:00         1           17
2   2018-04-14 07:00:00     2018-04-21 17:00:00         1           17
2   2018-04-14 07:00:00     2018-04-21 17:00:00         1           17
2   2018-04-14 07:00:00     2018-04-21 17:00:00         1           17
2   2018-04-14 07:00:00     2018-04-21 17:00:00         1           17
2   2018-04-14 07:00:00     2018-04-21 17:00:00         1           17
2   2018-04-14 07:00:00     2018-04-21 17:00:00         1           17

Answer 1

使用 generate_series 函数从子查询上的 trip 表创建日历。

然后 Left JOIN 在 dates 的子查询上你可能会得到匹配的 weather 你可以得到它的温度。如果 temperature 在 w.temperature 上为空，则获取 avg temperature

你可以试试这个。

SELECT  t.id,
        t.departure_time,
        t.arrival_time,
        l.id as "location_id",
        coalesce(w.temperature,(select FLOOR(avg(temperature)) from weather)) as "temperature"
FROM 
location l inner join 
(
  select id,
         location_id,
         departure_time,
         arrival_time,
         generate_series(departure_time :: timestamp,arrival_time::timestamp,'1 day'::interval) as dates
  from trip 
) t on t.location_id = l.id LEFT JOIN weather w on t.dates::date = w.date::date

sqlfiddle:http://sqlfiddle.com/#!17/bce59/48

编辑

您可以使用 CTE 查询按 year 获取 Avg 而不是 coalesce 函数中的子查询条款。

WITH weather_avg AS (
    SELECT floor(avg(a)) avgTemp 
    from
    ( 
      SELECT
        extract(YEAR from weather.date) AS YEAR,
        floor(avg(weather.temperature)) a
      FROM weather
      group by extract(YEAR from weather.date) 
    ) t
)
SELECT  t.id,
        t.departure_time,
        t.arrival_time,
        t.location_id as "location_id",
        coalesce(w.temperature,(select avgTemp from weather_avg)) as "temperature"
FROM 
(
  select t.id,
         t.location_id,
         t.departure_time,
         t.arrival_time,
         generate_series(departure_time :: timestamp,arrival_time::timestamp,'1 day'::interval) as dates
  from trip  t inner join location l on t.location_id = l.id
) t LEFT JOIN weather w 
on t.dates::date = w.date::date

sqlfiddle:http://sqlfiddle.com/#!17/bce59/76