SQL-合并不完整 [英] SQL - Combining incomplete

问题描述

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

I'm using Oracle 10g. I have a table with a number of fields of varying types. The fields contain observations that have been made by made about a particular thing on a particular date by a particular site.

所以:

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

每个记录中大约有40个观察值.表结构目前无法更改.

There are about 40 Observations in each record. The table structure cannot be changed at this point in time.

不幸的是，并非所有观测值都已填充(偶然或由于站点无法进行该记录).我需要将有关特定项目的所有记录合并到查询中的单个记录中，以使其尽可能完整.

Unfortunately not all the Observations have been populated (either accidentally or because the site is incapable of making that recording). I need to combine all the records about a particular item into a single record in a query, making it as complete as possible.

一种简单的方法是

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

但是理想情况下，我希望它选择可用的最新观察值，而不是最大值/最小值.我可以通过编写以下形式的子查询来做到这一点

But ideally I would like it to pick the most recent observation available, not the max/min value. I could do this by writing sub queries in the form

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个子查询.

And joining all the ROWNUMBER 1s together for an ItemID but because of the number of fields this would require 40 subqueries.

我的问题是我是否缺少一种更简洁的方法来做到这一点.

My question is whether there's a more concise way of doing this that I'm missing.

推荐答案

创建表格和示例日期

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.

，然后将MAX聚合函数上的KEEP子句与ORDER BY一起使用，该子句将带有NULL观察值的行放在末尾.您在ORDER BY中使用的任何日期都必须早于表中最早的实际观测值.

And then use the KEEP clause on the MAX aggregate function with an ORDER BY that puts the rows with NULL observations at the end. whatever date you use in the ORDER BY needs to be earlier than the earliest real observation in the table.

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语句添加到ORDER BY更好的方法来忽略NULL值，但是CASE可以完成工作.

I suspect that there is a more elegant solution to ignore the NULL values than adding the CASE statement to the ORDER BY but the CASE gets the job done.

这篇关于SQL-合并不完整的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！