如何使用解析窗口SQL函数在同一数据集的行组中查找id值

debugcn 发表于 Dev

用户名

--Dataset Name: Jobs
week   date    job_id
----------------------
wk1    01/15   300
wk1    01/15   301
wk1    01/15   302
wk2    01/22   300 
wk2    01/22   302
wk2    01/22   303
wk2    01/22   304
wk3    01/29   302
wk3    01/29   304
wk3    01/29   305

我有一个像上面的数据集。我想创建3个其他列：

is_job_id_present_in_wk1

is_job_id_present_in_wk2

is_job_id_present_in_wk3

我想编写一个SQL查询，将三个新列的每一行标记为1或0。我不想使用自我加入。我想利用一些分析窗口函数。

例如，对于给定数据集中的第一行，is_job_id_present_in_wk1，is_job_id_present_in_wk2和is_job_id_present_in_wk3的值将为1（因为在所有三个星期中都存在job_id 300）。

对于给定数据集中的第二行，is_job_id_present_in_wk1的值为1，is_job_id_present_in_wk2的值为0，is_job_id_present_in_wk3的值为0（因为job_id 301仅在整个第1周都存在）。

尝试到现在为止：

SELECT week, date, job_id
       , CASE WHEN job_id = 
                            FIRST_VALUE(CASE WHEN week='wk1' THEN job_id ELSE NULL END) OVER(ORDER BY job_id rows between current row and current row) 
 THEN 1 ELSE 0 END as is_job_id_present_in_wk1
 FROM jobs;

鳄鱼

尝试：

SELECT week, date, job_id,
        max( case when week = 'wk1' then 1 else 0 end )
            over (partition by  job_id) as is_job_id_present_in_wk1,
        max( case when week = 'wk2' then 1 else 0 end )
            over (partition by job_id) as is_job_id_present_in_wk2,
        max( case when week = 'wk3' then 1 else 0 end )
            over (partition by  job_id) as is_job_id_present_in_wk2
FROM jobs;

也尝试以下版本：

SELECT week, date, job_id
       , CASE WHEN EXISTS( SELECT 1 FROM jobs job1 
                           WHERE job1.job_id = jobs.job_id AND job1.week = 'wk1' )
              THEN 1 ELSE 0 END  as is_job_id_present_in_wk1
       , CASE WHEN EXISTS( SELECT 1 FROM jobs job1 
                           WHERE job1.job_id = jobs.job_id AND job1.week = 'wk2' )
              THEN 1 ELSE 0 END  as is_job_id_present_in_wk2
       , CASE WHEN EXISTS( SELECT 1 FROM jobs job1 
                           WHERE job1.job_id = jobs.job_id AND job1.week = 'wk3' )
              THEN 1 ELSE 0 END  as is_job_id_present_in_wk3
 FROM jobs;

因为它可能比带有分析功能的版本更快，尤其是在job_id + week列上创建复合索引时。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。