Python的Pandas库与Oracle Sql操作的一一对应关系

1、where

sql：

select * from temp1
   where position_dt >= 20170704
     and position_dt between 20170701 and 20170704
     and sk_f_prod in ('pd001', 'pd002')
     and secu_type_code not in ('期货', '远期')
     and account_project_name like '证券投资%'
     and (mkt_suspension_info is null or secu_mkt_code = '001')
     and case when prod_name is null then '无名' else prod_name end = '无名'
     )

pandas:

df = df[(df['position_dt'] >= 20170701)
    &(df['position_dt'] <= 20170704)
    &(df.sk_f_prod.isin(['pd001', 'pd002']))
    &(~df.secu_type_code.isin(['期货', '远期']))
    &(df.account_project_name.str.contains('证券投资'))
    &(df.mkt_suspension_info.isnull() | (df.secu_mkt_code == '001'))
    &(df['prod_name'].map((lambda x: x if x else '无名')) == '无名')]

2、group by:求最大值、求和、求数量

sql：

select position_dt,
       sk_f_prod,
       max(locc_position_cost),
       sum(orgc_posi_mkt_val),
       count(case
               when substr(subj_code, 1, 4) = '1102' then  1
               when secu_var = '股票品种' and secu_inner_code like '%600038%' then 0
               else 2
             end) quantity
  from sql_where
 group by position_dt, sk_f_prod

pandas:

df['amount'] = df.apply(lambda x: 1 if x.subj_code[:4] == '1102' else (0 if