现在有个指标是求各个年龄段的平均消费情况,我在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 **measur