需要能够使用STORE_LOCATION上的WHERE筛选器填充PERCENT_OF_TOTAL_ORDER列。
关于解析数据的第一个问题，我得到了一些很好的答案，并在这里学会了如何更好地描述我的请求：在Postgres中，我如何在同一个查询中计数和WHERE，然后对结果进行数学运算？
因此，请求的第二部分是现在能够使用STORE_LOCATION字段中的数据将数据写回PERCENT_OF_TOTAL_ORDER列，这是基于PERCENT_OF_TOTAL_ORDER。注：存储位置仅为north或south。
DB的当前状态如下所示：http://prntscr.com/m3ktu4。在PERCENT_OF_TOTAL_ORDER列中有几个<NULL>条目，而有些条目已经填充。这就是我想要填充的列。所以我想我想对SET和PERCENT_OF_TOTAL做一个SELECT语句和WHERE子句。
这个实际的表目前有大约500k行以上的行，有大约50k个不同的ORDER_NUMBERs，因此正在寻找最有效的代码来更新当前数据，然后计划将代码设置为CRON作业，以便每晚更新。
从上面提到的上一篇文章中，这两个解决方案都获得了%的正确率，但没有填充PERCENT_OF_TOTAL_ORDER列。
解决方案1:
SELECT selling_agent, order_number,
sum(sale_price_1) as sale_price_1,
sum(sale_price_2) as sale_price_2,
(sum(sale_price_1) + sum(sale_price_2)) * 100.00 / sum(sum(sale_price_1) + sum(sale_price_2)) over (partition by order_number) as orderPercentage
FROM sales_orders_test
GROUP by order_number, selling_agent
ORDER BY order_number, selling_agent
解决方案2:
SELECT s.*,
ROUND (100.00 * (s.sale_price_1 + s.sale_price_2) /
(SELECT
SUM(sale_price_1 + sale_price_2)
FROM sales_orders_test
WHERE order_number = s.order_Number), 2) percentage
FROM sales_orders_test s;
我试图在解决方案2上添加一个WHERE子句，其中包括：
UPDATE sales_orders_test
SET percent_of_total_order =
(
SELECT selling_agent, order_number,
sum(sale_price_1) as sale_price_1,
sum(sale_price_2) as sale_price_2,
(sum(sale_price_1) + sum(sale_price_2)) * 100.00 /
sum(sum(sale_price_1) + sum(sale_price_2)) over (partition by order_number) as orderPercentage
FROM sales_orders_test
GROUP by order_number, selling_agent
ORDER BY order_number, selling_agent
)
WHERE percent_of_total_order IS NULL
它给了我[42601] ERROR: subquery must return only one column
如何将基于STORE_LOCATION的百分比输入列中，以便根据此信息进行更新？
计划输出是一次填充一张表，然后每晚填充一次，最终结果如下：http://prntscr.com/m3l3fz
编辑：@used_by_everly的建议(谢谢)，这是DB现在的样子：
+-------+---------------+--------------+--------------+-------------+------------------------+----------------+
| pk_id | selling_agent | order_number | sale_price_1 | sale_price_2 | percent_of_total_order | store_location |
+-------+---------------+--------------+--------------+--------------+------------------------+----------------+
| 1 | jim | 123 | 1 | 2 | | south |
| 2 | steve | 123 | 1 | 3 | | south |
| 3 | carl | 123 | 1 | 4 | | north |
| 4 | carl | 456 | 1 | 5 | | north |
| 5 | steve | 456 | 1 | 5 | | north |
| 6 | jim | 456 | 1 | 6 | 36.84 | north |
| 7 | steve | 789 | 1 | 78 | | south |
| 8 | patty | 789 | 1 | 7 | | north |
| 9 | bob | 187 | 3 | 3 | 100 | south |
+-------+---------------+--------------+--------------+----------- ---+------------------------+----------------+
这是我想要的代码：
+-------+---------------+--------------+--------------+-------------+------------------------+----------------+
| pk_id | selling_agent | order_number | sale_price_1 | sale_price_2 | percent_of_total_order | store_location |
+-------+---------------+--------------+--------------+--------------+------------------------+----------------+
| 1 | jim | 123 | 1 | 2 | 42.86 | south |
| 2 | steve | 123 | 1 | 3 | 57.14 | south |
| 3 | carl | 123 | 1 | 4 | 100 | north |
| 4 | carl | 456 | 1 | 5 | 27.27 | north |
| 5 | steve | 456 | 1 | 5 | 40.91 | north |
| 6 | jim | 456 | 1 | 6 | 31.82 | north |
| 7 | steve | 789 | 1 | 78 | 100 | south |
| 8 | patty | 789 | 1 | 7 | 100 | north |
| 9 | bob | 187 | 3 | 3 | 100 | south |
+-------+---------------+--------------+--------------+----------- ---+------------------------+----------------+
我搞定了！所以我想在这里分享一下！
WITH perc_sales as (
SELECT pk_id,
selling_agent,
order_number,
store_location,
sum(sale_price_1) as Sale1,
sum(sale_price_2) as Sale2,
ROUND((sum(sale_price_1) + sum(sale_price_2)) * 100.00 /
GREATEST(sum(sum(sale_price_1) + sum(sale_price_2))
over (partition by order_number, store_location), 1), 2) as orderPercentage
FROM sales_orders_test
GROUP BY pk_id
)
UPDATE sales_orders_test AS PERC_UPDATE
SET percent_of_total_order = PS.orderPercentage
FROM perc_sales PS
WHERE PS.pk_id = PERC_UPDATE.pk_id;
这给了我：
+-------+---------------+--------------+--------------+--------------+------------------------+----------------+
| pk_id | selling_agent | order_number | sale_price_1 | sale_price_2 | percent_of_total_order | store_location |
+-------+---------------+--------------+--------------+--------------+------------------------+----------------+
| 1 | jim | 123 | 1 | 2 | 42.86 | south |
| 2 | steve | 123 | 1 | 3 | 57.14 | south |
| 3 | carl | 123 | 1 | 4 | 100 | north |
| 4 | carl | 456 | 1 | 5 | 27.27 | north |
| 5 | steve | 456 | 1 | 8 | 40.91 | north |
| 6 | jim | 456 | 1 | 6 | 31.82 | north |
| 7 | steve | 789 | 1 | 78 | 100 | south |
| 8 | patty | 789 | 1 | 7 | 100 | north |
| 9 | bob | 187 | 3 | 3 | 100 | south |
+-------+---------------+--------------+--------------+--------------+------------------------+----------------+
谢谢大家的建议！