将逗号分隔的邮政编码字段转换为城市列表。

Question

我有两张桌子：

1. 人，列user_id (唯一)和邮政编码(逗号分隔的值；多个邮政编码可能在一个值)；
2. 位置，列有邮政编码、城市和州(每个zip都与一个城市和州相关联，该表包括整个美国)。

我正试图创建一个按user_id密度对城市进行排名的表格。

因此，首先，我希望得到一个表，在"people“表中显示与每个user_id相关联的城市同时显示user_id。user_id可以与多个城市相关联。

然后，我计划只计算每个城市的唯一user_ids，并按user_id从最密集城市到最不密集城市进行排名。

Answer 1

将逗号分隔的列值转换为单独的行。

使用unnest()将逗号分隔的列转换为单独的行，首先用string_to_array()从字符串中构建数组。

select 
  user_id, 
  unnest(string_to_array(zipcodes, ',')) AS zipcode
from people

生成测试数据：

create table people(user_id int, zipcodes text);
insert into people values (1, '22333,12354,45398,12398');
into people values (2, '54389,45398,12398');
insert into people values (3, '34534,12398,94385');

结果：

 user_id | zipcode
---------+---------
       1 | 22333
       1 | 12354
       1 | 45398
       1 | 12398
       2 | 54389
       2 | 45398
       2 | 12398
       3 | 34534
       3 | 12398
       3 | 94385

按用户密度对城市进行排名

使用LEFT JOIN将有关城市的信息与相关邮政编码的提取信息结合起来。COUNT()您的用户并使用窗口函数DENSE_RANK()来分配排名位置。在这种情况下，领带的位置是一样的。

查询：

SELECT
    l.city
  , COUNT(DISTINCT p.user_id) AS distinct_users -- is distinct really needed?
  , DENSE_RANK() OVER (ORDER BY COUNT(DISTINCT p.user_id) DESC) AS city_ranking
FROM location l
LEFT JOIN (
  select 
    user_id, 
    unnest(string_to_array(zipcodes, ',')) AS zipcode
  from people
  ) p USING ( zipcode )
GROUP BY l.city
ORDER BY city_ranking

生成测试数据：

create table location(zipcode text, city text);
insert into location values 
  ('22333', 'City1'), 
  ('12354', 'City2'), 
  ('45398', 'City3'), 
  ('12398', 'City4'), 
  ('54389', 'City5'), 
  ('34534', 'City6'), 
  ('94385', 'City7');

结果：

 city  | distinct_users | city_ranking
-------+----------------+--------------
 City4 |              3 |            1
 City3 |              2 |            2
 City2 |              1 |            3
 City1 |              1 |            3
 City5 |              1 |            3
 City6 |              1 |            3
 City7 |              1 |            3

附加说明

考虑一下，您是否真的需要为邮政编码计算不同的用户。用户有可能不止一次拥有相同的邮政编码吗？

如果是这样的话，您可以在第一个查询中使用DISTINCT，这样就不需要在排名查询中这样做：

select distinct
  user_id,
  unnest(string_to_array(zipcodes, ',')) AS zipcode
from people;

删除排序查询中的不同部分，您就可以继续了。