在子查询中使用全局临时表会得到NULL

Question

我正在使用以下查询。

SELECT  SYSDATE,(SELECT P_PRICE_OPEN FROM GTT_ADJ_PRICE_TABLE WHERE FSYM_ID='P8R3C2-R' AND P_DATE='22-OCT-18' AND P_VOLUME<>0 AND ROWNUM=1) FROM DUAL;

GTT_ADJ_PRICE_TABLE是一个全局临时表，当我执行了与它相关的函数时，它会加载值。这个GTT在提交后保留行。这个查询给了我正确的结果。

但是，如果我运行查询

SELECT WEEK_END, WEEK_START, 
(SELECT P_PRICE_OPEN FROM GTT_ADJ_PRICE_TABLE WHERE P_DATE=WEEK_START) AS WEEKS_OPEN_PRICE,
(SELECT MAX(P_PRICE_HIGH) FROM GTT_ADJ_PRICE_TABLE WHERE FSYM_ID=FID AND P_DATE<=WEEK_END 
AND P_DATE>=WEEK_START AND P_VOLUME<>0) AS WEEKLY_HIGH,
(SELECT MIN(P_PRICE_LOW) FROM GTT_ADJ_PRICE_TABLE WHERE FSYM_ID=FID AND P_DATE<=WEEK_END 
AND P_DATE>=WEEK_START AND P_VOLUME<>0) AS WEEKLY_LOW,
(SELECT SUM(P_VOLUME) FROM GTT_ADJ_PRICE_TABLE WHERE FSYM_ID=FID AND P_DATE<=WEEK_END 
AND P_DATE>=WEEK_START AND P_VOLUME<>0) AS WEEKLY_VOLUME,
P_PRICE 
FROM (
SELECT ROWNUM,FID,WEEK_END,P_VOLUME,P_PRICE,
(SELECT P_DATE FROM FP_V2_FP_BASIC_PRICES WHERE FSYM_ID=FID AND P_DATE>=TRUNC(WEEK_END, 'IW') AND P_VOLUME<>0 AND ROWNUM=1) AS WEEK_START
FROM (
SELECT 
ROWNUM,FSYM_ID AS FID, WEEK_END,P_VOLUME, P_PRICE,P_PRICE_OPEN,P_PRICE_HIGH,P_PRICE_LOW
FROM (
SELECT ROWNUM,FSYM_ID,P_DATE AS WEEK_END, P_PRICE,P_VOLUME, P_PRICE_OPEN,P_PRICE_HIGH,P_PRICE_LOW,
CASE 
WHEN (TO_CHAR(P_DATE,'D') >= AVG(TO_CHAR(P_DATE,'D')) OVER (order by P_DATE DESC rows between 1 preceding and current row) and ROWNUM>=1)  or TO_CHAR(P_DATE,'D')=6
THEN 1
ELSE 0
END AS WEEKFLAG
FROM(
SELECT * FROM TABLE (ADJUSTED_PRICE('P8R3C2-R')) WHERE P_VOLUME<>0
) 
)WHERE WEEKFLAG=1
)
);

它在WEEKS_OPEN_PRICE、WEEKLY_HIGHT、WEEKY_LOW和WEEKLY_VOLUME中为NULL。请帮我解决这个问题。期待中的感谢。

Answer 1

尝试使用WITH子句。我希望下面的查询有效。我没有检查的可能。

WITH adjustedPrice AS (
SELECT *
FROM TABLE(adjusted_price('P8R3C2-R'))
WHERE p_volume <> 0
AND TO_CHAR(p_date, 'DY') NOT IN ('SAT', 'SUN') 
)
SELECT ap.week,
ap.week_start,
ap.week_end,
ap.weeks_high_price,
ap.weeks_low_price,
ap.weekly_volume,
ws.p_price_open AS weeks_open_price,
we.p_price      AS weeks_close_price
FROM (
SELECT TRUNC(p_date, 'IW') AS week,
TRUNC(MIN(p_date))  AS week_start,
TRUNC(MAX(p_date))  AS week_end,
MAX(p_price_high)   AS weeks_high_price,
MIN(p_price_low)    AS weeks_low_price,
SUM(p_volume)       AS weekly_volume
FROM adjustedPrice
GROUP BY TRUNC(p_date, 'IW')
) ap
INNER JOIN adjustedPrice ws ON ws.p_date = ap.week_start 
INNER JOIN adjustedPrice we ON we.p_date = ap.week_end
ORDER BY week DESC;

Answer 2

想作为评论发布。但它不适合那里，这就是为什么写它作为一个答案。

我试着用这个代码来获取周开始、周结束、周高价、周低价、周成交量。

select
trunc("P_DATE", 'IW') as week,
min(trunc("P_DATE")) as week_start,
max(trunc("P_DATE")) as week_end,
MAX(P_PRICE_HIGH) AS WEEKS_HIGH_PRICE,
MIN(P_PRICE_LOW) AS WEEKS_LOW_PRICE,
SUM(P_VOLUME) AS WEEKLY_VOLUME
from TABLE 
(ADJUSTED_PRICE('P8R3C2-R')) 
WHERE 
P_VOLUME<>0 AND to_char("P_DATE", 'DY') not in ('SAT','SUN')
group by 
trunc("P_DATE", 'IW')
ORDER BY 
trunc("P_DATE", 'IW') DESC;

这给了我不到2-4秒的成绩。但我想得到日期等于WEEK_START的周开盘价和日期等于WEEK_END的周收盘价。

我试着用同样的方法。但是它花费了太多的时间(比如300多秒(。

SELECT 
WEEK,WEEK_START, WEEK_END, WEEKS_HIGH_PRICE,WEEKS_LOW_PRICE,WEEKLY_VOLUME,
(SELECT P_PRICE_OPEN FROM TABLE  (ADJUSTED_PRICE('P8R3C2-R')) WHERE P_VOLUME<>0 AND P_DATE=WEEK_START) AS WEEKS_OPEN_PRICE,
(SELECT P_PRICE FROM TABLE  (ADJUSTED_PRICE('P8R3C2-R')) WHERE P_VOLUME<>0 AND P_DATE=WEEK_END) AS WEEKS_CLOSE_PRICE
FROM
(
select
trunc("P_DATE", 'IW') as week,
min(trunc("P_DATE")) as week_start,
max(trunc("P_DATE")) as week_end,
MAX(P_PRICE_HIGH) AS WEEKS_HIGH_PRICE,
MIN(P_PRICE_LOW) AS WEEKS_LOW_PRICE,
SUM(P_VOLUME) AS WEEKLY_VOLUME
from TABLE 
(ADJUSTED_PRICE('P8R3C2-R')) 
WHERE 
P_VOLUME<>0 AND to_char("P_DATE", 'DY') not in ('SAT','SUN')
group by 
trunc("P_DATE", 'IW')
ORDER BY 
trunc("P_DATE", 'IW') DESC
);

如果有人能帮助我提高输出时间，我们将不胜欢迎