sql - 使用 Oracle SQL 将重复计数连接到现有子查询
问题描述
我被要求构建一个查询,对某个状态测试的数据运行质量检查。
我有以下查询,它创建一个名为“标志”的变量来识别存在问题的情况。主查询实际上是在子查询中,我通过仅选择 Flag 具有值的情况来过滤掉没有标志的所有记录。
对于有重复测试的情况,我希望能够有一个 CASE WHEN。我们确实有一些重复项,对于相同的学生 ID(test.studentID、studenttestscore.studentID、students.ID),学生将在同一考试中具有相同的考试日期。
如您所见,测试日期是 STUDENTTEST.TEST_DATE。可以通过 STUDENTTEST.ID 或 TEST.NAME 识别考试。对于 STUDENTTEST.studentID,查找 STUDENTTEST.test_date 和 STUDENTTEST.ID 相同的记录可能更容易。但是一旦你得到这个计数,它就必须被附加到这里的子查询中,这样我们就可以有一个与之关联的学生姓名。
with query as (select STUDENTS.STUDENT_NUMBER as "Student ID", Students.Lastfirst as "Student Name",
STUDENTS.GRADE_LEVEL as "Current Grade Level",
TEST.NAME as "Test Name",
STUDENTTEST.GRADE_LEVEL as "Grade level at time of exam",
STUDENTTEST.TEST_DATE as TEST_DATE,
STUDENTTESTSCORE.NUMSCORE as Score, STUDENTTESTSCORE.PERCENTSCORE as PCTScore,
S_NY_STU_X.DATEOFENTRYGRade9 as Cohort,
CASE
WHEN STUDENTTEST.GRADE_LEVEL <9 or STUDENTTEST.GRADE_LEVEL>12 then 'Invalid Grade Level'
WHEN STUDENTTESTSCORE.NUMSCORE != STUDENTTESTSCORE.PERCENTSCORE then 'Mismatch between number and percent score'
WHEN TEST.NAME like '% - T%' then 'Transitional Global no longer administered'
WHEN STUDENTTEST.TEST_DATE>CURRENT_DATE THEN 'Test date invalid'
WHEN STUDENTTESTSCORE.NUMSCORE <0 THEN 'Test score out of range'
WHEN STUDENTTESTSCORE.NUMSCORE <0 THEN 'Test score out of range'
WHEN STUDENTTESTSCORE.NUMSCORE >100 THEN 'Test score out of range'
WHEN STUDENTTESTSCORE.PERCENTSCORE > 100 THEN 'Test score out of range'
END AS Flag
from STUDENTS STUDENTS,
STUDENTTEST STUDENTTEST,
STUDENTTESTSCORE STUDENTTESTSCORE,
TEST TEST,
TESTSCORE TESTSCORE,
S_NY_STU_X S_NY_STU_X
where
Test.Name like 'Regents%' and
STUDENTTESTSCORE.TESTSCOREID=TESTSCORE.ID
and STUDENTTESTSCORE.STUDENTTESTID=STUDENTTEST.ID
and STUDENTTESTSCORE.STUDENTID=STUDENTTEST.STUDENTID
and STUDENTS.ID=STUDENTTEST.STUDENTID
and STUDENTS.ID=STUDENTTESTSCORE.STUDENTID
and STUDENTTEST.TESTID=TEST.ID
and S_NY_STU_X.STUDENTSDCID=STUDENTS.DCID)
select * from query where flag is not null
太感谢了!!
解决方案
据我了解,您可以将此分析添加count()
到您的case
:
when count(1) over (partition by studenttest.test_date,
studenttest.id,
studenttest.studentid) > 1
then 'duplicate'
这是示例:
with t (test_date, test_id, student_id) as (
select date '2013-05-18', 707, 20214 from dual union all
select date '2013-05-18', 707, 20214 from dual union all
select date '2013-05-12', 707, 20214 from dual union all
select date '2023-05-18', 707, 1003 from dual union all
select date '2013-05-02', 705, 1003 from dual )
select t.*,
case
when test_date > current_date
then 'Test date invalid'
when count(1) over (partition by test_date, test_id, student_id) > 1
then 'duplicated test'
end as flag
from t
结果:
TEST_DATE TEST_ID STUDENT_ID FLAG
----------- ---------- ---------- -----------------
2013-05-02 705 1003
2013-05-12 707 20214
2013-05-18 707 20214 duplicated test
2013-05-18 707 20214 duplicated test
2023-05-18 707 1003 Test date invalid
推荐阅读
- reactjs - Typescript+React+Redux+reactrouter:"property does not exist on type IntrinsicAttributes & IntrinsicClassAttributes" 将props从父传到子
- swift - 在 Swift 中通过 IF 语句设置变量值
- common-lisp - Ltk按钮配置
- azure - Azure AD 加入设备上的其他本地管理员不允许添加为本地管理员的用户
- java - 解码 Ldap 属性中的特殊字符
- javascript - 如何在打字稿中安全地声明嵌套对象类型
- javascript - 如何计算数组中相同的对象属性值
- javascript - 如何使按钮在另一个功能发生时出现?
- php - “空格”字符的表单检查 - PHP
- bootstrap-4 - Bootstrap 滑块,向右排列值