Postgresql

GROUP BY,但每個使用者只使用一行

  • March 22, 2021

我花了幾個小時在 Postgres中做一個“簡單的” SELECT,但沒有成功。GROUP BYGROUP BY條款給我帶來了問題。

我有cities帶有列user_id和的表cityuser_id可以重複,因此該表可以包含以下資訊:

"Bill", "New York"
"Bill", "Chicago"
"Adam", "New York"
"Mike", "Los Angeles"
...

如果我想計算城市的數量,可以這樣:

SELECT cities.city, COUNT(*) FROM cities GROUP BY cities.city

但是,如果我想獲得這個計數並且每個使用者只選擇一個城市(“Bill”採用“New York”或“Chicago”並不重要),我怎麼能按 city.user_id 分組?

您的查詢並不完全計算城市,而是計算每個列出城市的使用者數。要在對使用者進行重複數據刪除後得到它:

SELECT city, count(*) AS users
FROM  (
  SELECT DISTINCT ON (user_id) city
  FROM   cities
  ) sub
GROUP  BY city;

這會像您指定的那樣user_id 任意選擇一行。所以我們不需要ORDER BY在內部SELECT

我們只需要city來自內部查詢的裸計數。

詳細解釋DISTINCT ON

對於任意選擇不是確定性的

在實現規定的要求時,對於每行幾行來說,上述方法通常是最快的。user_id

但是當我們任意選擇行時,結果是**不確定的。**可以為重複執行返回不同的數字,因為 Postgres 可以自由地為一個使用者選擇任何行。(不過,作為使用者數量,所有城市的總和是穩定的。)

結果通常是穩定的,但對錶的任何更改都可能觸發不同的結果。就像autovacuum在後台做它的工作,或者對錶進行任何不相關的寫操作。

要獲得確定性結果,您需要向內部查詢添加確定性 ORDER BY,以便DISTINCT ON始終選擇同一行。像:

SELECT city, count(*) AS users
FROM  (
  SELECT DISTINCT ON (user_id) city
  FROM   cities
  ORDER  BY user_id, city  -- making the pick determinisitic
  ) sub
GROUP  BY 1;

這相當於:

SELECT city, count(*) AS users
FROM  (
  SELECT min(city)
  FROM   cities
  GROUP  BY user_id
  ) sub
GROUP  BY 1;

看來您不太了解該group by條款…也許此連結可以提供幫助。

如果您的城市表中有主鍵,則可以使用它通過使用者名獲取第一行(或最後一行,您的選擇):

laetitia=# create table cities(id integer generated always as identity primary key,                                                                                                                                                                                           
 username text,                                                                                                                                                                                                                                                              
 cityname text);      
CREATE TABLE
laetitia=# insert into cities (username, cityname) values ('bill', 'New York');                                                         
INSERT 0 1      
laetitia=# insert into cities (username, cityname) values ('bill', 'Chivago');
INSERT 0 1   
laetitia=# insert into cities (username, cityname) values ('adam', 'New York');                                                         
INSERT 0 1              
laetitia=# insert into cities (username, cityname) values ('mike', 'Los Angeles');                                                      
INSERT 0 1          
laetitia=# update cities set cityname='Chicago' where cityname = 'Chivago';                                                             
UPDATE 1           
laetitia=# table cities
laetitia-# ;
id | username |  cityname                                                                                                                                                                                                                                                    
----+----------+-------------                                                                                                          
 1 | bill     | New York       
 3 | adam     | New York
 4 | mike     | Los Angeles                                                                                                                                                                                                                                                    2 | bill     | Chicago      
(4 rows)        
laetitia=# with firstline(id, username) as (                                                                                                                                                                                                                                    
 select min(id),                                                                                                                                                                                                                                                                   
   username                                                                                                                                                                                                                                                                    
 from cities                                                                                                                                                                                                                                                                   
 group by username                                                                                                                                                                                                                                                           
)                                                                                                                                                                                                                                                                             
select cities.username,                                                                                                                                                                                                                                                         
 cityname                                                                                                                                                                                                                                                                    
from cities                                                                                                                                                                                                                                                                     
 inner join firstline                                                                                                                                                                                                                                                            
 on cities.id = firstline.id                                                                                                                                                                                                                                               
;
username |  cityname   
----------+-------------
bill     | New York
adam     | New York
mike     | Los Angeles
(3 rows)

如果您沒有主鍵(不好)或不想使用它,您可以按字母順序為每個使用這種查詢的使用者獲取第一個城市名稱:

laetitia=# select c1.username,                                                                                                                                                                                                                                                  
 c1.cityname,                                                                                                                                                                                                                                                                  
 c2.cityname                                                                                                                                                                                                                                                                 
from cities c1                                                                                                                                                                                                                                                                  
 left join cities c2                                                                                                                                                                                                                                                             
   on c1.username = c2.username                                                                                                                                                                                                                                                  
   and c1.cityname > c2.cityname                                                                                                                                                                                                                                             
where c2.cityname is null                                                                                                                                                                                                                                                     
;
username |  cityname   | cityname 
----------+-------------+----------
adam     | New York    | 
mike     | Los Angeles | 
bill     | Chicago     | 
(3 rows)

祝你今天過得愉快!

引用自:https://dba.stackexchange.com/questions/287437