-- SELECT count(*) FROM fct_sales_item;
--
-- SELECT * FROM fct_sales_item
-- WHERE id<10;
--
-- SELECT DATE_FORMAT(dimDateID,'%Y-%m-%d') as 'date'
-- FROM dim_date
-- ORDER BY dimDateID DESC
-- LIMIT 10;
--
-- SELECT now();
--
-- 第四章-常用函数作业
-- 1.统计 每天 购物的 顾客数,销售金额,订单量
SELECT
DATE_FORMAT( fct_sales.dimDateID, '%Y-%m-%d' ) AS everyday,
COUNT( DISTINCT fct_sales.dimMemberID ) AS '顾客数',
sum( fct_sales.AMT ) AS '销售金额',
sum( fct_sales.QTY ) AS '订单量'
FROM
fct_sales
GROUP BY
fct_sales.dimDateID -- 以天分组计算
ORDER BY
fct_sales.dimDateID DESC;
-- 2.统计 每周 顾客数,销售金额,订单量
SELECT WEEK
( fct_sales.dimDateID, 1 ) AS '一年第几周',
COUNT( fct_sales.dimMemberID ) AS '顾客数',
sum( fct_sales.AMT ) AS '销售金额',
sum( fct_sales.QTY ) AS '订单量'
FROM
dw.fct_sales
GROUP BY
WEEK ( fct_sales.dimDateID, 1 ); -- 以周分组计算
-- 3. 统计各个时间段的 顾客数,销售金额, 订单量
SELECT HOUR
( fct_sales.dimDateID ) AS '每小时hour',
WEEKDAY( fct_sales.dimDateID ) AS '星期几',
COUNT( fct_sales.dimMemberID ) AS '顾客数',
sum( fct_sales.AMT ) AS '销售金额',
sum( fct_sales.QTY ) AS '订单量'
FROM
dw.fct_sales
GROUP BY
HOUR ( fct_sales.dimDateID ),
weekday ( fct_sales.dimDateID );
-- 4. 每天交易金额、订单数量、顾客数、平均客单价；
-- 方法一: dayofyear函数
SELECT
DAYOFYEAR( fct_sales.dimDateID ) AS '一年第几天',
sum( fct_sales.AMT ) AS '交易金额',
sum( fct_sales.QTY ) AS '订单数',
count( fct_sales.dimDateID ) AS '顾客数',
sum( fct_sales.AMT ) / count( fct_sales.dimDateID ) AS '平均客单价'
FROM
fct_sales
GROUP BY
DAYOFYEAR( fct_sales.dimDateID );-- 方法二: date_format函数
SELECT
DATE_FORMAT( fct_sales.dimDateID, '%j' ) AS '一年第几天',
sum( fct_sales.AMT ) AS '交易金额',
sum( fct_sales.QTY ) AS '订单数',
count( fct_sales.dimDateID ) AS '顾客数',
sum( fct_sales.AMT ) / count( fct_sales.dimDateID ) AS '平均客单价'
FROM
fct_sales
GROUP BY
DATE_FORMAT( fct_sales.dimDateID, '%j' );
-- 5. 查询某周 金额最大、最小的 订单号 及对应的顾客；【自行选择】
-- 这里选择一年中23周
SELECT
fct_sales.salesID,
fct_sales.dimMemberID
FROM
fct_sales
WHERE
fct_sales.AMT = ( SELECT max( fct_sales.AMT ) FROM fct_sales WHERE WEEK ( fct_sales.dimDateID ) = 23 )
OR fct_sales.AMT = ( SELECT min( fct_sales.AMT ) FROM fct_sales WHERE WEEK ( fct_sales.dimDateID ) = 23 );
-- 6. 统计XX日期到XX日期会员购买金额，去掉金额大于1000会员。【自行选择时间范围】
-- 统计 20170611到20170730 会员买$ 去掉 $>1000
SELECT
fct_sales.dimMemberID AS '顾客ID',
SUM( fct_sales.AMT ) AS '购买金额'
FROM
fct_sales
WHERE
( fct_sales.dimDateID BETWEEN 20170611 AND 20170730 )
GROUP BY
fct_sales.dimMemberID
HAVING
sum( fct_sales.AMT ) <= 1000;
-- 7. 计算平均客单价，平均每个顾客购买金额，计算客户平均购买次数
-- 客单价=销售总额（除去打折等优惠之后的算下来的钱）÷ 顾客总数
SELECT
avg( fct_sales.AMT ) AS '客单价',
sum( fct_sales.AMT ) / count( DISTINCT fct_sales.dimMemberID ) AS '平均每位顾客购买金额',
COUNT( fct_sales.salesID ) / COUNT( DISTINCT fct_sales.salesID ) AS '客户平均购买次数'
FROM
fct_sales;
-- 8. 统计订单明细表，把商品价格进3等分，统计每个区间的商品数
-- 9. 每天订单金额分布（按最大，最小分成3个等分区间）
SELECT
( max( AMT ) - min( AMT ) ) / 3 AS cut_amt,
min( AMT ) AS min_amt,
max( AMT ) AS max_amt
FROM
fct_sales;
SELECT
(
CASE
WHEN fct_sales.AMT <= ( 0.17000 + 8781.943333333 ) THEN
'[min_amt,cut_amt]'
WHEN fct_sales.AMT <= ( 0.17000 + 8781.943333333 * 2 ) AND ( AMT > ( 0.17000 + 8781.943333333 )) THEN
'(min_amt+cut_amt,min_amt+cut_amt*2]'
WHEN fct_sales.AMT <= 26346.00000 THEN
'(min_amt+cut_amt*2,min_amt + cut_amt*3]'
END
) AS qj,
count( amt )
FROM
fct_sales
GROUP BY
(
CASE
WHEN fct_sales.AMT <= ( 0.17000 + 8781.943333333 ) THEN
'[min_amt,cut_amt]'
WHEN fct_sales.AMT <= ( 0.17000 + 8781.943333333 * 2 ) AND ( AMT > ( 0.17000 + 8781.943333333 )) THEN
'(min_amt+cut_amt,min_amt+cut_amt*2]'
WHEN fct_sales.AMT <= 26346.00000 THEN
'(min_amt+cut_amt*2,min_amt + cut_amt*3]'
END
);
-- 第五章 子查询,实战练习题
-- 1. 计算2017年7月份2号每个会员购买金额, 以及每个会员购买金额占总金额的比
-- 思路:
-- 先对会员进行分组, 再根据会员组,也就是每位会员购买金额进行sum, 限制条件:日期
-- 通过子查询,统计在条件日期内, 全体会员购买总金额,相除计算比率
-- 比率格式: 利用concat 和 round 函数 进行数据格式处理
SELECT dw.fct_sales.dimMemberID,
sum(dw.fct_sales.AMT) AS member_money,
(SELECT sum(AMT) from dw.fct_sales WHERE dw.fct_sales.dimDateID =
'20170702' AND dw.fct_sales.dimMemberID <>0) AS total_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),'%') AS member_total_rave_2
FROM fct_sales
WHERE dw.fct_sales.dimDateID = '20170702' AND dw.fct_sales.dimMemberID <>0
GROUP by dw.fct_sales.dimMemberID
ORDER 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 AS type_money
from
( SELECT dw.fct_sales.dimMemberID,
sum(dw.fct_sales.amt) as member_money
FROM fct_sales
where dw.fct_sales.dimDateID='20170702' AND dimMemberID <>0
GROUP BY dw.fct_sales.dimMemberID
) AS temp
ORDER BY type_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 *
FROM dw.fct_sales
WHERE dw.fct_sales.dimDateID = '20170702' AND dw.fct_sales.dimMemberID<>0 AND dw.fct_sales.dimMemberID 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
)
ORDER BY dw.fct_sales.AMT DESC;
-- #1 计算 7 月份某周每个会员订单数，以及每个会员订单占整体比
-- 思路:
-- 用分组对会员进行分组, 统计每个会员订单数, 在通过子查询统计会员总订单数
-- 挑选条件: 日期, 某周
SELECT dw.fct_sales.dimMemberID,
COUNT(salesNo) AS sales_num,
CONCAT((count(salesNo)/(SELECT COUNT(salesNo) FROM dw.fct_sales WHERE (dw.fct_sales.dimDateID BETWEEN '20170703' AND '20170709') AND dimMemberID <>0 ))*100,'%') AS rate
FROM dw.fct_sales
WHERE (dw.fct_sales.dimDateID BETWEEN '20170703' AND '20170709') AND dimMemberID <>0
GROUP BY dw.fct_sales.dimMemberID
ORDER 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 ) AS cut_type,
COUNT(DISTINCT dimMemberID) AS memberID_number
FROM
(SELECT dw.fct_sales.dimMemberID,
sum(AMT) as sum_amt
FROM dw.fct_sales
WHERE (dw.fct_sales.dimDateID BETWEEN '20170703' AND '20170709') AND dimMemberID <>0
GROUP BY dimMemberID
) 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 *
from dw.fct_sales
where dimMemberID in
( select distinct dimMemberID
from dw.fct_sales
where AMT in
(select max(AMT)
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
and dimMemberID<>0
)
)
-- AND (dimDateID between '20170703' and '20170709' and dimMemberID<>0);
-- 月报：统计 201707 月内每天及本月累计销售金额、订单量、会员数、订单占比、会员渗透率
/* 思路:
select 天, 销售金额, 订单量, 会员数, 天订单/总订单(子查询), 天会员数(去重)/月总会员数(去重
FROM 交易事实表
WHERE 月
GROUP BY 天
HAVING 无
ORDER BY 天 -- 只能在最后使用!!!!
UNION
SELECT 月, ...., '100%', ''100%'
FROM 交易表
WHERE 月
GROUP BY 月
HAVING 无
ORDER BY 月
*/
-- 疑问 : SELECT count(DISTINCT dimMemberID) FROM fct_sales WHERE dimMemberID<>0
SELECT dw.fct_sales.dimDateID ,
sum(dw.fct_sales.AMT) AS sales_money ,
COUNT(dw.fct_sales.salesID) AS sales_number,
COUNT(DISTINCT dw.fct_sales.dimMemberID) AS member_number ,
count(dw.fct_sales.salesID)/(SELECT count(dw.fct_sales.salesID) FROM dw.fct_sales WHERE dw.fct_sales.dimDateID BETWEEN '20170701' AND '20170731' ) AS order_rate,
COUNT(DISTINCT dw.fct_sales.dimMemberID)/(SELECT count(dw.fct_sales.dimMemberID) FROM dw.fct_sales WHERE dw.fct_sales.dimDateID BETWEEN '20170701' AND '20170731' AND dw.fct_sales.dimMemberID <> 0) AS member_rate
FROM dw.fct_sales
WHERE dw.fct_sales.dimDateID BETWEEN '20170701' AND '20170731'
GROUP BY dimDateID
UNION
SELECT MONTH(dimDateID),
sum(dw.fct_sales.AMT) AS sales_money ,
COUNT(dw.fct_sales.salesID) AS sales_number,
COUNT(DISTINCT dw.fct_sales.dimMemberID) AS member_number ,
count(dw.fct_sales.salesID)/(SELECT count(dw.fct_sales.salesID) FROM dw.fct_sales WHERE dw.fct_sales.dimDateID BETWEEN '20170701' AND '20170731' ) AS order_rate,
COUNT(DISTINCT dw.fct_sales.dimMemberID)/(SELECT count(dw.fct_sales.dimMemberID) FROM dw.fct_sales WHERE dw.fct_sales.dimDateID BETWEEN '20170701' AND '20170731' AND dw.fct_sales.dimMemberID <> 0 ) AS member_rate
FROM dw.fct_sales
WHERE dw.fct_sales.dimDateID BETWEEN '20170701' AND '20170731'
GROUP BY MONTH(dimDateID)
ORDER BY dimDateID; -- 只能在最后使用!!!!
-- - 对星期进行排序
select date_format(dimDateID,'%W') as week_day
,sum(AMT) as sales_money
,count(distinct salesID) as order_number
,count(distinct dimMemberID) as member_number
,count(distinct salesID)/(select count(distinct salesID) from dw.fct_sales
where dimDateID between '20170703' and '20170710') as order_rate
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
group by date_format(dimDateID,'%W')
-- order by date_format(dimDateID,'%W')
union
select date_format(dimDateID,'%W') as week_day
,sum(AMT) as sales_money
,count(distinct salesID) as order_number
,count(distinct dimMemberID) as member_number
,count(distinct salesID)/(select count(distinct salesID) from dw.fct_sales
where dimDateID between '20170703' and '20170710') as order_rate
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
group by date_format(dimDateID,'%W');
-- - 要排序 , 用别名排序
select temp.*
from (
select date_format(dimDateID,'%W') as week_day
,sum(AMT) as sales_money
,count(distinct salesID) as order_number
,count(distinct dimMemberID) as member_number
,count(distinct salesID)/(select count(distinct salesID) from dw.fct_sales
where dimDateID between '20170703' and '20170710') as order_rate
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
group by date_format(dimDateID,'%W')
-- order by date_format(dimDateID,'%W')
union
select date_format(dimDateID,'%W') as week_day
,sum(AMT) as sales_money
,count(distinct salesID) as order_number
,count(distinct dimMemberID) as member_number
,count(distinct salesID)/(select count(distinct salesID) from dw.fct_sales
where dimDateID between '20170703' and '20170710') as order_rate
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
group by date_format(dimDateID,'%W')) temp
order by week_day
