我不确定将问题称为"以前的有效值"是否合适。事情是这样的:
我有一个表"A":
create table A (
name varchar(16),
performanceDate date,
value int
);
名称和性能日期都是主键。
有一个过程将每天为每个用户插入数据。因此,数据将如下所示:
select * from A;
|------+-----------------+-------|
| name | performanceDate | value |
|------+-----------------+-------|
| Joe | 2012-05-18 | null |
| Joe | 2012-05-17 | 2 |
| Joe | 2012-05-16 | null |
| Joe | 2012-05-15 | null |
| Joe | 2012-05-14 | 3 |
|------+-----------------+-------|
目前,我想获得一个性能的结果集日期介于 2012-05-16 和 2012-05-18 之间 如果当前日期的值为 null,则应将其替换为以前的有效值。 例如,2012-05-16 的值为 null,2012-05-16 之前的第一个有效值在 2012-05-14 上3
。 这样,结果如下所示:
|------+-----------------+-------|
| name | performanceDate | value |
|------+-----------------+-------|
| Joe | 2012-05-18 | 2 |
| Joe | 2012-05-17 | 2 |
| Joe | 2012-05-16 | 3 |
|------+-----------------+-------|
到目前为止,我计划先将数据插入临时表中(因为表"A"对我来说是只读的(,然后逐个更新值。 但这种方式非常缓慢。 你对此有什么想法吗?
您可以使用 LATERAL JOIN,DB2 尚未在 SQLFiddle 上,以下是它在 SQL Server 中的等效项。LATERAL 等同于 SQL Server 的 APPLY:
select x.name, x.performanceDate, coalesce(x.value, y.value) as value
from tbl x
outer apply
(
-- find nearest
select top 1 value
from tbl
where
x.value is null
and
(
name = x.name
and value is not null
and performanceDate < x.performanceDate
)
order by performanceDate desc
) as y
order by x.name, x.performanceDate desc
数据:
| NAME | PERFORMANCEDATE | VALUE |
|------|----------------------------|--------|
| Joe | May, 18 2012 08:00:00-0700 | (null) |
| Joe | May, 17 2012 08:00:00-0700 | 2 |
| Joe | May, 16 2012 08:00:00-0700 | (null) |
| Joe | May, 15 2012 08:00:00-0700 | (null) |
| Joe | May, 14 2012 08:00:00-0700 | 3 |
输出:
| NAME | PERFORMANCEDATE | VALUE |
|------|----------------------------|-------|
| Joe | May, 18 2012 08:00:00-0700 | 2 |
| Joe | May, 17 2012 08:00:00-0700 | 2 |
| Joe | May, 16 2012 08:00:00-0700 | 3 |
| Joe | May, 15 2012 08:00:00-0700 | 3 |
| Joe | May, 14 2012 08:00:00-0700 | 3 |
现场测试:http://www.sqlfiddle.com/#!6/e0158/8
基于 IBM 文档.. http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafztabref.htm
..,我希望这是:
select x.name, x.performanceDate, coalesce(x.value, y.value) as value
from tbl x,
lateral
(
-- find nearest
select top 1 value
from tbl
where
x.value is null
and
(
name = x.name
and value is not null
and performanceDate < x.performanceDate
)
order by performanceDate desc
fetch first 1 rows only
) as y
order by x.name, x.performanceDate desc
关于交叉应用/外部应用的有趣指标:
http://explainextended.com/2009/07/16/inner-join-vs-cross-apply/OUTER APPLY使用的另一个例子:http://www.ienablemuch.com/2012/04/outer-apply-walkthrough.html
with tmp1(name, performanceDate, value, cid) as (
select
name,
performanceDate,
value,
count(value) over (partition by name
order by performanceDate,
value nulls last) as cid from A),
tmp2(name, performanceDate, value) as (
select
name,
performanceDate,
first_value(value) over (partition by name, cid
order by performanceDate,
value nulls last from tmp1)
select * from tmp2;
with tmp1 (name, performanceDate_Begin, performanceDate_End, value) as (
select
name,
performanceDate as performanceDate_Begin,
lead(performanceDate) over (partition by name
order by performanceDate,
value nulls last) as performanceDate_End,value from A where value is not null),
tmp2 (name, performanceDate, value) as (
select
A.name,
A.performanceDate,
tmp1.value
from A
left join tmp1 on A.name = B.name
and A.performanceDate >= tmp1.performanceDate_Begin
and A.performanceDate < coalesce(tmp1.performanceDate_End,date '9999-12-31'))
select * from tmp2