表查询
select og_id ,order_goods.id , sku, num , price ,order_info.create_date
FROM order_goods, order_info
where order_goods.sku = 'WM-D39S0106'
and order_goods.id = order_info.id
and order_info.is_aberrant = 0
and order_info.order_state=5
数据按月统计
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from datetime import datetime
df = pd.read_csv("wm_viomall.csv") #读入数据
df['create_date'] = pd.to_datetime(df['create_date']) #将数据类型转换成datatime类型
df = df.set_index(['create_date']) #将create_date设为索引
print(len(df))
dataset = np.zeros((8,3))
for i in range(1,8):
df_new = df['2017-0'+ str(i)] #读取某月的数据
count = len(df_new) #计算某月行数
num = df_new['num'].values
sum = np.sum(num) #计算某月总num
#print(sum)
price = df_new['price'].values #读取某月价格
if len(price):
avg = np.average(price)
else:
avg = 0
dataset[i][0] = i
dataset[i][1] =sum
dataset[i][2] = avg
p1 =plt.plot(dataset[1:8,0] , dataset[1:8,1],'r', label = "sales")
for xy in zip(dataset[1:8,0] ,dataset[1:8,1]):
plt.annotate(xy[1], xy=xy, xytext=(-10,10), textcoords = 'offset points')
p2 = plt.plot(dataset[1:8,0] , dataset[1:8,2], label = "price")
plt.grid(True)
plt.xlabel("month")
plt.title("WM-D39S0106")
plt.legend(loc = 2) #图例
plt.show()