IBot's questions

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

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

期望的结果是得到这个表结果：

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

如果我正在运行查询，我有这张表：

CREATE TABLE IF NOT EXISTS public.preprocess_things
(
    preprocess_id integer NOT NULL DEFAULT nextval('preprocess_things_preprocess_id_seq'::regclass),
    arrive_date date NOT NULL,
    arrive_location character varying COLLATE pg_catalog."default" NOT NULL,
    data jsonb NOT NULL,
    CONSTRAINT preprocess_things_pkey PRIMARY KEY (preprocess_id),
    CONSTRAINT preprocess_things_arrive_date_arrive_location_bo_key UNIQUE (arrive_date, arrive_location)
)

我正在运行的查询是这样的：

SELECT DATE_TRUNC('month', arrive_date) AS grouped_date,
  LOWER(arrive_location) AS location,
  json_build_object(
    '1', SUM((data->'1')::int),
    '2', SUM((data->'2')::int),
    '3', SUM((data->'3')::int),
    '4', SUM((data->'4')::int),
    '5', SUM((data->'5')::int)
    ) AS data
FROM preprocess_things
GROUP BY grouped_date,
  location

目前的结果是：

我想应用另一个 SELECT，为每个没有空值的值对添加一行，其中键进入 thing_type 列，值进入总列；像这样：

小提琴数据库可以在这里找到。

更新：

感谢@Gerard H. Pille，我稍微调整了一下他对此的回应：

SELECT
  DATE_TRUNC('month', arrive_date) AS grouped_date,
  LOWER(arrive_location) AS location,
  x.key::int thing_type, sum(x.value::int) total
FROM preprocess_things
JOIN  jsonb_each(data) x ON (x.key::int = ANY('{1,2,3}'::int[]))
GROUP BY grouped_date,  location, x.key::int
ORDER BY grouped_date,  location, x.key::int;

由于在某些情况下，我只需要选择 thing_type 而不是全部计算，唯一的问题是通过添加 JOIN 并比较查询速度不是那么好。

嘿，我正在尝试根据查询结果插入或更新（如果约束重复），这些是 create table 语句：

CREATE TABLE IF NOT EXISTS public.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
);

CREATE TABLE IF NOT EXISTS public.preprocess_things (
  preprocess_id serial PRIMARY KEY,
  arrive_date date NOT NULL,
  arrive_location character varying NOT NULL,
  data jsonb NOT NULL,
  CONSTRAINT preprocess_things_arrive_date_arrive_location_bo_key UNIQUE (arrive_date, arrive_location)
);

这是 upsert 查询：

WITH result_query AS (
    SELECT DATE_TRUNC('day', arrive_date) AS date,
      arrive_date,
      arrive_location,
      thing_type,
      SUM(quantity) AS total_things
    FROM inventory
    GROUP BY date, arrive_location, thing_type
)
INSERT INTO preprocess_things (
    arrive_date,
    arrive_location,
    data
  )
SELECT r.date AS arrive_date,
  r.arrive_location,
  jsonb_build_object(r.thing_type, r.total_things)
FROM result_query r
ON CONFLICT (arrive_date, arrive_location) DO
UPDATE SET data = preprocess_things.data || EXCLUDED.data

result_query 行是：

date                   | arrive_location | thing_type | thing_count
2018-05-30 00:00:00-00 | location_00     |   3        | 2
2018-05-31 00:00:00-00 | location_00     |   3        | 8
2018-05-31 00:00:00-00 | location_00     |   4        | 7

尝试插入preprocess_things，其中 data 是一种jsonb类型，预期结果是：

id | arrive_date            | arrive_location | data
1  | 2018-05-30 00:00:00-00 | location_00     | { "3": 2 }
2  | 2018-05-31 00:00:00-00 | location_00     | { "3": 8, "4": 7 }

所以我试图根据查询结果进行更新：

/*
    Querying from this table:
     id | arrive_date | arrive_location | thing_type | thing_count
*/
CREATE TABLE IF NOT EXISTS public.inventory
(
  inventory_id serial NOT NULL,
  inventory_date date NOT NULL,
  arrive_location character varying NOT NULL,
  thing_type integer NOT NULL,
  quantity integer NOT NULL,
  PRIMARY KEY (inventory_id)
)    
/*
    Trying to insert on this table, where summary is a jsonb type:
    id | arrive_date | arrive_location | data
*/
CREATE TABLE IF NOT EXISTS public.preprocess_things
   (
      preprocess_id serial NOT NULL,
      arrive_date date NOT NULL,
      arrive_location character varying NOT NULL,
      data jsonb NOT NULL,
      CONSTRAINT preprocess_things_pkey PRIMARY KEY (preprocess_id),
      CONSTRAINT preprocess_things_arrive_date_arrive_location_bo_key   UNIQUE (arrive_date, arrive_location)
    )
/*Begin upsert*/
WITH result_query AS (
    SELECT DATE_TRUNC('day', inventory_date) AS arrive_date,
      arrive_location,
      thing_type,
      SUM(quantity) AS total_things
    FROM inventory
    GROUP BY arrive_date, arrive_location, thing_type
)
INSERT INTO preprocess_things (
    result_query.arrive_date,
    result_query.arrive_location,
    jsonb_build_object(result_query.thing_type || '', result_query.total_things)::jsonb
  ) ON CONFLICT (arrive_date, arrive_location) DO
UPDATE
  SET data= jsonb_insert(data, '{' || result_query.thing_type || '}', result_query.thing_sum)

有一个问题：

ERROR:  syntax error at or near "("
LINE 7:     jsonb_build_object(result_query.thing_type || '', total_things)::...

使用模拟数据的 Upsert 可以正常工作，但无法向 jsonb_build_object 发送参数