SQL案例解析
2021/11/28 19:12:30
本文主要是介绍SQL案例解析,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
SQL案例
案例解析
统计20170703—20170709周内每天及本周累计销售金额、订单量、会员数、订单占比
统计时间段内每天的累计销售金额、订单量、会员数
统计本周累计销售金额、订单量、会员数
订单占比
union合并表
对比 union all合并表——因为没有重复数据,所以两表合并结果一样
1、统计时间段内每天的累计销售金额、订单量、会员数
SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把时间转化为星期模式*/ ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order /*一个会员一周可以下多单,所以要统计会员数,需要去重*/ ,COUNT(DISTINCT dimMemberID) num_member FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0 group by DATE_FORMAT(dimDateID,'%W') ;
2、统计本周累计销售金额、订单量、会员数
SELECT SUM(AMT) as total_money ,COUNT(DISTINCT salesID) as total_num_order ,COUNT(DISTINCT dimMemberID) total_num_member FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0;
3、订单占比
SELECT DATE_FORMAT(dimDateID,'%W') AS week_1 ,CONCAT(ROUND( COUNT(DISTINCT salesID)/(SELECT COUNT(DISTINCT salesID) FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0),4)*100,'%') as order_rate FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0 group by DATE_FORMAT(dimDateID,'%W') ;
4、union合并表
SELECT DATE_FORMAT(dimDateID,'%W') AS week_1 ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order ,COUNT(DISTINCT dimMemberID) num_member ,CONCAT(ROUND( COUNT(DISTINCT salesID)/( SELECT COUNT(DISTINCT salesID) FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0),4)*100,'%') as order_rate FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0 group by DATE_FORMAT(dimDateID,'%W') UNION SELECT week(dimDateID,1) /*为了保证列数一样,week返回日期为一年中的第几周 weel(date,1):从周一开始为第一天*/ ,SUM(AMT) as total_money ,COUNT(DISTINCT salesID) as total_num_order ,COUNT(DISTINCT dimMemberID) total_num_member ,'100%' as total /*为确保列数一样*/ FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0 group by week(dimDateID,1); /*出现汇总函数需要进行分组*/
5、对比 union all合并表——因为没有重复数据,所以两表合并结果一样
SELECT DATE_FORMAT(dimDateID,'%W') AS week_1 ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order ,COUNT(DISTINCT dimMemberID) num_member ,CONCAT(ROUND( COUNT(DISTINCT salesID)/( SELECT COUNT(DISTINCT salesID) FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0),4)*100,'%') as order_rate FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0 group by DATE_FORMAT(dimDateID,'%W') UNION ALL SELECT week(dimDateID,1) /*为了保证列数一样,week返回日期为一年中的第几周 weel(date,1):从周一开始为第一天*/ ,SUM(AMT) as total_money ,COUNT(DISTINCT salesID) as total_num_order ,COUNT(DISTINCT dimMemberID) total_num_member ,'100%' as total /*为确保列数一样*/ FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0 group by week(dimDateID,1); /*出现汇总函数需要进行分组*/
6.区分union 和 union all ,利用重复数据对比,合并两个一模一样的表
-- union SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把时间转化为星期模式*/ ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order /*一个会员一周可以下多单,所以要统计会员数,需要去重*/ ,COUNT(DISTINCT dimMemberID) num_member FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0 group by DATE_FORMAT(dimDateID,'%W') UNION SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把时间转化为星期模式*/ ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order /*一个会员一周可以下多单,所以要统计会员数,需要去重*/ ,COUNT(DISTINCT dimMemberID) num_member FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0 group by DATE_FORMAT(dimDateID,'%W') ; -- union all SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把时间转化为星期模式*/ ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order /*一个会员一周可以下多单,所以要统计会员数,需要去重*/ ,COUNT(DISTINCT dimMemberID) num_member FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0 group by DATE_FORMAT(dimDateID,'%W') UNION ALL SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把时间转化为星期模式*/ ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order /*一个会员一周可以下多单,所以要统计会员数,需要去重*/ ,COUNT(DISTINCT dimMemberID) num_member FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0 group by DATE_FORMAT(dimDateID,'%W') ;
7.多表合并中的排序问题 order by
——把合并后的表作为一个临时表,再进行排序
注意临时表需要命名
排序要对新的列名进行排序
SELECT sn.* FROM ( SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把时间转化为星期模式*/ ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order /*一个会员一周可以下多单,所以要统计会员数,需要去重*/ ,COUNT(DISTINCT dimMemberID) num_member FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0 group by DATE_FORMAT(dimDateID,'%W') UNION SELECT DATE_FORMAT(dimDateID,'%W') as week_1 /*把时间转化为星期模式*/ ,SUM(AMT) as money ,COUNT(DISTINCT salesID) as num_order /*一个会员一周可以下多单,所以要统计会员数,需要去重*/ ,COUNT(DISTINCT dimMemberID) num_member FROM dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID <>0 group by DATE_FORMAT(dimDateID,'%W') ) as sn order by money desc; /*注意要对money排序,而不是SUM(AMT)*/ -- order by SUM(AMT) desc; /*会报错,需要用临时表的列名,并且要对临时表取列名,否则也会报错*/
这篇关于SQL案例解析的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-10-06小米11i印度快充版ROM合集:极致体验,超越期待
- 2024-10-06【ROM下载】小米11i 5G 印度版系统, 疾速跃迁,定义新速度
- 2024-10-06【ROM下载】小米 11 青春活力版,青春无极限,活力全开
- 2024-10-05小米13T Pro系统合集:性能与摄影的极致融合,值得你升级的系统ROM
- 2024-10-01基于Python+Vue开发的医院门诊预约挂号系统
- 2024-10-01基于Python+Vue开发的旅游景区管理系统
- 2024-10-01RestfulAPI入门指南:打造简单易懂的API接口
- 2024-10-01初学者指南:了解和使用Server Action
- 2024-10-01Server Component入门指南:搭建与配置详解
- 2024-10-01React 中使用 useRequest 实现数据请求