• 来自话题：2020-02-05 17:35:03 风吹碧霄
• 关注

0

• 问答

17

对7月份某周每位会员累计购买金额进行分段统计，并统计每段的会员数，本节的练习题，我写的和参考答案类似，为啥运行不了

我写的代码如下

select dimMemberID

,money

,case when money<500 then '小康'

when money>=500 and money<1000 then '中产'

when money>=1000 and money<2000 then '高产'

when money>=2000 then '富人'

end as type_level

,count(DISTINCT dimMemberID) as number_member

FROM

(SELECT dimMemberID,sum(AMT) AS money

from dw.fct_sales

where week(dimDateID) =week('20170709')

and dimMemberID<>0

group by dimMemberID

order by sum(AMT) desc) t2

group by

case when money<500 then '小康'

when money>=500 and money<1000 then '中产'

when money>=1000 and money<2000 then '高产'

when money>=2000 then '富人'

end;

请解答 • 一直机智的小二青

2020-03-18 17:42:42

个人觉得，逻辑上稍微有些问题，比如在主查询中，需要取出 dimMemberI、money、分类、计算会员数，这4个字段本身就不再同一个维度中，前两个字段是每位会员对应的消费金额，后两个字段其实是在前两个字段的基础上聚合出的数据。如果按照您的选取字段，即使能出来结果，出来的会是每位会员会对应一个计算会员数，但这不是所需要的结果，这是矛盾所在。有看到说不能在子查询中使用order by ,即使使用了也无法发挥作用，于是我将order by 放在了主查询中。以下是我修改的代码，如有问题，期待联系。

select type_level

,count(DISTINCT dimMemberID) as number_member

FROM

(

SELECT dimMemberID

,sum(AMT) AS money

,case when money<500 then '小康'

when money>=500 and money<1000 then '中产'

when money>=1000 and money<2000 then '高产'

when money>=2000 then '富人'

end as type_level

from dw.fct_sales

where week(dimDateID) =week('20170709')

and dimMemberID<>0

group by dimMemberID

) t2

group by type_level

order by count(DISTINCT dimMemberID) desc

;

点赞 0 回复 0
• zh

2020-02-05 19:21:16

修改代码：

select

case when money<500 then '小康'

when money>=500 and money<1000 then '中产'

when money>=1000 and money<2000 then '高产'

when money>=2000 then '富人'

end as type_level

,count(DISTINCT dimMemberID) as number_member

FROM

(SELECT dimMemberID,sum(AMT) AS money

from dw.fct_sales

where week(dimDateID) =week('20170709')

and dimMemberID<>0

group by dimMemberID

order by sum(AMT) desc) t2

group by type_level;

点赞 0 回复 0      