需求
我想在Oracle SQL中创建一个如下表:
COUNTRY NAME WMWHSE_ID DATE
US CRD2 1 040620
GR WAZ 2 040620
CN KOL 3 040620
FR DEL 4 040620
... ...
US CRD2 1 030620
GR WAZ 2 030620
CN KOL 3 030620
FR DEL 4 030620
... ...
WMWHSE_ID中的每个仓库都将打印一个DATE列为今天的日期,并且DATE =昨天,前天,前天...等将重复此操作,直到1周前。我总共有124个仓库,所以总共124 * 7 = 868行。
===
查询块
以下是我的查询。它使用CONNECT BY LEVEL <=实现7个日期(理论上)
select
SUBSTR(db_alias, 1, 2) AS COUNTRY,
db_alias as NAME,
To_Number(Regexp_Replace(Db_Logid, '[^0-9]', '')) As Wmwhse_Id,
to_char(sysdate, 'yyyyMMdd')+1-level as ACTDATE
from wmsadmin.pl_db, dual where db_alias not like '%BPV' and db_alias not like 'PRDO%' and db_alias not like 'ENTERPRISE'
connect by level <=7
order by ACTDATE desc, WMWHSE_ID asc
(需要使用GROUP BY,因为没有它,表看起来像:)
COUNTRY NAME WMWHSE_ID DATE
US CRD2 1 040620
GR WAZ 2 040620
CN KOL 3 040620
FR DEL 4 040620
... ...
US CRD2 1 030620
US CRD2 1 030620
US CRD2 1 030620
US CRD2 1 030620
... ...
===
问题
在CONNECT BY LEVEL <= n中,查询时间似乎随着n呈指数增长。我进行了一些测试,并得到以下结果:
CONNECT BY LEVEL <= n ROWS SECONDS
1 124 2-6
2 248 10+?
3 372 110
n = 4及以上的任何内容似乎都完全挂起了sqldeveloper。当n = 7时,我让计算机运行了30分钟以上,并且查询仍在运行。
是什么导致这种缓慢?有没有更好的方法来实现我的表?谢谢你的时间。
为什么查询这么慢?因为每次迭代将每一行与124个新行连接起来。因此,在第二层为124 * 124行,在第四层为236421376行,在第七层为450766669594624行。这就是为什么您会重复的原因。同时加入双也没有影响。
解决的办法是纠正connect by
零件,增加条件wmwhse_id = prior wmwhse_id
:
select country, name, wmwhse_id, trunc(sysdate) - level + 1 dt
from pl_db
connect by level <= 7 and prior wmwhse_id = wmwhse_id and prior sys_guid() is not null
order by dt desc, wmwhse_id
递归CTE甚至更容易,这在大多数数据库中都是标准的:
with r(country, name, wmwhse_id, dt, lvl) as (
select country, name, wmwhse_id, trunc(sysdate), 1 from pl_db union all
select country, name, wmwhse_id, trunc(sysdate) - lvl, lvl + 1 from r where lvl < 7)
select country, name, wmwhse_id, dt from r;
但最简单的方法是使用7个数字进行交叉联接,而与如何生成它们无关紧要:
select country, name, wmwhse_id, trunc(sysdate) - trim(column_value) + 1 dt
from pl_db cross join xmltable('1 to 7')
dbfiddle包含所有三个查询。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句