我的查询如下:
select lvl_desc, fct.patient_age_group as Age_group,fct.client_id,
fct.geog_algn_id,USC_3_CODE, PAYMENT_TYPE ,patient_gender_desc,
SUM(nvl(Tot_Mkt,0)) as Tot_Mkt
FROM MY_FACT_TABLE fct
inner join MY_DIM_TABLE geo on fct.client_id = geo.client_id
and fct.geog_algn_id = geo.geog_algn_id
and fct.lvl_desc = geo.geog_lvl_15_desc
WHERE fct.client_id = 10000
and fct.geog_algn_id = 10000
and geog_lvl_03_desc = 'BC'
and USC_3_CODE = 31000
AND PAYMENT_TYPE = 'BLA2'
AND patient_gender_desc = 'MALE'
AND patient_age_group in ('19-25','3-5', '30-34', '35-39')
and lvl_desc = 'BLA1'
GROUP BY fct.client_id,fct.geog_algn_id,lvl_desc,USC_3_CODE, PAYMENT_TYPE ,patient_gender_desc, patient_age_group
order by lvl_desc,age_group;我的查询返回年龄组30-34岁和35-39岁的数据,因为事实表有数据。我想返回失踪年龄组3-5和19-25的零(0)。我该怎么做?任何帮助都将不胜感激。谢谢。
发布于 2015-01-29 15:36:33
使用值提供一些数据集,或者在需要生成数据范围时提供一些集-返回函数 (比如generate_series()) (并使用左联接,在没有数据的情况下获取NULL ):
select lvl_desc, fct.patient_age_group as Age_group,fct.client_id,
fct.geog_algn_id,USC_3_CODE, PAYMENT_TYPE ,patient_gender_desc,
SUM(nvl(Tot_Mkt,0)) as Tot_Mkt
FROM (VALUES ('19-25', '3-5', '30-34', '35-39')) v(patient_age_group)
left join MY_FACT_TABLE fct on fct.patient_age_group = v.patient_age_group
left join MY_DIM_TABLE geo on fct.client_id = geo.client_id
and fct.geog_algn_id = geo.geog_algn_id
and fct.lvl_desc = geo.geog_lvl_15_desc
WHERE fct IS NULL OR (
fct.client_id = 10000
and fct.geog_algn_id = 10000
and geog_lvl_03_desc = 'BC'
and USC_3_CODE = 31000
AND PAYMENT_TYPE = 'BLA2'
AND patient_gender_desc = 'MALE'
and lvl_desc = 'BLA1'
)
GROUP BY fct.client_id,fct.geog_algn_id,lvl_desc,USC_3_CODE,
PAYMENT_TYPE ,patient_gender_desc, patient_age_group
order by lvl_desc,age_group;https://stackoverflow.com/questions/28218262
复制相似问题