文档章节

用SQL做数据分析_1

Master_Li
 Master_Li
发布于 2017/08/05 18:47
字数 1282
阅读 61
收藏 0
点赞 0
评论 2

题目:给定经纬度区间(经度(10,20),纬度(100,200)),50%以上订单起点在该区间内,定义司机为市区司机,相反,若50%以上订单起点在该区间外则定义司机为郊县司机。在北京市随机取1000个服务分在【90,100】的快车司机,请写出SQL代码查询市区司机和郊县司机的数量和市区与郊县司机占比。

分析:

首先在北京市随机取1000个服务分在【90,100】的快车司机,将结果作为司机表d

select 
	Top 1000 a.*, 
	c.service_score
	from a,c
	where (a.driver_id = c.driver_id) 
		and (c.service_score between 90 and 100) 
		and (a.driver_car_lev in ('1','2','3'))
		and (a.city_id = 1)

将司机表和订单表联合得到司机订单表,一个司机对应多个订单,每个订单对应一个起点经纬度,然后以driver_id分组,同时计算每个订单是属于市区订单还是郊区订单,将结果作为表e

select 
		b.*,
		COUNT(*) as order1,
		SUM(case when (current_lng between 10 and 20) and (current_lan between 100 and 200)  then 1 else 0 end) as order2,
		SUM(case when (current_lng not between 10 and 20) or (current_lan not between 100 and 200)  then 1 else 0 end) as order3
		from (select 
				Top 1000 a.*, 
				c.service_score
				from a,c
				where (a.driver_id = c.driver_id) 
					and (c.service_score between 90 and 100) 
					and (a.driver_car_lev in ('1','2','3'))
					and (a.city_id = 1))as d,b
		where b.driver_id = d.driver_id
		group by b.driver_id,b.order_id,b.current_lng,b.current_lan

对于表e,以司机为单位,计算每个司机的总订单数,市区订单数,市区订单比例,郊区订单数,郊区订单比例。将每个司机的处理结果作为表f

select 
	driver_id,
	COUNT(order1) as tlnum,
	SUM(order2) as sqnum,
	SUM(order2)*1.0/COUNT(order1) as sqrate,
	SUM(order3) as jqnum,
	SUM(order3)*1.0/COUNT(order1) as jqrate
	from 
	(select 
		b.*,
		COUNT(*) as order1,
		SUM(case when (current_lng between 10 and 20) and (current_lan between 100 and 200)  then 1 else 0 end) as order2,
		SUM(case when (current_lng not between 10 and 20) or (current_lan not between 100 and 200)  then 1 else 0 end) as order3
		from (select 
				Top 1000 a.*, 
				c.service_score
				from a,c
				where (a.driver_id = c.driver_id) 
					and (c.service_score between 90 and 100) 
					and (a.driver_car_lev in ('1','2','3'))
					and (a.city_id = 1))as d,b
		where b.driver_id = d.driver_id
		group by b.driver_id,b.order_id,b.current_lng,b.current_lan) as e
	group by driver_id 	

最后,只要对表f做一个统计和判断,即可得到市区司机和郊县司机的数量和市区与郊县司机占比

select 
	COUNT(*) as tldriver,
	SUM(case when (sqrate > 0.5) then 1 else 0 end) as sqdriver,
	SUM(case when (sqrate > 0.5) then 1 else 0 end)*1.0/COUNT(*) as sqdeiverrate,
	SUM(case when (jqrate > 0.5) then 1 else 0 end) as jqdriver,
	SUM(case when (jqrate > 0.5) then 1 else 0 end)*1.0/COUNT(*) as jqdeiverrate
	from
	(select 
		driver_id,
		COUNT(order1) as tlnum,
		SUM(order2) as sqnum,
		SUM(order2)*1.0/COUNT(order1) as sqrate,
		SUM(order3) as jqnum,
		SUM(order3)*1.0/COUNT(order1) as jqrate
		from 
			(select 
				b.*,
				COUNT(*) as order1,
				SUM(case when (current_lng between 10 and 20) and (current_lan between 100 and 200)  then 1 else 0 end) as order2,
				SUM(case when (current_lng not between 10 and 20) or (current_lan not between 100 and 200)  then 1 else 0 end) as order3
				from (select 
						Top 1000 a.*, 
						c.service_score
						from a,c
						where (a.driver_id = c.driver_id) 
							and (c.service_score between 90 and 100) 
							and (a.driver_car_lev in ('1','2','3'))
							and (a.city_id = 1))as d,b
				where b.driver_id = d.driver_id
				group by b.driver_id,b.order_id,b.current_lng,b.current_lan) as e
		group by driver_id) as f 	

答案:

为了测试SQL的正确性,以SQL Server数据库建表,使用C#程序批量填入数据

①使用对象资源管理器创建表a,b,c

②使用C#批量插入数据

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DD
{
    class Program
    {
     
        static void Main(string[] args)
        {
            String CONNECTION_STRING = "server = .;database=DD;Integrated Security = true;";

            SqlConnection conn = new SqlConnection(CONNECTION_STRING);
            conn.Open();
            
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;


            for (int i = 1; i <=1000; i++)
            {
                string sql =
                       "insert into a(driver_id, city_id, driver_car_lev) values (" + Convert.ToString(i) + "," + Convert.ToString(1) + "," + Convert.ToString(1) + ")";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }


            for(int i =1; i<=600; i++){
                for (int j = 1; j <= 3; j++)
                {
                    string sql =
                       "insert into b(driver_id, order_id, current_lng, current_lan) values (" + Convert.ToString(i) + "," + Convert.ToString(j) + "," + Convert.ToString(new Random().Next(10) + 10) + "," + Convert.ToString(new Random().Next(100) + 100) + ")";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
                for (int j = 4; j <= 4; j++)
                {
                    string sql =
                       "insert into b(driver_id, order_id, current_lng, current_lan) values (" + Convert.ToString(i) + "," + Convert.ToString(j) + "," + Convert.ToString(new Random().Next(10) + 30) + "," + Convert.ToString(new Random().Next(100) + 300) + ")";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
            }
            for (int i = 601; i <= 1000; i++)
            {
                for (int j = 1; j <= 3; j++)
                {
                    string sql =
                      "insert into b(driver_id, order_id, current_lng, current_lan) values (" + Convert.ToString(i) + "," + Convert.ToString(j) + "," + Convert.ToString(new Random().Next(10) + 30) + "," + Convert.ToString(new Random().Next(100) + 300) + ")";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
                for (int j = 4; j <= 4; j++)
                {
                    string sql =
                       "insert into b(driver_id, order_id, current_lng, current_lan) values (" + Convert.ToString(i) + "," + Convert.ToString(j) + "," + Convert.ToString(new Random().Next(10) + 10) + "," + Convert.ToString(new Random().Next(100) + 100) + ")";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
            }

            for (int i = 1; i <= 1000; i++)
            {
                string sql =
                       "insert into c(driver_id, service_score) values (" + Convert.ToString(i) + "," + Convert.ToString(new Random().Next(10) + 90) + ")";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }


            //SqlDataReader reader = cmd.ExecuteReader();
            //System.Console.Write("column1\tcolumn2\tcolumn3\n");
            //while (reader.Read())
            //{
            //    System.Console.Write(reader["driver_id"]);
            //    System.Console.Write(reader["city_id"]);
            //    System.Console.Write(reader["driver_car_lev"]);
            //}
            conn.Close();
            System.Console.Write("Insert complete!");
            Console.ReadKey(true);//暂停  

        }
    }
}

③测试SQL脚本

select 
	COUNT(*) as tldriver,
	SUM(case when (sqrate > 0.5) then 1 else 0 end) as sqdriver,
	SUM(case when (sqrate > 0.5) then 1 else 0 end)*1.0/COUNT(*) as sqdeiverrate,
	SUM(case when (jqrate > 0.5) then 1 else 0 end) as jqdriver,
	SUM(case when (jqrate > 0.5) then 1 else 0 end)*1.0/COUNT(*) as jqdeiverrate
	from
	(select 
		driver_id,
		COUNT(order1) as tlnum,
		SUM(order2) as sqnum,
		SUM(order2)*1.0/COUNT(order1) as sqrate,
		SUM(order3) as jqnum,
		SUM(order3)*1.0/COUNT(order1) as jqrate
		from 
			(select 
				b.*,
				COUNT(*) as order1,
				SUM(case when (current_lng between 10 and 20) and (current_lan between 100 and 200)  then 1 else 0 end) as order2,
				SUM(case when (current_lng not between 10 and 20) or (current_lan not between 100 and 200)  then 1 else 0 end) as order3
				from (select 
						Top 1000 a.*, 
						c.service_score
						from a,c
						where (a.driver_id = c.driver_id) 
							and (c.service_score between 90 and 100) 
							and (a.driver_car_lev in ('1','2','3'))
							and (a.city_id = 1))as d,b
				where b.driver_id = d.driver_id
				group by b.driver_id,b.order_id,b.current_lng,b.current_lan) as e
		group by driver_id) as f 	

结果:表d

表e

表f

最终结果

 

Over!!!

© 著作权归作者所有

共有 人打赏支持
Master_Li
粉丝 1
博文 73
码字总数 56835
作品 0
南京
程序员
加载中

评论(2)

Master_Li
Master_Li

引用来自“lijiachen56”的评论

您好大神,想请教一下表f
SUM(order2)*1.0/COUNT(order1) as sqrate
为什么要*1.0呢?
因为不确定sum()和count()返回的类型,所以为了保证结果是浮点类型,*1.0
l
lijiachen56
您好大神,想请教一下表f
SUM(order2)*1.0/COUNT(order1) as sqrate
为什么要*1.0呢?
加米谷:数据分析师是做什么的,需要掌握哪些技能?

数据分析师是做什么的: 1、支持各种常规或临时数据分析需求; 2、提供各类业务相关的分析及建议; 3、通过建模深入挖掘用户或产品方面的有价值的信息; 4、和各部门沟通协调需求并提出各种新...

加米谷大数据
07/13
0
0
大数据分析师是做什么的?需要掌握哪些技能?

数据分析师是做什么的: 1、支持各种常规或临时数据分析需求; 2、提供各类业务相关的分析及建议; 3、通过建模深入挖掘用户或产品方面的有价值的信息; 4、和各部门沟通协调需求并提出各种新...

加米谷
07/13
0
0
我是如何入门、成长并进阶为数据分析师的?

前几天和一朋友A聊天,一个在我看来完全可以在大部分行业领域公司独当一面的数据分析师,目前仍谦虚的以“数据猿”自称。从事数据分析的他,半路出家,起初虽然懂一些数据库的知识,但仍然和...

雄霸天下啦
2017/06/22
0
0
mysql多表查询实例结果及分析-2017.12.14

今天帮运营导数据时发现sql执行速度非常慢,嗯,按照之前的编写习惯,后来跟架构师一起分析讨论,改进了下,现记录分析如下 一、测试表、sql情况 set @bgDate = '20171213';set @edDate = '...

Ye_Kwii
2017/12/14
0
0
实战演练:洞若观火--治堵之道在清源

1 观察:把问题说清楚 在我印象中,DBA 都是很明事理的人,因为这个职业经常要干的一件事情就是:“把问题说清楚,把规矩讲明白”。这是一项软技能,不管是问题分析过程中与同行沟通,还是最...

技术小能手
07/09
0
0
Oracle数据库SQL优化的最佳思路

如何在 Oracle数据库里写出高质量的SQL语句,如何在Oracle数据库里对有性能问题的SQL做诊断和调整,这是DBA们在ORACLE数据库实践中不可避免的难题。下面就让我们来分析一下拿到一条问题sql后...

数据吃货
2013/12/04
345
0
当excel不够用时,如何利用Access进行数据分析?

知乎上有不少这样的问题: Excel经常卡死,都是数据统计,请问如何减负? 基础数据表3万多行,其他表要抓取该表数据,函数却无法运算? 5万多行的数据用Excel做地图经常卡死,除了换电脑还有...

李启方
03/22
0
0
count(1)和count(*)的区别

当表的数据量大些时,对表作分析之后,使用count(1)还要比使用count()用时多了! 从执行计划来看,count(1)和count()的效果是一样的。 但是在表做过分析之后,count(1)会比count()的用时少些...

eeeneo
2012/07/23
0
1
利用 SQL 进行数据分析初学者教程 - ep1

由 Tomi Mester 写于2017年5月9日 本文转载自SQL for Data Analysis - Tutorial for Beginners - ep1 如果你想成数据分析师或是数据科学家,那么 SQL 是必须的。在过去的几年中,我已经与许多...

Datartisan
2017/06/29
0
0
count(*),count(1)和count(主键)的区别

如果null参与聚集运算,则除count()之外其它聚集函数都忽略null。如: ID DD 1 e 2 null select count() from table --结果是2 select count(DD) from table ---结果是1 效率问题: 当表的数...

openlife
2015/07/14
0
3

没有更多内容

加载失败,请刷新页面

加载更多

下一页

虚拟机怎么安装vmware tools

https://blog.csdn.net/tjcwt2011/article/details/72638977

AndyZhouX
12分钟前
0
0
There is no session with id[xxx]

参考网页 https://blog.csdn.net/caimengyuan/article/details/52526765 报错 2018-07-19 23:04:35,330 [http-nio-1008-exec-8] DEBUG [org.apache.shiro.web.servlet.SimpleCookie] - Found......

karma123
12分钟前
0
0
vue-router懒加载

1. vue-router懒加载定义 当路由被访问的时候才加载对应组件 2. vue-router懒加载作用 当构建的项目比较大的时候,懒加载可以分割代码块,提高页面的初始加载效率。 ###3. vue-router懒加载实...

不负好时光
20分钟前
0
0
庆祝法国队夺冠:用Python放一场烟花秀

天天敲代码的朋友,有没有想过代码也可以变得很酷炫又浪漫?今天就教大家用Python模拟出绽放的烟花庆祝昨晚法国队夺冠,工作之余也可以随时让程序为自己放一场烟花秀。 这个有趣的小项目并不...

猫咪编程
22分钟前
0
0
SpringBoot | 第七章:过滤器、监听器、拦截器

前言 在实际开发过程中,经常会碰见一些比如系统启动初始化信息、统计在线人数、在线用户数、过滤敏高词汇、访问权限控制(URL级别)等业务需求。这些对于业务来说一般上是无关的,业务方是无需...

oKong
35分钟前
5
0
存储结构分四类:顺序存储、链接存储、索引存储 和 散列存储

存储结构分四类:顺序存储、链接存储、索引存储 和 散列存储 存储结构分四类:顺序存储、链接存储、索引存储 和 散列存储。 顺序结构和链接结构适用在内存结构中。 顺序表每个单元都是按物理...

DannyCoder
46分钟前
1
0
Firefox 61已经为Ubuntu 提供支持

最新和最好的Mozilla Firefox 61 “Quantum”网络浏览器已经为Ubuntu Linux操作系统的用户提供了支持,现在可以通过官方软件库进行更新。 Mozilla于2018年6月26日发布了Firefox 61版本,该版...

六库科技
今天
0
0
Win10升级后执行系统封装(Sysprep)报错

开始封装 一年多以前开始给公司封装Win10系统,便于统一给公司电脑初始化携带各种软件的系统,致力于装完既可以开发的状态。那时候最新的版本是Win10 1703版本,自然就以他为母盘,然后结合V...

lyunweb
今天
40
0
php 性能优化

#什么情况下会遇到性能问题 PHP 语法使用的不恰当

to_be_better
今天
0
0
Jenkins 构建触发器操作详解

前言 跑自动化用例每次用手工点击jenkins出发自动化用例太麻烦了,我们希望能每天固定时间跑,这样就不用管了,坐等收测试报告结果就行。 一、定时构建语法 * * * * * (五颗星,中间用空格隔...

覃光林
今天
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部