我有下一个数据:
这里我已经计算了 conf_id 的总数.但也要计算整个分区的总数.例如:按协议计算每个订单的总 suma(不是订单中的货物,四舍五入略有不同)
Here I already calculated total for conf_id. But want also calculate total for whole partition. eg: Calculate total suma by agreement for each its order (not goods at order which are with slightly different rounding)
如何求和 737.38 和 1238.3?例如.组中只取一个号码
How to sum 737.38 and 1238.3? eg. take only one number among group
(我不能求和(item_suma),因为它会返回1975.67.注意将conf_suma作为中间步骤)
(I can not sum( item_suma ), because it will return 1975.67. Notice round for conf_suma as intermediate step)
UPD完整查询.在这里,我想为每个组计算四舍五入的 suma.然后我需要计算这些组的总 suma
UPD Full query. Here I want to calculate rounded suma for each group. Then I need to calculate total suma for those groups
SELECT app_period( '2021-02-01', '2021-03-01' ); WITH target_date AS ( SELECT '2021-02-01'::timestamptz ), target_order as ( SELECT tstzrange( '2021-01-01', '2021-02-01') as bill_range, o.* FROM ( SELECT * FROM "order_bt" WHERE sys_period @> sys_time() ) o WHERE FALSE OR o.agreement_id = 3385 and o.period_id = 10 ), USAGE AS ( SELECT ocd.*, o.agreement_id as agreement_id, o.id AS order_id, (dense_rank() over (PARTITION BY o.agreement_id ORDER BY o.id )) as zzzz_id, (dense_rank() over (PARTITION BY o.agreement_id, o.id ORDER BY (ocd.ic).consumed_period )) as conf_id, sum( ocd.item_suma ) OVER( PARTITION BY (ocd.o).agreement_id ) AS agreement_suma2, (sum( ocd.item_suma ) OVER( PARTITION BY (ocd.o).agreement_id, (ocd.o).id, (ocd.ic).consumed_period )) AS x_suma, (sum( ocd.item_cost ) OVER( PARTITION BY (ocd.o).agreement_id, (ocd.o).id, (ocd.ic).consumed_period )) AS x_cost, (sum( ocd.item_suma ) OVER( PARTITION BY (ocd.o).agreement_id, (ocd.o).id, (ocd.ic).consumed_period ))::numeric( 10, 2) AS conf_suma, (sum( ocd.item_cost ) OVER( PARTITION BY (ocd.o).agreement_id, (ocd.o).id, (ocd.ic).consumed_period ))::numeric( 10, 2) AS conf_cost, max((ocd.ic).consumed) OVER( PARTITION BY (ocd.o).agreement_id, (ocd.o).id, (ocd.ic).consumed_period ) AS consumed, (sum( ocd.item_suma ) OVER( PARTITION BY (ocd.o).agreement_id, (ocd.o).id )) AS order_suma2 FROM target_order o LEFT JOIN order_cost_details( o.bill_range ) ocd ON (ocd.o).id = o.id AND (ocd.ic).consumed_period && o.app_period ) SELECT *, (conf_suma/6) ::numeric( 10, 2 ) as group_nds, (SELECT sum(x) from (SELECT sum( DISTINCT conf_suma ) AS x FROM usage sub_u WHERE sub_u.agreement_id = usage.agreement_id GROUP BY agreement_id, order_id) t) as total_suma, (SELECT sum(x) from (SELECT (sum( DISTINCT conf_suma ) /6)::numeric( 10, 2 ) AS x FROM usage sub_u WHERE sub_u.agreement_id = usage.agreement_id GROUP BY agreement_id, order_id) t) as total_nds FROM USAGE WINDOW w AS ( PARTITION BY usage.agreement_id ROWS CURRENT ROW EXCLUDE TIES) ORDER BY order_id, conf_id我的老问题
推荐答案更好的方法 dbfiddle:
better approach dbfiddle:
更多推荐
如何使用窗口函数`sum(DISTINCT <column>)OVER()`?
发布评论