计算动态最小值的SQL查询。

I got an sql issue. I have two tables which look like this:

我遇到一个sql问题。我有两张桌子是这样的:

first TABLE X    second TABLE Y

TabX_ID|  DATE | Value Z    TabY_ID|TabX_ID | DATE | Value X | Value Y
4711   | 15.01 |    12         1   | 4711   | 15.01|  123    |  876
4711   | 20.01 |    5          2   | 4711   | 16.01|  12     |  54
4711   | 25.01 |    67         3   | 4711   | 17.01|  23     |  38
                               4   | 4711   | 20.01|  56     |  13
                               5   | 4711   | 23.01|  1      |  5

I need to assing all the data from TABLE Y to the data in the TABLE X DATE to the fitting timeframe.

我需要收集表Y到表X中的数据到拟合的时间框架。

I cant use a simple min - max because it changes.

我不能使用简单的最小值，因为它会改变。

1. DATE min 15.01 DATE-max:19.01
2. DATE-min:20.01 DATE-max:24.01
3. DATE-min:25.01 DATE-max:...

So it looks like this

它是这样的

                     1 | 15.01 | 123 | 876
4711 | 15.01 | 12 -> 2 | 16.01 | 12  | 54    
                     3 | 17.01 | 23  | 38     

4711 | 20.01 | 5   -> 4 | 20.01 | 56  | 13
                      5 | 23.01 | 1   | 5

First I need to perform calculations with the TABLE Y VALUES X an Y and after that I need the VALUE Z from TABLE X. So it looks like this:

首先，我需要使用表Y值X an Y进行计算，然后需要表X的值Z。

 ID  | DATE  | Calculated_Val
 4711| 15.01 | 345
 4711| 20.01 | 892

Is there a way to do this?

有办法吗?

thx in advance

谢谢提前

2 个解决方案

#1

Not sure about MySQL but if you are doing this with Oracle, I would use the LEAD analytic function to get the next date value in the future in tableX and then join that to tableY.

我不太清楚MySQL，但如果你用Oracle做这个，我将使用LEAD analysis函数在tableX中获取下一个日期值，然后加入到tableY中。

An example of this would be:

这方面的一个例子是:

select
  tabX_id,
  date_val as min_date,
  next_date_val as max_date,
  valueZ,
  valueX,
  valueY,
  y.date_val as tabY_date
from (
  select
    tabX_id,
    date_val,
    lead(date_val) over (partition by tabx_id order by date_val) 
      as next_date_val,
    valueZ
  from tabX
) x
join tabY y on (x.tabX_id = y.tabX_id and 
                y.date_val >= x.date_val and 
                (x._next_date_val is null or y.date_val < x.next_date_val))

Note that I haven't modified the next value of the date so am using a less-than condition. This is probably appropriate if you have a time component in any of the date fields but might not be exactly what you want if they are just date value.

注意，我没有修改日期的下一个值，所以使用小于条件。如果您在任何日期字段中都有一个时间组件，这可能是适当的，但是如果它们只是日期值，则可能不是您想要的。

#2

This is a simple join and group by:

这是一个简单的join和group by:

 select x.TabX_ID, y.DATE, min(ValueX), min(ValueY)
 from TableX x
  join TableY y
    on x.TabX_ID = y.TabX_ID
   and x.DATE = y.DATE
 group by x.TabX_ID, y.DATE

#1