如何在fulfillment_summary中找到SQLite。如何找到订单占总订单的百分比
如何在fulfillment_summary中找到SQLite。如何找到订单占总订单的百分比
提问于 2022-10-31 18:57:13
这是我在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 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我意识到这是我的案子的问题
，
- ，Orders.order_id很好，因为计数返回每个仓库的订单数。-Yet总计无法运行“从Warehouse_Orders Orders中选择计数(*)”
我怎么才能找到总数？
我尝试过，将SQL查询分离开来，尝试不同的方法，但我很难返回总数。
回答 2
Stack Overflow用户
发布于 2022-10-31 19:37:00
您应该尽量避免多次运行相同的查询。对于更复杂的分析，您通常需要分步骤考虑:获取此结果集并查询下一步。您可以使用派生表来完成此操作。
还请注意，在判断和比较另一个小数之前，您应该将整数转换为十进制数。对你有用吗？
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
) totalsStack Overflow用户
发布于 2022-11-01 11:24:25
更新版本：
所以在网上搜索了很多之后，我意识到在使用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 * ..。对于每一份订单的计数。最终让百分比发挥作用。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/74267975
复制相关文章
相似问题
腾讯云开发者
