问题:像在spss中一样在sql中制作数据透视表

我在 PostgreSQL 中有很多数据。但我需要像 SPSS 一样做一些数据透视表。例如,我有城市和州的表格。

 create table cities
(
    city integer,
    state integer
);
insert into cities(city,state) values (1,1);
insert into cities(city,state) values (2,2);
insert into cities(city,state) values (3,1);
insert into cities(city,state) values (4,1);

实际上,在这张表中,我有 4 个城市和 2 个州。我想用百分比做数据透视表

city\state |state-1| state-2|
city1      |33%    |0%      |
city2      |0%     |100%    |
city3      |33%    |0%      |
city4      |33%    |0%      |
totalCount |3      |1       |

我了解如何在这种特殊情况下使用 sql 来做到这一点。但我想要的只是使用一些存储函数将一个变量与另一个变量交叉(只需计算不同的值并通过“count(*) where variable_in_column_namesu003d1 等等)使用一些存储的函数。我现在在看 plpython .我的一些问题是:

1.如何在没有形状适合输出列的数量和类型的临时表的情况下输出记录集。

2.也许有可行的解决方案?

如我所见,输入将是表名、第一个变量的列名、第二个变量的列名。在函数体中进行大量查询(count(*),遍历变量中的每个不同值并对其进行计数等等),然后返回一个带有百分比的表。

1.实际上我在一个查询中没有很多行(大约10k),并且可能是在原始python中做这些事情的最好方法,而不是plpython?

解答

您可能想尝试一下pandas,它是一个出色的 Python 数据分析库。

查询 PostgreSQL 数据库:

import psycopg2
import pandas as pd
from pandas.io.sql import frame_query

conn_string = "host='localhost' dbname='mydb' user='postgres' password='password'"
conn = psycopg2.connect(conn_string)
df = frame_query('select * from cities', con=conn)

其中df是一个数据帧像:

    city    state
0    1   1
1    2   2
2    3   1
3    4   1

然后,您可以使用pivot_table创建一个数据透视表并除以总数以获得百分比:

totals = df.groupby('state').size()
pivot = pd.pivot_table(df, rows='city', cols='state', aggfunc=len, fill_value=0) / totals

给你结果:

state   1   2
city        
1    0.333333   0
2    0          1
3    0.333333   0
4    0.333333   0

最后要获得您想要的布局,您只需要重命名索引和列,并附加总计:

totals_frame = pd.DataFrame(totals).T
totals_frame.index = ['totalCount']

pivot.index = ['city%i' % item for item in pivot.index]
final_result = pivot.append(totals_frame)
final_result.columns  = ['state-%i' % item for item in final_result.columns]

给你:

            state-1     state-2
city1       0.333333    0
city2       0.000000    1
city3       0.333333    0
city4       0.333333    0
totalCount  3.000000    1