帶有組子查詢的 GROUP BY？

這是我所擁有的簡化的娛樂：

CREATE TABLE "DocumentStore" (
 "ID" int NOT NULL,
 "Content" jsonb NOT NULL,
 CONSTRAINT documentstore_pk PRIMARY KEY ("ID")
);

INSERT INTO "DocumentStore" 
VALUES (1, '{ "ID": 1, "Group": 0, "RecordNumber": 0, "When": 1490280300 }');

INSERT INTO "DocumentStore" 
VALUES (2, '{ "ID": 2, "Group": 0, "RecordNumber": 1, "When": 1490280400 }');

INSERT INTO "DocumentStore" 
VALUES (3, '{ "ID": 3, "Group": 0, "RecordNumber": 2147483647, "When": 1490280500 }');

INSERT INTO "DocumentStore" 
VALUES (4, '{ "ID": 4, "Group": 1, "RecordNumber": 0, "When": 1490280600 }');

INSERT INTO "DocumentStore" 
VALUES (5, '{ "ID": 5, "Group": 1, "RecordNumber": 2147483647, "When": 1490280700 }');

INSERT INTO "DocumentStore" 
VALUES (6, '{ "ID": 6, "Group": 2, "RecordNumber": 0, "When": 1490280900 }');

INSERT INTO "DocumentStore" 
VALUES (7, '{ "ID": 7, "Group": 2, "RecordNumber": 1, "When": 1490281000 }');

我希望將事情分解Group並得到一個看起來像這樣的結果集：

+-------+------------+------------+-------+
| Group |    Start   |    Stop    | Count |
+-------+------------+------------+-------+
|   0   | 1490280300 | 1490280500 |   3   |
+-------+------------+------------+-------+
|   1   | 1490280600 | 1490280800 |   2   |
+-------+------------+------------+-------+
|   2   | 1490280900 |    NULL    |   2   |
+-------+------------+------------+-------+

Start並且Stop是 Unix 時間戳。為了簡單起見，我剛從今天早上抓起一些，把數字四捨五入。

一個組總是有一個"RecordNumber" = 0條目，所以如果有一個組，就有一個開始時間。沒有條目的組不"RecordNumber" = 2147483647應該有結束時間。

我試圖通過查找具有 a of並使用它的值的 jsonb 文件來查找它的時間，並Group通過查找具有 a of並使用它的值的 jsonb 文件來查找它的時間，並且最後數一下里面有多少文件。Start``Group``RecordNumber``0``When``Stop``Group``RecordNumber``2147483647``When``Group

我從這個開始：

SELECT
   "Content"->>'Group' AS "Group",
   (SELECT "Content"->>'When' FROM "DocumentStore" WHERE ("Content"->>'RecordNumber')::int = 0)::int AS "Start",
   (SELECT "Content"->>'When' FROM "DocumentStore" WHERE ("Content"->>'RecordNumber')::int = 2147483647)::int AS "End",
   COUNT("Content") AS "DetailCount"
       FROM "DocumentStore"
       GROUP BY "Group";

如果表中的所有文件都屬於相同的，則它可以工作Group。一旦有 2 個組，我的子查詢就會成為問題：more than one row returned by a subquery used as an expression. 我嘗試添加WHERE "Content"->>'Group' = "Group"到子查詢，但似乎我不能這樣做（column "Group" does not exist）。

我想這可能是一個加入自己的桌子的機會，但我不知道從哪裡開始。

使用CASE語句檢查 if RecordNumber = 0 | 2147483647。

試試這個版本：

select   Content->>'Group' as "Group", 
        min(case when Content->>'RecordNumber' = '0' 
                 then Content->>'When' 
                 else null 
            end ) "Start",
        max(case when Content->>'RecordNumber' = '2147483647' 
                 then Content->>'When' 
                 else null 
            end ) "Stop",
        count(*) as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

結果：

| Group | Start      | Stop       | Count |
|-------|------------|------------|-------|
| 0     | 1490280300 | 1490280500 | 3     |
| 1     | 1490280600 | 1490280700 | 2     |
| 2     | 1490280900 | NULL       | 2     |

正如Evan Carroll所指出的，您也可以利用該FILTER子句。查看Postgres 文件中的4.2.7 聚合表達式。

select   Content->>'Group' as "Group", 
        min(Content->>'When') filter (where Content->>'RecordNumber' = '0') "Start",
        max(Content->>'When') filter (where Content->>'RecordNumber' = '2147483647') "Stop",
        count(*)  as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

在這裡查看：http ://rextester.com/ADYG46620

引用自：https://dba.stackexchange.com/questions/168007