mysql - 尝试使用 mysql 确定前 70% 的购买

Question

我有一个场景，我试图计算购买的产品数量，其中产品购买总和 = MySQL 总购买量的 70%。

换句话说，有多少独特的产品代表了前 70% 的产品购买量。

考虑以下示例:

create table purchases
(id int not null auto_increment primary key,
`product`varchar(10),
`purchased` int);

insert into purchases values 
(1,'pen',1),(2,'pencil',5),(3,'pencil',5),
(4,'eraser',3),(5,'case',1),(6,'case',1),
(7,'pen',1),(8,'pen',1),(9,'pen',1),(10,'pen',1);

如您所见，我分别购买了 4 种独特产品 10 次。

总共购买了 20 件产品:10 支铅笔、5 支钢笔、3 block 橡皮和 2 个盒子。

我要确定的是需要多少产品才能占总购买量的 70%。所以在这种情况下，期望的结果是 2。

2 来自 10 支铅笔加上 5 支钢笔 = 15 = ~70% OF 20。

我创建了一个 sqlfiddle如果它有任何帮助。

如有任何帮助，我们将不胜感激。谢谢，皮特

Answer 1

用户变量和一些子查询可以帮助实现你想要的。

首先，你需要知道购买总额；那么您需要编写一个查询来累积购买并“标记”您想要的产品。像这样:

SQL Fiddle

MySQL 5.6 架构设置:

create table purchases (
  id int not null auto_increment primary key,
  `product`varchar(10),`purchased` int
);

insert into purchases 
values (1,'pen',1),(2,'pencil',5),(3,'pencil',5)
     , (4,'eraser',3),(5,'case',1),(6,'case',1)
     , (7,'pen',1),(8,'pen',1),(9,'pen',1)
     ,(10,'pen',1);

查询 1:

-- First, store "sum(purchased)" and the desired percentile into user variables
select sum(purchased), 0.7
into @total_purchases
   , @percentile
from purchases

Results :(无)

查询 2:

-- Test the query: This will "flag" the desired records
select p.*
     , @cummulative := @cummulative + sum_purchased as cummulative
     , @cummulative / @total_purchases as cummulative_p
     , @cummulative / @total_purchases <= @percentile as flag
from 
    (
        select @cummulative := 0
    ) as init,
    (
        select product, sum(purchased) as sum_purchased 
        from purchases 
        group by product 
        order by sum_purchased desc
    ) as p

Results :

| product | sum_purchased | cummulative | cummulative_p | flag |
|---------|---------------|-------------|---------------|------|
|  pencil |            10 |          10 |           0.5 |    1 |
|     pen |             5 |          15 |          0.75 |    0 |
|  eraser |             3 |          18 |           0.9 |    0 |
|    case |             2 |          20 |             1 |    0 |

查询 3:

-- Get the final result: Use the above query as a data source for your final result
select product, sum_purchased
from
  (
    select p.*
         , @cummulative := @cummulative + sum_purchased as cummulative
         , @cummulative / @total_purchases as cummulative_p
         , @cummulative / @total_purchases <= @percentile as flag
    from 
        (
            select @cummulative := 0
        ) as init,
        (
            select product, sum(purchased) as sum_purchased 
            from purchases 
            group by product 
            order by sum_purchased desc
        ) as p
  ) as a
where flag = 1

Results :

| product | sum_purchased |
|---------|---------------|
|  pencil |            10 |

---

---

另一种方式:

如果您想强制包含“打破”百分位数障碍的第一个产品，您可以尝试这种(类似的)方法:

SQL Fiddle

MySQL 5.6 架构设置:

create table purchases (
  id int not null auto_increment primary key,
  `product`varchar(10),`purchased` int
);

insert into purchases 
values (1,'pen',1),(2,'pencil',5),(3,'pencil',5)
     , (4,'eraser',3),(5,'case',1),(6,'case',1)
     , (7,'pen',1),(8,'pen',1),(9,'pen',1)
     ,(10,'pen',1);

查询 1:

-- First, store "sum(purchased)" and the desired percentile into user variables
select sum(purchased), 0.7
into @total_purchases
   , @percentile
from purchases

Results :(无)

查询 2:

-- Test the query: This will "flag" the desired records
select p.*
     , @cummulative := @cummulative + sum_purchased as cummulative
     , @cummulative / @total_purchases as cummulative_p
     , @flag as flag
     , @flag := case when @cummulative / @total_purchases > @percentile then 0 else 1 end as new_flag
from 
    (
        select @cummulative := 0
             , @flag := 1
    ) as init,
    (
        select product, sum(purchased) as sum_purchased 
        from purchases 
        group by product 
        order by sum_purchased desc
    ) as p

Results :

| product | sum_purchased | cummulative | cummulative_p | flag | new_flag |
|---------|---------------|-------------|---------------|------|----------|
|  pencil |            10 |          10 |           0.5 |    1 |        1 |
|     pen |             5 |          15 |          0.75 |    1 |        0 |
|  eraser |             3 |          18 |           0.9 |    0 |        0 |
|    case |             2 |          20 |             1 |    0 |        0 |

查询 3:

-- Get the final result: Use the above query as a data source for your final result
select product, sum_purchased
from
  (
    select p.*
         , @cummulative := @cummulative + sum_purchased as cummulative
         , @cummulative / @total_purchases as cummulative_p
         , @flag as flag
         , @flag := case when @cummulative / @total_purchases > @percentile then 0 else 1 end as new_flag
    from 
        (
            select @cummulative := 0
                 , @flag := 1
        ) as init,
        (
            select product, sum(purchased) as sum_purchased 
            from purchases 
            group by product 
            order by sum_purchased desc
        ) as p
  ) as a
where flag = 1

Results :

| product | sum_purchased |
|---------|---------------|
|  pencil |            10 |
|     pen |             5 |