本文介绍了如何使用窗口函数`sum(DISTINCT <column>)OVER()`?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有下一个数据:

这里我已经计算了 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.381238.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:

  1. 在每个订单分配row_number:row_number() over (partition by agreement_id, order_id) as nrow
  2. 只取第一个 suma:filter nrow = 1
with data as (
  select * from (values
      ( 1, 1, 1, 1.0049 ), (2, 1,1,1.0049), ( 3, 1,1,1.0049 ) ,
      ( 4, 1, 2, 1.0049 ), (5, 1,2,1.0057),
      ( 6, 2, 1, 1.53 ), ( 7,2,1,2.18), ( 8,2,2,3.48 )
 ) t (id, agreement_id, order_id, suma)
),
intermediate as (select
 *,
 row_number() over (partition by agreement_id, order_id ) as nrow,
 (sum( suma ) over ( partition by agreement_id, order_id ))::numeric( 10, 2) as order_suma,
from data)

select
  *,
  sum( order_suma ) filter (where nrow = 1) over (partition by agreement_id)
from intermediate```

这篇关于如何使用窗口函数`sum(DISTINCT <column>)OVER()`?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!