用SQL做数据分析_1 原

Master_Li

``````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)``````

``````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``````

``````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 	``````

``````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 	``````

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

②使用C#批量插入数据

``````using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

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();
}

//System.Console.Write("column1\tcolumn2\tcolumn3\n");
//{
//}
conn.Close();
System.Console.Write("Insert complete!");

}
}
}
``````

③测试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 	``````

Over！！！

评论(2)

引用来自“lijiachen56”的评论

SUM(order2)*1.0/COUNT(order1) as sqrate

l

SUM(order2)*1.0/COUNT(order1) as sqrate

07/13
0
0

07/13
0
0

2017/06/22
0
0
mysql多表查询实例结果及分析-2017.12.14

Ye_Kwii
2017/12/14
0
0

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

07/09
0
0
Oracle数据库SQL优化的最佳思路

2013/12/04
345
0

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

eeeneo
2012/07/23
0
1

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

openlife
2015/07/14
0
3

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

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

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

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

20分钟前
0
0

22分钟前
0
0
SpringBoot | 第七章：过滤器、监听器、拦截器

oKong
35分钟前
5
0

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

0
0
Win10升级后执行系统封装(Sysprep)报错

lyunweb

40
0
php 性能优化

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

to_be_better

0
0
Jenkins 构建触发器操作详解

0
0