SQL：将行转换为可变行数的列

I have two tables whose simplified structure looks like this:

我有两个表,其简化结构如下所示:

RESPONSES
id
created

ACCESSORY VALUES
id
response_id
sensor_id
value

I want to create a view that flattens all accessory values for a given response into one row over a time period (filtering on response.created). I think I want a Pivot table or Crosstab, but I'm unfamiliar with both and the examples I've found mainly deal with a known number of columns. To complicate things, a given sensor could only appear for part of the time period if a user started or stopped tracking it during the time in question. Ideally I'd hold a NULL in that column for the sensor in any rows when it was not present. Is this possible? If so, am I on the right track or looking in the wrong place?

我想创建一个视图,将给定响应的所有附件值在一段时间内展平为一行(在response.created上过滤)。我想我想要一个Pivot表或Crosstab,但我不熟悉这两个,我发现的例子主要涉及已知数量的列。更复杂的是,如果用户在相关时间内开始或停止跟踪,则给定的传感器只能在部分时间段内出现。理想情况下,当该列中的传感器不存在时,我会在该列中保留NULL。这可能吗?如果是这样,我是在正确的轨道上还是在错误的地方寻找?

The SQL to get the data as individual rows looks like

用于将数据作为单个行获取的SQL看起来像

SELECT r.id, a.sensor_id, a.value from results_response r 
INNER JOIN results_accessoryvalue a ON r.id = a.response_id
WHERE r.installation_id = 40 
AND r.created BETWEEN '2013-04-01' AND '2013-05-01' 
ORDER BY r.created

but I'm not having any luck trying to use it in a crosstab because I don't know how to specify dynamic columns.

但我没有运气试图在交叉表中使用它,因为我不知道如何指定动态列。

2 个解决方案

#1

This is an alternative approach using PostgreSQL arrays

这是使用PostgreSQL数组的另一种方法

create table responses (
    id serial not null  unique,
    x   integer,
    created timestamp default now()
);

 create table accessory (
    id serial not null,
    responses_id integer references responses (id),
    sensor_id  integer,
    value text,
    created timestamp default now()
);

insert into responses  (x) values  ( 1), (2),(3),(4);

insert into accessory  (responses_id ,  sensor_id, value ) values  
( 1, 1, 'A' ), ( 1, 2, 'Ab' ),  ( 1, 3, 'Ac' ),  ( 1, 4, 'Ad' ),
( 2, 4, 'Ab' ), ( 1, 2, 'bAb' ),  ( 3, 3, 'bAc' ),  ( 4, 4, 'bAd' );

select *, array(
    select value
    from accessory
    where accessory.responses_id  = responses.id  
    order by  accessory.created
    ) as accessory_values
from responses;

Query result includes an array column with all the accessory values that match response.id

查询结果包括一个数组列,其中所有附件值与response.id匹配

#2

you should use crosstab with some additons. i had this problem too and solved it for my proposal like this.

你应该使用带有一些附加物的交叉表。我也遇到了这个问题并为我的建议解决了这个问题。

first install crosstab extension

首先安装交叉表扩展

the trick i used is to create 2 additional functions. one to get the type information, needed as resultset for crosstab function. look at this:

我使用的技巧是创建2个附加功能。一个获取类型信息,需要作为交叉表函数的结果集。看这个:

CREATE OR REPLACE FUNCTION "TEMPORARY.TYPE.FROM.COLUMN"(text, text)
  RETURNS text AS
$BODY$
DECLARE
    typestring TEXT;
    returnrec RECORD;
BEGIN
    typestring := '';
    FOR returnrec IN EXECUTE $1 LOOP
        typestring := typestring||', "'||returnrec."Column"||'" '||$2;
    END LOOP;

  RETURN typestring;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Now we can write our second function:

现在我们可以编写第二个函数:

CREATE OR REPLACE FUNCTION "DYNAMIC.CROSSTAB"(text, text, text)
  RETURNS text AS
$BODY$
DECLARE
    typestring TEXT;
    executestring TEXT;
BEGIN
    DROP TABLE IF EXISTS "TBL.Crosstab";
    SELECT "REPORTING"."TEMPORARY.TYPE.FROM.COLUMN"($2,$3) INTO typestring;
    executestring := 'CREATE TEMPORARY TABLE "TBL.Crosstab" AS (SELECT * FROM crosstab('''||$1||''','''||$2||''') AS (row_name DATE'||typestring||'));';
    EXECUTE executestring;
  RETURN '"TBL.Crosstab"';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

To create your crosstab simply call:

要创建交叉表,只需调用:

SELECT * FROM "DYNAMIC.CROSSTAB"(text, text, text);

It returns the name of an temporary table for your session filled with your result. I needed it this way.

它返回填充了结果的会话的临时表的名称。我这样需要它。

Parameter explanation:

First = The query to get your data (must have 3 columns: row_name, cat and value)

First =获取数据的查询(必须有3列:row_name,cat和value)

Second = The query to get your columns wich returns all your categories (cat)

秒=获取列的查询将返回所有类别(cat)

Third = The columntype for our temporary type

第三个=临时类型的columntype

it's not perfect but fit's our needs. we have statements, fetching more than 450 coloumns and some thounds rows at this way. hope it helps

它并不完美,但符合我们的需求。我们有声明,以这种方式获取450多个coloumns和一些thounds行。希望能帮助到你

#1