mysql - 使用聚合函数 MYSQL 查找百分比

Question

所以我有 3 个表 properties、property_plots 和 orders。每个属性将有许多地 block ，每个地 block 的大小可以为 no_sqyds。现在用户可以购买数量为 no_sqyds 的地 block ，我将其存储在 orders 表中。

属性

property_plots

订单

所以我的问题是如何找到特定地 block 的购买百分比？另外，如何找到使用相同的整个属性(property)的购买百分比？

到目前为止，我尝试的是使用这个粗略查询在地 block 级别找到百分比

((SELECT sum(o.no_sqyds) FROM orders as o 
WHERE o.plot_id = pp.id)*100)/pp.no_sqyds FROM property_plots as pp

基于表 orders 表，我可以获得每个地 block 的百分比，但我也通过结合以下数据来查看属性级别。 (我必须得到所有地 block 百分比的平均值才能在属性级别找到？)

plot_id 1 = 100% purchase

plot_id 2 = 66.67% purchase

plot_id 3 = 50%

示例数据库 - https://pastebin.com/RYJwwRqJ

Answer 1

将它们加入并分组并计算。

SELECT 
  property_id
, prop.name AS property_name
, (SUM(order_no_sqyds)/SUM(plot_no_sqyds))*100 AS percentage
, SUM(plot_no_sqyds) AS plot_no_sqyds
, SUM(order_no_sqyds) AS order_no_sqyds
, COUNT(plot_id) AS total_plots
, SUM(total_orders) AS total_orders
FROM
(
    SELECT 
      plot.property_id
    , plot.id AS plot_id
    , plot.no_sqyds AS plot_no_sqyds
    , SUM(ordr.no_sqyds) AS order_no_sqyds
    , COUNT(DISTINCT ordr.id) AS total_orders
    FROM property_plots AS plot
    LEFT JOIN orders AS ordr
      ON ordr.plot_id = plot.id
     AND ordr.property_id = plot.property_id
    GROUP BY 
      plot.property_id
    , plot.id
    , plot.no_sqyds
) q
INNER JOIN properties AS prop
   ON prop.id = q.property_id
GROUP BY property_id, prop.name
ORDER BY property_id

<表类="s-表"><头>property_id属性名称百分比plot_no_sqydsorder_no_sqydstotal_plots总订单数<正文>1湖景公园66.666722515034

db<>fiddle 上的演示 here

另一种计算它的方法是加入订单的聚合。

SELECT 
  plot.property_id
, prop.name AS property_name
, (SUM(ordr.no_sqyds)/SUM(plot.no_sqyds))*100 AS percentage
, SUM(plot.no_sqyds) AS plot_no_sqyds
, SUM(ordr.no_sqyds) AS order_no_sqyds
, COUNT(DISTINCT plot.id) AS total_plots
, SUM(total_orders) AS total_orders
FROM property_plots AS plot
INNER JOIN properties AS prop
   ON prop.id = plot.property_id
LEFT JOIN (
   SELECT plot_id, property_id
   , SUM(no_sqyds) AS no_sqyds
   , COUNT(DISTINCT id) AS total_orders
   FROM orders
   GROUP BY plot_id, property_id
) AS ordr
  ON ordr.plot_id = plot.id
 AND ordr.property_id = plot.property_id
GROUP BY 
  plot.property_id
, prop.name

仅绘图

SELECT 
  plot.property_id
, plot.id AS plot_id
, (SUM(ordr.no_sqyds)/plot.no_sqyds)*100 AS percentage
, plot.no_sqyds AS plot_no_sqyds
, SUM(ordr.no_sqyds) AS order_no_sqyds
, COUNT(DISTINCT ordr.id) AS total_orders
FROM property_plots AS plot
LEFT JOIN orders AS ordr
  ON ordr.plot_id = plot.id
 AND ordr.property_id = plot.property_id
GROUP BY 
  plot.property_id
, plot.id
, plot.no_sqyds