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