def data(self, datatype, params): # 解析参数 try: params = params if isinstance(params, dict) else json.loads(params) events = params.pop("events", []) start_day = params.pop("startDay") end_day = params.pop("endDay") attrs_map = params.pop("attrs", []) # 查询天数最多为60天 tm_s_stamp = time.mktime(time.strptime(start_day, "%Y-%m-%d")) tm_e_stamp = time.mktime(time.strptime(end_day, "%Y-%m-%d")) num = (tm_e_stamp - tm_s_stamp) / 86400 if num > 90 or num < 0: return {"errinfo": "日期跨度超出范围!"} except: import sys import traceback exc_type, exc_value, exc_traceback = sys.exc_info() errinfo = traceback.format_exception(exc_type, exc_value, exc_traceback) logging.error(json.dumps(errinfo)) return {"errinfo": "传递参数错误!"} # 生成查询 try: try: query = str( self.create_query_sql(datatype, start_day, end_day, params, events, attrs_map).decode("utf-8")) except: query = self.create_query_sql( datatype, start_day, end_day, params, events, attrs_map).decode("utf-8").encode("utf-8") except: import sys import traceback exc_type, exc_value, exc_traceback = sys.exc_info() errinfo = traceback.format_exception(exc_type, exc_value, exc_traceback) logging.error(json.dumps(errinfo)) return {"errinfo": "生成查询错误!"} # 返回结果 result = [] try: client = ClickHouseClient() for row in client.select(datatype, query): key = row._id uv = row.uv pv = row.pv result.append({"_id": key, "uv": uv, "pv": pv}) except: import sys import traceback exc_type, exc_value, exc_traceback = sys.exc_info() errinfo = traceback.format_exception(exc_type, exc_value, exc_traceback) logging.error(json.dumps(errinfo)) return {"errinfo": "查询错误!"} return result
def submit(self, db_name, query, result, tm): client = ClickHouseClient() item = {"tm": tm} for row in client.select(db_name, query): tm = row.partition.strftime("%Y-%m-%d") item.setdefault("tm", tm) uv = row.uv item.setdefault("numbers", []).append(uv) if bool(item): result.append(item)
def submit(self, db_name, query, result, tm): client = ClickHouseClient() item = [0, 0] for row in client.select(db_name, query): result.setdefault(tm, [0, 0]) appoint = row.appoint uv = row.uv if appoint == "cur_day": result[tm][0] = uv elif appoint == "remain_day": result[tm][1] = uv
def query(self): query = '''SELECT DISTINCT (flight_seg) FROM airlines.rt_et_departed_round_trip WHERE (is_round_trip = 1)''' logger.info(query) result = {"data": []} client = ClickHouseClient() for row in client.select("airlines", query): if row.flight_seg not in result["data"]: result["data"].append(row.flight_seg) result["data"].sort() return result
def query_bak(self, params): air_line = params["airLine"].strip() air_type = params["airType"].strip() start_date = params["startDate"].strip() end_date = params["endDate"].strip() channel = None if air_type == "order": channel = params.get("channel", None) if channel is None or channel == "all": query = '''select %(airline_col)s , toDayOfWeek(partition) week , count() dall , countIf(toDate(backDepTime) - toDate(depTime) = 0) d0 , countIf(toDate(backDepTime) - toDate(depTime) = 1) d1 , countIf(toDate(backDepTime) - toDate(depTime) = 2) d2 , countIf(toDate(backDepTime) - toDate(depTime) = 3) d3 , countIf(toDate(backDepTime) - toDate(depTime) = 4) d4 , countIf(toDate(backDepTime) - toDate(depTime) = 5) d5 , countIf(toDate(backDepTime) - toDate(depTime) = 6) d6 , countIf(toDate(backDepTime) - toDate(depTime) = 7) d7 , countIf(toDate(backDepTime) - toDate(depTime) = 8) d8 , countIf(toDate(backDepTime) - toDate(depTime) = 9) d9 , countIf(toDate(backDepTime) - toDate(depTime) = 10) d10 , countIf(toDate(backDepTime) - toDate(depTime) > 10) dgt10 from airlines.biqu_%(airType)s_round_trip_record where (partition between toDate('%(startDate)s') and toDate('%(endDate)s')) and %(airLineCond)s group by %(airline)s, week order by %(airline)s, week''' % { "airLineCond": "airline" + " ".join(["!=", "''"]) if air_line == "all" else " ".join(["=", air_line]), "startDate": start_date, "endDate": end_date, "airType": air_type, "airline_col": "airline" if air_line != "all" else "'all' airline", "airline": "airline" if air_line != "all" else "'all'", } elif air_type == "order" and bool(channel): query = '''select %(airline_col)s , toDayOfWeek(partition) week , count() dall , countIf(toDate(backDepTime) - toDate(depTime) = 0) d0 , countIf(toDate(backDepTime) - toDate(depTime) = 1) d1 , countIf(toDate(backDepTime) - toDate(depTime) = 2) d2 , countIf(toDate(backDepTime) - toDate(depTime) = 3) d3 , countIf(toDate(backDepTime) - toDate(depTime) = 4) d4 , countIf(toDate(backDepTime) - toDate(depTime) = 5) d5 , countIf(toDate(backDepTime) - toDate(depTime) = 6) d6 , countIf(toDate(backDepTime) - toDate(depTime) = 7) d7 , countIf(toDate(backDepTime) - toDate(depTime) = 8) d8 , countIf(toDate(backDepTime) - toDate(depTime) = 9) d9 , countIf(toDate(backDepTime) - toDate(depTime) = 10) d10 , countIf(toDate(backDepTime) - toDate(depTime) > 10) dgt10 from airlines.biqu_%(airType)s_round_trip_record where (partition between toDate('%(startDate)s') and toDate('%(endDate)s')) and channel = '%(channel)s' and %(airLineCond)s group by %(airline)s, week order by %(airline)s, week''' % { "airLineCond": "airline" + " ".join(["!=", "''"]) if air_line == "all" else " ".join(["=", air_line]), "startDate": start_date, "endDate": end_date, "airType": air_type, "channel": channel, "airline_col": "airline" if air_line != "all" else "'all' airline", "airline": "airline" if air_line != "all" else "'all'", } logger.info(query) result = {"data": []} client = ClickHouseClient() for row in client.select("airlines", query): item = OrderedDict() item.setdefault("week", row.week) item.setdefault("dall", row.dall) item.setdefault("d0", row.d0 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d1", row.d1 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d2", row.d2 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d3", row.d3 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d4", row.d4 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d5", row.d5 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d6", row.d6 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d7", row.d7 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d8", row.d8 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d9", row.d9 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault( "d10", row.d10 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault( "dgt10", row.dgt10 / float(row.dall) if row.dall != 0 else 0.0) result["data"].append(item) return json.dumps(result)
def query(self, params): week_days = { 1: u"周一", 2: u"周二", 3: u"周三", 4: u"周四", 5: u"周五", 6: u"周六", 7: u"周日", } air_line = params["airLine"].strip() air_type = params["airType"].strip() start_date = params["startDate"].strip() end_date = params["endDate"].strip() query = '''select %(airline_col)s , toDayOfWeek(partition) week , count() dall , countIf(toDate(backDepTime) - toDate(depTime) = 0) d0 , countIf(toDate(backDepTime) - toDate(depTime) = 1) d1 , countIf(toDate(backDepTime) - toDate(depTime) = 2) d2 , countIf(toDate(backDepTime) - toDate(depTime) = 3) d3 , countIf(toDate(backDepTime) - toDate(depTime) = 4) d4 , countIf(toDate(backDepTime) - toDate(depTime) = 5) d5 , countIf(toDate(backDepTime) - toDate(depTime) = 6) d6 , countIf(toDate(backDepTime) - toDate(depTime) = 7) d7 , countIf(toDate(backDepTime) - toDate(depTime) = 8) d8 , countIf(toDate(backDepTime) - toDate(depTime) = 9) d9 , countIf(toDate(backDepTime) - toDate(depTime) = 10) d10 , countIf(toDate(backDepTime) - toDate(depTime) > 10) dgt10 from airlines.biqu_%(airType)s_round_trip_record where (partition between toDate('%(startDate)s') and toDate('%(endDate)s')) and %(airLineCond)s %(channelCond)s group by %(airline)s, week order by %(airline)s, week''' % { "airLineCond": "airline" + " ".join(["!=", "''"]) if air_line == "all" else "airline" + " ".join(["=", "'" + air_line + "'"]), "startDate": start_date, "endDate": end_date, "airType": "order" if air_type == "meituan" else air_type, "airline_col": "airline" if air_line != "all" else "'all' airline", "airline": "airline" if air_line != "all" else "'all'", "channelCond": " and channel = '21000000005' " if air_type == "meituan" else "", } logger.info(query) result = {"data": []} client = ClickHouseClient() for row in client.select("airlines", query): item = OrderedDict() item.setdefault("week", week_days[row.week]) item.setdefault("dall", row.dall) item.setdefault("d0", row.d0 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d1", row.d1 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d2", row.d2 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d3", row.d3 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d4", row.d4 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d5", row.d5 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d6", row.d6 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d7", row.d7 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d8", row.d8 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault("d9", row.d9 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault( "d10", row.d10 / float(row.dall) if row.dall != 0 else 0.0) item.setdefault( "dgt10", row.dgt10 / float(row.dall) if row.dall != 0 else 0.0) result["data"].append(item) for week_order in [1, 2, 3, 4, 5, 6, 7]: if week_days[week_order] not in [ item["week"] for item in result["data"] ]: result["data"].insert( week_order - 1, { "week": week_days[week_order], "dall": 0, "d0": 0.0, "d1": 0.0, "d2": 0.0, "d3": 0.0, "d4": 0.0, "d5": 0.0, "d6": 0.0, "d7": 0.0, "d8": 0.0, "d9": 0.0, "d10": 0.0, "dgt10": 0.0 }) return result
def run_query(self, datatype, params, result): try: params = params if isinstance(params, dict) else json.loads(params) start_day = params["startDay"] end_day = params["endDay"] funnel = params["funnel"] attrs = params.get("attrs", {}) # 查询天数最多为60天 tm_s_stamp = time.mktime(time.strptime(start_day, "%Y-%m-%d")) tm_e_stamp = time.mktime(time.strptime(end_day, "%Y-%m-%d")) num = (tm_e_stamp - tm_s_stamp) / 86400 if num > 60 or num < 0: return {"errinfo": "日期跨度超出范围!"} except: import sys import traceback exc_type, exc_value, exc_traceback = sys.exc_info() errinfo = traceback.format_exception(exc_type, exc_value, exc_traceback) logger.error(json.dumps(errinfo)) return {"errinfo": "传递参数错误!"} try: try: query = str(self.create_query_sql(datatype, start_day, end_day, funnel, attrs).decode("utf-8")) except: query = self.create_query_sql(datatype, start_day, end_day, funnel, attrs).decode("utf-8").encode("utf-8") funnel_length = self.funnel_length(funnel) except: import sys import traceback exc_type, exc_value, exc_traceback = sys.exc_info() errinfo = traceback.format_exception(exc_type, exc_value, exc_traceback) logger.error(json.dumps(errinfo)) return {"errinfo": "生成查询错误!"} logger.info(query) try: client = ClickHouseClient() for row in client.select(datatype, query): day = row.partition.strftime("%Y-%m-%d") funnel_step_user = [eval("row.step_%d" % (step+1, )) for step in range(0, funnel_length)] result.setdefault(day, funnel_step_user) # 单独处理任意事件 if len(self.every_event_indexes) != 0 and self.every_event_indexes[0] == 0: users_query = self.get_users_query(datatype, start_day, end_day, attrs) for row in client.select(datatype, users_query): day = row.partition.strftime("%Y-%m-%d") uv = row.uv for index in self.every_event_indexes: # 当任意事件在步骤一 if index == 0: result[day].insert(index, uv) # 当任意事件不在步骤一 else: result[day].insert(index, result[day][index-1]) except: import sys import traceback exc_type, exc_value, exc_traceback = sys.exc_info() errinfo = traceback.format_exception(exc_type, exc_value, exc_traceback) logger.error(json.dumps(errinfo)) return {"errinfo": "查询错误!"}