postgres 10-将分类列转换为存在状态矩阵 [英] postgres 10 - convert categorical column to presence absence matrix

问题描述

我想创建一个新表，如下所示：

I'd like to create a new table like so:

原始表：

site_id, site_period
1,       period_a
2,       period_b
2,       period_c
3,       period_d
4,       period_a
4,       period_b

所需的表：

site_id, period_a, period_b, period_c, period_d
1,       1,        0,        0,        0
2,       0,        1,        1,        0
3,       0,        0,        0,        1
4,       1,        1,        0,        0

这是可能是一个重复的问题，因为这是一个相对简单的问题，但是我不知道该用什么词汇来描述它来找到解决方案。我熟悉编码逻辑，但对SQL查询却不太满意。谢谢！

This is probably a duplicate question as this is a relatively simple problem, but I didn't know what vocabulary to use to describe it to find a solution. I'm familiar with coding logic, but not terribly comfortable with sql queries. Thanks!

推荐答案

您可以使用 CREATE TABLE ... AS SELECT

CREATE TABLE desiredtable
AS
SELECT site_id,
       count(CASE site_period
               WHEN 'period_a' THEN
                 1
             END) period_a,
       ...
       count(CASE site_period
               WHEN 'period_d' THEN
                 1
             END) period_d
       FROM originaltable
       GROUP BY site_id;

这篇关于postgres 10-将分类列转换为存在状态矩阵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！