Question :
Here’s a simplified recreation of what I have:
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 }');
I’m looking to break things down by Group
and get a result set that looks something like this:
+-------+------------+------------+-------+
| Group | Start | Stop | Count |
+-------+------------+------------+-------+
| 0 | 1490280300 | 1490280500 | 3 |
+-------+------------+------------+-------+
| 1 | 1490280600 | 1490280800 | 2 |
+-------+------------+------------+-------+
| 2 | 1490280900 | NULL | 2 |
+-------+------------+------------+-------+
Start
and Stop
are Unix timestamps. I just grabbed ones from this morning and rounded out the numbers for simplicity.
A group will always have a "RecordNumber" = 0
entry so if there’s a group, there’s a start time. A group that doesn’t have an entry with "RecordNumber" = 2147483647
should not have an end time.
I’m trying to summarize every Group
by looking up it’s Start
time by finding the jsonb document for that Group
that has a RecordNumber
of 0
and using it’s When
value, and look up it’s Stop
time by finding the jsonb document for that Group
that has a RecordNumber
of 2147483647
and use it’s When
value, and finally a count of how many documents are in that Group
.
I started with this:
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";
It works if all the documents in the table belong to the same Group
. As soon as there are 2 groups my subqueries become a problem: more than one row returned by a subquery used as an expression
. I tried adding WHERE "Content"->>'Group' = "Group"
to the subqueries but it seems I can’t do that (column "Group" does not exist
).
I’m thinking this might be a chance to join a table with itself but I’m not sure where to begin with that.
Answer :
Use a CASE
statement to check if RecordNumber = 0 | 2147483647
.
Try this version:
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'
;
The result:
| Group | Start | Stop | Count |
|-------|------------|------------|-------|
| 0 | 1490280300 | 1490280500 | 3 |
| 1 | 1490280600 | 1490280700 | 2 |
| 2 | 1490280900 | NULL | 2 |
As Evan Carroll pointed out, you can also take advantage of the FILTER
clause. Have a look at 4.2.7 Aggregate expressions in the Postgres docs.
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'
;
Check it here: http://rextester.com/ADYG46620