如何在SQLite中找到fulfillment_summary.如何查找订单占总订单的百分比

Question

这是我在SQLite中的QUERY。目前正在使用谷歌数据分析证书。

SELECT
Warehouse.warehouse_id,
Warehouse.state || ': ' || Warehouse.warehouse_alias AS warehouse_name,
COUNT(Orders.order_id) AS number_of_orders,
(SELECT
COUNT(*)
FROM Warehouse_Orders Orders)
AS total_orders,
CASE
WHEN COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Warehouse_Orders Orders) <= 0.20
THEN 'Fulfilled 0-20% of Orders'
WHEN COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Warehouse_Orders Orders) > 0.20
AND COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Warehouse_Orders Orders) <= 0.60
THEN 'Fulfilled 21-60% of Orders'
ELSE 'Fulfilled more than 60% of Orders'
END AS fulfillment_summary
FROM Warehouse_Stats Warehouse
LEFT JOIN Warehouse_Orders Orders
ON Warehouse.warehouse_id = Orders.warehouse_id
GROUP BY
Warehouse.warehouse_id,
warehouse_name
HAVING
COUNT(Orders.order_id) >1

我意识到我的CASE 有问题

• Orders.order_id很好，因为COUNT返回每个仓库的订单数。-然而，总数并没有起作用；从仓库订单中选择计数(*(">

如何找到总数？

我试过，把我的SQL查询拆开，尝试不同的方法，但我很难返回总数。

Answer 1

您应该尽量避免多次运行同一查询。对于更复杂的分析，您通常需要分步骤思考：获取此结果集，并为下一步查询该结果集。可以使用派生表来执行此操作。

还要注意，在划分和比较为另一个十进制之前，您应该将整数转换为十进制数字。它对你有用吗？

SELECT *, 
CASE WHEN 
(CAST(total_orders as REAL)/CAST(number_of_orders as REAL)) <= 0.2
THEN 'Fulfilled 0-20% of Orders'
WHEN 
(CAST(total_orders as REAL)/CAST(number_of_orders as REAL)) BETWEEN 0.2 and 0.6
THEN 'Fulfilled 21-60% of Orders'
ELSE 'Fulfilled more than 60% of Orders'
END AS fulfillment_summary
FROM 
(
SELECT
Warehouse.warehouse_id,
Warehouse.state || ': ' || Warehouse.warehouse_alias AS warehouse_name,
COUNT(Orders.order_id) AS number_of_orders,
(SELECT COUNT(*) FROM Warehouse_Orders Orders) AS total_orders
FROM Warehouse_Stats Warehouse
LEFT JOIN Warehouse_Orders Orders ON Warehouse.warehouse_id = Orders.warehouse_id
GROUP BY Warehouse.warehouse_id, warehouse_name
HAVING COUNT(Orders.order_id) >1
) totals

Answer 2

更新版本：

因此，在网上搜索了很多之后，我意识到在使用SQLite时，在计算百分比之前，必须先转换数字。

这是对我有效的查询的最终结果

SELECT
Warehouse.warehouse_id,
Warehouse.state || ': ' || Warehouse.warehouse_alias AS warehouse_name,
COUNT(Orders.order_id) AS number_of_orders,
(SELECT
COUNT(*)
FROM Warehouse_Orders Orders)
AS total_orders,
CASE
WHEN 1.0 * COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Warehouse_Orders) <= 0.20
THEN 'Fulfilled 0-20% of Orders'
WHEN 1.0 * COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Warehouse_Orders) > 0.20
AND 1.0 * COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Warehouse_Orders) <= 0.60
THEN 'Fulfilled 21-60% of Orders'
ELSE 'Fulfilled more than 60% of Orders'
END AS fulfillment_summary
FROM Warehouse_Stats Warehouse
LEFT JOIN Warehouse_Orders Orders
ON Warehouse.warehouse_id = Orders.warehouse_id
GROUP BY
Warehouse.warehouse_id,
warehouse_name
HAVING
COUNT(Orders.order_id) >1

正如你所看到的，我输入了1.0*。。。对于单个订单的每个计数。这最终使百分比起作用。