基于月份的聚合数据的SQL查询(Postgres)

Question

我查询的表是：

表testing_table：

testType | period_from | period_to| copies |
       1 |     20180101|  20181201|       1|
       2 |    20180101 |  20191201|       1|
       3 |     20190101|  20191201|       1|

我想循环遍历数组，并使用下面的查询生成如下所示的值：

DateVar | ABTEST | CDTEST | EFTEST |
20180101|       4|       0|       0|
20180201|       3|       4|       2|

dateVar = '20180101'，'20180201'，'20180501‘。

我正在尝试开发这样的sql查询：

SELECT
     SUM (
     CASE
     WHEN (testType = 1 AND (period_from <= dateVar AND period_to >= dateVar)) THEN
     copies
     ELSE
     0
     END
     ) AS "ABTEST",
     SUM (
     CASE
     WHEN (testType = 2 AND (period_from <= dateVar AND period_to >= dateVar)) THEN
     copies
     ELSE
     0
     END
     ) AS "CDTEST",
     SUM (
     CASE
     WHEN (testType = 3 AND (period_from <= dateVar AND period_to >= dateVar)) THEN
     copies
     ELSE
     0
     END
     ) AS "EFTEST"
    FROM
     testing_table;

我不知道该怎么处理它。我会调查功能吗？

Answer 1

我认为您应该使用unnest函数来完成您想要完成的任务，我已经编写了一个您可能想要检查的查询。

SELECT DTVAR, 
            SUM(CASE 
            WHEN TestType = 1
                THEN copies
            ELSE 0
            END) AS 'ABTEST', 
            SUM(CASE 
            WHEN TestType = 2
                THEN copies
            ELSE 0
            END) AS 'CDTEST', 
            SUM(CASE 
            WHEN TestType = 3
                THEN copies
            ELSE 0
            END) AS 'EFTEST'
FROM (
    SELECT DTVAR, TestType, sum(copies) AS copies
    FROM testing_table
    INNER JOIN (
        SELECT DTVAR
        FROM unnest(dateVar ['20180101','20180201','20180501']) AS DTVAR
        ) AA
        ON (
                period_from <= DTVAR
                AND period_to >= DTVAR
                )
    GROUP BY DTVAR, TestType
    ) A
GROUP BY DTVAR

希望这有帮助..。