主页 / dba / 问题 / 307794
Accepted
IBot
Asked: 2022-02-22 15:57:58 +0800 CST

GROUP BY 日期列，然后按自定义白天分组

  • 772

所以我必须根据日期和产品到达的时间对表格进行分组，时间将是：

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 到达日期 到达时间天 到达位置 数据
33 2018-05-30 0 位置_00 {“3”：10}
34 2018-05-30 2 位置_00 { "5": 2, "1": 3 }
36 2018-05-31 2 位置_00 {“4”：7}
37 2018-05-31 4 位置_00 {“2”：3}

我只有按天分组的当前查询小提琴，是否可以有日期然后是白天？

group-by postgresql-13

1 个回答

  1. Best Answer

    我不明白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;

    小提琴

    • 1

相关问题