时间戳

首页 > 解决方案 > 向工作查询添加额外的 WHERE 子句以进一步按百分比排序

postgresql - 向工作查询添加额外的 WHERE 子句以进一步按百分比排序

问题描述

需要能够使用 STORE_LOCATION 上的 WHERE 过滤器填充 PERCENT_OF_TOTAL_ORDER 列。

关于解析数据的第一个问题，我得到了一些很好的答案，并在这里了解了如何更好地描述我的请求：在 Postgres 中，我如何在同一个查询中进行 COUNT 和 WHERE，然后对结果进行数学运算？

所以请求的第二部分是现在能够PERCENT_OF_TOTAL_ORDER使用字段中的数据将数据写回列STORE_LOCATION，基于PERCENT_OF_TOTAL_ORDER存在。注意：商店位置仅为north或south。

数据库的当前状态如下所示： http: //prntscr.com/m3ktu4。该列中有几个<NULL>条目PERCENT_OF_TOTAL_ORDER，而有些已填写。这就是我要填充的列。所以我想我SELECT想WHERE对SET.PERCENT_OF_TOTAL

这个实际的表目前有约 500k+ 行，有约 50k 不同ORDER_NUMBER的 s，因此寻找最有效的代码来更新当前数据，然后计划将代码设置为 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;

我尝试WHERE在解决方案 2 中添加一个子句：

    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_already 的建议（谢谢），这是现在看起来的数据库：

    +-------+---------------+--------------+--------------+-------------+------------------------+----------------+
    | 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          |
    +-------+---------------+--------------+--------------+-----------        ---+------------------------+----------------+

标签： postgresql

解决方案

我知道了！所以我想我会在这里分享它！

    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          |
+-------+---------------+--------------+--------------+--------------+------------------------+----------------+

谢谢大家的提示！

推荐阅读