MySQL实验二

一：数据预处理
1.1 找出无用数据

查询该表的行数

SELECT COUNT(*) FROM dwv_order_make_haikou_1

SELECT * FROM haikou_1 WHERE haikou_1.arrive_time='0000-00-00 00:00:00' 
OR departure_time='0000-00-00 00:00:00'
OR normal_time=NULL OR passenger_count=NULL
OR dest_lng=NULL
OR dest_lat=NULL
OR starting_lng= NULL
OR starting_lat=NULL
OR order_id=NULL

查出数据46万条

删除

DELETE FROM haikou_1 WHERE haikou_1.arrive_time='0000-00-00 00:00:00' 
OR departure_time='0000-00-00 00:00:00'
OR normal_time=NULL OR passenger_count=NULL
OR dest_lng=NULL
OR dest_lat=NULL
OR starting_lng= NULL
OR starting_lat=NULL
OR order_id=NULL

分组后排序

SELECT Geohash,number,CAST(number/(SELECT count( 0 ) FROM 
haikou_1 where UNIX_TIMESTAMP(departure_time)<1496419200 AND UNIX_TIMESTAMP(departure_time)>1496332800 and left(`geo`,5)='w7w3x') 
as CHAR(15))as demand FROM
(select left(`geo`,6) AS Geohash,count(0) AS number from haikou_1 where UNIX_TIMESTAMP(departure_time)<1496419200 
AND UNIX_TIMESTAMP(departure_time)>1496332800
and left(`geo`,5)='w7w3x' GROUP BY Geohash ORDER BY number desc) as T