-- 先找出當(dāng)日累計(jì)購買金額在100-200之間的會(huì)員
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;
-- 用查找出來的會(huì)員做過濾條件，查找消費(fèi)記錄
SELECT *
FROM dw.fct_sales
where dimDateID = '20170702'
and dimMemberID <>0
and dimMemberID in (
                    SELECT dimMemberID 
                       -- ,SUM(AMT) as money    子查詢?cè)趙here后面，當(dāng)做一個(gè)過濾條件,這個(gè)字段也必須有且只有一個(gè),所以最后結(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ù)，因?yàn)榍懊嬲Z句沒有出現(xiàn)money，所以為了避免報(bào)錯(cuò)，之后盡量寫全函數(shù)

-- 1、統(tǒng)計(jì)2017年7月2日每位會(huì)員的累計(jì)購買金額
SELECT dimMemberID 
      ,SUM(AMT) as money 
FROM dw.fct_sales
where dimDateID = '20170702'
and dimMemberID <> 0
group by dimMemberID ;
-- 2、把第一步統(tǒng)計(jì)出來的數(shù)據(jù)作為臨時(shí)表，對(duì)統(tǒng)計(jì)出來的數(shù)據(jù)進(jìn)行分段
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                      /*臨時(shí)表可以生成多個(gè)字段，并不是每個(gè)字段都需要用也行*/
            ,SUM(AMT) as money 
      FROM dw.fct_sales
      where dimDateID = '20170702'
      and dimMemberID <> 0
      group by dimMemberID) as sn;            /*子查詢?cè)趂rom后面做臨時(shí)表來用,必須給這個(gè)子查詢生成的臨時(shí)表取一個(gè)表的別名，否則會(huì)報(bào)錯(cuò)*/

-- 1、2017年7月2日每個(gè)會(huì)員購買金額
SELECT dimMemberID 
      ,SUM(AMT) as money
FROM dw.fct_sales
where dimDateID = '20170702'
and dimMemberID <>0                   /*去除非會(huì)員,即 dimMemberID 為 0 的數(shù)據(jù)*/
group by dimMemberID ;
-- 2、總體金額
SELECT SUM(AMT) 
FROM dw.fct_sales
where dimDateID = '20170702';
-- 3、合并兩個(gè)表   注意在合并時(shí)where的條件要寫全，不要遺漏，不然容易出現(xiàn)邏輯錯(cuò)誤
SELECT dimMemberID 
      ,SUM(AMT) as money
      ,(SELECT SUM(AMT)                                /*統(tǒng)計(jì)總金額*/
        FROM dw.fct_sales
        where dimDateID = '20170702'
        and dimMemberID<>0) as total_money
      ,CONCAT(                                         /*加上%號(hào)*/
       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)計(jì)占比*/
FROM dw.fct_sales
where dimDateID = '20170702'
and dimMemberID <>0                                    /*去除非會(huì)員,即 dimMemberID 為 0 的數(shù)據(jù)*/
group by dimMemberID ;