具有多个表格连接的每组最多N组 [英] Top N Per Group with Multiple Table Joins

问题描述

基于我的研究，这是一个非常普遍的问题，通常有一个相当简单的解决方案。我的任务是将来自获取所有结果的几个查询更改为每组最多3个。起初，这一切进展顺利，我使用了本网站的几条建议和答案来实现这一目标（查看次数最多的产品）。但是，由于多次加入，我遇到了最后一个最畅销产品的困难。

基本上，我需要按每个产品的最高销售额排列所有产品，其中每个供应商的最高产品数量为3 有多个表被加入来创建原始查询，并且每次我尝试使用这些变量来生成排名时，都会产生无效结果。以下应该有助于更好地理解问题（为了简洁起见，我已经删除了不必要的字段）：

产品表

  productid | vendorid |批准|活动|删除
  

供应商表格

  vendorid |批准|活动|删除
  

订单表

  orderid | `状态`|已删除
  

订单商品表

  orderitemid | orderid | productid |价格
  

现在，我对获取所有结果的原始查询如下所示：

  SELECT COUNT（oi.price）AS`NumSales`，
 p.productid，
 p。 vendorid 
 FROM产品p 
 INNER JOIN供应商v ON（p.vendorid = v.vendorid）
 INNER JOIN orders_items oi ON（p.productid = oi.productid）
 INNER JOIN命令o ON（oi.orderid = o.orderid）
 WHERE（p.Approved = 1 AND p.Active = 1 AND p.Deleted = 0）
 AND（v.Approved = 1 AND v。 Active = 1 AND v.Deleted = 0）
 AND o.`Status` ='SETTLED'
 AND o.Deleted = 0 
 GROUP BY oi.productid 
 ORDER BY COUNT （oi.price）DESC 
 LIMIT 100; 
  

最后，（这里是我被困住的地方），我试图改变上面的语句这样，我只收到每个供应商的前3名产品（通过销售＃）。我会补充到目前为止我所拥有的，但是我很尴尬地这样做，这个问题已经是一段文字了。我试过变量，但一直得到无效的结果。任何帮助将不胜感激。

解决方案

即使您指定了LIMIT 100，这种类型的查询也需要完整扫描和表然后每个记录检查和排列编号，最后筛选出你想要显示的100个。

  select 
 vendorid，productid，NumSales 
 from 
（
选择
 vendorid，productid，NumSales，
 @r：= IF（@ g = vendorid，@ r +1,1）RowNum，
 @g：= vendorid $ b $ from（select @g：= null）initvars 
 CROSS JOIN 
（
 SELECT COUNT（oi。价格）AS NumSales，
 p.productid，
 p.vendorid 
 FROM产品p 
 INNER JOIN供应商v ON（p.vendorid = v.vendorid）
 INNER JOIN orders_items oi ON（p.productid = oi.productid）
 INNER JOIN命令o ON（oi.orderid = o.orderid）
 WHERE（p.Approved = 1 AND p.Active = 1 AND p.Deleted = 0）
 AND（v.Approved = 1 AND v.Active = 1 AND v.Deleted = 0）
 AND o.`Status` ='SETTLED'
 AND o .Deleted = 0 
 GROUP BY p.vendorid，p.productid 
 ORDER BY p.vendorid，NumSales DESC 
）T 
）U 
 Where RowNum <= 3 
 ORDER BY NUMSales DESC 
 LIMIT 100; 
  

这里的方法是

1. 按组获取NumSales


2. 使用变量对每个供应商/产品的销售额排序
   
3. 过滤编号数据集以允许每个供应商最多3个
   
4. 通过NumSales DESC订购剩余的零件，并仅返回100美元
   

Based on my research, this is a very common problem which generally has a fairly simple solution. My task is to alter several queries from get all results into get top 3 per group. At first this was going well and I used several recommendations and answers from this site to achieve this (Most Viewed Products). However, I'm running into difficulty with my last one "Best Selling Products" because of multiple joins.

Basically, I need to get all products in order by # highest sales per product in which the maximum products per vendor is 3 I've got multiple tables being joined to create the original query, and each time I attempt to use the variables to generate rankings it produces invalid results. The following should help better understand the issue (I've removed unnecessary fields for brevity):

Product Table

productid | vendorid | approved | active | deleted

Vendor Table

vendorid | approved | active | deleted

Order Table

orderid | `status` | deleted

Order Items Table

orderitemid | orderid | productid | price

Now, my original query to get all results is as follows:

SELECT COUNT(oi.price) AS `NumSales`, 
       p.productid, 
       p.vendorid
FROM products p
INNER JOIN vendors v ON (p.vendorid = v.vendorid)
INNER JOIN orders_items oi ON (p.productid = oi.productid)
INNER JOIN orders o ON (oi.orderid = o.orderid)
WHERE (p.Approved = 1 AND p.Active = 1 AND p.Deleted = 0)
AND (v.Approved = 1 AND v.Active = 1 AND v.Deleted = 0)
AND o.`Status` = 'SETTLED'
AND o.Deleted = 0
GROUP BY oi.productid
ORDER BY COUNT(oi.price) DESC
LIMIT 100;

Finally, (and here's where I'm stumped), I'm trying to alter the above statement such that I received only the top 3 product (by # sold) per vendor. I'd add what I have so far, but I'm embarrassed to do so and this question is already a wall of text. I've tried variables but keep getting invalid results. Any help would be greatly appreciated.

解决方案

Even though you specify LIMIT 100, this type of query will require a full scan and table to be built up, then every record inspected and row numbered before finally filtering for the 100 that you want to display.

select
    vendorid, productid, NumSales
from
(
    select
        vendorid, productid, NumSales,
        @r := IF(@g=vendorid,@r+1,1) RowNum,
        @g := vendorid
    from (select @g:=null) initvars
    CROSS JOIN 
    (
        SELECT COUNT(oi.price) AS NumSales, 
               p.productid, 
               p.vendorid
        FROM products p
        INNER JOIN vendors v ON (p.vendorid = v.vendorid)
        INNER JOIN orders_items oi ON (p.productid = oi.productid)
        INNER JOIN orders o ON (oi.orderid = o.orderid)
        WHERE (p.Approved = 1 AND p.Active = 1 AND p.Deleted = 0)
        AND (v.Approved = 1 AND v.Active = 1 AND v.Deleted = 0)
        AND o.`Status` = 'SETTLED'
        AND o.Deleted = 0
        GROUP BY p.vendorid, p.productid
        ORDER BY p.vendorid, NumSales DESC
    ) T
) U
WHERE RowNum <= 3
ORDER BY NumSales DESC
LIMIT 100;

The approach here is

1. Group by to get NumSales
2. Use variables to row number the sales per vendor/product
3. Filter the numbered dataset to allow for a max of 3 per vendor
4. Order the remaining by NumSales DESC and return only 100

这篇关于具有多个表格连接的每组最多N组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！