文章/答案/技术大牛

发布

社区首页 >问答首页 >获取多列的和并计算每一行的比率

问获取多列的和并计算每一行的比率
EN

Stack Overflow用户

提问于 2013-02-06 02:34:43

回答 1查看 5.4K关注 0票数 1

下面是我的brand_of_items表的架构。为了简单起见，这里显示了两个列: id (主列和AI)，符号(varchar 50，唯一)

Table - brand_of_items
id   symbol
0    a
1    b
2    c
..   ..
10   j

下面是我的items_of_brand的架构。

Table - mainIndexQuantity
id  brand_of_items_id   vol  item_type  salefinalizeddate
0         1              5      0       2005-5-11
1         1              6      0       2004-5-11
2         1              7      0       2011-5-11
3         1              8      0       2011-5-12
4         1              9      0       2011-5-12
5         1              10     0       2011-5-11
6         1              5      1       2012-5-11
7         1              6      1       2012-5-11
8         1              7      1       2011-5-11
9         1              8      1       2010-5-12
10        1              9      1       2012-5-12
11        1              10     1       2005-5-12

mainIndexQuantity表brand_of_items_id列是指向brand_of_items (id)的外键。mainIndexQuantity表item_type列不是外键，它应该是外键。

这两种商品类型是:0=零售和1=批发。

我想要计算每一个each_brand_of_items表条目的项目类型(零售与批发)的比率。我们的目标是看看A品牌的商品是在零售还是批发上卖得更多。

**添加复杂性:我希望向mainIndexQuantity表中添加一个date列，并希望找出RetailVolume和WholesaleVolume之和的difference，并将结果按salefinalizeddate字段分组。

这有助于确定哪些商品在哪个季节卖出得更多，RetailVolume和WholeSaleVolume之和的(增量)差异将有助于选择最需要注意的项目。

mysql

sql

sum

pivot

回答 1

Stack Overflow用户

回答已采纳

发布于 2013-02-06 02:40:45

试试这个：

SELECT 
  b.id,
  b.symbol,
  IFNULL(SUM(m.item_type = 1), 0) / (COUNT(*) * 1.0) AS wholesaleRatio,
  IFNULL(SUM(m.item_type = 0), 0) / (COUNT(*) * 1.0) AS RetailRatio
FROM brand_of_items b
LEFT JOIN mainIndexQuantity m ON b.id = m.brand_of_items_id
GROUP BY b.id, 
         b.symbol;

SQL Fiddle Demo。

这将给你：

| ID | SYMBOL | WHOLESALERATIO | RETAILRATIO |
----------------------------------------------
|  0 |      a |              0 |           0 |
|  1 |      b |            0.5 |         0.5 |
|  2 |      c |              0 |           0 |
| 10 |      j |              0 |           0 |

假设：

wholesaleRatio是指将整个销售类型的项目计数到所有项目的计数。
RetailRatio是retail类型项的计数到所有项的计数。

如果这个定额是vol列的总和与总vol的总和，那么您可以这样做：

SELECT 
  b.id,
  b.symbol,
  SUM(CASE WHEN m.item_type = 1 THEN m.vol ELSE 0 END) / SUM(m.vol) AS wholesaleRatio,
  SUM(CASE WHEN m.item_type = 0 THEN m.vol ELSE 0 END) / SUM(m.vol) AS RetailRatio
FROM brand_of_items b
LEFT JOIN mainIndexQuantity m ON b.id = m.brand_of_items_id
GROUP BY b.id, 
         b.symbol;

注意：

我使用了LEFT JOIN，以便在结果集中得到不匹配的行，即那些没有条目的品牌项目-- MainIndexQuantity表。如果不想包含它们，请使用INNER JOIN。
使用1.0进行乘法以获得小数位数，如@JW所指出的那样。

更新1

要包含Total Volume、Retail Volume Sum和Wholesale Volume sum，请尝试如下：

SELECT 
  b.id,
  b.symbol,
  IFNULL(SUM(m.item_type = 1), 0) * 1.0 / COUNT(*)     AS wholesaleRatio,
  IFNULL(SUM(m.item_type = 0), 0) * 1.0 / COUNT(*)     AS RetailRatio,
  IFNULL(SUM(m.vol), 0)                                AS 'Total Volume',
  SUM(CASE WHEN m.item_type = 1 THEN m.vol ELSE 0 END) AS 'Retail Volume sum',
  SUM(CASE WHEN m.item_type = 1 THEN m.vol ELSE 0 END) AS 'Wholesale Volume sum'
FROM brand_of_items b
LEFT JOIN mainIndexQuantity m ON b.id = m.brand_of_items_id
GROUP BY b.id, 
         b.symbol;

Updated SQL Fiddle Demo。

这将给你：

| ID | SYMBOL | WHOLESALERATIO | RETAILRATIO | TOTAL VOLUME | RETAIL VOLUME SUM | WHOLESALE VOLUME SUM |
--------------------------------------------------------------------------------------------------------
|  0 |      a |              0 |           0 |            0 |                 0 |                    0 |
|  1 |      b |            0.5 |         0.5 |           90 |                45 |                   45 |
|  2 |      c |              0 |           0 |            0 |                 0 |                    0 |
| 10 |      j |              0 |           0 |            0 |                 0 |                    0 |

如果要按这些总计和和对结果集进行排序，将此查询放入子查询中，则可以这样做：

SELECT *
FROM
(
   SELECT 
      b.id,
      b.symbol,
      IFNULL(SUM(m.item_type = 1), 0) * 1.0 / COUNT(*)     AS wholesaleRatio,
      IFNULL(SUM(m.item_type = 0), 0) * 1.0 / COUNT(*)     AS RetailRatio,
      IFNULL(SUM(m.vol), 0)                                AS TotalVolume,
      SUM(CASE WHEN m.item_type = 1 THEN m.vol ELSE 0 END) AS RetailVolumeSum,
      SUM(CASE WHEN m.item_type = 1 THEN m.vol ELSE 0 END) AS WholesaleVolumeSum
    FROM brand_of_items b
    LEFT JOIN mainIndexQuantity m ON b.id = m.brand_of_items_id
    GROUP BY b.id, 
             b.symbol
) AS sub
ORDER BY RetailVolumeSum    DESC, 
         WholesaleVolumeSum DESC;

但你最后的要求还不清楚，你是在寻找那些有最高退休/批发比率和价值的商品，还是选择其中价值最高的产品？

对于后一项：

SELECT *
FROM
(
   SELECT 
      b.id,
      b.symbol,
      IFNULL(SUM(m.item_type = 1), 0) * 1.0 / COUNT(*)     AS wholesaleRatio,
      IFNULL(SUM(m.item_type = 0), 0) * 1.0 / COUNT(*)     AS RetailRatio,
      IFNULL(SUM(m.vol), 0)                                AS TotalVolume,
      SUM(CASE WHEN m.item_type = 1 THEN m.vol ELSE 0 END) AS RetailVolumeSum,
      SUM(CASE WHEN m.item_type = 1 THEN m.vol ELSE 0 END) AS WholesaleVolumeSum
    FROM brand_of_items b
    LEFT JOIN mainIndexQuantity m ON b.id = m.brand_of_items_id
    GROUP BY b.id, 
             b.symbol
) AS sub
ORDER BY RetailVolumeSum    DESC, 
         WholesaleVolumeSum DESC,
         TotalVolume        DESC
LIMIT 1;

更新2

要获得总销量最高的品牌，您可以这样做：

SELECT 
  b.id,
  b.symbol,
  IFNULL(SUM(m.item_type = 1), 0) * 1.0 / COUNT(*)     AS wholesaleRatio,
  IFNULL(SUM(m.item_type = 0), 0) * 1.0 / COUNT(*)     AS RetailRatio,
  IFNULL(SUM(m.vol), 0)                                AS TotalVolume,
  SUM(CASE WHEN m.item_type = 1 THEN m.vol ELSE 0 END) AS RetailVolumeSum,
  SUM(CASE WHEN m.item_type = 1 THEN m.vol ELSE 0 END) AS WholesaleVolumeSum
FROM brand_of_items b
LEFT JOIN mainIndexQuantity m ON b.id = m.brand_of_items_id
GROUP BY b.id, 
         b.symbol
HAVING SUM(m.vol) = (SELECT MAX(TotalVolume)
                     FROM
                     (
                       SELECT brand_of_items_id, SUM(vol) AS TotalVolume
                       FROM mainIndexQuantity
                       GROUP BY brand_of_items_id
                     ) t);

Like this。

注意：

这将给你的品牌有最高的总销量，如果你要寻找那些有最高的比率，你必须替换有子句，以得到最大的比率，而不是总量的最大值。
这将为您提供总容量最高的项，因此您可能期望拥有的项超过项，以防有多个项目的总容量最高，如在this updated fiddle demo中。在这种情况下，要只获得一个，就必须使用LIMIT返回一个。

票数 5

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/14720657

复制

相似问题

问获取多列的和并计算每一行的比率
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取多列的和并计算每一行的比率EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取多列的和并计算每一行的比率
EN