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

Question

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

RESPONSES
id
created

ACCESSORY VALUES
id
response_id
sensor_id
value

我想创建一个 View ，在一段时间内将给定响应的所有附件值展平到一行中(过滤 response.created)。我想我想要一个数据透视表或交叉表，但我对这两者都不熟悉，而且我发现的示例主要处理已知数量的列。更复杂的是，如果用户在相关时间内开始或停止跟踪给定传感器，则该传感器只能在部分时间段内出现。理想情况下，当传感器不存在时，我会在该列中为任何行的传感器保留一个 NULL。这可能吗？如果是这样，我是在正确的轨道上还是在错误的地方？

将数据作为单独的行获取数据的 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

但我在交叉表中尝试使用它时运气不佳，因为我不知道如何指定动态列。

Answer 1

您应该使用带有一些附加组件的交叉表。我也遇到了这个问题，并根据我的建议解决了这个问题。

首先安装交叉表扩展

我使用的技巧是创建 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;

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

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;

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

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

它为您的 session 返回一个临时表的名称，其中填充了您的结果。我需要这样。

参数说明:

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

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

第三个 = 我们临时类型的列类型

它并不完美，但符合我们的需求。我们有语句，以这种方式获取超过 450 列和一些 thounds 行。希望对你有帮助