代码之家 › 专栏 › 技术社区 › John Nilsson

具有“相关”子查询的高效联接

oracle sql

John Nilsson · 技术社区 · 17 年前

给定Oracle中的三个表日期(date aDate、doUse boolean)、天(rangeId int、day int、qty int)和范围(rangeId int、startDate date)

给定一个范围,可以这样做

SELECT rangeId, aDate, CASE WHEN doUse = 1 THEN qty ELSE 0 END AS qty
FROM (
    SELECT aDate, doUse, SUM(doUse) OVER (ORDER BY aDate) day
    FROM Dates 
    WHERE aDate >= :startDAte
) INNER JOIN (
    SELECT rangeId, day,qty
    FROM Days
    WHERE rangeId = :rangeId
) USING (day)
ORDER BY day ASC

我要做的是查询范围中的所有范围,而不仅仅是一个。

请记住,Dates表非常庞大,因此我希望避免从表中的第一个日期计算day值,而每个范围的Days不应超过100天左右。

Dates                            Days
aDate        doUse               rangeId     day     qty
2008-01-01   1                   1           1       1
2008-01-02   1                   1           2       10
2008-01-03   0                   1           3       8
2008-01-04   1                   2           1       2
2008-01-05   1                   2           2       5

Ranges
rangeId      startDate
1            2008-01-02
2            2008-01-03


Result
rangeId      aDate        qty
1            2008-01-02   1
1            2008-01-03   0
1            2008-01-04   10
1            2008-01-05   8
2            2008-01-03   0
2            2008-01-04   2
2            2008-01-05   5

2 回复 | 直到 17 年前

Quassnoi 17 年前

试试这个:

SELECT  rt.rangeId, aDate, CASE WHEN doUse = 1 THEN qty ELSE 0 END AS qty
FROM    (
    SELECT  *
    FROM    (
        SELECT  r.*, t.*, SUM(doUse) OVER (PARTITION BY rangeId ORDER BY aDate) AS span
        FROM    (
            SELECT  r.rangeId, startDate, MAX(day) AS dm
            FROM    Range r, Days d
            WHERE   d.rangeid = r.rangeid
            GROUP BY
                r.rangeId, startDate
            ) r, Dates t
        WHERE   t.adate >= startDate
        ORDER BY
            rangeId, t.adate
        )
    WHERE
        span <= dm
    ) rt, Days d
WHERE   d.rangeId = rt.rangeID
    AND d.day = GREATEST(rt.span, 1)

顺便说一句,在我看来,保存所有这些的唯一一点是 Dates 在数据库中是要得到一个连续的日历与假日标记。

SELECT :startDate + ROWNUM
FROM   dual
CONNECT BY
       1 = 1
WHERE  rownum < :length

只保留假期 . 一个简单的连接将显示 日期 是假期,不是。

John Nilsson 17 年前

好吧,也许我找到了一个方法。像这样的事情:

SELECT irangeId, aDate + sum(case when doUse = 1 then 0 else 1) over (partionBy rangeId order by aDate) as aDate, qty
FROM Days INNER JOIN (
    select irangeId, startDate + day - 1 as aDate, qty
    from Range inner join Days using (irangeid)
) USING (aDate)