sql - 根据另一列中的某个值将列中的值分配给所有行

Question

DB-小提琴

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    order_date DATE,
    country VARCHAR(255),
    customer VARCHAR(255)
);

INSERT INTO customers
(order_date, country, customer)
VALUES 
('2020-04-10', 'DE', 'user_01'),
('2020-04-11', 'DE', 'user_01'),
('2020-04-13', 'AT', 'user_01'),

('2020-04-20', 'AT', 'user_02'),
('2020-04-22', 'AT', 'user_02'),

('2020-05-19', 'DE', 'user_03'),

('2020-06-17', 'AT', 'user_04'),
('2020-06-23', 'AT', 'user_04'),
('2020-07-04', 'DE', 'user_04'),

('2020-08-19', 'NL', 'user_05'),
('2020-10-23', 'FR', 'user_05');

预期结果：

order_date   |   customer  |  first_country   |   second_country  | 
-------------|-------------|------------------|-------------------|-----------
2020-04-10   |   user_01   |       DE         |         DE        |
2020-04-11   |   user_01   |       DE         |         DE        |
2020-04-13   |   user_01   |       AT         |         DE        |
-------------|-------------|------------------|-------------------|-----------             
2020-04-20   |   user_02   |       AT         |        NULL       |
2020-04-22   |   user_02   |       AT         |        NULL       |
-------------|-------------|------------------|-------------------|-----------          
2020-05-19   |   user_03   |       DE         |        NULL       | 
-------------|-------------|------------------|-------------------|----------- 
2020-06-17   |   user_04   |       AT         |         DE        |
2020-06-23   |   user_04   |       AT         |         DE        |
2020-07-04   |   user_04   |       DE         |         DE        |
-------------|-------------|------------------|-------------------|----------- 
2020-08-19   |   user_05   |       NL         |        NULL       |
2020-10-23   |   user_05   |       FR         |        NULL       |

---

在上面的示例中，可能有一位客户同时在DE和中订购AT。
对于那些客户，我想将DE列中的国家/地区分配给second_country他们的每个order_dates.

参考这个问题的答案，我尝试使用这样的方法：

SELECT
t1.order_date AS order_date,
t1.customer AS customer,
t1.first_country AS first_country,
(CASE WHEN t1.rn = 1 THEN 'DE' ELSE NULL END) AS second_country

FROM

   (SELECT
   c.order_date AS order_date,
   c.country AS first_country,
   c.customer AS customer,

   row_number() OVER (PARTITION BY c.customer ORDER BY 
   (CASE WHEN c.country ='DE' THEN 1 ELSE 2 END),
   c.order_date) AS rn

   FROM customers c) t1

GROUP BY 1,2,3,4
ORDER BY 1,2,3,4;

但是，它并没有给我预期的结果。
我如何修改查询以使其工作？

标签： sqlpostgresql

Answer 1

我认为您可以为此使用窗口函数：

select c.*,
       (case when count(*) filter (where country = 'DE') over (partition by customer) > 0 and
                  count(*) filter (where country = 'AT') over (partition by customer) > 0
             then 'DE'
        end) as second_country
from c;