从第二个表中查找相关值而不依赖于PL / SQL

I have the following two tables in my database:

我的数据库中有以下两个表:

a) A table containing values acquired at a certain date (you may think of these as, say, temperature readings):

a)包含在特定日期获得的值的表格(您可以将这些值视为温度读数):

  sensor_id |       acquired      | value
 ----------+---------------------+--------
 1         | 2009-04-01 10:00:00 | 20
 1         | 2009-04-01 10:01:00 | 21
 1         | 2009-04 01 10:02:00 | 20
 1         | 2009-04 01 10:09:00 | 20
 1         | 2009-04 01 10:11:00 | 25
 1         | 2009-04 01 10:15:00 | 30
 ...

The interval between the readings may differ, but the combination of (sensor_id, acquired) is unique.

读数之间的间隔可能不同,但(sensor_id,获取)的组合是唯一的。

b) A second table containing time periods and a description (you may think of these as, say, periods when someone turned on the radiator):

b)包含时间段和描述的第二个表(您可以将这些视为有人打开散热器的时间段):

  sensor_id |      start_date     |       end_date      | description
 ----------+---------------------+---------------------+------------------
 1         | 2009-04-01 10:00:00 | 2009-04-01 10:02:00 | some description
 1         | 2009-04-01 10:10:00 | 2009-04-01 10:14:00 | something else

Again, the length of the period may differ, but there will never be overlapping time periods for any given sensor.

同样,周期的长度可能不同,但对于任何给定的传感器,永远不会有重叠的时间段。

I want to get a result that looks like this for any sensor and any date range:

我希望得到任何传感器和任何日期范围的结果:

 sensor id |     start date      | v1 |       end date      | v2 | description
----------+---------------------+----+---------------------+----+------------------
1         | 2009-04-01 10:00:00 | 20 | 2009-04-01 10:02:00 | 20 | some description
1         | 2009-04-01 10:10:00 | 25 | 2009-04-01 10:14:00 | 30 | some description

Or in text from: given a sensor_id and a date range of range_start and range_end, find me all time periods which have overlap with the date range (that is, start_date < range_end and end_date > range_start) and for each of these rows, find the corresponding values from the value table for the time period's start_date and end_date (find the first row with acquired > start_date and acquired > end_date).

或者在文本中:给定sensor_id和日期范围range_start和range_end,找到与日期范围重叠的所有时间段(即start_date range_start),并为每个行找到时间段start_date和end_date的值表中的相应值(找到具有获取的> start_date和acquire> end_date的第一行)。和end_date>

If it wasn't for the start_value and end_value columns, this would be a textbook trivial example of how to join two tables.

如果它不是start_value和end_value列,那么这将是一个关于如何连接两个表的教科书的简单示例。

Can I somehow get the output I need in one SQL statement without resorting to writing a PL/SQL function to find these values?

我可以以某种方式在一个SQL语句中获得我需要的输出,而无需编写PL / SQL函数来查找这些值吗?

Unless I have overlooked something blatantly obvious, this can't be done with simple subselects.

除非我忽略了一些显而易见的东西,否则这不能用简单的子选择来完成。

Database is Oracle 11g, so any Oracle-specific features are acceptable.

数据库是Oracle 11g,因此任何特定于Oracle的功能都是可以接受的。

Edit: yes, looping is possible, but I want to know if this can be done with a single SQL select.

编辑:是的,循环是可能的,但我想知道是否可以使用单个SQL选择来完成。

2 个解决方案

#1

You can give this a try. Note the caveats at the end though.

你可以尝试一下。请注意最后的警告。

SELECT
    RNG.sensor_id,
    RNG.start_date,
    RDG1.value AS v1,
    RNG.end_date,
    RDG2.value AS v2,
    RNG.description
FROM
    Ranges RNG
INNER JOIN Readings RDG1 ON
    RDG1.sensor_id = RNG.sensor_id AND
    RDG1.acquired => RNG.start_date
LEFT OUTER JOIN Readings RDG1_NE ON
    RDG1_NE.sensor_id = RDG1.sensor_id AND
    RDG1_NE.acquired >= RNG.start_date AND
    RDG1_NE.acquired < RDG1.acquired
INNER JOIN Readings RDG2 ON
    RDG2.sensor_id = RNG.sensor_id AND
    RDG2.acquired => RNG.end_date
LEFT OUTER JOIN Readings RDG1_NE ON
    RDG2_NE.sensor_id = RDG2.sensor_id AND
    RDG2_NE.acquired >= RNG.end_date AND
    RDG2_NE.acquired < RDG2.acquired
WHERE
    RDG1_NE.sensor_id IS NULL AND
    RDG2_NE.sensor_id IS NULL

This uses the first reading after the start date of the range and the first reading after the end date (personally, I'd think using the last date before the start and end would make more sense or the closest value, but I don't know your application). If there is no such reading then you won't get anything at all. You can change the INNER JOINs to OUTER and put additional logic in to handle those situations based on your own business rules.

这使用了范围开始日期之后的第一个读数和结束日期之后的第一个读数(个人而言,我认为使用开始和结束之前的最后日期会更有意义或最接近的值,但我不是知道你的申请)。如果没有这样的阅读,那么你根本就得不到任何东西。您可以将INNER JOIN更改为OUTER,并根据您自己的业务规则添加其他逻辑来处理这些情况。

#2

It seems pretty straight forward.

看起来很简单。

Find the sensor values for each range. Find a row - I will call acquired of this row just X - where X > start_date and not exists any other row with acquired > start_date and acquired < X. Do the same for end date.

找到每个范围的传感器值。找到一行 - 我将调用此行的获取只是X - 其中X> start_date并且不存在任何其他具有获取的> start_date且获得的行。对于结束日期执行相同的操作。
Select only the ranges that meet the query - start_date before and end_date after the dates supplied by the query.

仅选择符合查询的范围 - 查询提供的日期之前的start_date和end_date。

In SQL this would be something like that.

在SQL中,这将是这样的。

SELECT R1.*, SV1.aquired, SV2.aquired
FROM ranges R1
INNER JOIN sensor_values SV1 ON SV1.sensor_id = R1.sensor_id
INNER JOIN sensor_values SV2 ON SV2.sensor_id = R1.sensor_id  
WHERE SV1.aquired > R1.start_date
AND NOT EXISTS (
    SELECT *
    FROM sensor_values SV3
    WHERE SV3.aquired > R1.start_date
    AND SV3.aquired < SV1.aquired)
AND SV2.aquired > R1.end_date
AND NOT EXISTS (
    SELECT *
    FROM sensor_values SV4
    WHERE SV4.aquired > R1.end_date
    AND SV4.aquired < SV2.aquired)
AND R1.start_date < @range_start
AND R1.end_date > @range_end

#1