现在有个指标是求各个年龄段的平均消费情况,我在hive中建立的视图如下，但感觉这样太麻烦，缺乏灵活性，在kylin中我测试了sum(case age>=14 and age< 18 then..)没有效果.

请问有什么更好的办法吗？(如果像下面这么建立的话，假如哪天一改需求的话，就太麻烦了)

```sql

CREATE VIEW v_summary_avg_amount_2016 AS

SELECT

tw.brand_id                                                             AS brand_id,

tw.trans_code                                                         AS trans_code,

tw.trans_date                                                          AS trans_date,

year(tw.trans_date) * 100 + month(tw.trans_date) AS trans_month,

-- 各年龄段的消费统计

-- 14~18岁的消费金额

CASE WHEN age >= 14 and age < 18 THEN(

CASE WHEN trans_code = '3001' THEN amount * coupon_consume_quantity WHEN trans_code IN ('0002', '2002', '1002') THEN amount END)

END AS **measure_by_age_14_18_amount**,

-- 18~25岁的消费金额

CASE WHEN age >= 18 and age < 25 THEN(

CASE WHEN trans_code = '3001' THEN amount * coupon_consume_quantity WHEN trans_code IN ('0002', '2002', '1002') THEN amount END)

END AS **measure_by_age_18_25_amount**,

-- 25~35岁的消费金额

CASE WHEN age >= 25 and age < 35 THEN(

CASE WHEN trans_code = '3001' THEN amount * coupon_consume_quantity WHEN trans_code IN ('0002', '2002', '1002') THEN amount END)

END AS **measure_by_age_25_35_amount**,

-- 35~45岁的消费金额

CASE WHEN age >= 35 and age < 45 THEN(

CASE WHEN trans_code = '3001' THEN amount * coupon_consume_quantity WHEN trans_code IN ('0002', '2002', '1002') THEN amount END)

END AS **measure_by_age_35_45_amount**,

-- 45~60岁的消费金额

CASE WHEN age >= 45 and age < 60 THEN(

CASE WHEN trans_code = '3001' THEN amount * coupon_consume_quantity WHEN trans_code IN ('0002', '2002', '1002') THEN amount END)

END AS **measure_by_age_45_60_amount**,

-- 60~80岁的消费金额

CASE WHEN age >= 60 and age < 80 THEN(

CASE WHEN trans_code = '3001' THEN amount * coupon_consume_quantity WHEN trans_code IN ('0002', '2002', '1002') THEN amount END)

END AS **measure_by_age_60_80_amount**,

-- 14~18岁的用户

-- 在kylin中再通过count(distinct measure_by_age_14_18_master_id)来求UV

CASE WHEN age >= 14 and age < 18 THEN master_id end AS **measure_by_age_14_18_master_id**,

CASE WHEN age >= 18 and age < 25 THEN master_id end AS **measure_by_age_18_25_master_id**,

CASE WHEN age >= 25 and age < 35 THEN master_id end AS **measure_by_age_25_35_master_id**,

CASE WHEN age >= 35 and age < 45 THEN master_id end AS **measure_by_age_35_45_master_id**,

CASE WHEN age >= 45 and age < 60 THEN master_id end AS **measure_by_age_45_60_master_id**,

CASE WHEN age >= 60 and age < 80 THEN master_id end AS **measure_by_age_60_80_master_id**

FROM bi.trans_water tw LEFT OUTER JOIN bi.membership mm

ON tw.membership_id=mm.id

AND tw.trans_year='2016' -- hive中的分区字段

WHERE trans_code IN ('0002','2002','1002','3001')

AND mm.join_date between '2016-01-01' and '2016-12-31';

```