我用inner join查询一个时间序列图。一个标签列表被用作inner join的where条件。我用union all得到一个结果表，其中包含两种情况的数据：一个查询的标签在列表中，一个查询的标签列表为空。我希望行的null值从第一个select之前的union all，如果标签list为空，但PostgreSQL返回SELECT 0，即根本没有行。我想要null行，因为没有它们，我无法以编程方式分离大小写。
我如何得到null行的结果？或者如果标签列表不为空，是否有一种方法可以只从union all之前的select获得结果，如果标签列表为空，则只从union all之后的select获得结果？如果这些都不可能实现，然后建议如何从当前查询中提取这两个案例的数据或修改它是非常受欢迎的。没有必要处理餐馆有多个标签：一家餐馆要么有一个标签，要么没有任何标签。
查询示例，带有伪值和注解的表在这里：db<>fiddle。

-- I want null values from this first SELECT before UNION ALL unless
-- a more elegant/efficient solution is possible. But I get 'SELECT 0'.
SELECT 
  f.days AS days,
  SUM (f.waste_kgs) AS waste,
  SUM (f.prepared_kgs) AS prepared,
  f.waste_type_id AS waste_id,
  f.restaurant_id AS restaurant
FROM food f
INNER JOIN tags t
  ON f.restaurant_id = t.restaurant_id
WHERE t.name = ANY (array[]::text[]) -- Empty tag list as an argument.
  AND f.days BETWEEN '2000-1-1' and '2099-1-1'
  AND f.restaurant_id = ANY (array[1, 2, 3])
GROUP BY days, restaurant, waste_id 
-- Separately running the first SELECT returns
-- days     waste   prepared    waste_id    restaurant
-- SELECT 0

UNION ALL 
-- The same query without the INNER JOIN and tag list argument. 
SELECT
  f.days AS days,
  SUM (f.waste_kgs) AS waste,
  SUM (f.prepared_kgs) AS prepared,
  f.waste_type_id AS waste_id,
  f.restaurant_id AS restaurant
FROM food f
WHERE f.days BETWEEN '2000-1-1' and '2099-1-1'
  AND f.restaurant_id = ANY (array[1, 2, 3])
GROUP BY days, restaurant, waste_id;

-- The whole query returns
-- days       waste     prepared    waste_id    restaurant
-- 2023-01-01   2.5        16.0           1              1
-- 2023-01-02   8.6         7.3           1              2
-- 2023-01-03   10.5        1.8           1              3
-- 2023-01-03   0.8         0.0           2              3
-- SELECT 4
-- No null values to differentiate the empty tag list argument case.

字符串
下面是示例中使用的来自db<>fiddle的表和值。

CREATE TABLE restaurants (
  id int PRIMARY KEY,
  name text,
  type text
);
CREATE TABLE food (
  id int PRIMARY KEY,
  restaurant_id int REFERENCES restaurants (id),
  waste_type_id smallint NOT NULL,
  product_id int NOT NULL,
  waste_kgs decimal NOT NULL, 
  prepared_kgs decimal NOT NULL,
  customers smallint NOT NULL,
  days date NOT NULL
);
CREATE TABLE tags (
  id int PRIMARY KEY,
  restaurant_id int REFERENCES restaurants (id),
  name text
);
INSERT INTO restaurants VALUES
(1, 'restaurant_1'),
(2, 'restaurant_2'),
(3, 'restaurant_3');
INSERT INTO food VALUES 
(1, 1, 1, 1, 1.7, 8.0, 96, '2023-1-1'),
(2, 1, 1, 10, 0.5, 7.0, 96, '2023-1-1'),
(3, 1, 1, 15, 0.3, 1.0, 96, '2023-1-1'),
(4, 2, 1, 12, 7.0, 0.8, 39, '2023-1-2'),
(5, 2, 1, 10, 1.1, 5.0, 39, '2023-1-2'),
(6, 2, 1, 11, 0.5, 1.5, 39, '2023-1-2'),
(7, 3, 1, 8, 10.0, 0.3, 97, '2023-1-3'),
(8, 3, 2, 17, 0.8, 0.0, 97, '2023-1-3'),
(9, 3, 1, 11, 0.5, 1.5, 39, '2023-1-3');
INSERT INTO tags VALUES
(1, 1, 'tag_1');

型
下面的查询来自相同的表，带有一个空的标签列表，给出了一个表，其中包含来自inner join的null值，所以我可以将它与union all一起使用-就像我试图对上面的查询所做的那样-并轻松地分离结果。

-- The first row of the result displays calculations from the tagged restaurants. 
-- In this case it has null values because tag list is empty.
-- The second row of the result displays calculations from all the restaurants without
-- considering any tags.
SELECT Total, Total - Drinks AS "Without drinks", Drinks   
FROM (
  SELECT 
  SUM (f.prepared_kgs) 
  FILTER (
    WHERE f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Total,
  SUM (f.prepared_kgs)
  FILTER (
    WHERE (f.product_id = 10 OR f.product_id = 17) 
    AND f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Drinks
  FROM food f
  INNER JOIN tags t 
    ON t.restaurant_id = f.restaurant_id 
  WHERE t.name = ANY (array[]::text[]) -- Empty tag list as an argument.  
)
UNION ALL
-- The same query without the INNER JOIN and tag list argument.
SELECT Total, Total - Drinks AS "Without drinks", Drinks   
FROM (
  SELECT 
  SUM (f.prepared_kgs) 
  FILTER (
    WHERE f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Total,
  SUM (f.prepared_kgs)
  FILTER (
    WHERE (f.product_id = 10 OR f.product_id = 17) 
    AND f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Drinks
  FROM food f
);
-- If tag list had elements, my program uses the first row.
-- Otherwise it uses the second row. Time series query returns 
-- rows for each day but the logic I try to use is the same.
total   Without drinks  drinks
 null             null    null
 25.1             13.1    12.0
SELECT 2

型