0

So I have to group a table according to the date and then the timeday when a product arrived, the timedays would be:

morning = [5, 6, 7 , 8, 9]
mid_morning = [10, 11]
midday = [12, 13, 14]
evening = [15, 16, 17 ,18 ,19, 20]
night = [21, 22, 23, 0, 1, 2, 3, 4]

This is the table:

 CREATE TABLE inventory (
      inventory_id serial PRIMARY KEY,
      arrive_date date NOT NULL,
      arrive_location character varying NOT NULL,
      thing_type integer NOT NULL,
      quantity integer NOT NULL
    );

INSERT INTO inventory (arrive_date, arrive_location, thing_type, quantity) VALUES
  ('2018-05-30 05:00:00-00', 'location_00', 3, 2)
, ('2018-05-30 06:00:00-00', 'location_00', 3, 8)
, ('2018-05-30 12:50:00-00', 'location_00', 5, 2)
, ('2018-05-30 13:40:00-00', 'location_00', 1, 3)
, ('2018-05-31 13:00:00-00', 'location_00', 4, 7)
, ('2018-05-31 18:00:00-00', 'location_00', 2, 3)
;

The desired result would be to have this table result:

preprocess_id arrive_date arrive_timeday arrive_location data
33 2018-05-30 0 location_00 { "3": 10 }
34 2018-05-30 2 location_00 { "5": 2, "1": 3 }
36 2018-05-31 2 location_00 { "4": 7 }
37 2018-05-31 4 location_00 { "2": 3 }

The current query fiddle that I have only group by day, is it possible to have the date and then the daytime?

1 Answer 1

1

I don't understand how preprocess_id was produced and how an actual hour was mapped to arrive_timeday in your desire result. Any way, you can use a CASE expression or a mapping table to map to the number.

Using CASE expression

SELECT arrive_date, arrive_timeday, arrive_location
     , jsonb_object_agg(thing_type, total_things)
FROM  (
   SELECT arrive_date
        , arrive_timeday
        , arrive_location
        , thing_type
        , sum(quantity) AS total_things
   FROM   (
       SELECT date_trunc('day', arrive_date) AS arrive_date
            , case 
              when extract(hour from arrive_date) in (5, 6, 7 , 8, 9) then 0
              when extract(hour from arrive_date) in (10, 11) then 1
              when extract(hour from arrive_date) in (12, 13, 14) then 2
              when extract(hour from arrive_date) in (15, 16, 17 ,18 ,19, 20) then 4
              when extract(hour from arrive_date) in (21, 22, 23, 0, 1, 2, 3, 4) then 8
              end arrive_timeday
            , arrive_location
            , thing_type
            , quantity
       FROM   inventory
   ) inv
   GROUP  BY arrive_date, arrive_timeday, arrive_location, thing_type
   ) sub
GROUP  BY arrive_date, arrive_timeday, arrive_location
ORDER  BY arrive_date, arrive_timeday, arrive_location;

Using mapping table:

Table creation:

CREATE TABLE hour_mapping (
  hour_from integer NOT NULL,
  hour_to integer NOT NULL,
  timeday integer NOT NULL,
  descpt character varying NOT NULL
);

INSERT INTO hour_mapping (hour_from, hour_to, timeday, descpt) VALUES 
  (5, 9, 0, 'morning')
, (10, 11, 1, 'mid_morning')
, (12, 14, 2, 'midday')
, (15, 20, 4, 'evening')
, (21, 23, 8, 'night')
, (0, 4, 8, 'night')
;

Query

SELECT arrive_date, arrive_timeday, arrive_location
     , jsonb_object_agg(thing_type, total_things)
FROM  (
   SELECT date_trunc('day', arrive_date) AS arrive_date
        , m.timeday as arrive_timeday
        , arrive_location
        , thing_type
        , sum(quantity) AS total_things
   FROM  inventory inv, hour_mapping m
   WHERE extract(hour from inv.arrive_date) between m.hour_from and hour_to
   GROUP  BY arrive_date, arrive_timeday, arrive_location, thing_type
   ) sub
GROUP  BY arrive_date, arrive_timeday, arrive_location
ORDER  BY arrive_date, arrive_timeday, arrive_location;

Fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.