在sql中制作枢轴表，类似于spss

Question

我在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_names=1等等“来对其进行区分)。

1. 如何输出不具有符合输出列数目和类型的形状的临时表的记录集。
2. 也许有可行的解决办法？

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

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

Answer 1

您可能需要尝试一下熊猫，这是一个优秀的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是一个类似于DataFrame的：

    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

Answer 2

查看PostgreSQL窗口函数。可能会给您提供一个非(Pl)python解决方案。http://blog.hashrocket.com/posts/sql-window-functions