import ast import json import re import sys import traceback import pandas as pd # 聚合函数 AGGREGATION_MAP = { 'SUM': 'SUM', 'AVG': 'AVG', 'CNT': 'COUNT', 'MAX': 'MAX', 'MIN': 'MIN', 'CNT_DISTINCT': 'COUNT(DISTINCT {})' } AGGREGATION_SUFFIX_MAP = { 'SUM': '求和', 'AVG': '均值', 'CNT': '计数', 'MAX': '最大值', 'MIN': '最小值', 'CNT_DISTINCT': '去重计数' } # 筛选操作符 FILTER_OPERATOR_MAP = { 'BT': {"val_nums": 2, 'template': "{field} BETWEEN {value_1} AND {value_2}"}, 'CLOSE_BT_OPEN': {"val_nums": 2, 'template': "{field} >= {value_1} AND {field} < {value_2}"}, 'EQ': {"val_nums": 1, 'template': "{field} = {value}"}, 'GE': {"val_nums": 1, 'template': "{field} >= {value}"}, 'GT': {"val_nums": 1, 'template': "{field} > {value}"}, 'IN': {"val_nums": 9, 'template': "{field} IN ({values})"}, 'IS_NULL': {"val_nums": 0, 'template': "{field} IS NULL"}, 'LE': {"val_nums": 1, 'template': "{field} <= {value}"}, 'LT': {"val_nums": 1, 'template': "{field} < {value}"}, 'NE': {"val_nums": 1, 'template': "{field} != {value}"}, 'NI': {"val_nums": 9, 'template': "{field} NOT IN ({values})"}, 'NOT_NULL': {"val_nums": 0, 'template': "{field} IS NOT NULL"}, 'OPEN_BT_CLOSE': {"val_nums": 2, 'template': "{field} > {value_1} AND {field} <= {value_2}"}, 'OPEN_BT_OPEN': {"val_nums": 2, 'template': "{field} > {value_1} AND {field} < {value_2}"}, 'CONTAINS': {"val_nums": 1, 'template': "{field} LIKE '%{value}%'"}, 'NOT_CONTAINS': {"val_nums": 1, 'template': "{field} NOT LIKE '%{value}%'"}, 'STARTS_WITH': {"val_nums": 1, 'template': "{field} LIKE '{value}%'"}, 'NOT_STARTS_WITH': {"val_nums": 1, 'template': "{field} NOT LIKE '{value}%'"}, 'ENDS_WITH': {"val_nums": 1, 'template': "{field} LIKE '%{value}'"}, 'NOT_ENDS_WITH': {"val_nums": 1, 'template': "{field} NOT LIKE '%{value}'"}, 'CUSTOM': 'CUSTOM', 'SPARK_EXPR': 'SPARK_EXPR' } # 引用 IDENTIFIER_QUOTE = '`' QUOTE_FLAG = True # 副词 ADV_FILTER_EXP_MAP = { 'TODAY': "{field} = '{{today}}'", 'YESTERDAY': "{field} = date_sub('{{today}}', 1)", 'DAY_BEFORE_YESTERDAY': "{field} = date_sub('{{today}}', 2)", 'LAST_14_DAY': "{field} between date_sub('{{today}}', 13) and '{{today}}'", 'LAST_1_YEAR': "{field} between date_sub('{{today}}', 364) and '{{today}}'", 'LAST_30_DAY': "{field} between date_sub('{{today}}', 29) and '{{today}}'", 'LAST_7_DAY': "{field} between date_sub('{{today}}', 6) and '{{today}}'", 'LAST_90_DAY': "{field} between date_sub('{{today}}', 89) and '{{today}}'", 'LAST_MONTH': "{field} between datesub(add_months('{{today}}', -1), day('{{today}}') - 1) and date_sub('{{today}}', day('{{today}}'))", 'LAST_WEEK': "{field} between date_sub('{{today}}', case when dayofweek('{{today}}') = 1 then 13 else dayofweek('{{today}}')+5 end) and date_sub('{{today}}', case when dayofweek('{{today}}') = 1 then 7 else dayofweek('{{today}}')-1 end)", 'MONTH_BEFORE_LAST_MONTH': "{field} between date_sub(add_months('{{today}}', -2), day(add_months('{{today}}', -2))-1) and date_sub(add_months('{{today}}', -1), day(add_months('{{today}}', -1)))", 'MONTH_TO_DAY': "{field} between date_sub('{{today}}', day('{{today}}')-1) and '{{today}}'", 'MONTH_TO_YESTERDAY': "{field} between date_sub('{{today}}', day('{{today}}')-1) and date_sub('{{today}}', 1)", 'QUARTER_TO_DAY': """{field} between concat(year('{{today}}'), case when quarter('{{today}}') = 1 then '-01-01' when quarter('{{today}}') = 2 then '-04-01' when quarter('{{today}}') = 3 then '-07-01' when quarter('{{today}}') = 4 then '-10-01' end) and '{{today}}'""", 'QUARTER_TO_YESTERDAY': """{field} between concat(year('{{today}}'), case when quarter('{{today}}') = 1 then '-01-01' when quarter('{{today}}') = 2 then '-04-01' when quarter('{{today}}') = 3 then '-07-01' when quarter('{{today}}') = 4 then '-10-01' end) and date_sub('{{today}}', 1)""", 'WEEK_TO_DAY': "{field} between date_sub('{{today}}', (dayofweek('{{today}}')+5)%7) and '{{today}}'", 'WEEK_TO_YESTERDAY': "{field} between date_sub('{{today}}', (dayofweek('{{today}}')+5)%7) and date_sub('{{today}}', 1)", 'YEAR_TO_DAY': "{field} between concat(year('{{today}}'), '-01-01') and '{{today}}'", 'YEAR_TO_LAST_MONTH': "{field} between concat(year('{{today}}'), '-01-01') and date_sub('{{today}}', day('{{today}}'))", 'YEAR_TO_LAST_QUARTER': """{field} between concat(year('{{today}}'), '-01-01') and date_sub(concat(year('{{today}}'), case when quarter('{{today}}') = 1 then '-01-01' when quarter('{{today}}') = 2 then '-04-01' when quarter('{{today}}') = 3 then '-07-01' when quarter('{{today}}') = 4 then '-10-01' end), 1)""", 'YEAR_TO_YESTERDAY': "{field} between concat(year('{{today}}'), '-01-01') and date_sub('{{today}}', 1)", } # 自带日期转换 PARTIAL_DATE_EXPRESSION = { 'day': "`{old}` as `{new}`", 'month': "concat(year(`{old}`), '-', lpad(month(`{old}`), 2, '0') as `{new}`", 'year': "year(`{old}`) as `{new}`", 'quarter': "concat(year(`{old}`), '-S', quarter(`{old}`)) as `{new}`", 'week': "weekofyear(`{old}`) as `{new}`", 'dayofweek': "dayofweek(`{old}`)-1 as `{new}`", 'hour': "hour(`{old}`)-8 as `{new}`", 'minute': "minute(`{old}`)-8 as `{new}`", } # 获取新增字段 def get_added_fields_info(added_fields_df): if added_fields_df.empty: return {} added_fields_info = {} for _, row in added_fields_df.iterrows(): try: content = json.loads(row['calc_field_logic']) except: print(f'ERROR: 新增字段解析错误: {row["calc_field_logic"]}') continue field_name = content['name'] field_id = content['fdId'] added_fields_info[field_id] = {"field_name": field_name, 'calculation': content} added_fields_info[field_name] = {"field_id": field_id, 'calculation': content} return added_fields_info def get_fid_name_map(field_def_df): field_id_name = {} for i, row in field_def_df.iterrows(): field_id_name[row['field_id']] = row['field_name'] return field_id_name # 字段映射关系 def get_fields_rename_map(field_info): ret = {} try: tmp_map = json.loads(field_info) except: return ret dimensions, metrics = tmp_map.get("dimensions"), tmp_map.get("metrics") if dimensions and dimensions != 'null': for one_map in dimensions: ret[one_map["name"]] = one_map["alias"] if metrics and metrics != 'null': for one_map in metrics: ret[one_map["name"]] = one_map["alias"] return ret # 处理过滤条件中的consolidation def get_consolidation_field(consolidation_dict): field_name = quote_identifier(consolidation_dict["sourceName"]) group_type = consolidation_dict["groupType"] fd_type = consolidation_dict["sourceFdType"] group_rules = consolidation_dict.get('groups') fixed_step = consolidation_dict.get('fixedStepSetting') when_part = [] else_part = None if group_type == 'ITEM': for group in group_rules: group_name = group["groupName"] if group.get('isOtherGroup', False): else_part = f"ELSE '{group_name}'" else: selected_values = group.get('selectedValues', []) when_value = str(selected_values) when_value = when_value[1:-1] # 去除中括号 if when_value is None: when_part.append(f"WHEN {field_name} IS NULL THEN '{group_name}'") else: when_part.append(f"WHEN {field_name} IN ({when_value}) THEN '{group_name}'") elif group_type == "CONDITION": for group in group_rules: group_name = group["groupName"] if group.get('isOtherGroup', False): else_part = f"ELSE '{group_name}'" else: rules = group["rules"] cond_list = [] combine_type = " " + rules["combineType"] + " " # 加空格以便join for cond in rules["conditions"]: filter_type = cond["filterType"] filter_value = cond["filterValue"] op_dict = FILTER_OPERATOR_MAP[filter_type] format_args = get_format_args(field_name, fd_type, op_dict, [filter_value]) cond_str = op_dict["template"].format(**format_args) cond_list.append(cond_str) when_str = "WHEN " + combine_type.join(cond_list) + f" THEN '{group_name}'" when_part.append(when_str) elif group_type == "CUSTOM_STEP": for group in group_rules: group_name = group["groupName"] if group.get('isOtherGroup', False): else_part = f"ELSE '{group_name}'" else: setting = group['customStepSetting'] operator = setting['operator'] start = setting['startValue'] end = setting['endValue'] condition = '' if operator == 'BT': condition = f"{field_name} BETWEEN {start} AND {end}" elif operator == 'OPEN_BT_CLOSE': condition = f"{field_name} > {start} AND {field_name} <= {end}" elif operator == 'OPEN_BT_OPEN': condition = f"{field_name} > {start} AND {field_name} < {end}" elif operator == 'CLOSE_BT_OPEN': condition = f"{field_name} >= {start} AND {field_name} < {end}" else: raise ValueError(f"未知的操作符: {operator}") when_part.append(f"WHEN {condition} THEN '{group_name}'") elif group_type == "FIXED_STEP": start = fixed_step['startValue'] end = fixed_step['endValue'] step = fixed_step['stepSize'] # 生成每个区间的case when部分 lower = start while lower < end: upper = lower + step case_part = f"WHEN {field_name} >= {lower} AND {field_name} < {upper} THEN '{lower}-{upper}'" when_part.append(case_part) lower = upper # 处理最后一个区间 case_part = f"WHEN {field_name} >= {lower} AND {field_name} <= {end} THEN '{lower}-{end}'" when_part.append(case_part) else_part = "ELSE NULL" else: raise ValueError(f"未知的groupType: {group_type}") field = "CASE " field += "\n".join(when_part) if else_part: field += f"\n{else_part}" field += "\nEND" return field def build_with_part(new_date_fields, new_dimension_fields, dataset_fid_name_map, added_fields_info, dataset_id): sql_part = 'WITH tmp as (\nSELECT *,\n' with_expressions = [] for fid, new_name in new_date_fields: old_fid, partial_date = fid.split('_') if old_fid in dataset_fid_name_map: old_name = dataset_fid_name_map[old_fid] elif old_fid in added_fields_info: old_name = added_fields_info[old_fid]["calculation"]["formula"].replace("[]", "").replace("]", '') else: raise ValueError(f"字段 {fid} {new_name} 不存在") tmp_part = PARTIAL_DATE_EXPRESSION.get(partial_date, None) if tmp_part: tmp_part = tmp_part.format(old=old_name, new=new_name) with_expressions.append(tmp_part) else: raise ValueError(f"日期转换方式 {partial_date} 不存在") for fid, new_name in new_dimension_fields: field_def = added_fields_info[fid] new_name = field_def["field_name"] formula = field_def["calculation"]["formula"] if "consolidation" in formula: consolidation_dict = json.loads(formula)["consolidation"] tmp_part = get_consolidation_field(consolidation_dict) tmp_part += f" AS `{new_name}`" else: tmp_part = quote_identifier(formula, formula=True) + f" AS `{new_name}`" with_expressions.append(tmp_part) sql_part += ',\n'.join(with_expressions) sql_part += f"\nFROM `{dataset_id}\n`" return sql_part # 处理计算字段 def process_calculation_fields(measure_fields, measure_aggs, calculation_fields, card_id, card_name): ## 数值字段数量 小于 聚合函数数量，不合法 if len(measure_fields) < len(measure_aggs): print(f"警告: 卡片 {card_id} {card_name}: 数值字段数量小于聚合函数数量，不合法") print(f"警告: 卡片 {card_id} {card_name}: 不添加任何数值字段.") return [], [], False ## 数值字段 大于 聚合函数数量，存在聚合类型的计算字段，尝试填充 elif len(measure_fields) > len(measure_aggs): ## 计算数值字段数量 num_cals = 0 for field in measure_fields: if field in calculation_fields and calculation_fields[field]["calculation"]["isAggregated"] is True: num_cals += 1 ## 如果不存在任何计算字段，补全剩余的NUL聚合函数 if num_cals == 0: measure_aggs.extend(['NULL'] * (len(measure_fields) - len(measure_aggs))) return measure_fields, measure_aggs, True ## 如果存在计算字段，且相加后的 聚合函数数量 仍小于 数值字段数量，不合法 if num_cals + len(measure_aggs) != len(measure_fields): print(f"警告: 卡片 {card_id} {card_name}: 数值字段数量大于聚合函数数量，不合法") print(f"警告: 卡片 {card_id} {card_name}: 不添加任何数值字段.") return [], [], False ## 通过验证，填充聚合函数 new_measure_fields, new_measure_aggs, agg_flag = [], [], False for i, field in enumerate(measure_fields): ## 非计算字段 if field not in calculation_fields: new_measure_fields.append(quote_identifier(field)) new_measure_aggs.append(measure_aggs.pop(0)) ## 计算字段 else: formula = calculation_fields[field]["calculation"]["formula"] formula = formula.replace('\n', '') new_measure_fields.append(quote_identifier(formula, formula=True)) if calculation_fields[field]["calculation"]["isAggregated"] is True: new_measure_aggs.append("NUL") agg_flag = True else: new_measure_aggs.append(measure_aggs.pop(0)) return new_measure_fields, new_measure_aggs, agg_flag def quote_identifier(identifier, formula=False): if not QUOTE_FLAG: return identifier if not identifier: return '' # 简单处理，如果包含非字母数字下划线或可能是关键字，则加反引号 # 更复杂的关键字检查可以添加 if formula: params = re.findall(r"\[DYNAMIC_PARAMS\.\w+\]", identifier) for p in params: subs = p[1:-1] subs = "{{{"+subs+"}}}" identifier = identifier.replace(p, subs, 1) identifier = identifier.replace('[', IDENTIFIER_QUOTE).replace(']', IDENTIFIER_QUOTE) else: identifier = identifier.replace('\n', ' ') if not re.match(r'[a-zA-Z_][a-zA-Z0-9_]*$', identifier): return f'{IDENTIFIER_QUOTE}{identifier}{IDENTIFIER_QUOTE}' return identifier def parse_multi_value_field(field_value): # 解析包含多个值的字段 if not field_value or field_value == "": return [] try: res = ast.literal_eval(field_value) except Exception: print(field_value) print(traceback.format_exc()) return ast.literal_eval(field_value) # 处理过滤条件的操作符 def get_format_args(field, fd_type, op_dict, values): # 按照数据类型及操作符，判断是否需要加引号 if fd_type in ('DECIMAL', 'DOUBLE', 'INT', 'FLOAT', 'LONG', 'SHORT'): values = [x for x in values if x] elif fd_type in ('DATE', 'STRING', 'SUB_DATE', 'TIMESTAMP'): if op_dict.get('quote', True): values = [f"'{x}'" for x in values] elif fd_type == 'BOOL': values = [value.upper() for value in values] else: pass # 按照操作符所需参数个数构造format参数 format_dict = {} value_nums = op_dict['val_nums'] if value_nums == 9: format_dict.update(**{"values": ", ".join(values)}) elif value_nums == 2: format_dict.update(**{"value_1": values[0], "value_2": values[1]}) elif value_nums == 1: format_dict.update(**{"value": values[0]}) else: pass format_dict["field"] = field return format_dict def parse_filter_string(filter_relation_str): conditions = {} if not filter_relation_str or filter_relation_str == "[]": return conditions raw_conditions = json.loads(filter_relation_str) for cond_dict in raw_conditions: fdId = cond_dict.get("fdId") field = cond_dict.get("name") fd_type = cond_dict.get("fdType") op_name = cond_dict.get("filterType") op_dict = FILTER_OPERATOR_MAP.get(op_name) values = cond_dict.get("filterValue") # list is_aggregated = cond_dict.get("isAggregated", False) # 检查条件合法 if any([fdId is None, field is None, fd_type is None, op_name is None, values is None]): print(f"fdId: {fdId} field: {field} fd_type: {fd_type} op_name: {op_name} values: {values}") print(f"警告: 无法解析筛选条件，缺少必须字段，跳过此条件。") continue if op_dict is None: print(f"警告: 无法解析筛选条件，未定义的筛选类型: {op_name}，跳过此条件。") continue # 特殊操作符 if op_dict == 'CUSTOM': if "advFilter" not in cond_dict: print(f"警告: CUSTOM筛选类型不存在advFilter, 跳过此条件。") continue if 'formula' in cond_dict: field = quote_identifier(cond_dict['formula'], formula=True) else: field = quote_identifier(cond_dict['name']) expression = ADV_FILTER_EXP_MAP.get(cond_dict["advFilter"]) if not expression: print(f"警告: CUSTOM筛选类型出现未定义的advFilter: {cond_dict['advFilter']}, 跳过此条件。") continue expression = expression.format(field=field) conditions[fdId] = {"exp": expression, "agg": is_aggregated} continue elif op_dict == 'SPARK_EXPR': if 'formula' in cond_dict: