主页 / dba / 问题 / 80003
Accepted
DaveB
Asked: 2014-10-13 12:32:32 +0800 CST

PostgreSQL 将第二个“组”作为数组返回

  • 772

我有一些包含网站和访问者的表格，我正在尝试编写一个查询，为每个网站生成“前 10 个”，这些表格看起来像这样......

websites
-------
website_id (pk)
url
description

visitors
-------
visitor_id (pk)
ip_address

website_visits
--------
visit_id (pk)
website_id (fk)
visitor_id (fk)
visit_date
visit_duration

我知道我可以使用两个 GROUP BY 来生成一个列表，该列表使用下面的查询按网站排序...

SELECT w.website_id, v.visitor_id, count(wv.visit_id)
FROM website_visits ws, websites w, visitors v
WHERE wv.website_id = w.website_id and wv.visitor_id = v.visitor_id
GROUP BY w.website_id, v.visitor_id
ORDER BY w.website_id ASC, count(wv.visit_id) DESC

但我想通过 a) 只显示每个站点的前 10 个，以及 b) 将每个前 10 个结果分组到一个数组中来改进这一点，这样结果看起来像这样：

website_id    top_ten
432           [{visitor_id:354,visits:958},{visitor_id:987,visits:971}...etc]
325           [{visitor_id:456,visits:955},{visitor_id:1056,visits:465}...etc]

（我在这里使用了 JSON 但它可以是任何格式）

我已经尝试在子查询中使用 SELECT ARRAY(...) 函数，但我似乎无法让它工作，我不确定这是否是正确的方法？

有人可以建议吗？谢谢

顺便使用PostgreSQL 9.0

postgresql array

1 个回答

  1. Best Answer

    也许是这样的：

    with ranked_visits as (
  SELECT w.website_id, 
         v.visitor_id, 
         count(wv.visit_id) as visits,
         row_number() over (partition by w.website_id order by count(wv.visit_id) desc) as rnk
  FROM website_visits wv
    JOIN websites w ON wv.website_id = w.website_id
    JOIN visitors v ON wv.visitor_id = v.visitor_id
  GROUP BY w.website_id, v.visitor_id
  ORDER BY w.website_id ASC, count(wv.visit_id) DESC
)
select website_id, string_agg('visitor_id: '||visitor_id||',visits:'||visits, ', ')
from ranked_visits
where rnk <= 10
group by website_id;
    • 3

相关问题