Sqllite:随着时间的推移发现异常值 [英] Sqllite: finding abnormal values over time

问题描述

我有以下 sqllite 表:

I have the following sqllite table:

CREATE TABLE test (
    id INTEGER NOT NULL, 
    date TEXT, 
    account TEXT, 
    ........
    value TEXT, 
    .......
    PRIMARY KEY (id), 
    CONSTRAINT composite UNIQUE (date, account)
    )

我想在 2 个不同的日期查找值大于 0 的所有帐号.我在想:

I want to find all the account numbers where the value is greater than 0 on 2 separate dates . I'm thinking:

SELECT * from test WHERE value> 0 GROUP BY account

可能是一个开始，但我不知道如何评估组的大小

is probably a start, but I don't know how to evaluate the size of groups

推荐答案

表达此查询的一种方法是聚合具有大于零值的帐户，然后保留具有两个或多个不同日期的帐户:

One way to phrase this query is to aggregate over accounts having a greater than zero value, and then retain those accounts having two or more distinct dates:

SELECT
    account
FROM test
WHERE value > 0
GROUP BY account
HAVING COUNT(DISTINCT date) >= 2

我看到您的 value 列被声明为 TEXT.如果您想与此列进行数字比较，我认为这可能应该是一个整数.

I see that your value column is declared as TEXT. I think this should probably be an integer if you want to do numeric comparisons with this column.

这篇关于Sqllite:随着时间的推移发现异常值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！