GROUP BY date column and then group by custom daytime



  • 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 https://dbfiddle.uk/?rdbms=postgres_13&fiddle=f9acac2150ec7d4854fa2ebff7710d8a that I have only group by day, is it possible to have the date and then the daytime?



  • 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;
    

    https://dbfiddle.uk/?rdbms=postgres_13&fiddle=5e878c9fea252a97e3ea14f45d016c50




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2