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": "查询错误！"}