如何将每日温度数据汇总为每周数据

Question

我想将每日温度数据聚合为每周数据。目前它使用三个SELECT语句。有没有办法把它们组合成一个？

CREATE TABLE dailyX (
    city    INTEGER,    -- Index of city
    day          DATE,                          -- Date in yyyymmdd format
    first        FLOAT,                         -- first temperature reading for the day
    high         BINARY_DOUBLE,                 -- High temperature
    low          BINARY_DOUBLE,                 -- Low temperature
    last        FLOAT,                          -- Last temperature reading for the day
    count   INTEGER                             -- Number of readings
);

该stmt通过查找一周的最高和最低温度，然后将读数的数量相加，将每日数据汇总到每周的时间框架中。

SELECT city,to_char(day,'iw'), max(high), min(low), sum(NVL(count,0))
           FROM dailyX
          WHERE day > to_date(20140101,'YYYYMMDD')
       GROUP BY city,to_char(day,'iw');

本周的第一次阅读通常是周一的第一次。

select p_city,p_week,p_first
           FROM (select city p_city, to_char(day,'iw') p_week,first p_first,
                        rank() over (partition by city,to_char(day,'iw') order by day ASC) as RNK from dailyX
                  where day > to_date(20140101,'YYYYMMDD'))
          where RNK=1;  

此stmt查找本周的最后一次读数，通常是周五的最后一个读数。

select p_city,p_week,p_last
           FROM (select city p_city, to_char(day,'iw') p_week,last p_last,
                        rank() over (partition by city,to_char(day,'iw') order by day DESC) as RNK from dailyX
                  where day > to_date(20140101,'YYYYMMDD'))
          where RNK=1;

Answer 1

您可以使用keep:

SELECT city, to_char(day,'iw'), max(high), min(low), sum(NVL(count,0)),
       max(first) keep (dense_rank first order by day) as first,
       max(last) keep (dense_rank last order by day) as last
FROM dailyX
WHERE day > to_date(20140101,'YYYYMMDD')
GROUP BY city, to_char(day,'iw');