-- 先找出當(dāng)日累計購買金額在100-200之間的會員
SELECT dimMemberID 
      ,SUM(AMT) as money
FROM dw.fct_sales
where dimDateID ='20170702'
and dimMemberID <> 0
group by dimMemberID
-- having sum(AMT)>100 and sum(AMT)<200;
having money BETWEEN 100 and 200;
-- 用查找出來的會員做過濾條件，查找消費記錄
SELECT *
FROM dw.fct_sales
where dimDateID = '20170702'
and dimMemberID <>0
and dimMemberID in (
                    SELECT dimMemberID 
                       -- ,SUM(AMT) as money    子查詢在where后面，當(dāng)做一個過濾條件,這個字段也必須有且只有一個,所以最后結(jié)果要返回過濾條件
                    FROM dw.fct_sales
                    where dimDateID ='20170702'
                    and dimMemberID <> 0
                    group by dimMemberID
                    having sum(AMT)>100 and sum(AMT)<200);
                    -- having money BETWEEN 100 and 200 );  不能直接用命名的列名money，要寫全函數(shù)，因為前面語句沒有出現(xiàn)money，所以為了避免報錯，之后盡量寫全函數(shù)

-- 1、統(tǒng)計2017年7月2日每位會員的累計購買金額
SELECT dimMemberID 
      ,SUM(AMT) as money 
FROM dw.fct_sales
where dimDateID = '20170702'
and dimMemberID <> 0
group by dimMemberID ;
-- 2、把第一步統(tǒng)計出來的數(shù)據(jù)作為臨時表，對統(tǒng)計出來的數(shù)據(jù)進行分段
SELECT dimMemberID 
      ,money 
      ,case when money <100 then 'D'
            when money >=100 and money <500 then 'C'
            when money >=500 and money <1000 then 'B'
            when money >=1000 then 'A'
            else '其它'
       end as type1
FROM (SELECT dimMemberID                      /*臨時表可以生成多個字段，并不是每個字段都需要用也行*/
            ,SUM(AMT) as money 
      FROM dw.fct_sales
      where dimDateID = '20170702'
      and dimMemberID <> 0
      group by dimMemberID) as sn;            /*子查詢在from后面做臨時表來用,必須給這個子查詢生成的臨時表取一個表的別名，否則會報錯*/

-- 1、2017年7月2日每個會員購買金額
SELECT dimMemberID 
      ,SUM(AMT) as money
FROM dw.fct_sales
where dimDateID = '20170702'
and dimMemberID <>0                   /*去除非會員,即 dimMemberID 為 0 的數(shù)據(jù)*/
group by dimMemberID ;
-- 2、總體金額
SELECT SUM(AMT) 
FROM dw.fct_sales
where dimDateID = '20170702';
-- 3、合并兩個表   注意在合并時where的條件要寫全，不要遺漏，不然容易出現(xiàn)邏輯錯誤
SELECT dimMemberID 
      ,SUM(AMT) as money
      ,(SELECT SUM(AMT)                                /*統(tǒng)計總金額*/
        FROM dw.fct_sales
        where dimDateID = '20170702'
        and dimMemberID<>0) as total_money
      ,CONCAT(                                         /*加上%號*/
       ROUND(                                          /*四舍五入保留4位小數(shù)，帶%一定要思考保留小數(shù)位數(shù)*/
       SUM(AMT)/(SELECT SUM(AMT) 
                 FROM dw.fct_sales
                 where dimDateID = '20170702'
                 and dimMemberID<>0),4)*100,'%') 
       as member_rate                                  /*合并表，統(tǒng)計占比*/
FROM dw.fct_sales
where dimDateID = '20170702'
and dimMemberID <>0                                    /*去除非會員,即 dimMemberID 為 0 的數(shù)據(jù)*/
group by dimMemberID ;