如何创建条件互斥且不共享密钥的列的Postgres报告

Question

背景

我有一个在RDS上运行的Postgres 11数据库。我有两个表，orders和items，就像这样：

CREATE TABLE schema.orders (
order_number TEXT,
order_date TIMESTAMPTZ,
sales_channel_name TEXT
);
CREATE TABLE schema.items (
order_number TEXT REFERENCES schema.orders(order_number),
key TEXT
quantity INT
);

我需要创建一个报告，在两个单独的列中显示行项目的计数，其中列key中有某个字符串，列key中没有某些字符串，按关联的order_date的日期分组。

所需输出的示例如下：

day         |  double_items_count               |  normal_items_count
-----------------------------------------------------------------------------------------------
2020-04-09 00:00:00    |        22                         |     13

每个所需列都可以定义为以下查询之一：

SELECT 
date_trunc('day', o.order_date AT TIME ZONE 'America/Los_Angeles') AS day,
2*(count(*)) AS double_items_count
FROM 
schema.items i  
INNER JOIN
schema.orders o 
ON i.order_number = o.order_number
WHERE 
i.key ILIKE '%some_string%'
AND o.sales_channel_name = 'foo_sales_channel'
GROUP BY day 
ORDER BY day DESC
;
SELECT 
date_trunc('day', o.order_date AT TIME ZONE 'America/Los_Angeles') AS day,
count(*) AS normal_items_count
FROM 
schema.items  
INNER JOIN
schema.orders_new o 
ON i.order_number = o.order_number
WHERE 
i.key NOT ILIKE '%some_string%'
AND o.sales_channel_name = 'foo_sales_channel'
GROUP BY day 
ORDER BY day DESC
;

我还有一个视图schema.items_from_channel，它只返回我感兴趣的记录：

CREATE VIEW schema.items_from_channel AS (
SELECT 
date_trunc('day', o.date at time zone 'America/Los_Angeles') as day,
o.order_number,
i.key,
o.sales_channel
FROM 
schema.orders o 
INNER JOIN
schema.items i 
ON 
o.order_number = i.order_number 
WHERE o.sales_channel = "foo_sales_channel"
ORDER BY day DESC
);

问题

通常，我会用对视图的直接查询来处理这类事情，或者用前面描述的两个查询的联接来处理。

但是，由于所需的列依赖于互斥的WHERE条件，我不知道如何构造一个同时产生这两个条件的查询(例如，使用子查询(。

由于除了day之外，这两个查询没有共享密钥，所以我不知道如何以产生合理结果的方式将它们连接起来[在day上连接会产生夸大的数字，这对我来说是有意义的]。

UNION不会产生所需的结果，因为它返回所需的所有数据，但不维护所需的列格式。

我被难住了。如何生成所需的报告？在一天的大部分时间里，我一直在谷歌上搜索相关的SE网站，但还没有找到解决方案。非常感谢所有的指导！

Answer 1

具有以下数据：

select * from orders;
order_number |     order_date      | sales_channel_name 
--------------+---------------------+--------------------
1 | 2020-04-09 01:00:00 | foo_sales_channel
2 | 2020-04-09 02:00:00 | foo_sales_channel
3 | 2020-04-09 03:00:00 | foo_sales_channel
4 | 2020-04-09 04:00:00 | foo_sales_channel
(4 rows)
select * from items;
id |      key       | order_number 
----+----------------+--------------
1 | some_string    |            1
2 | some_string    |            2
3 | another_string |            3
4 | another_string |            4
(4 rows)
SELECT 
date_trunc('day', o.order_date AT TIME ZONE 'America/Los_Angeles') AS day,
2*(count(*)) AS double_items_count
FROM 
items i  
INNER JOIN
orders o 
ON i.order_number = o.order_number
WHERE 
i.key ILIKE '%some_string%'
AND o.sales_channel_name = 'foo_sales_channel'
GROUP BY day 
ORDER BY day DESC;
day           | double_items_count 
------------------------+--------------------
2020-04-09 00:00:00+02 |                  4
(1 row)
SELECT 
date_trunc('day', o.order_date AT TIME ZONE 'America/Los_Angeles') AS day,
count(*) AS normal_items_count
FROM 
items  i
INNER JOIN
orders o 
ON i.order_number = o.order_number
WHERE 
i.key NOT ILIKE '%some_string%'
AND o.sales_channel_name = 'foo_sales_channel'
GROUP BY day 
ORDER BY day DESC
;
day           | normal_items_count 
------------------------+--------------------
2020-04-09 00:00:00+02 |                  2
(1 row)

这里有一个解决方案：

SELECT
date_trunc('day', o.order_date AT TIME ZONE 'America/Los_Angeles') AS day,
2*count(*) FILTER (WHERE i.key ILIKE '%some_string%') AS double_items_count,
count(*)   FILTER (WHERE i.key NOT ILIKE '%some_string%') AS normal_items_count
FROM
items  i
INNER JOIN
orders o
ON i.order_number = o.order_number
WHERE
o.sales_channel_name = 'foo_sales_channel'
GROUP BY day
ORDER BY day DESC
;
day           | double_items_count | normal_items_count 
------------------------+--------------------+--------------------
2020-04-09 00:00:00+02 |                  4 |                  2
(1 row)