搜索

登录

注册

  • 来自话题: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