postgresql – 使用限制条件连接表格

huangapple
go评论112阅读模式
英文:

postgresql - join tables with limit

问题

我想展示用户每家商店的购买摘要，每家商店最多显示前3次购买的情况。为简单起见，购买次数不可配置。因此，不是前N次购买，而是前3次购买。

假设我有以下带有这些列的表：

  1. Store
  2. 	id
  3. 	name
  5. Product
  6. 	id
  7. 	name
  8. 	storeId
  9. 	price
  11. Purchase
  12. 	id
  13. 	userId
  14. 	productId
  15. 	count
  16. 	purchaseTime

这是我想要显示的内容：

  1. -----------------------------------------------
  2. | 商店       | 商品            | 数量   | 价格   |
  3. -----------------------------------------------
  4. | 迪士尼    | 毛绒玩偶         | 1     | 30  |
  5. |           | 唐老鸭帽子      | 3     | 15  |
  6. |           | 门票            | 2     | 100 |
  7. -----------------------------------------------
  8. | 环球影城 | 钢铁侠模型      | 1     | 100 |
  9. |           | 蝙蝠侠模型      | 1     | 90  |
  10. -----------------------------------------------

最好的情况是，如果用户在两次独立购买中购买了唐老鸭帽子（假设价格永远不会改变），购买次数将被合并。

例如：如果用户购买了2个唐老鸭帽子，然后再购买1个帽子，结果将是3个唐老鸭帽子 - 而不是2个唐老鸭帽子和1个唐老鸭帽子。

我正在使用Hibernate和PostgreSQL。

我甚至不知道从哪里开始，特别是如何限制每个商店购买次数为3次。

任何提示、伪代码解决方案或实际解决方案都将不胜感激。

谢谢

注意：
不幸的是，我在这方面存在一个重大疏忽。
我想我希望每个摘要都在1行内呈现。

例如

  1. id | 商店       | 商品1            | 数量1 | 价格1 | 商品2         | 数量2 | 价格2 | 商品3    | 数量3 | 价格3
  2. ----------------------------------------------------------------------------------------------------------------------------------
  3. 2  | 迪士尼    | 毛绒玩偶         | 1      | 30     | 唐老鸭帽子    | 3      | 15     | 门票     | 2     | 100
  4. 5  | 环球影城 | 钢铁侠模型     | 1      | 100    | 蝙蝠侠模型 | 1      | 90     | null   | null  | null

原因是：我需要对结果进行分页 - 每页显示10个购买摘要。如果每个摘要返回1-3行，很难弄清楚下一页的结果。

英文:

I want to show a user of his purchase summary from each store, up to first 3 purchases from each store.
For simplicity sake, the number of purchases is not configurable. So, it is not the first N, but the first 3

Let say I have these tables with these columns

  1. Store
  2. 	id
  3. 	name
  5. Product
  6. 	id
  7. 	name
  8. 	storeId
  9. 	price
  11. Purchase
  12. 	id
  13. 	userId
  14. 	productId
  15. 	count
  16. 	purchaseTime

This is what I want to display

  1. -----------------------------------------------
  2. | store     | product         | count | price |
  3. -----------------------------------------------
  4. | disney    | stitch doll     | 1     | 30  |
  5. |           | donald cap      | 3     | 15  |
  6. |           | ticket          | 2     | 100 |
  7. -----------------------------------------------
  8. | universal | iron man figure | 1     | 100 |
  9. |           | batman figure   | 1     | 90  |
  10. -----------------------------------------------

Preferably, if the user purchases donald caps on 2 separate purchases (assumed that price will never change), the number of purchases will be combined

for example: if the user purchases 2 donald caps and then purchase 1 cap, the result will be 3 donald caps - instead of 2 donald caps & 1 donald cap

I am using hibernate and postgresql

I don't even know where to start, in particular how to limit each store purchases to 3 for each store

Any hint, psudo solution or solutions will be appreciated

thanks

Note:
Unfortunately, there is a major oversight on my part.
I think I want to get each summary in 1 line

For example

  1. id | Store     | Product1        | count1 | price1 | Product2      | count2 | price2 | Product3 | count3 | price3
  2. ----------------------------------------------------------------------------------------------------------------------------------
  3. 2  | Disney    | stitch doll     | 1      | 30     | donald cap    | 3      | 15     | ticket    | 2     | 100
  4. 5  | Universal | iron man figure | 1      | 100    | batman figure | 1      | 90     | null      | null  | null

The reason is: I need to paginate the result - displaying 10 purchase summaries per page
If each summary returns 1-3 rows, it is hard to figure out the result of the next page

答案1

得分: 1

你可以使用侧连接（lateral join）来检索每个商店的前3笔购买记录：

  1. select s.name, p.*
  2. from store s
  3. cross join lateral (
  4.     select pr.name as product, pu.count, pr.price
  5.     from purchase pu
  6.     inner join product pr on pr.id = pu.productid
  7.     where pr.storeid = s.id and pu.userid = ?
  8.     order by pu.purchasetime
  9.     limit 3
  10. ) p

或者，你也可以使用 row_number() 函数：

  1. select *
  2. from (
  3.     select pr.name as product, pu.count, pr.price,
  4.         row_number() over(partition by s.id order by pu.purchasetime) rn
  5.     from purchase pu
  6.     inner join product pr on pr.id = pu.productid
  7.     inner join store s on s.id = pr.storeid 
  8.     where pu.userid = ?
  9. ) t
  10. where rn <= 3
英文:

You could use a lateral join to retrieve the first 3 purchases of each store:

  1. select s.name, p.*
  2. from store s
  3. cross join lateral (
  4. 	select pr.name as product, pu.count, pr.price
  5. 	from purchase pu
  6. 	inner join product pr on pr.id = pu.productid
  7. 	where pr.storeid = s.id and pu.userid = ?
  8. 	order by pu.purchasetime
  9. 	limit 3
  10. ) p

Alternatively, you can use row_number():

  1. select *
  2. from (
  3. 	select pr.name as product, pu.count, pr.price,
  4. 		row_number() over(partition by s.id order by pu.purchasetime) rn
  5. 	from purchase pu
  6. 	inner join product pr on pr.id = pu.productid
  7. 	inner join store s on s.id = pr.storeid 
  8. 	where pu.userid = ?
  9. ) t
  10. where rn &lt;= 3

huangapple
  • 本文由 发表于 2020年9月3日 18:41:07
  • 转载请务必保留本文链接：https://go.coder-hub.com/63721941.html
  • datetime
  • greatest-n-per-group
  • hibernate
  • java
  • postgresql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定