我正在尝试用下面的sql创建一个表,它已经运行了几天了。
下面是sql语句。
create table I83094_Emnmt1 AS
Select
'I83094' AS Audit_Id
,rap01.plcy AS plcy
,raa02.enddt_t As enddt
,RPAD(NVU_GET_TERM_ID(RAP01.PLCY, Raa02.ENDDT_T),1) AS Term_Id
,nvu_get_nxt_proc_enddt(RAP01.PLCY, Raa02.ENDDT_T) As next_enddt
,exception_date AS exception_date
,RAP07.MVRDT_t
,RAP07.MVRDT_s
,rap07a.prefdt_t
,RAP07.MRTLST
,RAP07.BRTHDT_T AS BirthDate
,floor((tt.ja2_effdt_t - RAP07.BRTHDT_T)/365 ) AS Operator_Age
,rap07a.perseq
,tt.ja2_effdt_t AS TERM_DATE
,rap01.j01_PT_LINE_cat_Cd AS j01_PT_LINE_cat_Cd
,rap01.j01_pt_cdb_part_id AS j01_pt_cdb_part_id
,Rap01.J01_Pt_State_Cd As J01_Pt_State_Cd
---
from RAP01
----
Join RAA02
ON raa02.j46_pt_line_cat_cd = rap01.j01_pt_line_cat_cd
AND raa02.j46_pt_cdb_part_id = rap01.j01_pt_cdb_part_id
AND raa02.j46_pt_state_cd = rap01.j01_pt_state_cd
AND raa02.plcy = rap01.plcy
AND raa02.sprodt_t between '14-OCT-2013' AND '14-OCT-2018'
AND raa02.ahevnt = '0993'
---
JOIN ewt_prama_term tt
ON tt.ja2_pt_line_cat_cd = rap01.j01_pt_line_cat_cd
AND tt.ja2_pt_cdb_part_id = rap01.j01_pt_cdb_part_id
AND tt.ja2_pt_state_cd = rap01.j01_pt_state_cd
AND tt.ja2_plcy = rap01.plcy
and raa02.enddt_t between tt.ja2_effdt_t and tt.ja2_expdt_t - 1
-----
JOIN rap07a
ON rap07a.j36_pt_line_cat_cd = rap01.j01_pt_line_cat_cd
AND rap07a.j36_pt_cdb_part_id = rap01.j01_pt_cdb_part_id
AND rap07a.j36_pt_state_cd = rap01.j01_pt_state_cd
AND rap07a.plcy = rap01.plcy
--and RAP07a.perseq = rap18.perseq
AND raa02.enddt_t BETWEEN rap07a.prefdt_t AND (rap07a.dropdt_t - 1)
----
JOIN RAP07
ON RAP07.J37_PT_LINE_CAT_CD = rap01.j01_pt_line_cat_cd
AND RAP07.J37_PT_CDB_PART_ID = rap01.j01_pt_cdb_part_id
AND RAP07.J37_PT_STATE_CD = rap01.j01_pt_state_cd
AND rap07.plcy = RAP01.PLCY
AND RAP07.perseq = rap07a.perseq
AND RAP07.MRTLST in ('MA','IC','DC','WC','EC','DP')
--AND RAP07.DRVDES_01 IN ('R','P')
--AND RAP07.MVRDT_T < (select exception_date from I83094_exception)
AND floor((tt.ja2_effdt_t - RAP07.BRTHDT_T)/365) < 50
AND raa02.enddt_t BETWEEN RAP07.enddt_t AND (RAP07.dropdt_t - 1)
---
join I83094_exception exp
ON exp.line_cd = rap01.line3
AND exp.state_cd = rap01.state
AND exp.company_cd = rap01.co3
AND exp.marital_status_cd = RAP07.MRTLST
---
WHERE Rap01.J01_Pt_Line_Cat_Cd = 'A'
AND Rap01.Line3 IN ('010','019')
AND RAP07.MVRDT_T < exp.exception_date;字段'J01_Pt_State_Cd‘代表不同的状态,这个表包含数十亿的数据,执行起来需要很长时间。我试着创建表格,但不得不等待2到3天。如何改进查询,以便在更短的时间内执行查询。
发布于 2018-11-26 19:02:24
group by使用分组函数,如sum、max、min和count。当您使用这些函数中的一些函数时,您通常希望按某些属性对分组进行求和,例如sum(sales) group by vendor。
当您在没有分组函数的查询中使用此关键字时,它的工作方式类似于distinct,但您必须在group by中包含所有字段。
你得到的错误只是,你没有在你的group by中包含你的所有字段。
发布于 2018-11-26 19:30:46
我认为你误解了group by表达式的作用。
根据您的评论,我假设您正在尝试对大量数据运行此操作,这需要很长时间。group by对此没有帮助,group by表达式用于聚合特定字段的数据,例如,如果我想知道每个用户登录服务所花费的时间,我可以在用户名上使用group by。
为了加快你的查询速度,我推荐标准的东西,确保你的表是indexed properly的,并确保所有这些连接都是必要的。
https://stackoverflow.com/questions/53479570
复制相似问题