android - 如何在不使用 windows 功能的情况下计算运行总数

问题描述

Android SQLite 版本为 3.19，不支持 over 和 row_number() 等 windows 函数。我没有任何自动递增的列。我使用来自不同表的数据创建了视图。我的查看详情如下。

1 表示有货
 2 表示缺货
 3 表示重置计数并从给定数量开始。

 uniquekey 是每一行的唯一 ID

我想得到如下运行总数：

  uniquekey date ProductName uniqueKeyProduct InOutType 数量 runningTotal

 edfrgdctydkkc 2020-06-07 苹果 dheykdhr 1 10 10
 edfrgdctkduxc 2020-06-08 橙色 xdefrttk 1 20 20
 fdfrgdctydysc 2020-06-08 苹果 dheykdhr 2 5 5
 3dfgrtkvctyf 2020-06-08 苹果 dheykdhr 1 2 7
 ctgrteerylkdc 2020-06-09 橙色 xdefrttk 2 8 12
 edffjritydmnc 2020-06-10 橙色 xdefrttk 3 5 5
 kkdjdjrgdctyk 2020-06-10 苹果 dheykdhr 3 2 2
 egdhgdctyjjdc 2020-06-11 橙色 xdefrttk 1 20 25
 edfryrytymnbc 2020-06-15 苹果 dheykdhr 1 10 12
 fgeegdctydk3c 2020-06-18 苹果 dheykdhr 2 2 10
 hyidfhhhfd89c 2020-06-20 橙色 xdefrttk 2 8 17

标签： androidsqliteandroid-sqlite

我没有任何自动递增的列

假设您没有使用WITHOUT ROWID定义表，那么您仍然有一个等效列，即rowid列。

AUTOINCREMENT 是 rowid 列的别名，效率低，很少需要
您不妨阅读https://www.sqlite.org/rowidtable.html

我想得到如下运行总数：

由于日期列不能确定插入顺序（例如 2020-06-08 处理了 2 个 Apple 行），因此已使用rowid列（在这方面不是 100% 故障安全）。

您可能希望考虑将日期设为包含插入日期和时间的列。

实现上述目的的一种方法是使用TRIGGER。触发器由 INSERT UPDATE 或 DELETE 事件驱动。因此，每当插入一行时使用触发器然后设置运行总计是有意义的。

以下 TRIGGER 维护您提供的数据的运行总计。

 CREATE TRIGGER IF NOT EXISTS mytable_after_insert 
    AFTER INSERT ON mytable
    BEGIN
        UPDATE mytable SET runningtotal = 
             CASE
                /* in stock */
                WHEN new.inouttype = 1 THEN 
                    COALESCE(
                        (
                        SELECT mt1.runningtotal
                        FROM mytable AS mt1
                        WHERE mt1.rowid < new.rowid AND mt1.uniquekeyproduct = new.uniquekeyproduct
                        ORDER BY rowid DESC 
                        LIMIT 1
                        ),0
                    )
                    + new.quantity
                /* out stock */ 
                WHEN new.inouttype = 2 THEN
                    COALESCE(
                        (
                            SELECT mt2.runningtotal
                            FROM mytable AS mt2
                            WHERE mt2.rowid < new.rowid AND mt2.uniquekeyproduct = new.uniquekeyproduct
                            ORDER BY rowid DESC 
                            LIMIT 1
                        ),0
                    )
                    - new.quantity
                /* reset */
                WHEN new.inouttype = 3 THEN new.quantity
            END
            WHERE rowid = new.rowid
        ;
    END
;

请注意，表名mytable可能必须更改。也许触发器的名称也应该更改。

以下是用于测试上述内容的 SQL。它

放下触发器
丢表
创建表（这可能需要适当修改）
创建触发器
插入所有行（注意 runningtotal 列默认为 0）
查询显示 runningtotal 和 rowid 的结果表

DROP TRIGGER IF EXISTS mytable_after_insert;
DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable 
    (
        uniquekey TEXT PRIMARY KEY, 
        date TEXT, 
        productname TEXT, 
        uniquekeyproduct TEXT, 
        inouttype INTEGER, 
        quantity INTEGER, 
        runningtotal INTEGER DEFAULT 0
    )
;
 CREATE TRIGGER IF NOT EXISTS mytable_after_insert 
    AFTER INSERT ON mytable
    BEGIN
        UPDATE mytable SET runningtotal = 
             CASE
                /* in stock */
                WHEN new.inouttype = 1 THEN 
                    COALESCE(
                        (
                        SELECT mt1.runningtotal
                        FROM mytable AS mt1
                        WHERE mt1.rowid < new.rowid AND mt1.uniquekeyproduct = new.uniquekeyproduct
                        ORDER BY rowid DESC 
                        LIMIT 1
                        ),0
                    )
                    + new.quantity
                /* out stock */ 
                WHEN new.inouttype = 2 THEN
                    COALESCE(
                        (
                            SELECT mt2.runningtotal
                            FROM mytable AS mt2
                            WHERE mt2.rowid < new.rowid AND mt2.uniquekeyproduct = new.uniquekeyproduct
                            ORDER BY rowid DESC 
                            LIMIT 1
                        ),0
                    )
                    - new.quantity
                /* reset */
                WHEN new.inouttype = 3 THEN new.quantity
            END
            WHERE rowid = new.rowid
        ;
    END
;
INSERT INTO mytable (
    uniquekey,date,productname,uniquekeyproduct,inouttype,quantity /* running total not supplied so defaults to 0 */
    )
    VALUES 
        ('edfrgdctydkkc','2020-06-07','Apple','dheykdhr',1,10),
        ('edfrgdctkduxc','2020-06-08','Orange','xdefrttk',1,20),
        ('fdfrgdctydysc','2020-06-08','Apple','dheykdhr',2,5),
        ('3dfrgrtkvctyf','2020-06-08','Apple','dheykdhr',1,2),
        ('ctgrteerylkdc','2020-06-09','Orange','xdefrttk',2,8),
        ('edffjritydmnc','2020-06-10','Orange','xdefrttk',3,5),
        ('kkdjdjrgdctyk','2020-06-10','Apple','dheykdhr',3,2),
        ('egdhgdctyjjdc','2020-06-11','Orange','xdefrttk',1,20),
        ('edfryrytymnbc','2020-06-15','Apple','dheykdhr',1,10),
        ('fgeegdctydk3c','2020-06-18','Apple','dheykdhr',2,2),
        ('hyidfhhhfd89c','2020-06-20','Orange','xdefrttk',2,8)
;
SELECT rowid,* FROM mytable ORDER BY rowid ASC

结果是：-

请注意，如果遵循规范化规则，那么产品应该有另一个表，因此产品名称不会重复，所需要的只是 uniquekeyproduct。
- 例如，如果更改产品名称，则必须更新 mytable 表中的所有行，而如果有产品表，更改名称只需要更新 1 行，它会在查询 mytable 时反映出来（ProductName列将不再需要）。
COALESCE 用于转换插入产品的第一行时返回的空值，因为没有符合库存和库存操作选择标准的行。见https://www.sqlitetutorial.net/sqlite-functions/sqlite-coalesce/
CASE ..... END 用于对inouttype列进行条件处理。它有 3 个 WHEN 子句，每种类型（1,2 或 3）一个。见https://www.sqlite.org/lang_expr.html#case
在前两个 WHEN 子句中使用的子查询中，表分别被赋予了一个临时名称 mt1 和 mt2，使用AS来确保数据的来源没有歧义。
请注意，由于 3.19 已经很老了，上面已经在更新的版本上进行了测试，因此可能存在一些问题。如果遇到问题，您可能希望查看https://www.sqlite.org/changes.html 。

android - 如何在不使用 windows 功能的情况下计算运行总数

问题描述

解决方案

推荐阅读