对于第一个SQL：

SELECT state_name,  
       state_population,
       SUM(state_population)
        OVER() AS national_population
FROM population   
ORDER BY state_name

Pandas：

df.assign(national_population=df.state_population.sum()).sort_values('state_name')

对于第二个SQL：

SELECT state_name,  
       state_population,
       region,
       SUM(state_population)
        OVER(PARTITION BY region) AS regional_population
FROM population    
ORDER BY state_name

Pandas：

df.assign(regional_population=df.groupby('region')['state_population'].transform('sum')) \
  .sort_values('state_name')

• • 演示：**

In [238]: df
Out[238]:
   region state_name  state_population
0       1        aaa               100
1       1        bbb               110
2       2        ccc               200
3       2        ddd               100
4       2        eee               100
5       3        xxx                55

全国人口：

In [246]: df.assign(national_population=df.state_population.sum()).sort_values('state_name')
Out[246]:
   region state_name  state_population  national_population
0       1        aaa               100                  665
1       1        bbb               110                  665
2       2        ccc               200                  665
3       2        ddd               100                  665
4       2        eee               100                  665
5       3        xxx                55                  665

区域人口：

In [239]: df.assign(regional_population=df.groupby('region')['state_population'].transform('sum')) \
     ...:   .sort_values('state_name')
Out[239]:
   region state_name  state_population  regional_population
0       1        aaa               100                  210
1       1        bbb               110                  210
2       2        ccc               200                  400
3       2        ddd               100                  400
4       2        eee               100                  400
5       3        xxx                55                   55

另一个常见的窗口是OVER(ORDER BY ...)。

SELECT *
    ,SUM(values) OVER(ORDER BY date) AS cum_sum
FROM df;

Pandas的等值是cumsum()

df['cum_sum'] = df['values'].sort_values(by='date').cumsum()

另一个常见的窗口函数是ROW_NUMBER()。

SELECT *
    ,ROW_NUMBER() OVER () AS row_number
FROM df;

它的等价物Pandas的等价物是range()。

df['row_number'] = range(1, len(df)+1)

还有一个基于panda的模块pandasql，它可以让你在本地 Dataframe 上运行sql查询，所以如果你对sql很熟悉，你可以直接在 Dataframe 上运行查询。

# !pip isntall pandasql
from pandasql import sqldf
df = sqldf("""
SELECT state_name,  
       state_population,
       SUM(state_population)
        OVER() AS national_population
FROM population   
ORDER BY state_name 
""")