需要加快查询速度，无法收集输出

早上好。我已经成功地编写了一个3000行的巨型查询，它似乎能够获得我想要的数据，但似乎无法完成任何允许我保存数据的任务。运行查询在30秒内完成一次，在180秒内完成一次，但几乎总是需要800秒左右。当我昨天尝试导出数据时，它运行了20个小时（根据任务进度面板），我不得不退出，不知道什么时候可以完成。
其目的是编写一个查询，可以输出学生的倾向性分数，以便在评估教师实施的干预措施在夏季改变课程的有效性时减少一些特征。
代码由一系列重复的代码块组成。
我使用“with”开始代码，以便定义一些要在查询中使用的变量。
第一节/“[姓名]为”计算所有参加考试的学生在参加课程时的平均绩点。似乎不慢。然后将这些数据从该节收集到一个名为t\u initialdata的节中，我使用该节作为一个表，将各种片段收集到一个空间中，这样就可以避免在代码的其余部分使用联接。我收集了这学期的平均绩点以及他们在“学生群体”和“大学”中的地位。所以在这一点上，我已经创建了一个表，其中包含了两个学期的所有学生的gpa、学生人数和大学会员资格，以及一组特定的课程。
这就是代码在重复中变得密集的地方。我会贴出每种类型的例子。
共分为3个部分，每种类型的协变量各一个：平均绩点、学生人数和大学。他们都有这样的项目（平均绩点：高于或低于2.75，学生人数：12个不同的选项，大学：26个不同的选项）：

v_gpaprob275Opre
  AS (SELECT COUNT(DISTINCT t_initialdata.person_uid) AS v_gpaprob275Opre
  FROM t_initialdata
  WHERE (t_initialdata.semester = '201610' AND t_initialdata.gpa >= '2.75')),
 v_gpaprob275Opost
  AS (SELECT COUNT(DISTINCT t_initialdata.person_uid) AS v_gpaprob275Opost
  FROM t_initialdata
  WHERE (t_initialdata.semester = '201640' AND t_initialdata.gpa >= '2.75')),
 v_gpaprobU275pre
  AS (SELECT COUNT(DISTINCT t_initialdata.person_uid) AS v_gpaprobU275pre
  FROM t_initialdata
  WHERE (t_initialdata.semester = '201610' AND t_initialdata.gpa < '2.75')),
 v_gpaprobU275post
  AS (SELECT COUNT(DISTINCT t_initialdata.person_uid) AS v_gpaprobU275post
  FROM t_initialdata
  WHERE (t_initialdata.semester = '201640' AND t_initialdata.gpa < '2.75')),
 v_gpaprobcalc275O
  AS (SELECT NVL(NULLIF(TRUNC(((v_gpaprob275Opre+v_gpaprob275Opost)/(v_gpaprob275Opre+v_gpaprobU275pre+v_gpaprob275Opost+v_gpaprobU275post)),3),0),.001) AS v_gpaproboutcome275O FROM v_gpaprob275Opre, v_gpaprobU275pre, v_gpaprob275Opost, v_gpaprobU275post),
 v_gpaprobcalcU275
  AS (SELECT NVL(NULLIF(TRUNC(((v_gpaprobU275pre+v_gpaprobU275post)/(v_gpaprob275Opre+v_gpaprobU275pre+v_gpaprob275Opost+v_gpaprobU275post)),3),0),.001) AS v_gpaproboutcomeU275 FROM v_gpaprob275Opre, v_gpaprobU275pre, v_gpaprob275Opost, v_gpaprobU275post),

上面收集了实施干预前后平均绩点高于或低于2.75的学生数，然后计算出该组学生平均绩点高于或低于2.75的基本概率。所有12个学生群体和所有26所大学也是如此。您还可以看到我使用nvl/nullif来避免0，因为我稍后使用自然日志，如果您的赔率/概率为“0”，则会给出错误
上面的代码大约需要400行。
接下来我有24个代码块，看起来像：

v_scoreO000 AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcome00 AS v_scoreO000 FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalc00),
v_scoreO0AL AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeAL AS v_scoreO0AL FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcAL),
v_scoreO0AR AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeAR AS v_scoreO0AR FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcAR),
v_scoreO0AS AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeAS AS v_scoreO0AS FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcAS),
v_scoreO0BU AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeBU AS v_scoreO0BU FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcBU),
v_scoreO0CA AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeCA AS v_scoreO0CA FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcCA),
v_scoreO0CE AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeCE AS v_scoreO0CE FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcCE),
v_scoreO0CT AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeCT AS v_scoreO0CT FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcCT),
v_scoreO0ED AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeED AS v_scoreO0ED FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcED),
v_scoreO0EH AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeEH AS v_scoreO0EH FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcEH),
v_scoreO0EN AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeEN AS v_scoreO0EN FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcEN),
v_scoreO0GW AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeGW AS v_scoreO0GW FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcGW),
v_scoreO0HE AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeHE AS v_scoreO0HE FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcHE),
v_scoreO0HH AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeHH AS v_scoreO0HH FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcHH),
v_scoreO0HS AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeHS AS v_scoreO0HS FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcHS),
v_scoreO0LS AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeLS AS v_scoreO0LS FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcLS),
v_scoreO0LW AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeLW AS v_scoreO0LW FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcLW),
v_scoreO0MD AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeMD AS v_scoreO0MD FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcMD),
v_scoreO0NC AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeNC AS v_scoreO0NC FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcNC),
v_scoreO0NU AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeNU AS v_scoreO0NU FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcNU),
v_scoreO0PH AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomePH AS v_scoreO0PH FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcPH),
v_scoreO0SJ AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeSJ AS v_scoreO0SJ FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcSJ),
v_scoreO0SM AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeSM AS v_scoreO0SM FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcSM),
v_scoreO0UC AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeUC AS v_scoreO0UC FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcUC),
v_scoreO0VP AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeVP AS v_scoreO0VP FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcVP),
v_scoreO0YC AS (SELECT v_gpaproboutcome275O*v_stupopproboutcome0*v_collegeproboutcomeYC AS v_scoreO0YC FROM v_gpaprobcalc275O, v_stupopprobcalc0, v_collegeprobcalcYC),

接下来是24个代码块，如下所示：

v_lnoddsO000 AS (SELECT TRUNC(ln(v_scoreO000/(1-v_scoreO000)),4) AS v_lnoddsO000 FROM v_scoreO000),
v_lnoddsO0AL AS (SELECT TRUNC(ln(v_scoreO0AL/(1-v_scoreO0AL)),4) AS v_lnoddsO0AL FROM v_scoreO0AL),
v_lnoddsO0AR AS (SELECT TRUNC(ln(v_scoreO0AR/(1-v_scoreO0AR)),4) AS v_lnoddsO0AR FROM v_scoreO0AR),
v_lnoddsO0AS AS (SELECT TRUNC(ln(v_scoreO0AS/(1-v_scoreO0AS)),4) AS v_lnoddsO0AS FROM v_scoreO0AS),
v_lnoddsO0BU AS (SELECT TRUNC(ln(v_scoreO0BU/(1-v_scoreO0BU)),4) AS v_lnoddsO0BU FROM v_scoreO0BU),
v_lnoddsO0CA AS (SELECT TRUNC(ln(v_scoreO0CA/(1-v_scoreO0CA)),4) AS v_lnoddsO0CA FROM v_scoreO0CA),
v_lnoddsO0CE AS (SELECT TRUNC(ln(v_scoreO0CE/(1-v_scoreO0CE)),4) AS v_lnoddsO0CE FROM v_scoreO0CE),
v_lnoddsO0CT AS (SELECT TRUNC(ln(v_scoreO0CT/(1-v_scoreO0CT)),4) AS v_lnoddsO0CT FROM v_scoreO0CT),
v_lnoddsO0ED AS (SELECT TRUNC(ln(v_scoreO0ED/(1-v_scoreO0ED)),4) AS v_lnoddsO0ED FROM v_scoreO0ED),
v_lnoddsO0EH AS (SELECT TRUNC(ln(v_scoreO0EH/(1-v_scoreO0EH)),4) AS v_lnoddsO0EH FROM v_scoreO0EH),
v_lnoddsO0EN AS (SELECT TRUNC(ln(v_scoreO0EN/(1-v_scoreO0EN)),4) AS v_lnoddsO0EN FROM v_scoreO0EN),
v_lnoddsO0GW AS (SELECT TRUNC(ln(v_scoreO0GW/(1-v_scoreO0GW)),4) AS v_lnoddsO0GW FROM v_scoreO0GW),
v_lnoddsO0HE AS (SELECT TRUNC(ln(v_scoreO0HE/(1-v_scoreO0HE)),4) AS v_lnoddsO0HE FROM v_scoreO0HE),
v_lnoddsO0HH AS (SELECT TRUNC(ln(v_scoreO0HH/(1-v_scoreO0HH)),4) AS v_lnoddsO0HH FROM v_scoreO0HH),
v_lnoddsO0HS AS (SELECT TRUNC(ln(v_scoreO0HS/(1-v_scoreO0HS)),4) AS v_lnoddsO0HS FROM v_scoreO0HS),
v_lnoddsO0LS AS (SELECT TRUNC(ln(v_scoreO0LS/(1-v_scoreO0LS)),4) AS v_lnoddsO0LS FROM v_scoreO0LS),
v_lnoddsO0LW AS (SELECT TRUNC(ln(v_scoreO0LW/(1-v_scoreO0LW)),4) AS v_lnoddsO0LW FROM v_scoreO0LW),
v_lnoddsO0MD AS (SELECT TRUNC(ln(v_scoreO0MD/(1-v_scoreO0MD)),4) AS v_lnoddsO0MD FROM v_scoreO0MD),
v_lnoddsO0NC AS (SELECT TRUNC(ln(v_scoreO0NC/(1-v_scoreO0NC)),4) AS v_lnoddsO0NC FROM v_scoreO0NC),
v_lnoddsO0NU AS (SELECT TRUNC(ln(v_scoreO0NU/(1-v_scoreO0NU)),4) AS v_lnoddsO0NU FROM v_scoreO0NU),
v_lnoddsO0PH AS (SELECT TRUNC(ln(v_scoreO0PH/(1-v_scoreO0PH)),4) AS v_lnoddsO0PH FROM v_scoreO0PH),
v_lnoddsO0SJ AS (SELECT TRUNC(ln(v_scoreO0SJ/(1-v_scoreO0SJ)),4) AS v_lnoddsO0SJ FROM v_scoreO0SJ),
v_lnoddsO0SM AS (SELECT TRUNC(ln(v_scoreO0SM/(1-v_scoreO0SM)),4) AS v_lnoddsO0SM FROM v_scoreO0SM),
v_lnoddsO0UC AS (SELECT TRUNC(ln(v_scoreO0UC/(1-v_scoreO0UC)),4) AS v_lnoddsO0UC FROM v_scoreO0UC),
v_lnoddsO0VP AS (SELECT TRUNC(ln(v_scoreO0VP/(1-v_scoreO0VP)),4) AS v_lnoddsO0VP FROM v_scoreO0VP),
v_lnoddsO0YC AS (SELECT TRUNC(ln(v_scoreO0YC/(1-v_scoreO0YC)),4) AS v_lnoddsO0YC FROM v_scoreO0YC),

以上模块首先计算三种情况下的概率：gpa高于/低于2.75、学生群体成员和学生学院成员。然后第二个块将概率转换为赔率，然后将赔率转换为自然对数-这是我们的倾向性得分。
这里是它看起来变慢的地方，但我包括了上面所有的内容，因为也许这才是真正让事情变慢的原因？我不知道到底是什么在踢我。以下是用于将其全部选择到网格中以便保存的代码：

SELECT t_initialdata.person_uid,
             CASE WHEN t_initialdata.semester = '201610' then '0'
             ELSE '1'
             END AS "Pre(0)-Post(1)",
             CASE
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = '00' THEN v_lnoddsO000
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'AL' THEN v_lnoddsO0AL
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'AR' THEN v_lnoddsO0AR
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'AS' THEN v_lnoddsO0AS
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'BU' THEN v_lnoddsO0BU
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'CA' THEN v_lnoddsO0CA
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'CE' THEN v_lnoddsO0CE
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'CT' THEN v_lnoddsO0CT
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'ED' THEN v_lnoddsO0ED
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'EH' THEN v_lnoddsO0EH
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'EN' THEN v_lnoddsO0EN
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'GW' THEN v_lnoddsO0GW
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'HE' THEN v_lnoddsO0HE
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'HH' THEN v_lnoddsO0HH
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'HS' THEN v_lnoddsO0HS
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'LS' THEN v_lnoddsO0LS
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'LW' THEN v_lnoddsO0LW
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'MD' THEN v_lnoddsO0MD
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'NC' THEN v_lnoddsO0NC
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'NU' THEN v_lnoddsO0NU
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'PH' THEN v_lnoddsO0PH
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'SJ' THEN v_lnoddsO0SJ
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'SM' THEN v_lnoddsO0SM
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'UC' THEN v_lnoddsO0UC
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'VP' THEN v_lnoddsO0VP
             WHEN t_initialdata.gpa >= '2.75' AND t_initialdata.student_population = '0' AND t_initialdata.college = 'YC' THEN v_lnoddsO0YC

上面，从“案例”开始有24个区块，与上面的章节非常相似。我觉得这就是我放慢速度的地方。
我已经做了一个多月了。我相信我自己可以解决它，通过利用别人的错误和问题-但我已经到了我认为我没有时间自己来解决这个问题。我以为我已经解决了，但现在它不让我保存我正在查询的数据。
我可以进行这个查询并将其显示在网格上。大约需要10分钟。我可以按ctrl+end并让它非常快速地显示表中的所有行。但一旦我想把它保存到excel文件里。。。它失败了。我试着通过生成一个报告，然后保存它。我试着在按下ctrl+end后保存它。我试着保存它，让它重新运行查询。在所有情况下，查询都只是在不停地运行。
我需要帮助(

需要加快查询速度，无法收集输出

暂无答案！

相关问题

热门标签

最新问答