swift - 当 SQLite Swift 中有条件时，如何计算列的总和？

Question

我想使用SQLite3 Swift做一个计算，当flag为0时累加值，当flag为1时从累加值中减去它们。

我也想只在Flag为0时获取累计值的平均值，如果为1则想获取之前的平均值。

我附上了图片。

Answer 1

如果您使用的是 Sqlite 3.25 或更高版本，窗口函数可以轻松获取 RsSum 结果。我花了一段时间才弄清楚 RsAvg 数字是如何计算的，但我也能够获得这些值:

CREATE TABLE input(no INTEGER PRIMARY KEY, flag INTEGER, amt NUMERIC);
INSERT INTO input(no, flag, amt)
VALUES (1, 0, 10), (2, 0, 20), (3, 0, 30), (4, 1, 10), (5, 0, 10), (6, 0, 20)
     , (7, 1, 30);

WITH
  sumcte AS (
   SELECT no, flag
        , sum(CASE WHEN flag = 0 THEN amt ELSE -amt END) OVER (ORDER BY no) AS RsSum
   FROM input)
, avgcte AS (
   SELECT no, flag, RsSum
        , cast(RsSum AS REAL) / count(no) FILTER (WHERE flag = 0)
                                          OVER (ORDER BY no) As RsAvg
   FROM sumcte)
SELECT RsSum
     , (CASE WHEN flag = 0 THEN RsAvg
             ELSE lag(RsAvg, 1) OVER (ORDER BY no) END) As RsAvg
FROM avgcte ORDER BY no;

产生

RsSum       RsAvg     
----------  ----------
10          10.0
30          15.0
60          20.0
50          20.0
60          15.0
80          16.0
50          16.0

(将 RsAvg 解释为该行的 RsSum 除以迄今为止看到的 flag 为 0 的行数是我可以做到的唯一方法让数字发挥作用。)

但是，当连续两行的 flag 为 1 时，这将会中断。如果我找到针对这种情况的解决方法，我会再次更新。