python如何解析復(fù)雜sql,實(shí)現(xiàn)數(shù)據(jù)庫和表的提取的實(shí)例剖析
更新時(shí)間：2020年05月15日 12:16:32 作者：birdflyinhigh
這篇文章主要介紹了python如何解析復(fù)雜sql,實(shí)現(xiàn)數(shù)據(jù)庫和表的提取的實(shí)例剖析，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看看吧
需求：
公司的數(shù)據(jù)分析師，提交一個(gè)sql, 一般都三四百行。由于數(shù)據(jù)安全的需要，不能開放所有的數(shù)據(jù)庫和數(shù)據(jù)表給數(shù)據(jù)分析師查詢，所以需要解析sql中的數(shù)據(jù)庫和表，與權(quán)限管理系統(tǒng)中記錄的數(shù)據(jù)庫和表權(quán)限信息比對，實(shí)現(xiàn)非法查詢的攔截。
解決辦法：
在解決這個(gè)問題前，現(xiàn)在github找了一下輪子，發(fā)現(xiàn)python下面除了sql parse沒什么好的解析數(shù)據(jù)庫和表的輪輪。到是在java里面找到presto-parser解析的比較準(zhǔn)。于是自己結(jié)合sql parse源碼寫了個(gè)類，供大家參考，測試了一下，檢測還是準(zhǔn)的。
測試sql
select
b.product_name "產(chǎn)品",
count(a.order_id) "訂單量",
b.selling_price_max "銷售價(jià)",
b.gross_profit_rate_max/100 "毛利率",
case when b.business_type =1 then '自營消化' when b.business_type =2 then '服務(wù)商消化' end "消化模式"
from(select 'CRM簽單' label,date(d.update_ymd) close_ymd,c.product_name,c.product_id,
a.order_id,cast(a.recipient_amount as double) amt,d.cost
from mysql4.dataview_fenxiao.fx_order a
left join mysql4.dataview_fenxiao.fx_order_task b on a.order_id = b.order_id
left join mysql7.dataview_trade.ddc_product_info c on cast(c.product_id as varchar) = a.product_ids and c.snapshot_version = 'SELLING'
inner join (select t1.par_order_id,max(t1.update_ymd) update_ymd,
sum(case when t4.product2_type = 1 and t5.shop_id is not null then t5.price else t1.order_hosted_price end) cost
from hive.bdc_dwd.dw_mk_order t1
left join hive.bdc_dwd.dw_mk_order_status t2 on t1.order_id = t2.order_id and t2.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
left join mysql7.dataview_trade.mk_order_merchant t3 on t1.order_id = t3.order_id
left join mysql7.dataview_trade.ddc_product_info t4 on t4.product_id = t3.MERCHANT_ID and t4.snapshot_version = 'SELLING'
left join mysql4.dataview_scrm.sc_tprc_product_info t5 on t5.product_id = t4.product_id and t5.shop_id = t1.seller_id
where t1.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
and t2.valid_state in (100,200) ------有效訂單
and t1.order_mode = 10 --------產(chǎn)品消耗訂單
and t2.complete_state = 1 -----訂單已經(jīng)完成
group by t1.par_order_id
) d on d.par_order_id = b.task_order_id
where c.product_type = 0 and date(from_unixtime(a.last_recipient_time)) > date('2016-01-01') and a.payee_type <> 1 -----------已收款
UNION ALL
select '企業(yè)管家消耗' label,date(c.update_ymd) close_ymd,b.product_name,b.product_id,
a.task_id,(case when a.yb_price = 0 and b.product2_type = 1 then b.selling_price_min else a.yb_price end) amt,
(case when a.yb_price = 0 and b.product2_type = 2 then 0 when b.product2_type = 1 and e.shop_id is not null then e.price else c.order_hosted_price end) cost
from mysql8.dataview_tprc.tprc_task a
left join mysql7.dataview_trade.ddc_product_info b on a.product_id = b.product_id and b.snapshot_version = 'SELLING'
inner join hive.bdc_dwd.dw_mk_order c on a.order_id = c.order_id and c.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
left join hive.bdc_dwd.dw_mk_order_status d on d.order_id = c.order_id and d.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
left join mysql4.dataview_scrm.sc_tprc_product_info e on e.product_id = b.product_id and e.shop_id = c.seller_id
where d.valid_state in (100,200) and d.complete_state = 1 and c.order_mode = 10
union ALL
select '交易管理系統(tǒng)' 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 when t1.order_mode <> 11 then t7.user_amount when t1.order_mode = 11 and t4.product2_type = 1 and t5.shop_id is not null then t5.price else t8.cost end) cost
from hive.bdc_dwd.dw_mk_order t1
left join hive.bdc_dwd.dw_mk_order_business t2 on t1.order_id = t2.order_id and t2.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
left join mysql7.dataview_trade.mk_order_merchant t3 on t1.order_id = t3.order_id
left join mysql7.dataview_trade.ddc_product_info t4 on t4.product_id = t3.MERCHANT_ID and t4.snapshot_version = 'SELLING'
left join mysql4.dataview_scrm.sc_tprc_product_info t5 on t5.product_id = t4.product_id and t5.shop_id = t1.seller_id
left join hive.bdc_dwd.dw_fact_task_ss_daily t6 on t6.task_id = t2.task_id and t6.acct_time=date_format(date_add('day',-1,current_date),'%Y-%m-%d')
left join (select a.task_id,sum(a.user_amount) user_amount
from hive.bdc_dwd.dw_fn_deal_asyn_order a
where a.is_new=1 and a.service='Trade_Payment' and a.state=1 and a.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
group by a.task_id)t7 on t7.task_id = t2.task_id
left join (select t1.par_order_id,sum(t1.order_hosted_price - t1.order_refund_price) cost
from hive.bdc_dwd.dw_mk_order t1
where t1.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2) and t1.order_type = 1 and t1.order_stype = 4 and t1.order_mode = 12
group by t1.par_order_id) t8 on t1.order_id = t8.par_order_id
where t1.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
and t1.order_type = 1 and t1.order_stype in (4,5) and t1.order_mode <> 12 and t4.product_id is not null and t1.order_hosted_price > 0 and t6.is_deal = 1 and t6.close_ymd >= '2018-12-31'
)a
left join mysql7.dataview_trade.ddc_product_info b on a.product_id = b.product_id and b.snapshot_version = 'SELLING'
where b.product2_type = 1 -------標(biāo)品
and close_ymd between DATE_ADD('day',-7,CURRENT_DATE) and DATE_ADD('day',-1,CURRENT_DATE)
GROUP BY b.product_name,
b.selling_price_max,
b.gross_profit_rate_max/100,
b.actrul_supply_num,
case when b.business_type =1 then '自營消化' when b.business_type =2 then '服務(wù)商消化' end
order by count(a.order_id) desc
limit 10
可以看到該sql比較雜，也沒有格式化，不太好提取數(shù)據(jù)庫和表。所以第一步需要對sql進(jìn)行格式化
直接上代碼:
# coding=utf-8
from __future__ import absolute_import
from __future__ import division
from __future__ import print_function
from __future__ import unicode_literals
import sqlparse
from sqlparse.sql import Identifier, IdentifierList
from sqlparse.tokens import Keyword, Name
RESULT_OPERATIONS = {'UNION', 'INTERSECT', 'EXCEPT', 'SELECT'}
ON_KEYWORD = 'ON'
PRECEDES_TABLE_NAME = {'FROM', 'JOIN', 'DESC', 'DESCRIBE', 'WITH'}
class BaseExtractor(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())
for statement in self._parsed:
self.__extract_from_token(statement)
self._limit = self._extract_limit_from_query(statement)
self._table_names = self._table_names - self._alias_names
@property
def tables(self):
return self._table_names
@property
def limit(self):
return self._limit
def is_select(self):
return self._parsed[0].get_type() == 'SELECT'
def is_explain(self):
return self.stripped().upper().startswith('EXPLAIN')
def is_readonly(self):
return self.is_select() or self.is_explain()
def stripped(self):
return self.sql.strip(' \t\n;')
def get_statements(self):
statements = []
for statement in self._parsed:
if statement:
sql = str(statement).strip(' \n;\t')
if sql:
statements.append(sql)
return statements
@staticmethod
def __precedes_table_name(token_value):
for keyword in PRECEDES_TABLE_NAME:
if keyword in token_value:
return True
return False
@staticmethod
def get_full_name(identifier):
if len(identifier.tokens) > 1 and identifier.tokens[1].value == '.':
return '{}.{}'.format(identifier.tokens[0].value,
identifier.tokens[2].value)
return identifier.get_real_name()
@staticmethod
def __is_result_operation(keyword):
for operation in RESULT_OPERATIONS:
if operation in keyword.upper():
return True
return False
@staticmethod
def __is_identifier(token):
return isinstance(token, (IdentifierList, Identifier))
def __process_identifier(self, identifier):
if '(' not in '{}'.format(identifier):
self._table_names.add(self.get_full_name(identifier))
return
# store aliases
if hasattr(identifier, 'get_alias'):
self._alias_names.add(identifier.get_alias())
if hasattr(identifier, 'tokens'):
# some aliases are not parsed properly
if identifier.tokens[0].ttype == Name:
self._alias_names.add(identifier.tokens[0].value)
self.__extract_from_token(identifier)
def as_create_table(self, table_name, overwrite=False):
exec_sql = ''
sql = self.stripped()
if overwrite:
exec_sql = 'DROP TABLE IF EXISTS {};\n'.format(table_name)
exec_sql += 'CREATE TABLE {} AS \n{}'.format(table_name, sql)
return exec_sql
def __extract_from_token(self, token):
if not hasattr(token, 'tokens'):
return
table_name_preceding_token = False
for item in token.tokens:
if item.is_group and not self.__is_identifier(item):
self.__extract_from_token(item)
if item.ttype in Keyword:
if self.__precedes_table_name(item.value.upper()):
table_name_preceding_token = True
continue
if not table_name_preceding_token:
continue
if item.ttype in Keyword or item.value == ',':
if (self.__is_result_operation(item.value) or
item.value.upper() == ON_KEYWORD):
table_name_preceding_token = False
continue
# FROM clause is over
break
if isinstance(item, Identifier):
self.__process_identifier(item)
if isinstance(item, IdentifierList):
for token in item.tokens:
if self.__is_identifier(token):
self.__process_identifier(token)
def _get_limit_from_token(self, token):
if token.ttype == sqlparse.tokens.Literal.Number.Integer:
return int(token.value)
elif token.is_group:
return int(token.get_token_at_offset(1).value)
def _extract_limit_from_query(self, statement):
limit_token = None
for pos, item in enumerate(statement.tokens):
if item.ttype in Keyword and item.value.lower() == 'limit':
limit_token = statement.tokens[pos + 2]
return self._get_limit_from_token(limit_token)
def get_query_with_new_limit(self, new_limit):
if not self._limit:
return self.sql + ' LIMIT ' + str(new_limit)
limit_pos = None
tokens = self._parsed[0].tokens
# Add all items to before_str until there is a limit
for pos, item in enumerate(tokens):
if item.ttype in Keyword and item.value.lower() == 'limit':
limit_pos = pos
break
limit = tokens[limit_pos + 2]
if limit.ttype == sqlparse.tokens.Literal.Number.Integer:
tokens[limit_pos + 2].value = new_limit
elif limit.is_group:
tokens[limit_pos + 2].value = (
'{}, {}'.format(next(limit.get_identifiers()), new_limit)
)
str_res = ''
for i in tokens:
str_res += str(i.value)
return str_res
class SqlExtractor(BaseExtractor):
"""提取sql語句"""
@staticmethod
def get_full_name(identifier, including_dbs=False):
if len(identifier.tokens) > 1 and identifier.tokens[1].value == '.':
a = identifier.tokens[0].value
b = identifier.tokens[2].value
db_table = (a, b)
full_tree = '{}.{}'.format(a, b)
if len(identifier.tokens) == 3:
return full_tree
else:
i = identifier.tokens[3].value
c = identifier.tokens[4].value
if i == ' ':
return full_tree
full_tree = '{}.{}.{}'.format(a, b, c)
return full_tree
return None, None
if __name__ == '__main__':
sql = """select
b.product_name "產(chǎn)品",
count(a.order_id) "訂單量",
b.selling_price_max "銷售價(jià)",
b.gross_profit_rate_max/100 "毛利率",
case when b.business_type =1 then '自營消化' when b.business_type =2 then '服務(wù)商消化' end "消化模式"
from(select 'CRM簽單' label,date(d.update_ymd) close_ymd,c.product_name,c.product_id,
a.order_id,cast(a.recipient_amount as double) amt,d.cost
from mysql4.dataview_fenxiao.fx_order a
left join mysql4.dataview_fenxiao.fx_order_task b on a.order_id = b.order_id
left join mysql7.dataview_trade.ddc_product_info c on cast(c.product_id as varchar) = a.product_ids and c.snapshot_version = 'SELLING'
inner join (select t1.par_order_id,max(t1.update_ymd) update_ymd,
sum(case when t4.product2_type = 1 and t5.shop_id is not null then t5.price else t1.order_hosted_price end) cost
from hive.bdc_dwd.dw_mk_order t1
left join hive.bdc_dwd.dw_mk_order_status t2 on t1.order_id = t2.order_id and t2.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
left join mysql7.dataview_trade.mk_order_merchant t3 on t1.order_id = t3.order_id
left join mysql7.dataview_trade.ddc_product_info t4 on t4.product_id = t3.MERCHANT_ID and t4.snapshot_version = 'SELLING'
left join mysql4.dataview_scrm.sc_tprc_product_info t5 on t5.product_id = t4.product_id and t5.shop_id = t1.seller_id
where t1.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
and t2.valid_state in (100,200) ------有效訂單
and t1.order_mode = 10 --------產(chǎn)品消耗訂單
and t2.complete_state = 1 -----訂單已經(jīng)完成
group by t1.par_order_id
) d on d.par_order_id = b.task_order_id
where c.product_type = 0 and date(from_unixtime(a.last_recipient_time)) > date('2016-01-01') and a.payee_type <> 1 -----------已收款
UNION ALL
select '企業(yè)管家消耗' label,date(c.update_ymd) close_ymd,b.product_name,b.product_id,
a.task_id,(case when a.yb_price = 0 and b.product2_type = 1 then b.selling_price_min else a.yb_price end) amt,
(case when a.yb_price = 0 and b.product2_type = 2 then 0 when b.product2_type = 1 and e.shop_id is not null then e.price else c.order_hosted_price end) cost
from mysql8.dataview_tprc.tprc_task a
left join mysql7.dataview_trade.ddc_product_info b on a.product_id = b.product_id and b.snapshot_version = 'SELLING'
inner join hive.bdc_dwd.dw_mk_order c on a.order_id = c.order_id and c.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
left join hive.bdc_dwd.dw_mk_order_status d on d.order_id = c.order_id and d.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
left join mysql4.dataview_scrm.sc_tprc_product_info e on e.product_id = b.product_id and e.shop_id = c.seller_id
where d.valid_state in (100,200) and d.complete_state = 1 and c.order_mode = 10
union ALL
select '交易管理系統(tǒng)' 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 when t1.order_mode <> 11 then t7.user_amount when t1.order_mode = 11 and t4.product2_type = 1 and t5.shop_id is not null then t5.price else t8.cost end) cost
from hive.bdc_dwd.dw_mk_order t1
left join hive.bdc_dwd.dw_mk_order_business t2 on t1.order_id = t2.order_id and t2.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
left join mysql7.dataview_trade.mk_order_merchant t3 on t1.order_id = t3.order_id
left join mysql7.dataview_trade.ddc_product_info t4 on t4.product_id = t3.MERCHANT_ID and t4.snapshot_version = 'SELLING'
left join mysql4.dataview_scrm.sc_tprc_product_info t5 on t5.product_id = t4.product_id and t5.shop_id = t1.seller_id
left join hive.bdc_dwd.dw_fact_task_ss_daily t6 on t6.task_id = t2.task_id and t6.acct_time=date_format(date_add('day',-1,current_date),'%Y-%m-%d')
left join (select a.task_id,sum(a.user_amount) user_amount
from hive.bdc_dwd.dw_fn_deal_asyn_order a
where a.is_new=1 and a.service='Trade_Payment' and a.state=1 and a.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
group by a.task_id)t7 on t7.task_id = t2.task_id
left join (select t1.par_order_id,sum(t1.order_hosted_price - t1.order_refund_price) cost
from hive.bdc_dwd.dw_mk_order t1
where t1.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2) and t1.order_type = 1 and t1.order_stype = 4 and t1.order_mode = 12
group by t1.par_order_id) t8 on t1.order_id = t8.par_order_id
where t1.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
and t1.order_type = 1 and t1.order_stype in (4,5) and t1.order_mode <> 12 and t4.product_id is not null and t1.order_hosted_price > 0 and t6.is_deal = 1 and t6.close_ymd >= '2018-12-31'
)a
left join mysql7.dataview_trade.ddc_product_info b on a.product_id = b.product_id and b.snapshot_version = 'SELLING'
where b.product2_type = 1 -------標(biāo)品
and close_ymd between DATE_ADD('day',-7,CURRENT_DATE) and DATE_ADD('day',-1,CURRENT_DATE)
GROUP BY b.product_name,
b.selling_price_max,
b.gross_profit_rate_max/100,
b.actrul_supply_num,
case when b.business_type =1 then '自營消化' when b.business_type =2 then '服務(wù)商消化' end
order by count(a.order_id) desc
limit 10"""
sql_extractor = SqlExtractor(sql)
print(sql_extractor.sql)
print(sql_extractor.tables)
輸出結(jié)果：
{'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'}
格式化結(jié)果:
SELECT b.product_name "產(chǎn)品",
count(a.order_id) "訂單量",
b.selling_price_max "銷售價(jià)",
b.gross_profit_rate_max/100 "毛利率",
CASE
WHEN b.business_type =1 THEN '自營消化'
WHEN b.business_type =2 THEN '服務(wù)商消化'
END "消化模式" from
(SELECT 'CRM簽單' label,date(d.update_ymd) close_ymd,c.product_name,c.product_id, a.order_id,cast(a.recipient_amount AS DOUBLE) amt,d.cost
FROM mysql4.dataview_fenxiao.fx_order a
LEFT JOIN mysql4.dataview_fenxiao.fx_order_task b ON a.order_id = b.order_id
LEFT JOIN mysql7.dataview_trade.ddc_product_info c ON cast(c.product_id AS varchar) = a.product_ids
AND c.snapshot_version = 'SELLING'
INNER JOIN
(SELECT t1.par_order_id,max(t1.update_ymd) update_ymd, sum(CASE
WHEN t4.product2_type = 1
AND t5.shop_id IS NOT NULL THEN t5.price
ELSE t1.order_hosted_price
END) cost
FROM hive.bdc_dwd.dw_mk_order t1
LEFT JOIN hive.bdc_dwd.dw_mk_order_status t2 ON t1.order_id = t2.order_id
AND t2.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) AS varchar),9,2)
LEFT JOIN mysql7.dataview_trade.mk_order_merchant t3 ON t1.order_id = t3.order_id
LEFT JOIN mysql7.dataview_trade.ddc_product_info t4 ON t4.product_id = t3.MERCHANT_ID
AND t4.snapshot_version = 'SELLING'
LEFT JOIN mysql4.dataview_scrm.sc_tprc_product_info t5 ON t5.product_id = t4.product_id
AND t5.shop_id = t1.seller_id
WHERE t1.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) AS varchar),9,2)
AND t2.valid_state IN (100,200)------有效訂單
AND t1.order_mode = 10 --------產(chǎn)品消耗訂單
AND t2.complete_state = 1 -----訂單已經(jīng)完成
GROUP BY t1.par_order_id ) d ON d.par_order_id = b.task_order_id
WHERE c.product_type = 0
AND date(from_unixtime(a.last_recipient_time)) > date('2016-01-01')
AND a.payee_type <> 1 -----------已收款
UNION ALL SELECT '企業(yè)管家消耗' label,date(c.update_ymd) close_ymd,b.product_name,b.product_id, a.task_id,(CASE
WHEN a.yb_price = 0
AND b.product2_type = 1 THEN b.selling_price_min
ELSE a.yb_price
END) amt, (CASE
WHEN a.yb_price = 0
AND b.product2_type = 2 THEN 0
WHEN b.product2_type = 1
AND e.shop_id IS NOT NULL THEN e.price
ELSE c.order_hosted_price
END) cost
FROM mysql8.dataview_tprc.tprc_task a
LEFT JOIN mysql7.dataview_trade.ddc_product_info b ON a.product_id = b.product_id
AND b.snapshot_version = 'SELLING'
INNER JOIN hive.bdc_dwd.dw_mk_order c ON a.order_id = c.order_id
AND c.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) AS varchar),9,2)
LEFT JOIN hive.bdc_dwd.dw_mk_order_status d ON d.order_id = c.order_id
AND d.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) AS varchar),9,2)
LEFT JOIN mysql4.dataview_scrm.sc_tprc_product_info e ON e.product_id = b.product_id
AND e.shop_id = c.seller_id
WHERE d.valid_state IN (100,200)
AND d.complete_state = 1
AND c.order_mode = 10
UNION ALL SELECT '交易管理系統(tǒng)' 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
WHEN t1.order_mode <> 11 THEN t7.user_amount
WHEN t1.order_mode = 11
AND t4.product2_type = 1
AND t5.shop_id IS NOT NULL THEN t5.price
ELSE t8.cost
END) cost
FROM hive.bdc_dwd.dw_mk_order t1
LEFT JOIN hive.bdc_dwd.dw_mk_order_business t2 ON t1.order_id = t2.order_id
AND t2.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE) AS varchar),9,2)
LEFT JOIN mysql7.dataview_trade.mk_order_merchant t3 ON t1.order_id = t3.order_id
LEFT JOIN mysql7.dataview_trade.ddc_product_info t4 ON t4.product_id = t3.MERCHANT_ID
AND t4.snapshot_version = 'SELLING'
LEFT JOIN mysql4.dataview_scrm.sc_tprc_product_info t5 ON t5.product_id = t4.product_id
AND t5.shop_id = t1.seller_id
LEFT JOIN hive.bdc_dwd.dw_fact_task_ss_daily t6 ON t6.task_id = t2.task_id
AND t6.acct_time=date_format(date_add('day',-1,CURRENT_DATE),'%Y-%m-%d')
LEFT JOIN
(SELECT a.task_id,sum(a.user_amount) user_amount
FROM hive.bdc_dwd.dw_fn_deal_asyn_order a
WHERE a.is_new=1
AND a.service='Trade_Payment'
AND a.state=1
AND a.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE) AS varchar),9,2)
GROUP BY a.task_id)t7 ON t7.task_id = t2.task_id
LEFT JOIN
(SELECT t1.par_order_id,sum(t1.order_hosted_price - t1.order_refund_price) cost
FROM hive.bdc_dwd.dw_mk_order t1
WHERE t1.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) AS varchar),9,2)
AND t1.order_type = 1
AND t1.order_stype = 4
AND t1.order_mode = 12
GROUP BY t1.par_order_id) t8 ON t1.order_id = t8.par_order_id
WHERE t1.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) AS varchar),9,2)
AND t1.order_type = 1
AND t1.order_stype IN (4,5)
AND t1.order_mode <> 12
AND t4.product_id IS NOT NULL
AND t1.order_hosted_price > 0
AND t6.is_deal = 1
AND t6.close_ymd >= '2018-12-31' )a
LEFT JOIN mysql7.dataview_trade.ddc_product_info b ON a.product_id = b.product_id
AND b.snapshot_version = 'SELLING'
WHERE b.product2_type = 1 -------標(biāo)品
AND close_ymd BETWEEN DATE_ADD('day',-7,CURRENT_DATE) AND DATE_ADD('day',-1,CURRENT_DATE)
GROUP BY b.product_name,
b.selling_price_max,
b.gross_profit_rate_max/100,
b.actrul_supply_num,
CASE
WHEN b.business_type =1 THEN '自營消化'
WHEN b.business_type =2 THEN '服務(wù)商消化'
END
ORDER BY count(a.order_id) DESC
LIMIT 10
以上這篇python如何解析復(fù)雜sql,實(shí)現(xiàn)數(shù)據(jù)庫和表的提取的實(shí)例剖析就是小編分享給大家的全部內(nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
您可能感興趣的文章:
- 使用Python實(shí)現(xiàn)將多表分批次從數(shù)據(jù)庫導(dǎo)出到Excel
- python的mysql數(shù)據(jù)庫建立表與插入數(shù)據(jù)操作示例
- python 獲取sqlite3數(shù)據(jù)庫的表名和表字段名的實(shí)例
- Python獲取數(shù)據(jù)庫數(shù)據(jù)并保存在excel表格中的方法
- Python實(shí)現(xiàn)將MySQL數(shù)據(jù)庫表中的數(shù)據(jù)導(dǎo)出生成csv格式文件的方法
- Python實(shí)現(xiàn)mysql數(shù)據(jù)庫更新表數(shù)據(jù)接口的功能
- Python實(shí)現(xiàn)將sqlite數(shù)據(jù)庫導(dǎo)出轉(zhuǎn)成Excel(xls)表的方法
- Python MySQL進(jìn)行數(shù)據(jù)庫表變更和查詢
- Python如何讀取MySQL數(shù)據(jù)庫表數(shù)據(jù)
- python數(shù)據(jù)庫操作常用功能使用詳解(創(chuàng)建表/插入數(shù)據(jù)/獲取數(shù)據(jù))
- Python 如何實(shí)現(xiàn)數(shù)據(jù)庫表結(jié)構(gòu)同步
相關(guān)文章
在 Python 中，提供了很多種字符串格式化的方式，分別是 %-formatting、str.format 和 f-string 。這篇文章主要介紹了Python 中幾種字符串格式化方法及其比較,需要的朋友可以參考下2019-07-07
Arrow 是用于處理日期和時(shí)間的 Python 模塊。 與內(nèi)置的日期和時(shí)間工具相比，它使創(chuàng)建，操作，格式化和轉(zhuǎn)換日期，時(shí)間和時(shí)間戳變得更加容易2022-10-10
這篇文章主要介紹了Python爬蟲爬取新聞資訊案例詳解,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-07-07
python提取照片坐標(biāo)信息的實(shí)例代碼
這篇文章主要介紹了python提取照片坐標(biāo)信息的實(shí)例代碼，文中給大家提到了Python利用exifread庫來解析照片的經(jīng)緯度，通過實(shí)例代碼給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-08-08
python使用Paramiko模塊實(shí)現(xiàn)遠(yuǎn)程文件拷貝
這篇文章主要為大家詳細(xì)介紹了python使用Paramiko模塊實(shí)現(xiàn)遠(yuǎn)程文件拷貝，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-04-04
Python對象與json數(shù)據(jù)的轉(zhuǎn)換問題實(shí)例詳解
JSON(JavaScript?Object?Notation)?是一種輕量級的數(shù)據(jù)交換格式，很受廣大用戶喜愛，今天通過本文給大家介紹Python對象與json數(shù)據(jù)的轉(zhuǎn)換問題,需要的朋友可以參考下2022-07-07
今天我們先講一下編寫python腳本處理json的核心功能，有些散亂，后期在進(jìn)行整體腳本的編寫,對python?json字典關(guān)系相關(guān)知識感興趣的朋友一起看看吧2022-06-06
Python 字符串操作(string替換、刪除、截取、復(fù)制、連接、比較、查找、包含、大小寫轉(zhuǎn)換、分割等)
這篇文章主要介紹了Python 字符串操作(string替換、刪除、截取、復(fù)制、連接、比較、查找、包含、大小寫轉(zhuǎn)換、分割等),需要的朋友可以參考下2018-03-03
Django中提供了“信號調(diào)度”，用于在框架執(zhí)行操作時(shí)解耦。通俗來講，就是一些動(dòng)作發(fā)生的時(shí)候，信號允許特定的發(fā)送者去提醒一些接受者。2017-05-05
這篇文章主要介紹了python import 上級目錄的導(dǎo)入,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11