mysql笔试题

-- 查询每位会员在7月份的订单数、订单总额，并以订单总额进行降序排列

select 
	dimMemberID, -- 会员ID
	count(salesID) as 'order_num', -- 订单数
	sum(AMT) as 'total_AMT' -- 订单总额  
from dw.fct_sales t1
where date_format(dimDateID,'%Y%m')='201707' and dimMemberID <> 0
group by dimMemberID 
order by sum(AMT) desc;


-- 查询7月2日当天累计消费金额在100元以上的会员，统计他们的累计消费金额及累计消费金额占当天销售总额的比重，并进行降序排列

select 
	dimMemberID, -- 会员ID
	total_AMT, -- 会员累计消费金额
	total_AMT/(select sum(AMT) from dw.fct_sales where dimDateID='20170702' and dimMemberID <> 0) as 'percent_AMT' -- 消费额占比
from
	(select 
			dimMemberID, -- 会员ID
		  sum(AMT) as 'total_AMT' -- 会员累计消费金额
	from dw.fct_sales
	where dimDateID='20170702' and dimMemberID <> 0
	group by dimMemberID
	having sum(AMT)>100) t
order by percent_AMT desc;


-- 查询7月份每天及整月的会员数、会员销售总额、会员销售订单量

select 
	'7月每天',
	count(distinct dimMemberID), -- 每天会员数
	sum(AMT), -- 每天会员销售额
	count(salesID) -- 每天会员订单量
from dw.fct_sales
where date_format(dimDateID,'%Y%m') = '201707' and dimMemberID <> 0
group by dimDateID 

union 

select 
	'7月整月',
	count(distinct dimMemberID), -- 整月会员数
	sum(AMT), -- 整月会员销售额
	count(salesID) -- 整月会员订单量
from dw.fct_sales
where date_format(dimDateID,'%Y%m') = '201707' and dimMemberID <> 0


-- 查询7月份每位会员的第一次购物时间，最后一次购物时间，第一次到最后一次购物的间隔。

select 
	dimMemberID, -- 会员ID
	min(dimDateID), -- 第一次购物时间
	max(dimDateID), -- 最后一次购物时间
	datediff(max(dimDateID),min(dimDateID)) -- 购物时间间隔
from dw.fct_sales
where date_format(dimDateID,'%Y%m')='201707' and dimMemberID <> 0
group by dimMemberID;



-- 查找7月份第二周订单数最多的会员的所有消费记录

set @start_date := '20170709';
set @end_date := '20170715';

select *
from dw.fct_sales
where dimDateID between @start_date and @end_date and dimMemberID <> 0 
and dimMemberID in
	(select dimMemberID -- 获取消费记录最多的会员ID
	 from 
		 (select 
				dimMemberID,
			  count(salesID)
		from dw.fct_sales
		where dimDateID between @start_date and @end_date and dimMemberID <> 0
		group by dimMemberID
		order by count(salesID) desc
		limit 1) v
	)
	

-- 对7月份第二周每位会员累计购买金额进行分段统计（分段范围自行确定），并统计每段的会员数，每段内的平均消费金额

set @start_date := '20170709';
set @end_date := '20170715';

select 
	(case when total_AMT >=0 and total_AMT < 100 then '低消费'
	      when total_AMT >=100 and total_AMT < 200 then '中消费'
	      when total_AMT >=200 then '高消费'
	      else '数据异常' end) as div_AMT, -- 消费金额分级
	 count(distinct dimMemberID), -- 会员数
	 avg(total_AMT) -- 平均消费额
from 
	(select 
			dimMemberID,
		  sum(AMT) as 'total_AMT'
	 from dw.fct_sales
	 where dimDateID between @start_date and @end_date and dimMemberID <> 0 
	 group by dimMemberID) t
group by div_AMT




-- 查询201707月内每周及当月总计会员消费金额、会员订单量、会员数、会员订单数占比（当周会员订单数与整月会员订单数比）、会员渗透率（当周会员数与当月会员数的比）

SELECT
		CASE
			WHEN t.wweek IS NULL THEN '总计' ELSE t.wweek END AS weeknumber,
		t.sale_money,
		t.order_number,
		t.mem_number,
		t.order_rate,
		t.mem_rate 
	FROM
		(
		SELECT 
			WEEK(dimDateID, 1) AS wweek,
			sum(AMT) AS sale_money,
			count(DISTINCT salesNo) AS order_number,
			count(DISTINCT dimMemberID) AS mem_number,
			count(DISTINCT salesNo) / (SELECT count(DISTINCT salesNo) FROM dw.fct_sales WHERE dimDateID BETWEEN 20170701 AND 20170731 AND dimMemberID <> 0) AS order_rate,
			count(DISTINCT dimMemberID) / (SELECT count(DISTINCT dimMemberID) FROM dw.fct_sales WHERE dimDateID BETWEEN 20170701 AND 20170731 AND dimMemberID <> 0) AS mem_rate 
		FROM
			dw.fct_sales 
		WHERE
			dimDateID BETWEEN 20170701 AND 20170731 AND dimMemberID <> 0 
		GROUP BY WEEK ( dimDateID, 1 ) WITH ROLLUP 
	) t;

　　

-- 查询每位会员在7月份的订单数、订单总额，并以订单总额进行降序排列
select dimMemberID, -- 会员IDcount(salesID) as 'order_num', -- 订单数sum(AMT) as 'total_AMT' -- 订单总额  from dw.fct_sales t1where date_format(dimDateID,'%Y%m')='201707' and dimMemberID <> 0group by dimMemberID order by sum(AMT) desc;

-- 查询7月2日当天累计消费金额在100元以上的会员，统计他们的累计消费金额及累计消费金额占当天销售总额的比重，并进行降序排列
select dimMemberID, -- 会员IDtotal_AMT, -- 会员累计消费金额total_AMT/(select sum(AMT) from dw.fct_sales where dimDateID='20170702' and dimMemberID <> 0) as 'percent_AMT' -- 消费额占比from(select dimMemberID, -- 会员ID  sum(AMT) as 'total_AMT' -- 会员累计消费金额from dw.fct_saleswhere dimDateID='20170702' and dimMemberID <> 0group by dimMemberIDhaving sum(AMT)>100) torder by percent_AMT desc;

-- 查询7月份每天及整月的会员数、会员销售总额、会员销售订单量
select '7月每天',count(distinct dimMemberID), -- 每天会员数sum(AMT), -- 每天会员销售额count(salesID) -- 每天会员订单量from dw.fct_saleswhere date_format(dimDateID,'%Y%m') = '201707' and dimMemberID <> 0group by dimDateID 
union 
select '7月整月',count(distinct dimMemberID), -- 整月会员数sum(AMT), -- 整月会员销售额count(salesID) -- 整月会员订单量from dw.fct_saleswhere date_format(dimDateID,'%Y%m') = '201707' and dimMemberID <> 0

-- 查询7月份每位会员的第一次购物时间，最后一次购物时间，第一次到最后一次购物的间隔。
select dimMemberID, -- 会员IDmin(dimDateID), -- 第一次购物时间max(dimDateID), -- 最后一次购物时间datediff(max(dimDateID),min(dimDateID)) -- 购物时间间隔from dw.fct_saleswhere date_format(dimDateID,'%Y%m')='201707' and dimMemberID <> 0group by dimMemberID;


-- 查找7月份第二周订单数最多的会员的所有消费记录
set @start_date := '20170709';set @end_date := '20170715';
select *from dw.fct_saleswhere dimDateID between @start_date and @end_date and dimMemberID <> 0 and dimMemberID in(select dimMemberID -- 获取消费记录最多的会员ID from  (select dimMemberID,  count(salesID)from dw.fct_saleswhere dimDateID between @start_date and @end_date and dimMemberID <> 0group by dimMemberIDorder by count(salesID) desclimit 1) v)
-- 对7月份第二周每位会员累计购买金额进行分段统计（分段范围自行确定），并统计每段的会员数，每段内的平均消费金额
set @start_date := '20170709';set @end_date := '20170715';
select (case when total_AMT >=0 and total_AMT < 100 then '低消费'      when total_AMT >=100 and total_AMT < 200 then '中消费'      when total_AMT >=200 then '高消费'      else '数据异常' end) as div_AMT, -- 消费金额分级 count(distinct dimMemberID), -- 会员数 avg(total_AMT) -- 平均消费额from (select dimMemberID,  sum(AMT) as 'total_AMT' from dw.fct_sales where dimDateID between @start_date and @end_date and dimMemberID <> 0  group by dimMemberID) tgroup by div_AMT



-- 查询201707月内每周及当月总计会员消费金额、会员订单量、会员数、会员订单数占比（当周会员订单数与整月会员订单数比）、会员渗透率（当周会员数与当月会员数的比）
SELECTCASEWHEN t.wweek IS NULL THEN '总计' ELSE t.wweek END AS weeknumber,t.sale_money,t.order_number,t.mem_number,t.order_rate,t.mem_rate FROM(SELECT WEEK(dimDateID, 1) AS wweek,sum(AMT) AS sale_money,count(DISTINCT salesNo) AS order_number,count(DISTINCT dimMemberID) AS mem_number,count(DISTINCT salesNo) / (SELECT count(DISTINCT salesNo) FROM dw.fct_sales WHERE dimDateID BETWEEN 20170701 AND 20170731 AND dimMemberID <> 0) AS order_rate,count(DISTINCT dimMemberID) / (SELECT count(DISTINCT dimMemberID) FROM dw.fct_sales WHERE dimDateID BETWEEN 20170701 AND 20170731 AND dimMemberID <> 0) AS mem_rate FROMdw.fct_sales WHEREdimDateID BETWEEN 20170701 AND 20170731 AND dimMemberID <> 0 GROUP BY WEEK ( dimDateID, 1 ) WITH ROLLUP ) t;