python如何解析复杂sql,实现数据库和表的提取的实例剖析
需求：
公司的数据分析师，提交一个sql,一般都三四百行。由于数据安全的需要，不能开放所有的数据库和数据表给数据分析师查询，所以需要解析sql中的数据库和表，与权限管理系统中记录的数据库和表权限信息比对，实现非法查询的拦截。
解决办法：
在解决这个问题前，现在github找了一下轮子，发现python下面除了sqlparse没什么好的解析数据库和表的轮轮。到是在java里面找到presto-parser解析的比较准。于是自己结合sqlparse源码写了个类，供大家参考，测试了一下，检测还是准的。
测试sql
select
b.product_name"产品",
count(a.order_id)"订单量",
b.selling_price_max"销售价",
b.gross_profit_rate_max/100"毛利率",
casewhenb.business_type=1then'自营消化'whenb.business_type=2then'服务商消化'end"消化模式"
from(select'CRM签单'label,date(d.update_ymd)close_ymd,c.product_name,c.product_id,
a.order_id,cast(a.recipient_amountasdouble)amt,d.cost
frommysql4.dataview_fenxiao.fx_ordera
leftjoinmysql4.dataview_fenxiao.fx_order_taskbona.order_id=b.order_id
leftjoinmysql7.dataview_trade.ddc_product_infoconcast(c.product_idasvarchar)=a.product_idsandc.snapshot_version='SELLING'
innerjoin(selectt1.par_order_id,max(t1.update_ymd)update_ymd,
sum(casewhent4.product2_type=1andt5.shop_idisnotnullthent5.priceelset1.order_hosted_priceend)cost
fromhive.bdc_dwd.dw_mk_ordert1
leftjoinhive.bdc_dwd.dw_mk_order_statust2ont1.order_id=t2.order_idandt2.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)
leftjoinmysql7.dataview_trade.mk_order_merchantt3ont1.order_id=t3.order_id
leftjoinmysql7.dataview_trade.ddc_product_infot4ont4.product_id=t3.MERCHANT_IDandt4.snapshot_version='SELLING'
leftjoinmysql4.dataview_scrm.sc_tprc_product_infot5ont5.product_id=t4.product_idandt5.shop_id=t1.seller_id
wheret1.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)
andt2.valid_statein(100,200)------有效订单
andt1.order_mode=10--------产品消耗订单
andt2.complete_state=1-----订单已经完成
groupbyt1.par_order_id
)dond.par_order_id=b.task_order_id
wherec.product_type=0anddate(from_unixtime(a.last_recipient_time))>date('2016-01-01')anda.payee_type<>1-----------已收款
UNIONALL
select'企业管家消耗'label,date(c.update_ymd)close_ymd,b.product_name,b.product_id,
a.task_id,(casewhena.yb_price=0andb.product2_type=1thenb.selling_price_minelsea.yb_priceend)amt,
(casewhena.yb_price=0andb.product2_type=2then0whenb.product2_type=1ande.shop_idisnotnullthene.priceelsec.order_hosted_priceend)cost
frommysql8.dataview_tprc.tprc_taska
leftjoinmysql7.dataview_trade.ddc_product_infobona.product_id=b.product_idandb.snapshot_version='SELLING'
innerjoinhive.bdc_dwd.dw_mk_ordercona.order_id=c.order_idandc.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)
leftjoinhive.bdc_dwd.dw_mk_order_statusdond.order_id=c.order_idandd.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)
leftjoinmysql4.dataview_scrm.sc_tprc_product_infoeone.product_id=b.product_idande.shop_id=c.seller_id
whered.valid_statein(100,200)andd.complete_state=1andc.order_mode=10
unionALL
select'交易管理系统'label,date(t6.close_ymd)close_ymd,t4.product_name,t4.product_id,
t1.order_id,(t1.order_hosted_price-t1.order_refund_price)amt,
(casewhent1.order_mode<>11thent7.user_amountwhent1.order_mode=11andt4.product2_type=1andt5.shop_idisnotnullthent5.priceelset8.costend)cost
fromhive.bdc_dwd.dw_mk_ordert1
leftjoinhive.bdc_dwd.dw_mk_order_businesst2ont1.order_id=t2.order_idandt2.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)
leftjoinmysql7.dataview_trade.mk_order_merchantt3ont1.order_id=t3.order_id
leftjoinmysql7.dataview_trade.ddc_product_infot4ont4.product_id=t3.MERCHANT_IDandt4.snapshot_version='SELLING'
leftjoinmysql4.dataview_scrm.sc_tprc_product_infot5ont5.product_id=t4.product_idandt5.shop_id=t1.seller_id
leftjoinhive.bdc_dwd.dw_fact_task_ss_dailyt6ont6.task_id=t2.task_idandt6.acct_time=date_format(date_add('day',-1,current_date),'%Y-%m-%d')
leftjoin(selecta.task_id,sum(a.user_amount)user_amount
fromhive.bdc_dwd.dw_fn_deal_asyn_ordera
wherea.is_new=1anda.service='Trade_Payment'anda.state=1anda.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)
groupbya.task_id)t7ont7.task_id=t2.task_id
leftjoin(selectt1.par_order_id,sum(t1.order_hosted_price-t1.order_refund_price)cost
fromhive.bdc_dwd.dw_mk_ordert1
wheret1.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)andt1.order_type=1andt1.order_stype=4andt1.order_mode=12
groupbyt1.par_order_id)t8ont1.order_id=t8.par_order_id
wheret1.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)
andt1.order_type=1andt1.order_stypein(4,5)andt1.order_mode<>12andt4.product_idisnotnullandt1.order_hosted_price>0andt6.is_deal=1andt6.close_ymd>='2018-12-31'
)a
leftjoinmysql7.dataview_trade.ddc_product_infobona.product_id=b.product_idandb.snapshot_version='SELLING'
whereb.product2_type=1-------标品
andclose_ymdbetweenDATE_ADD('day',-7,CURRENT_DATE)andDATE_ADD('day',-1,CURRENT_DATE)
GROUPBYb.product_name,
b.selling_price_max,
b.gross_profit_rate_max/100,
b.actrul_supply_num,
casewhenb.business_type=1then'自营消化'whenb.business_type=2then'服务商消化'end
orderbycount(a.order_id)desc
limit10
可以看到该sql比较杂，也没有格式化，不太好提取数据库和表。所以第一步需要对sql进行格式化
直接上代码:
#coding=utf-8
from__future__importabsolute_import
from__future__importdivision
from__future__importprint_function
from__future__importunicode_literals
importsqlparse
fromsqlparse.sqlimportIdentifier,IdentifierList
fromsqlparse.tokensimportKeyword,Name
RESULT_OPERATIONS={'UNION','INTERSECT','EXCEPT','SELECT'}
ON_KEYWORD='ON'
PRECEDES_TABLE_NAME={'FROM','JOIN','DESC','DESCRIBE','WITH'}
classBaseExtractor(object):
def__init__(self,sql_statement):
self.sql=sqlparse.format(sql_statement,reindent=True,keyword_case='upper')
self._table_names=set()
self._alias_names=set()
self._limit=None
self._parsed=sqlparse.parse(self.stripped())
forstatementinself._parsed:
self.__extract_from_token(statement)
self._limit=self._extract_limit_from_query(statement)
self._table_names=self._table_names-self._alias_names
@property
deftables(self):
returnself._table_names
@property
deflimit(self):
returnself._limit
defis_select(self):
returnself._parsed[0].get_type()=='SELECT'
defis_explain(self):
returnself.stripped().upper().startswith('EXPLAIN')
defis_readonly(self):
returnself.is_select()orself.is_explain()
defstripped(self):
returnself.sql.strip('\t\n;')
defget_statements(self):
statements=[]
forstatementinself._parsed:
ifstatement:
sql=str(statement).strip('\n;\t')
ifsql:
statements.append(sql)
returnstatements
@staticmethod
def__precedes_table_name(token_value):
forkeywordinPRECEDES_TABLE_NAME:
ifkeywordintoken_value:
returnTrue
returnFalse
@staticmethod
defget_full_name(identifier):
iflen(identifier.tokens)>1andidentifier.tokens[1].value=='.':
return'{}.{}'.format(identifier.tokens[0].value,
identifier.tokens[2].value)
returnidentifier.get_real_name()
@staticmethod
def__is_result_operation(keyword):
foroperationinRESULT_OPERATIONS:
ifoperationinkeyword.upper():
returnTrue
returnFalse
@staticmethod
def__is_identifier(token):
returnisinstance(token,(IdentifierList,Identifier))
def__process_identifier(self,identifier):
if'('notin'{}'.format(identifier):
self._table_names.add(self.get_full_name(identifier))
return
#storealiases
ifhasattr(identifier,'get_alias'):
self._alias_names.add(identifier.get_alias())
ifhasattr(identifier,'tokens'):
#somealiasesarenotparsedproperly
ifidentifier.tokens[0].ttype==Name:
self._alias_names.add(identifier.tokens[0].value)
self.__extract_from_token(identifier)
defas_create_table(self,table_name,overwrite=False):
exec_sql=''
sql=self.stripped()
ifoverwrite:
exec_sql='DROPTABLEIFEXISTS{};\n'.format(table_name)
exec_sql+='CREATETABLE{}AS\n{}'.format(table_name,sql)
returnexec_sql
def__extract_from_token(self,token):
ifnothasattr(token,'tokens'):
return
table_name_preceding_token=False
foritemintoken.tokens:
ifitem.is_groupandnotself.__is_identifier(item):
self.__extract_from_token(item)
ifitem.ttypeinKeyword:
ifself.__precedes_table_name(item.value.upper()):
table_name_preceding_token=True
continue
ifnottable_name_preceding_token:
continue
ifitem.ttypeinKeywordoritem.value==',':
if(self.__is_result_operation(item.value)or
item.value.upper()==ON_KEYWORD):
table_name_preceding_token=False
continue
#FROMclauseisover
break
ifisinstance(item,Identifier):
self.__process_identifier(item)
ifisinstance(item,IdentifierList):
fortokeninitem.tokens:
ifself.__is_identifier(token):
self.__process_identifier(token)
def_get_limit_from_token(self,token):
iftoken.ttype==sqlparse.tokens.Literal.Number.Integer:
returnint(token.value)
eliftoken.is_group:
returnint(token.get_token_at_offset(1).value)
def_extract_limit_from_query(self,statement):
limit_token=None
forpos,iteminenumerate(statement.tokens):
ifitem.ttypeinKeywordanditem.value.lower()=='limit':
limit_token=statement.tokens[pos+2]
returnself._get_limit_from_token(limit_token)
defget_query_with_new_limit(self,new_limit):
ifnotself._limit:
returnself.sql+'LIMIT'+str(new_limit)
limit_pos=None
tokens=self._parsed[0].tokens
#Addallitemstobefore_struntilthereisalimit
forpos,iteminenumerate(tokens):
ifitem.ttypeinKeywordanditem.value.lower()=='limit':
limit_pos=pos
break
limit=tokens[limit_pos+2]
iflimit.ttype==sqlparse.tokens.Literal.Number.Integer:
tokens[limit_pos+2].value=new_limit
eliflimit.is_group:
tokens[limit_pos+2].value=(
'{},{}'.format(next(limit.get_identifiers()),new_limit)
)
str_res=''
foriintokens:
str_res+=str(i.value)
returnstr_res
classSqlExtractor(BaseExtractor):
"""提取sql语句"""
@staticmethod
defget_full_name(identifier,including_dbs=False):
iflen(identifier.tokens)>1andidentifier.tokens[1].value=='.':
a=identifier.tokens[0].value
b=identifier.tokens[2].value
db_table=(a,b)
full_tree='{}.{}'.format(a,b)
iflen(identifier.tokens)==3:
returnfull_tree
else:
i=identifier.tokens[3].value
c=identifier.tokens[4].value
ifi=='':
returnfull_tree
full_tree='{}.{}.{}'.format(a,b,c)
returnfull_tree
returnNone,None
if__name__=='__main__':
sql="""select
b.product_name"产品",
count(a.order_id)"订单量",
b.selling_price_max"销售价",
b.gross_profit_rate_max/100"毛利率",
casewhenb.business_type=1then'自营消化'whenb.business_type=2then'服务商消化'end"消化模式"
from(select'CRM签单'label,date(d.update_ymd)close_ymd,c.product_name,c.product_id,
a.order_id,cast(a.recipient_amountasdouble)amt,d.cost
frommysql4.dataview_fenxiao.fx_ordera
leftjoinmysql4.dataview_fenxiao.fx_order_taskbona.order_id=b.order_id
leftjoinmysql7.dataview_trade.ddc_product_infoconcast(c.product_idasvarchar)=a.product_idsandc.snapshot_version='SELLING'
innerjoin(selectt1.par_order_id,max(t1.update_ymd)update_ymd,
sum(casewhent4.product2_type=1andt5.shop_idisnotnullthent5.priceelset1.order_hosted_priceend)cost
fromhive.bdc_dwd.dw_mk_ordert1
leftjoinhive.bdc_dwd.dw_mk_order_statust2ont1.order_id=t2.order_idandt2.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)
leftjoinmysql7.dataview_trade.mk_order_merchantt3ont1.order_id=t3.order_id
leftjoinmysql7.dataview_trade.ddc_product_infot4ont4.product_id=t3.MERCHANT_IDandt4.snapshot_version='SELLING'
leftjoinmysql4.dataview_scrm.sc_tprc_product_infot5ont5.product_id=t4.product_idandt5.shop_id=t1.seller_id
wheret1.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)
andt2.valid_statein(100,200)------有效订单
andt1.order_mode=10--------产品消耗订单
andt2.complete_state=1-----订单已经完成
groupbyt1.par_order_id
)dond.par_order_id=b.task_order_id
wherec.product_type=0anddate(from_unixtime(a.last_recipient_time))>date('2016-01-01')anda.payee_type<>1-----------已收款
UNIONALL
select'企业管家消耗'label,date(c.update_ymd)close_ymd,b.product_name,b.product_id,
a.task_id,(casewhena.yb_price=0andb.product2_type=1thenb.selling_price_minelsea.yb_priceend)amt,
(casewhena.yb_price=0andb.product2_type=2then0whenb.product2_type=1ande.shop_idisnotnullthene.priceelsec.order_hosted_priceend)cost
frommysql8.dataview_tprc.tprc_taska
leftjoinmysql7.dataview_trade.ddc_product_infobona.product_id=b.product_idandb.snapshot_version='SELLING'
innerjoinhive.bdc_dwd.dw_mk_ordercona.order_id=c.order_idandc.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)
leftjoinhive.bdc_dwd.dw_mk_order_statusdond.order_id=c.order_idandd.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)
leftjoinmysql4.dataview_scrm.sc_tprc_product_infoeone.product_id=b.product_idande.shop_id=c.seller_id
whered.valid_statein(100,200)andd.complete_state=1andc.order_mode=10
unionALL
select'交易管理系统'label,date(t6.close_ymd)close_ymd,t4.product_name,t4.product_id,
t1.order_id,(t1.order_hosted_price-t1.order_refund_price)amt,
(casewhent1.order_mode<>11thent7.user_amountwhent1.order_mode=11andt4.product2_type=1andt5.shop_idisnotnullthent5.priceelset8.costend)cost
fromhive.bdc_dwd.dw_mk_ordert1
leftjoinhive.bdc_dwd.dw_mk_order_businesst2ont1.order_id=t2.order_idandt2.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)
leftjoinmysql7.dataview_trade.mk_order_merchantt3ont1.order_id=t3.order_id
leftjoinmysql7.dataview_trade.ddc_product_infot4ont4.product_id=t3.MERCHANT_IDandt4.snapshot_version='SELLING'
leftjoinmysql4.dataview_scrm.sc_tprc_product_infot5ont5.product_id=t4.product_idandt5.shop_id=t1.seller_id
leftjoinhive.bdc_dwd.dw_fact_task_ss_dailyt6ont6.task_id=t2.task_idandt6.acct_time=date_format(date_add('day',-1,current_date),'%Y-%m-%d')
leftjoin(selecta.task_id,sum(a.user_amount)user_amount
fromhive.bdc_dwd.dw_fn_deal_asyn_ordera
wherea.is_new=1anda.service='Trade_Payment'anda.state=1anda.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)
groupbya.task_id)t7ont7.task_id=t2.task_id
leftjoin(selectt1.par_order_id,sum(t1.order_hosted_price-t1.order_refund_price)cost
fromhive.bdc_dwd.dw_mk_ordert1
wheret1.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)andt1.order_type=1andt1.order_stype=4andt1.order_mode=12
groupbyt1.par_order_id)t8ont1.order_id=t8.par_order_id
wheret1.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)asvarchar),9,2)
andt1.order_type=1andt1.order_stypein(4,5)andt1.order_mode<>12andt4.product_idisnotnullandt1.order_hosted_price>0andt6.is_deal=1andt6.close_ymd>='2018-12-31'
)a
leftjoinmysql7.dataview_trade.ddc_product_infobona.product_id=b.product_idandb.snapshot_version='SELLING'
whereb.product2_type=1-------标品
andclose_ymdbetweenDATE_ADD('day',-7,CURRENT_DATE)andDATE_ADD('day',-1,CURRENT_DATE)
GROUPBYb.product_name,
b.selling_price_max,
b.gross_profit_rate_max/100,
b.actrul_supply_num,
casewhenb.business_type=1then'自营消化'whenb.business_type=2then'服务商消化'end
orderbycount(a.order_id)desc
limit10"""
sql_extractor=SqlExtractor(sql)
print(sql_extractor.sql)
print(sql_extractor.tables)
输出结果：
{'mysql8.dataview_tprc.tprc_task','hive.bdc_dwd.dw_mk_order','mysql4.dataview_fenxiao.fx_order_task','mysql4.dataview_fenxiao.fx_order','hive.bdc_dwd.dw_mk_order_business','mysql7.dataview_trade.mk_order_merchant','mysql4.dataview_scrm.sc_tprc_product_info','hive.bdc_dwd.dw_fn_deal_asyn_order','hive.bdc_dwd.dw_fact_task_ss_daily','mysql7.dataview_trade.ddc_product_info','hive.bdc_dwd.dw_mk_order_status'}
格式化结果:
SELECTb.product_name"产品",
count(a.order_id)"订单量",
b.selling_price_max"销售价",
b.gross_profit_rate_max/100"毛利率",
CASE
WHENb.business_type=1THEN'自营消化'
WHENb.business_type=2THEN'服务商消化'
END"消化模式"from
(SELECT'CRM签单'label,date(d.update_ymd)close_ymd,c.product_name,c.product_id,a.order_id,cast(a.recipient_amountASDOUBLE)amt,d.cost
FROMmysql4.dataview_fenxiao.fx_ordera
LEFTJOINmysql4.dataview_fenxiao.fx_order_taskbONa.order_id=b.order_id
LEFTJOINmysql7.dataview_trade.ddc_product_infocONcast(c.product_idASvarchar)=a.product_ids
ANDc.snapshot_version='SELLING'
INNERJOIN
(SELECTt1.par_order_id,max(t1.update_ymd)update_ymd,sum(CASE
WHENt4.product2_type=1
ANDt5.shop_idISNOTNULLTHENt5.price
ELSEt1.order_hosted_price
END)cost
FROMhive.bdc_dwd.dw_mk_ordert1
LEFTJOINhive.bdc_dwd.dw_mk_order_statust2ONt1.order_id=t2.order_id
ANDt2.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)ASvarchar),9,2)
LEFTJOINmysql7.dataview_trade.mk_order_merchantt3ONt1.order_id=t3.order_id
LEFTJOINmysql7.dataview_trade.ddc_product_infot4ONt4.product_id=t3.MERCHANT_ID
ANDt4.snapshot_version='SELLING'
LEFTJOINmysql4.dataview_scrm.sc_tprc_product_infot5ONt5.product_id=t4.product_id
ANDt5.shop_id=t1.seller_id
WHEREt1.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)ASvarchar),9,2)
ANDt2.valid_stateIN(100,200)------有效订单
ANDt1.order_mode=10--------产品消耗订单
ANDt2.complete_state=1-----订单已经完成
GROUPBYt1.par_order_id)dONd.par_order_id=b.task_order_id
WHEREc.product_type=0
ANDdate(from_unixtime(a.last_recipient_time))>date('2016-01-01')
ANDa.payee_type<>1-----------已收款
UNIONALLSELECT'企业管家消耗'label,date(c.update_ymd)close_ymd,b.product_name,b.product_id,a.task_id,(CASE
WHENa.yb_price=0
ANDb.product2_type=1THENb.selling_price_min
ELSEa.yb_price
END)amt,(CASE
WHENa.yb_price=0
ANDb.product2_type=2THEN0
WHENb.product2_type=1
ANDe.shop_idISNOTNULLTHENe.price
ELSEc.order_hosted_price
END)cost
FROMmysql8.dataview_tprc.tprc_taska
LEFTJOINmysql7.dataview_trade.ddc_product_infobONa.product_id=b.product_id
ANDb.snapshot_version='SELLING'
INNERJOINhive.bdc_dwd.dw_mk_ordercONa.order_id=c.order_id
ANDc.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)ASvarchar),9,2)
LEFTJOINhive.bdc_dwd.dw_mk_order_statusdONd.order_id=c.order_id
ANDd.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)ASvarchar),9,2)
LEFTJOINmysql4.dataview_scrm.sc_tprc_product_infoeONe.product_id=b.product_id
ANDe.shop_id=c.seller_id
WHEREd.valid_stateIN(100,200)
ANDd.complete_state=1
ANDc.order_mode=10
UNIONALLSELECT'交易管理系统'label,date(t6.close_ymd)close_ymd,t4.product_name,t4.product_id,t1.order_id,(t1.order_hosted_price-t1.order_refund_price)amt,(CASE
WHENt1.order_mode<>11THENt7.user_amount
WHENt1.order_mode=11
ANDt4.product2_type=1
ANDt5.shop_idISNOTNULLTHENt5.price
ELSEt8.cost
END)cost
FROMhive.bdc_dwd.dw_mk_ordert1
LEFTJOINhive.bdc_dwd.dw_mk_order_businesst2ONt1.order_id=t2.order_id
ANDt2.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)ASvarchar),9,2)
LEFTJOINmysql7.dataview_trade.mk_order_merchantt3ONt1.order_id=t3.order_id
LEFTJOINmysql7.dataview_trade.ddc_product_infot4ONt4.product_id=t3.MERCHANT_ID
ANDt4.snapshot_version='SELLING'
LEFTJOINmysql4.dataview_scrm.sc_tprc_product_infot5ONt5.product_id=t4.product_id
ANDt5.shop_id=t1.seller_id
LEFTJOINhive.bdc_dwd.dw_fact_task_ss_dailyt6ONt6.task_id=t2.task_id
ANDt6.acct_time=date_format(date_add('day',-1,CURRENT_DATE),'%Y-%m-%d')
LEFTJOIN
(SELECTa.task_id,sum(a.user_amount)user_amount
FROMhive.bdc_dwd.dw_fn_deal_asyn_ordera
WHEREa.is_new=1
ANDa.service='Trade_Payment'
ANDa.state=1
ANDa.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)ASvarchar),9,2)
GROUPBYa.task_id)t7ONt7.task_id=t2.task_id
LEFTJOIN
(SELECTt1.par_order_id,sum(t1.order_hosted_price-t1.order_refund_price)cost
FROMhive.bdc_dwd.dw_mk_ordert1
WHEREt1.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)ASvarchar),9,2)
ANDt1.order_type=1
ANDt1.order_stype=4
ANDt1.order_mode=12
GROUPBYt1.par_order_id)t8ONt1.order_id=t8.par_order_id
WHEREt1.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE)ASvarchar),9,2)
ANDt1.order_type=1
ANDt1.order_stypeIN(4,5)
ANDt1.order_mode<>12
ANDt4.product_idISNOTNULL
ANDt1.order_hosted_price>0
ANDt6.is_deal=1
ANDt6.close_ymd>='2018-12-31')a
LEFTJOINmysql7.dataview_trade.ddc_product_infobONa.product_id=b.product_id
ANDb.snapshot_version='SELLING'
WHEREb.product2_type=1-------标品
ANDclose_ymdBETWEENDATE_ADD('day',-7,CURRENT_DATE)ANDDATE_ADD('day',-1,CURRENT_DATE)
GROUPBYb.product_name,
b.selling_price_max,
b.gross_profit_rate_max/100,
b.actrul_supply_num,
CASE
WHENb.business_type=1THEN'自营消化'
WHENb.business_type=2THEN'服务商消化'
END
ORDERBYcount(a.order_id)DESC
LIMIT10
以上这篇python如何解析复杂sql,实现数据库和表的提取的实例剖析就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。