sql - 结合两个查询postgres的结果

Question

我有两个这样的查询:

SELECT project_id, user_ip, count(*) AS Count 
FROM "user" 
WHERE user_ip IS NOT NULL 
GROUP BY user_ip, project_id;

SELECT project_id, user_id, count(*) AS Count 
FROM "user" 
WHERE user_id IS NOT NULL 
GROUP BY user_id, project_id;

它们都是一样的，只是一个根据 IP 返回用户，另一个根据 ID 返回用户。

用户只能拥有其中一个，所以基本上如果 user_ip 为 Null 则 user_id 将具有值，如果 则相反>user_id 为 Null那么 user_ip 就会有值(value)。

所以我只想让这些查询成为一个查询。

第一个查询给出了这个:

project_id | user_ip | Count            
  1        | 1.2.3.4 | 40        
  2        | 1.2.3.5 | 25    
  3        | 1.2.3.6 | 9          
  4        | 1.2.3.7 | 7 

第二个给出了这个:

project_id | user_id | Count            
  1        |  1234   | 100        
  2        |  4567   | 50    
  3        |  4321   | 49  

所以我只想做一个会返回这个的查询:

project_id | user_id | user_ip | Count            
  1        |  1234   |         | 100 
  1        |         | 1.2.3.4 | 40       
  2        |  4567   |         | 50
  2        |         | 1.2.3.5 | 25     
  3        |  4321   |         | 49 
  3        |         | 1.2.3.6 | 9          
  4        |         | 1.2.3.7 | 7

我尝试进行左连接，我也尝试使用 Union 进行连接，但出现错误:UNION types text and integer cannot be matched

SELECT project_id, user_ip, count(*) AS Count 
FROM "user" 
WHERE user_ip IS NOT NULL 
GROUP BY user_ip, project_id  
UNION 
SELECT project_id, user_id, count(*) AS Count 
FROM "user" 
WHERE user_id IS NOT NULL GROUP 
BY user_id, project_id;

有人可以帮助我实现我想要的结果吗？我能用什么？

Answer 1

在投影中的 SELECT 子句中添加 NULL as user_id/ip。

SELECT project_id, NULL as user_id, user_ip, count(*) AS Count ...
union
SELECT project_id, user_id, NULL as user_ip, count(*) AS Count 

---

或者，尝试使用 group by project_id, user_id, user_ip 代替 union，这也应该有效。这可能会更快。

SELECT project_id, user_id, user_ip, count(*) AS Count 
FROM "user" 
GROUP BY project_id, user_id, user_ip;