Postgesql:count 包含标签属于指定用户的项目的标签

Question

我有两个包含varchar的表：

items_tags_sets：项目、标签

和

用户：user_id、名称。

如何发出请求，该请求将显示与我的项目相同的标签和数量。 请求还应在结果中包含指向标记属于指定用户的事实的指针 (its_tag(。

CREATE TABLE items_tags_sets (
uit_set_id varchar PRIMARY KEY,
item_id varchar,
user_id varchar,
tag varchar
);
CREATE TABLE users (
user_id varchar PRIMARY KEY,
name varchar
);
insert into items_tags_sets values('uid1', 'it1', 'usr1', 'tag_test1');
insert into items_tags_sets values('uid2', 'it2', 'usr2', 'tag_test1');
insert into items_tags_sets values('uid3', 'it3', 'usr3', 'tag_test2');
insert into items_tags_sets values('uid4', 'it4', 'usr1', 'tag_test2');
insert into items_tags_sets values('uid5', 'it1', 'usr2', 'tag_test3');
insert into items_tags_sets values('uid6', 'it2', 'usr3', 'tag_test3');
insert into items_tags_sets values('uid7', 'it3', 'usr1', 'tag_test4');
insert into items_tags_sets values('uid8', 'it4', 'usr2', 'tag_test4');
insert into items_tags_sets values('uid9', 'it1', 'usr3', 'tag_test5');
insert into items_tags_sets values('uid10', 'it2', 'usr1', 'tag_test5');
insert into items_tags_sets values('uid11', 'it1', 'usr2', 'tag_test1');
insert into items_tags_sets values('uid12', 'it1', 'usr3', 'tag_test1');
insert into items_tags_sets values('uid13', 'it1', 'usr2', 'tag_test3');
insert into users values('usr1', 'name1');
insert into users values('usr2', 'name2');
insert into users values('usr3', 'name3');
--------------------------------------------------------------
SELECT
items_tags_sets.item_id,
items_tags_sets.tag,
count(items_tags_sets.tag) as tags_count,
items_tag_you.user_id as its_tag
FROM items_tags_sets
LEFT OUTER JOIN users as items_tag_you on items_tag_you.user_id = items_tags_sets.user_id and items_tag_you.user_id = 'usr1'
WHERE items_tags_sets.item_id = 'it1'
GROUP BY items_tags_sets.item_id, items_tags_sets.tag, items_tag_you.user_id;

我需要这样的结果：

----------------------------------------------
| item_id |    tag    | tags_count | its_tag |
|--------------------------------------------|
|   it1   | tag_test1 |      3     |  usr1   |
|   it1   | tag_test3 |      2     |         |
|   it1   | tag_test5 |      1     |         |
----------------------------------------------

代码链接：https://rextester.com/GMJQR57790

Answer 1

而不是函数 "count(( ...分组依据"使用窗口函数对应部分"count(( 超过...". 按该计数的结果降序，然后在计数上也使用"distinct on"子句。

select distinct on (3)
its.item_id                                "Item Id"
, its.tag                                    "Tag" 
, count(its.tag) over (partition by its.tag) "Tags Count" 
, usr.user_id                                "Its Tag"
from items_tags_sets its
left join users      usr 
on (    usr.user_id = its.user_id 
and usr.user_id = 'usr1'
) 
where its.item_id = 'it1'
order by 3 desc; 

我不明白为什么你会用更复杂的"items_tag_you"来别名一个简单的表名"users"，从而设置一个更难理解的表名。所以我没有，但我确实为两个表使用了一个简单的别名。每日提示：使您的查询尽可能易于阅读，您可以随时在最终输出中添加更多描述性术语。