Sql 根据潜在空值的条件对记录进行计数

我有这些表格:

CREATE TABLE customer_orders (
  "order_id" INTEGER,
  "customer_id" INTEGER,
  "pizza_id" INTEGER,
  "exclusions" VARCHAR(4),
  "extras" VARCHAR(4),
  "order_time" TIMESTAMP
);
INSERT INTO customer_orders
  ("order_id", "customer_id", "pizza_id", "exclusions", "extras", "order_time")
VALUES
  ('1', '101', '1', '', '', '2020-01-01 18:05:02'),
  ('2', '101', '1', '', '', '2020-01-01 19:00:52'),
  ('3', '102', '1', '', '', '2020-01-02 23:51:23'),
  ('3', '102', '2', '', NULL, '2020-01-02 23:51:23'),
  ('4', '103', '1', '4', '', '2020-01-04 13:23:46'),
  ('4', '103', '1', '4', '', '2020-01-04 13:23:46'),
  ('4', '103', '2', '4', '', '2020-01-04 13:23:46'),
  ('5', '104', '1', NULL, '1', '2020-01-08 21:00:29'),
  ('6', '101', '2', NULL, NULL, '2020-01-08 21:03:13'),
  ('7', '105', '2', NULL, '1', '2020-01-08 21:20:29'),
  ('8', '102', '1', NULL, NULL, '2020-01-09 23:54:33'),
  ('9', '103', '1', '4', '1, 5', '2020-01-10 11:22:59'),
  ('10', '104', '1', NULL, NULL, '2020-01-11 18:34:49'),
  ('10', '104', '1', '2, 6', '1, 4', '2020-01-11 18:34:49');

CREATE TABLE runner_orders (
  "order_id" INTEGER,
  "runner_id" INTEGER,
  "pickup_time" VARCHAR(19),
  "distance" DECIMAL(5,2) NULL,
  "duration" VARCHAR(10),
  "cancellation" VARCHAR(23)
);
INSERT INTO runner_orders
  ("order_id", "runner_id", "pickup_time", "distance", "duration", "cancellation")
VALUES
  ('1', '1', '2020-01-01 18:15:34', '20', '32', ''),
  ('2', '1', '2020-01-01 19:10:54', '20', '27', ''),
  ('3', '1', '2020-01-03 00:12:37', '13.4', '20', NULL),
  ('4', '2', '2020-01-04 13:53:03', '23.4', '40', NULL),
  ('5', '3', '2020-01-08 21:10:57', '10', '15', NULL),
  ('6', '3', NULL, NULL, NULL, 'Restaurant Cancellation'),
  ('7', '2', '2020-01-08 21:30:45', '25', '25mins', NULL),
  ('8', '2', '2020-01-10 00:15:02', '23.4', '15', NULL),
  ('9', '2', NULL, NULL, NULL, 'Customer Cancellation'),
  ('10', '1', '2020-01-11 18:50:20', '10', '10', NULL);

他们给了我一个问题:对于每个顾客，有多少送来的披萨至少有一次变化，有多少没有变化？

现在，我试着这样做:

WITH cte_1
AS (
    SELECT co.customer_id
        , co.order_id
        , co.exclusions
        , isnull(co.exclusions,'') AS exc
        , isnull(co.extras,'') AS ext
    FROM customer_orders as co
    INNER JOIN runner_orders as ru 
        ON co.order_id = ru.order_id
    WHERE NOT ru.cancellation IN ('Restaurant Cancellation', 'Customer Cancellation')    
)    
SELECT customer_id
    , COUNT(order_id) AS no_changes 
FROM cte_1
WHERE exc = '' AND ext = ''
GROUP BY customer_id

你可以看到这一点很长时间了，但至少是有效的，数字是正确的.但当我试着做同样的事情来获得披萨的结果时，就像这样改变:

WITH cte_1
AS (
    SELECT co.customer_id
        , co.order_id
        , co.exclusions
        , isnull(co.exclusions,'') AS exc
        , isnull(co.extras,'') AS ext
    FROM customer_orders as co
    INNER JOIN runner_orders as ru 
        ON co.order_id = ru.order_id
    WHERE NOT ru.cancellation IN ('Restaurant Cancellation', 'Customer Cancellation')    
)    
SELECT customer_id
    , COUNT(order_id) AS with_changes 
FROM cte_1
WHERE not exc = '' 
GROUP BY customer_id
UNION
SELECT customer_id
    , COUNT(order_id) AS with_changes 
FROM cte_1
WHERE not ext = '' 
GROUP BY customer_id

它不起作用，我不确定这是因为空格和空格还是总体上的方法.如果有更好更快的方法来做这件事，请.

预期结果将类似于: