的Oracle SQL - 从合并这两个查询两个结果转换成一个

Question

我有这两个单独的查询：

查询1

select '2013-03-03' As "Week_Of", 
count(player_id) as cohort_size 
from player 
where trunc(create_dtime) > To_Date('2013-Mar-03','yyyy-mon-dd')-7 
and trunc(create_dtime) <= To_Date('2013-Mar-03','yyyy-mon-dd') 
and world_flag != '1' 
; 

，输出：

Week_of  Cohort_size 
2013-03-03  18183 

查询2

select '2013-03-03' As "Week_Of", 
count(player_id) as Day_0_Ret 
from player 
where trunc(init_dtime)-trunc(create_dtime) >= 0 
and trunc(create_dtime) > To_Date('2013-Mar-03','yyyy-mon-dd')-7 
and trunc(create_dtime) <= To_Date('2013-Mar-03','yyyy-mon-dd') 
and world_flag != '1' 
; 

哪些产出：

Week_of  Day_0_Ret 
2013-03-03  15684 

我想带这两个查询在一起，所以我有一个查询输出：

Week_of   Cohort_Size  Day_0_Ret 
2013-03-03  18183    15684 

Answer 1

使用case语句来完成一个条件计数：

select '2013-03-03' As "Week_Of", 
count(player_id) as cohort_size , 
count(case 
     when trunc(init_dtime)-trunc(create_dtime) >= 0 
     then player_id 
     end) as Day_0_Ret 
from player 
where trunc(create_dtime) > To_Date('2013-Mar-03','yyyy-mon-dd')-7 
and trunc(create_dtime) <= To_Date('2013-Mar-03','yyyy-mon-dd') 
and world_flag != '1' 
;