mysql查询笔试题_mysql笔试题 旧城等待, 2022-10-27 05:21 216阅读 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 \-- 查询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
相关 mysql查询笔试题_mysql笔试题 \-- 查询每位会员在7月份的订单数、订单总额,并以订单总额进行降序排列 select dimMemberID, -- 会员ID count(salesID) as ‘o 旧城等待,/ 2022年10月27日 05:21/ 0 赞/ 217 阅读
相关 笔试题② 1、写出几种常见的设计模式,并详细列举出一种。 单例设计模式,工厂设计模式,适配器设计模式,模板设计模式,装饰设计模式 2、描述下tcp接收数据的过程。 1:建立服务器端 ﹏ヽ暗。殇╰゛Y/ 2022年10月03日 01:00/ 0 赞/ 192 阅读
相关 笔试题 输入一个数组大小n,步长r和数组a, 做一个操作,使得b\[i\]=MED(a\[i\], a\[i+1\]modn, ... a\[i+r-1\]modn),MED方法是返 待我称王封你为后i/ 2022年07月16日 10:44/ 0 赞/ 247 阅读
相关 笔试题 strstr include <stdio.h> include <stdlib.h> include <string.h> include 落日映苍穹つ/ 2022年07月16日 00:51/ 0 赞/ 191 阅读
相关 面试笔试题 1.写一个函数,返回一个字符串中只出现一次的第一个字符 [第一个只出现一次的字符(包含代码-C++)][-C] (1)用查找子串的方式,每次遍历一 逃离我推掉我的手/ 2022年07月13日 13:54/ 0 赞/ 258 阅读
相关 笔试题 最近做了一些公司的笔试题,将所记部分整理一下。 1、查询该目录及子目录下所有的以.conf为后缀的文件:find . -name “\.conf”由此题扩展到linux的指 左手的ㄟ右手/ 2022年06月08日 08:28/ 0 赞/ 237 阅读
相关 笔试题 搜狗2017年校招笔试编程题题目: 在一个圆形上的若干点,点的位置以角度的形式表示,(0-360),输入若干的点的位置,点的距离以劣弧对应角度表示,求距离最远的两个点的角 小咪咪/ 2022年06月08日 02:44/ 0 赞/ 302 阅读
相关 华为笔试题 今天,帮同学看了到华为的笔试题,题目如下: 举办一场8小时的聚会,时间段从12:00到20:00点,让来访的客人事先填好到达的时间和离开的时间,为了掌握聚会期间的座位数目,需 迈不过友情╰/ 2022年05月16日 04:41/ 0 赞/ 280 阅读
相关 MySQL面试笔试题 Mysql数据库软件是一个客户端或服务器系统,支持各种客户端程序和库的多线程SQL服务器、不同的后端、广泛的应用程序编程接口和管理工具 小灰灰/ 2020年05月31日 14:16/ 1 赞/ 994 阅读
还没有评论,来说两句吧...