sqlite 获取上一个计算记录除以组- SQL

mkshixfv 于 2022-12-13 发布在 SQLite

关注(0)|答案(1)|浏览(143)

我正在努力构建两个计算列（名为balance和avg）。

name    seq side    price   qnt
groupA  1   B       30      100
groupA  2   B       36      200
groupA  3   S       23      300
groupA  4   B       30      100
groupA  5   B       54      400
                
groupB  1   B       70      300
groupB  2   B       84      300
groupB  3   B       74      600
groupB  4   S       90      100

2个新计算列的依据：

余额：每组的第一行（seq = 1）必须具有相同的值qnt。接下来的记录遵循以下公式（基于Excel的方案）：

if(side="B"; `previous balance record` + `qnt`; `previous balance record` - `qnt`)

avg：每组的第一行（seq = 1）必须具有相同的值price。接下来的记录遵循以下公式（基于Excel的方案）：

if(side="B"; ((`price` \* `qnt`) + (`previous balance record` \* `previous avg record`)) / (`qnt` + `previous balance record`); `previous avg record`)

数字示例（A组的第二行计算如下）：

--> balance: 100 + 200 = 300
--> avg: ((36 * 200) + (100 * 30)) / (200 + 100) = 34

我认为这个问题必须用CTE来解决，因为我需要以前的记录，每次都在计算中。
我不想聚合组-我的目标是显示每个记录。
最后，这是我期望的输出：

name    seq side    price   qnt     balance avg
groupA  1   B         30    100     100     30
groupA  2   B         36    200     300     34
groupA  3   S         23    300     0       34
groupA  4   B         30    100     100     30
groupA  5   B         54    400     500     49,2
                            
groupB  1   B         70    300     300     70
groupB  2   B         84    300     600     77
groupB  3   B         74    600     1200    75,5
groupB  4   S         90    100     1100    75,5

提前感谢您！
下面是我的dbfiddle测试：https://dbfiddle.uk/TSarc3Nl
我试着解释了部分编码（注解）以使事情更容易。

sqlite

来源：https://stackoverflow.com/questions/74764932/get-the-previous-calculated-record-divided-by-group-sql

1条答案

按热度按时间

fruv7luv1#

余额可以从累计和中导出（使用case表达式表示何时扣除而不是添加）。
然后递归部分只需要一个自己的case表达式。

WITH
  adjust_table AS
(
  SELECT
    *,
    SUM(
      CASE WHEN side='B'
           THEN qnt
           ELSE -qnt
      END
    ) 
      OVER (
        PARTITION BY name
            ORDER BY seq
      )
        AS balance
  FROM
    mytable
),
  recurse AS
(
  SELECT adjust_table.*, price AS avg FROM adjust_table WHERE seq = 1
 
  UNION ALL
  
  SELECT
    n.*,
    CASE WHEN n.side='B'
         THEN ((n.price * n.qnt * 1.0) + (s.balance * s.avg)) / (n.qnt + s.balance)
         ELSE s.avg
    END
      AS avg
  FROM
    adjust_table   n
  INNER JOIN
    recurse        s
      ON  n.seq  = s.seq + 1
      AND n.name = s.name
)
SELECT
  *
FROM
  recurse
ORDER BY
  name,
  seq

https://dbfiddle.uk/mWz945pG
虽然我不确定avg是要做什么，所以有可能我弄错了，或者它可能被简化为不需要递归。
注意：***切勿***使用,连接表。

***EDIT：***无递归版本

使用窗口函数来累计余额以及总支出。
然后，使用该函数启用另一个窗口函数，以累计销售“收回”了多少“支出”。
您的平均值是调整后的支出除以当前余额。

WITH
  accumulate AS
(
  SELECT
    *,
    SUM(
      CASE WHEN side='B' THEN qnt ELSE -qnt END
    ) 
      OVER (
        PARTITION BY name
            ORDER BY seq
      )
        AS balance,
    1.0
    *
    SUM(
      CASE WHEN side='B' THEN price * qnt END
    )
      OVER (
        PARTITION BY name
            ORDER BY seq
      )
        AS total_spend
  FROM
    mytable
)
SELECT
  *,
  (
    total_spend
    -
    SUM(
      CASE WHEN side='S'
           THEN qnt * total_spend / (balance+qnt)
           ELSE 0
      END
    )
      OVER (
        PARTITION BY name
            ORDER BY seq
      )
        -- cumulative sum for amount 'recouped' by sales
  ) 
  / NULLIF(balance, 0)
      AS avg
FROM
  accumulate

https://dbfiddle.uk/O0HEr556
注：我仍然不明白为什么你要用这种方式计算平均价格，但它符合你想要的结果/公式，没有递归。

赞(0）回复(0）举报 2022-12-13

我来回答

sqlite 获取上一个计算记录除以组- SQL

1条答案

相关问题

热门标签

最新问答