mysql好找工作_mysql实战练习

--第五章 子查询,实战练习题--1. 计算2017年7月份2号每个会员购买金额, 以及每个会员购买金额占总金额的比--思路:--先对会员进行分组, 再根据会员组,也就是每位会员购买金额进行sum, 限制条件:日期--通过子查询,统计在条件日期内, 全体会员购买总金额,相除计算比率--比率格式: 利用concat 和 round 函数 进行数据格式处理

SELECTdw.fct_sales.dimMemberID,sum(dw.fct_sales.AMT) ASmember_money,

(SELECT sum(AMT) from dw.fct_sales WHERE dw.fct_sales.dimDateID =

'20170702' AND dw.fct_sales.dimMemberID <>0) AStotal_money,

concat(round((sum(dw.fct_sales.AMT)/(SELECT sum(AMT) from dw.fct_sales WHERE dw.fct_sales.dimDateID =

'20170702' AND dw.fct_sales.dimMemberID <>0))*100,4),'%') ASmember_total_rave_2FROMfct_salesWHERE dw.fct_sales.dimDateID = '20170702' AND dw.fct_sales.dimMemberID <>0

GROUP bydw.fct_sales.dimMemberIDORDER BY SUM(AMT) DESC;--2. 2017年07月2号 对每位会员累计购买金额进行分段--思路:--先进行会员分组,限制条件: 日期--对每一位会员sum购买金额--把上面查询的 作为表即子查询,生成temp临时表,用来from(除了它,其他都只能是一个字段)--注意: 用临时表时需用临时表别名alis, 相当于数据库中有一个临时表temp--再进行分段使用 case 再排序order by

SELECT dw.temp.dimMemberID,

member_money,CASE

WHEN member_money < 100 THEN 'D'

WHEN member_money < 500 AND member_money >= 100 THEN 'C'

WHEN member_money < 1000 AND member_money >= 500 THEN 'B'

WHEN member_money >= 1000 THEN 'A'

END AStype_moneyfrom(SELECTdw.fct_sales.dimMemberID,sum(dw.fct_sales.amt) asmember_moneyFROMfct_saleswhere dw.fct_sales.dimDateID='20170702' AND dimMemberID <>0

GROUP BYdw.fct_sales.dimMemberID

)AS temp

ORDER BYtype_money;--3. 2017年07月2号 统计累计购买金额在100到200的会员,寻找这批会员的消费记录--思路:--把累计金额100到200的会员作为筛选表 在这批会员中寻找消费记录--先筛选 : 时间条件 再在会员分组中筛选 金额范围--还需在外查询中 设置: 时间条件, 因为in后面只是会员ID ,会员也可能在 其他时间段有记录--IN 后面必须只有一个字段

--SELECT dw.fct_sales.dimMemberID--FROM fct_sales--WHERE dw.fct_sales.dimDateID = '20170702'--GROUP BY dw.fct_sales.dimMemberID--HAVING SUM(dw.fct_sales.AMT) >= 100 AND SUM(dw.fct_sales.AMT) <= 200 AND dw.fct_sales.dimMemberID <> 0

SELECT *

FROMdw.fct_salesWHERE dw.fct_sales.dimDateID = '20170702' AND dw.fct_sales.dimMemberID<>0 AND dw.fct_sales.dimMemberID IN(SELECTdw.fct_sales.dimMemberIDFROMfct_salesWHERE dw.fct_sales.dimDateID = '20170702'

GROUP BYdw.fct_sales.dimMemberIDHAVING SUM(dw.fct_sales.AMT) >= 100 AND SUM(dw.fct_sales.AMT) <= 200 AND dw.fct_sales.dimMemberID <> 0)ORDER BY dw.fct_sales.AMT DESC;--#1 计算 7 月份某周每个会员订单数，以及每个会员订单占整体比--思路:--用分组对会员进行分组, 统计每个会员订单数, 在通过子查询统计会员总订单数--挑选条件: 日期, 某周

SELECTdw.fct_sales.dimMemberID,COUNT(salesNo) ASsales_num,

CONCAT((count(salesNo)/(SELECT COUNT(salesNo) FROM dw.fct_sales WHERE (dw.fct_sales.dimDateID BETWEEN '20170703' AND '20170709') AND dimMemberID <>0 ))*100,'%') ASrateFROMdw.fct_salesWHERE (dw.fct_sales.dimDateID BETWEEN '20170703' AND '20170709') AND dimMemberID <>0

GROUP BYdw.fct_sales.dimMemberIDORDER BY rate DESC;--#2 对 7 月份某周每位会员累计购买金额进行分段统计，并统计每段的会员数--思路:--子查询: 安照购买金额统计,以会员为分组 筛选条件: 时间--将子查询作为 临时表,再统计每段会员数--SELECT dw.fct_sales.dimMemberID,--sum(AMT)--FROM dw.fct_sales--WHERE (dw.fct_sales.dimDateID BETWEEN '20170703' AND '20170709') AND dimMemberID <>0--GROUP BY dimMemberID--

SELECT(CASE

WHEN temp.sum_amt <= 300 THEN '0-300'

WHEN temp.sum_amt <= 800 AND temp.sum_amt >300 THEN '300-800'

WHEN temp.sum_amt >800 THEN '800-'

END ) AScut_type,COUNT(DISTINCT dimMemberID) ASmemberID_numberFROM(SELECTdw.fct_sales.dimMemberID,sum(AMT) assum_amtFROMdw.fct_salesWHERE (dw.fct_sales.dimDateID BETWEEN '20170703' AND '20170709') AND dimMemberID <>0

GROUP BYdimMemberID

)as temp

GROUP BY CASE

WHEN temp.sum_amt <= 300 THEN '0-300'

WHEN temp.sum_amt <= 800 AND temp.sum_amt >300 THEN '300-800'

WHEN temp.sum_amt >800 THEN '800-'

END;--#3 查找 7 月份某周订单金额最大的会员，这个会员的所有消费记录--思路:--

select *

fromdw.fct_saleswhere dimMemberID in(select distinctdimMemberIDfromdw.fct_saleswhere AMT in(select max(AMT)fromdw.fct_saleswhere dimDateID between '20170703' and '20170709'

and dimMemberID<>0)

)--AND (dimDateID between '20170703' and '20170709' and dimMemberID<>0);