1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
import pandas as pd
from pot_libs.mysql_util.mysql_util import MysqlUtil
async def location_stats_statics(table_name, cid, start, end):
sql = "SELECT mtid,lid,item,ad_type FROM location WHERE cid=%s"
location_map = {}
async with MysqlUtil() as conn:
locations = await conn.fetchall(sql, args=(cid,))
if not locations:
return location_map
for loca in locations:
location_map[loca["lid"]] = loca
datas_sql = f"SELECT * from {table_name} where lid in %s and create_time" \
f" BETWEEN '{start}' and '{end}' order by create_time desc"
lids = list(location_map.keys())
async with MysqlUtil() as conn:
results = await conn.fetchall(datas_sql, args=(lids,))
if not results:
return {}
df = pd.DataFrame(list(results))
for lid in lids:
max_value = df.loc[df["lid"] == lid].value_max.max()
if not pd.isna(max_value):
max_datas = df.loc[
df.loc[df["lid"] == lid].value_max.idxmax()].to_dict()
max_value_time = max_datas.get("value_max_time")
max_value_time = '' if pd.isnull(max_value_time) else str(
max_value_time)
max_value = round(max_value, 2)
else:
max_value, max_value_time = "", ""
min_value = df.loc[df["lid"] == lid].value_min.min()
if not pd.isna(min_value):
min_datas = df.loc[
df.loc[df["lid"] == lid].value_min.idxmin()].to_dict()
min_value_time = min_datas.get("value_min_time")
min_value_time = '' if pd.isnull(min_value_time) else str(
min_value_time)
min_value = round(min_value, 2)
else:
min_value, min_value_time = "", ""
mean_value = df.loc[df["lid"] == lid].value_avg.mean()
if not pd.isna(mean_value):
mean_value = round(mean_value, 2) if mean_value else ""
else:
mean_value = ''
location_map[lid].update({
"max_value": max_value, "max_value_time": max_value_time,
"min_value": min_value, "min_value_time": min_value_time,
"mean_value": mean_value,
})
return location_map