B
B
Bogdan2019-12-04 17:14:05
PostgreSQL
Bogdan, 2019-12-04 17:14:05

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

but the construction came out mega-large, is it possible somehow to cram it into one request? Thanks
P.S. I tried like this, but then the window function does not work correctly, it does not consider the previous values
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 question

Ask a Question

731 491 924 answers to any question