mysql查询笔试题_mysql笔试题

旧城等待， 2022-10-27 05:21 197阅读 0赞

\-- 查询每位会员在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

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