mysql查询笔试题_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;

原文：https://www.cnblogs.com/Iceredtea/p/12960709.html