Answer the question
In order to leave comments, you need to log in
Window functions and aggregation?
Hello. Tell me please. There is a request that should calculate the balance through the window function, and then pack it all into jsonb.
How would it be possible to do it through CTE
1) first, in the request, we make the necessary data using the window function
2) we make a request already to generate jsonb
WITH "DevicesStatistics" as (
SELECT
"Devices".id,
"Cartridges".id as "cartridgeId",
"Cartridges"."quantityResource" as "quantityResource",
"Statistics"."quantityPrinted" as "quantityPrinted",
"Cartridges"."quantityResource" - SUM("Statistics"."quantityPrinted")
OVER (PARTITION by "Cartridges".id ORDER BY "Statistics"."lastActive") as "quantityBalance",
"Statistics"."lastActive" as "lastActive"
FROM "Devices"
LEFT JOIN "Statistics" ON "Devices".id = "Statistics"."deviceId"
LEFT JOIN "Cartridges" ON "Statistics"."cartridgeId" = "Cartridges"."id"
ORDER BY "Devices".code
)
select
id,
COALESCE(SUM("quantityPrinted"), 0) AS "quantityPrinted",
COALESCE(JSONB_AGG(
JSON_BUILD_OBJECT(
'id', "cartridgeId",
'quantityResource', "quantityResource",
'quantityPrinted', "quantityPrinted",
'quantityBalance', "quantityBalance",
'lastActive', "lastActive"
) ORDER BY "lastActive" DESC
) FILTER (WHERE "cartridgeId" IS NOT NULL), '[]') AS cartridges
from "DevicesStatistics"
group by id
SELECT
JSONB_AGG(
(select x from (select
"Cartridges".id,
"Cartridges"."quantityResource",
"Statistics"."quantityPrinted",
"Cartridges"."quantityResource" - SUM("Statistics"."quantityPrinted")
OVER (PARTITION by "Cartridges".id ORDER BY "Statistics"."lastActive") as balance
) as x) order by "Statistics"."lastActive" DESC
) AS cartridges
FROM "Devices"
LEFT JOIN "Statistics" ON "Devices".id = "Statistics"."deviceId"
LEFT JOIN "Cartridges" ON "Statistics"."cartridgeId" = "Cartridges"."id"
WHERE "Devices".code = '10037' ;
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question