GROUP BY 日期列，然後按自定義白天分組

所以我必鬚根據日期和產品到達的時間對錶格進行分組，時間將是：

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]

這是表格：

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

期望的結果是得到這個表結果：

我只有按天分組的目前查詢小提琴，是否可以有日期然後是白天？

我不明白preprocess_id是如何產生的，以及實際小時數是如何映射到arrive_timeday您的期望結果中的。無論如何，您都可以使用CASE表達式或映射表來映射到數字。

使用CASE表達式

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;

使用映射表：

表創建：

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')
;

詢問

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://dba.stackexchange.com/questions/307794