SQL - 组合不完整

Question

我正在使用 Oracle 10g。我有一个包含许多不同类型字段的表。这些字段包含特定站点在特定日期对特定事物进行的观察。

所以:

ItemID, Date, Observation1, Observation2, Observation3...

每个记录中大约有 40 个观察值。此时无法更改表结构。

不幸的是，并不是所有的观察都被填充(无论是意外还是因为该站点无法进行记录)。我需要将有关特定项目的所有记录合并到查询中的单个记录中，使其尽可能完整。

一个简单的方法来做到这一点就像

SELECT
    ItemID,
    MAX(Date),
    MAX(Observation1),
    MAX(Observation2)
    etc.
FROM
    Table
GROUP BY
    ItemID

但理想情况下，我希望它选择可用的最新观察结果，而不是最大值/最小值。我可以通过在表单中​​编写子查询来做到这一点

SELECT
    ItemID,
    ObservationX,
    ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY Date DESC) ROWNUMBER
FROM
    Table
WHERE
    ObservationX IS NOT NULL

并将所有 ROWNUMBER 1 连接在一起以获得 ItemID，但由于字段数量，这将需要 40 个子查询。

我的问题是是否有一种更简洁的方法来做到这一点，我很想念。

Answer 1

创建表和示例日期

SQL> create table observation(
  2    item_id number,
  3    dt      date,
  4    val1    number,
  5    val2    number );

Table created.

SQL> insert into observation values( 1, date '2011-12-01', 1, null );

1 row created.

SQL> insert into observation values( 1, date '2011-12-02', null, 2 );

1 row created.

SQL> insert into observation values( 1, date '2011-12-03', 3, null );

1 row created.

SQL> insert into observation values( 2, date '2011-12-01', 4, null );

1 row created.

SQL> insert into observation values( 2, date '2011-12-02', 5, 6 );

1 row created.

然后使用 KEEP关于 MAX 的条款带有 ORDER BY 的聚合函数将具有 NULL 观察值的行放在最后。您在 ORDER BY 中使用的任何日期需要早于表中最早的真实观察。

SQL> ed
Wrote file afiedt.buf

  1  select item_id,
  2         max(val1) keep( dense_rank last
  3                              order by (case when val1 is not null
  4                                             then dt
  5                                             else date '1900-01-01'
  6                                          end) ) val1,
  7         max(val2) keep( dense_rank last
  8                              order by (case when val2 is not null
  9                                             then dt
 10                                             else date '1900-01-01'
 11                                          end) ) val2
 12    from observation
 13*  group by item_id
SQL> /

   ITEM_ID       VAL1       VAL2
---------- ---------- ----------
         1          3          2
         2          5          6

我怀疑有比添加 CASE 更优雅的解决方案来忽略 NULL 值。声明 ORDER BY但 CASE完成工作。