控制台输入Geohash的位数得到对应Geohash的订单量和占比

MySQL语句包含嵌套查询

# 现在要做：
# 2：取Geohash的前N位
# 3：计算前位Geohash的demand
# 4：根据出租车的位置经纬度返回最近的Geohash

from pygeohash import encode, decode
import plotly
import plotly.plotly as pyf
import plotly.graph_objs as go
import numpy as np
import pandas as pd
import math
from matplotlib.path import Path
import numpy as np
import plotly.offline as of
import plotly.graph_objs as go
import plotly.plotly as py
import numpy as np
import pandas as pd
import folium
import webbrowser
from folium.plugins import HeatMap
import datetime
import time
import pymysql.cursors
import decimal
import geohash

def geo_demand(geo_top_number):  #按照时间获取每个geo的接客概率
    conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='xu19931026',
        db='cd_taxi',
        charset='utf8'
    )
    if conn:
        print("连接成功!")
    cursor = conn.cursor()  # 获取游标
    sql = "SELECT Geohash,number,CAST(number/(SELECT count( 0 ) FROM Order_Data where FROM_UNIXTIME(BeginTime) <'2016-11-01 24:00:00') as CHAR(15))as demand FROM (select left(`order_data`.`Geo`,%s) AS `Geohash`,count(0) AS `number` from `order_data` where (from_unixtime(`order_data`.`BeginTime`)< '2016-11-01 24:00:00') group by `Geohash` order by `number` desc) AS T;"
    cursor.execute(sql,geo_top_number)
    conn.commit()
    result = cursor.fetchall()
    df = list(result)  # 将元组转换为列表
    conn.close()  # 关闭数据库连接
    print('数据库处理数据完毕')
    geo_dict = {}  # 字典
    geo_list = []
    order_number = []
    demand = []
    for point in df:
        geo_list.append((point[0]))  # 将字符串的经纬度转换为float格式
        order_number.append(point[1])  # 50610之类的本身就是int
        demand.append(float(point[2]))  # Decimal转为float
    num = len(geo_list)  # num为数组大小
    geo_data = [[geo_list[i], demand[i]] for i in range(num)]
    geo_nb = [[geo_list[i], order_number[i]] for i in range(num)]
    return geo_data,geo_nb #返回Geohash对应的订单量与订单量百分比即demand

if __name__ == '__main__':
    print("  请按照格式输入得到Geohash的前几位：例：6    ") #'2016-11-01 24:00:00'
    geo_top_suzi = input("请输入:") # 接收键盘输入如：2016-11-01 24:00:00
    geo_data, geo_nb=geo_demand(geo_top_suzi)